Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




16.7.5. Preparing the Cluster for Replication

Preparing the MySQL Cluster for replication consists of the following steps:

  1. Check all MySQL servers for version compatibility (see Section 16.7.2, “Assumptions and General Requirements”).

  2. Create a slave account on the master Cluster with the appropriate privileges:

         ->    ON *.* TO 'slave_user'@'slave_host'
         ->    IDENTIFIED BY 'slave_password';

    where slave_user is the slave account username, slave_host is the hostname or IP address of the replication slave, and slave_password is the password to assign to this account.

    For example, to create a slave user account with the name “myslave,” logging in from the host named “rep-slave,” and using the password “53cr37,” use the following GRANT statement:

         -> ON *.* TO 'myslave'@'rep-slave'
         -> IDENTIFIED BY '53cr37';

    For security reasons, it is preferable to use a unique user account — not employed for any other purpose — for the replication slave account.

  3. Configure the slave to use the master. Using the MySQL Monitor, this can be accomplished with the CHANGE MASTER TO statement:

         -> MASTER_HOST='master_host',
         -> MASTER_PORT=master_port,
         -> MASTER_USER='slave_user',
         -> MASTER_PASSWORD='slave_password';

    where master_host is the hostname or IP address of the replication master, master_port is the port for the slave to use for connecting to the master, slave_user is the username set up for the slave on the master, and slave_password is the password set for that user account in the previous step.

    For example, to tell the slave to replicate from the MySQL server whose hostname is “rep-master,” using the replication slave account created in the previous step, use the following statement:

         -> MASTER_HOST='rep-master'
         -> MASTER_PORT=3306,
         -> MASTER_USER='myslave'
         -> MASTER_PASSWORD='53cr37';

    (For a complete list of clauses that can be used with this statement, see Section, “CHANGE MASTER TO Syntax”.)

    You can also configure the slave to use the master by setting the corresponding startup options in the slave server's my.cnf file. To configure the slave in the same way as the preceding example CHANGE MASTER TO statement, the following information would need to be included in the slave's my.cnf file:


    See Section 6.9, “Replication Startup Options”, for additional options that can be set in my.cnf for replication slaves.

    Note: To provide replication backup capability, you will also need to add an ndb-connectstring option to the slave's my.cnf file prior to starting the replication process. See Section 16.7.9, “MySQL Cluster Backups With Replication”, for details.

  4. If the master cluster is already in use, you can create a backup of the master and load this onto the slave to cut down on the amount of time required for the slave to synchronize itself with the master. If the slave is also running MySQL Cluster, this can be accomplished using the backup and restore procedure described in Section 16.7.9, “MySQL Cluster Backups With Replication”.


    In the event that you are not using MySQL Cluster on the replication slave, you can create a backup with this command on the replication master:

    shellM> mysqldump --master-data=1

    Then import the resulting data dump onto the slave by copying the dump file over to the slave. After this, you can use the mysql client to import the data from the dumpfile into the slave database as shown here, where dump_file is the name of the file that was generated using mysqldump on the master, and db_name is the name of the database to be replicated:

    shellS> mysql -u root -p db_name < dump_file

    For a complete list of options to use with mysqldump, see Section 8.10, “mysqldump — A Database Backup Program”.

    Note that if you copy the data to the slave in this fashion, you should make sure that the slave is started with the --skip-slave-start option on the command line, or else include skip-slave-start in the slave's my.cnf file to keep it from trying to connect to the master to begin replicating before all the data has been loaded. Once the loading of data has completed, follow the additional steps outlined in the next two sections.

  5. Ensure that each MySQL server acting as a replication master is configured with a unique server ID, and with binary logging enabled, using the row format. (See Section 6.3, “Row-Based Replication”.) These options can be set either in the master server's my.cnf file, or on the command line when starting the master mysqld process. See Section 16.7.6, “Starting Replication (Single Replication Channel)”, for information regarding the latter option.

  Published under the terms of the GNU General Public License Design by Interspire