How to Setup MySQL Master/Slave Replication ?

It's not usual to find an easy source on how to setup MySQL replication, I thought it might be useful at least for the beginners to write a direct and simple howto blog on setting up Master/Slave replication in MySQL using the classic method (binary log information). Check out my post GTID In Action for information about transaction-based replication using GTID.

Before going through the replication setup steps, I think it's better to explain first how Replication works in MySQL.

MySQL replication mainly consists of three-part process:

• The master server records all data changes to its binary logs (binary log events) and send it to the slave using a thread called (Binlog dump thread) once the slave connects to the master.
• The slave copies the binary log events sent by the master's binlog dump thread to its relay logs using a thread called (Slave I/O thread).
• The slave applies these changes from the relay logs to its data by replaying (executing) all events using a thread called (Slave SQL thread).

Now, lets go through the setup process which is divided into 3 main sections (assuming you have already installed MySQL on master and slave servers):

• Master's side configurations.
• Slave's side configurations.
• Replication checking and troubleshooting.

Master's side configuration:

• Add the following variables to the MySQL configuration file (my.cnf):

  [mysqld]
  server-id=1		## must be unique
  log-bin=mysql-bin
  binlog_format=ROW
  

• Restart MySQL so that configuration changes take place:

  shell> /etc/init.d/mysql restart
  

• Create a MySQL user to be used by the slave:

  SQL> GRANT REPLICATION SLAVE ON *.* TO 'slave_user_name'@'slave_ip' IDENTIFIED BY 's3cret';
  

• Take a full snapshot from the master's databases:

  shell> mysqldump -u root -p --all-databases --flush-privileges --single-transaction --master-data=2 --flush-logs --triggers --routines --events --hex-blob >/path/to/backupdir/full_backup-$TIMESTAMP.sql
  

  
  

  Note:

  If you have MyISAM tables you should omit the option --single-transaction while --master-data=2 will automatically turn on --lock-all-tables option which is required for MyISAM tables).
• After preparing the backup file transfer it to the slave server.
• If you are setting up fresh M/S servers (no data is there on the master) you don't have to backup the master databases and all what you need is to execute the following command on the master server just to know the master's coordinates which will be used later on the slave's:

  SQL>  SHOW MASTER STATUS\G
  *************************** 1. row ***************************
               File: mysql-bin.000275
           Position: 120
       Binlog_Do_DB: 
   Binlog_Ignore_DB: 
  Executed_Gtid_Set:
  

And that is it on the master's, let's do the slave's work.

Slave's side configuration:

• Add the following variables to the my.cnf file:

  [mysqld]
  server-id=2			## must be unique.
  relay_log=relay-log
  skip-slave-start		## useful to make any checks before starting the slave (this way, slave must be started manually after each mysql restart)	
  

• Restart MySQL so that configuration changes take place:

  shell> /etc/init.d/mysql restart
  

• Restore the full backup taken from the master (if performed):

  shell> mysql -u root -p < /path/to/backupdir/full_backup-$TIMESTAMP.sql.
  

• Get the master's coordinates information from the backup file:

  shell> head -n 50 /path/to/backupdir/full_backup-$TIMESTAMP.sql|grep "CHANGE MASTER TO"
  

  
  

  OR from the output of "SHOW MASTER STATUS;" in case of no backups taken (check the final step in the "Master's side configuration").

• Set the master information on the slave's:

  SQL> CHANGE MASTER TO
      -> MASTER_HOST='master_ip',
      -> MASTER_PORT=3306,
      -> MASTER_USER='slave_user_name',		## The user which was created on the master 
      -> MASTER_PASSWORD='s3cret',
      -> MASTER_LOG_FILE='mysql-bin.000275',	## taken from the above step
      -> MASTER_LOG_POS=120;			## taken from the above step
  

• Start replication:

  SQL> START SLAVE;
  

Replication checking and troubleshooting:

• Once the slave is started, check the replication status on the slave by the following command:

  SQL> SHOW SLAVE STATUS\G
  *************************** 1. row ***************************
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 127.0.0.1
                    Master_User: slave_user_name
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000275
            Read_Master_Log_Pos: 120
                 Relay_Log_File: mysql-relay.000001
                  Relay_Log_Pos: 283
          Relay_Master_Log_File: mysql-bin.000275
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
  		.
  		.
  		.
     	       Last_SQL_Error:
  		.
  		.
  		.
  

• If the Slave_IO_State= connecting .... then make sure that the slave user information is set correctly and there is no firewall restrictions between the two servers (master and slave) this could be checked by connecting to the master's MySQL from the salve server by the replication user (in this example, slave_user_name).
• If both Slave_IO_Running and Slave_SQL_Running = Yes, then the replication had been set up correctly.
• If the Slave_SQL_Running = No, check the value of Last_SQL_Error for more details about the SQL error.
• If you know that error and you want to ignore it, you can execute "SET GLOBAL sql_slave_skip_counter = 1;" on the slave and then start the slave again "START SLAVE;".
• To restrict all normal users from changing data on the slave - which might break the replication - the option "read-only" should be added in the slave's my.cnf file.
• the server option "server-id" must be unique among all servers inside the replication (masters and slaves).
• If your database size is big (100GB or so) Xtrabackup tool could be used instead of mysqldump - when preparing the master snapshot - for faster backup and restore operations. For more information on how to use Xtrabackup, check out this link.
• For more information on how to setup MySQL replication, check out the manual documentation.
• For more information on how to troubleshoot MySQL replication, check out the Replication Troubleshooting - Classic VS GTID blog.