WL#155: Online Backup: Auto-magic slave setup
Affects: Server-6.x — Status: On-Hold — Priority: Low
Slave should be able to set itself up with one command using the hot backup feature of the master. REFERENCE: - 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. SYNTAX - AMSU <Slave URL> <Master URL> <user name> 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: 0. AMSU checks that the master is alive and it can ftp a file from the master. 1. 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 privilege. See MySQL server documentation: Section 188.8.131.52, “Creating a User for Replication”. 2. 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 184.108.40.206, “Setting the Replication Master Configuration”. 3. 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 WL#4634 4. Update the configuration of the slave. See MySQL server documentation: Section 220.127.116.11, “Setting the Replication Slave Configuration”. 5. Start the slave, using the --skip-slave-start option so that replication does not start. 6. 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 required, see MySQL server documentation: Section 18.104.22.168, “Setting the Master Configuration on the Slave”. 7. Start the slave threads: mysql> START SLAVE;
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.