WL#155: Online Backup: Auto-magic slave setup

Affects: Server-6.x   —   Status: On-Hold

Slave should be able to set itself up with one command using the hot backup 
feature of the master.

- WL#4634 PCD: Backup everything that mysqldump can backup
 Business Requirement
Currently Replication requires many manual steps to be set-up.
see "Setting Up Replication with Existing Data" in the MySQL Manual.
With Online backup being implemented, the most obvious coordination
between replication and backup that comes to mind is the automatic setup of a
replication slave.
From the business point of view also both the replication and backup are
requirements of
users who have critical data and do not want to have downtime.
There are 2 typical replication scenarios
1. A replication slave is used in cases where there is a possibility of a
failure, and the customer does not expect any interruption of service.
2. Replication is used as a Load balancing strategy, where more slaves are
brought online when the user requirements increase.

In both the above scenarios, there is a need to setup backup slaves
automatically where there is need
to migrate the data from the master to the slave.
It will greatly add to the "Ease of Use" if this Replication Slave setup could
be done automatically.
The current setup involves usage of mysqldump to ship the current data from
master to slave.
Using mysqldump will disallow writes while the dump is taking place.
It is also not an elegant implementation for slave setup.
One of the primary requirements of online backup is the replacement of mysqldump.
In the Replication scenario replacing mysqldump can be extended to mean enabling
the automatic slave setup.


It is assumed that the AMSU runs from the slave machine. The user which runs the
AMSU on the slave has the super user rights on the slave. This user also should
be able to FTP from the master and change/check configuration on the master.
 Steps that the AMSU needs to take:

AMSU checks that the master is alive and it can ftp a file from the master.

The AMSU tries to connect to the Master with the user name to check if the user
is setup on master.
If this fails, the Master, With the MySQL master running; needs to create a user
to be used by the slave when connecting to the master during replication. This
may be automatically done from Slave by connecting to the master as a client
that has the CREATE USER privilege.This will need an optional parameter, the
username and password of a known user on the master that has the CREATE USER

See MySQL server documentation: 
Section, “Creating a User for Replication”.

AMSU to Check on the master that if the server-id is already configured and
binary logging is enabled on the master server.
There are three sub-tasks here:

 (T1) Check if server_id is set and check if server is configured for
binlogging. This can be done in a client connection to the master, like "SELECT
@@global.server_id, @@global.sql_log_bin". This requries SELECT privileges on
master, nothing else.

 (T2) If the server_id is wrong, the script can change it dynamically by "SET
@@global.server_id = X". This requires SUPER privilege on master.

 (T3) If log_bin is wrong, the AMSU will fail since this needs a  restart of the
master server.

See MySQL server documentation: 
Section, “Setting the Replication Master Configuration”.

Take a Full backup (The user information needs to be a part of online backup).
FTP the backup to the slave. (this could be via FTP). This is a dependency on

Update the configuration of the slave. 
See MySQL server documentation:
Section, “Setting the Replication Slave Configuration”.

Start the slave, using the --skip-slave-start option so that replication does
not start.

Configure the slave with the replication coordinates from the master. This tells
the slave the binary log file and position within the file where replication
needs to start. Also, configure the slave with the login credentials and host
name of the master. For more information on the CHANGE MASTER TO statement
see MySQL server documentation:
Section, “Setting the Master Configuration on the Slave”.

Start the slave threads: