We had a requirement to run mysql replication with one master and 2 slaves. By following normal procedure this is doable. Interestingly we got few points which may be useful to others when working with mysql replication. Following given the points that we had to deal with;
1. A slave with mysql higher version: One slave had mysql ver 5.5 while others are with mysql 5.1. In this case the mysql server 5.5 does not allow to configure mysql masters details in my.cnf where we need to give some important parameters of master server. Following blog page describes the way to give those parameters in the shell and configure the server to connect with the master server.
https://tapasmishra.wordpress.com/2012/06/11/how-to-configure-mysql-5-5-server-as-replication-slave/
Following setting was taken from the above post
http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o
According to the above post, when defining the master parameters, we need to give MASTER_LOG_FILE and MASTER_LOG_POS. These parameters can be observed by executing 'show master status' in the master server. After getting the log file name and the log position of the master server, the database in the master file should be copied to the slave server. Please note that it is very important to keep the same data in the databases in both master and slave servers when defining the master log positions. mysqldump command can be used to copy the master server's database to slave server.
We gave the master_log_file and master_log_pos parameters along with the master_host, master_port parameters with the CHANGE MASTER TO .. command.
After rectifying the above described issues, the replications were started correctly.
1. A slave with mysql higher version: One slave had mysql ver 5.5 while others are with mysql 5.1. In this case the mysql server 5.5 does not allow to configure mysql masters details in my.cnf where we need to give some important parameters of master server. Following blog page describes the way to give those parameters in the shell and configure the server to connect with the master server.
https://tapasmishra.wordpress.com/2012/06/11/how-to-configure-mysql-5-5-server-as-replication-slave/
Following setting was taken from the above post
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.2.101′,2. After configuring the master parameters in the slave server as above, we encountered an interesting issue in the synchronization. The issue was identified referring /var/log/mysqld.log and also from 'show slave status' in the shell. The issue was the slave replication SQL thread aborted due to non executable sql commands in master log. Referring below post;
MASTER_PORT=3306,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’PASSWORD';
http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o
According to the above post, when defining the master parameters, we need to give MASTER_LOG_FILE and MASTER_LOG_POS. These parameters can be observed by executing 'show master status' in the master server. After getting the log file name and the log position of the master server, the database in the master file should be copied to the slave server. Please note that it is very important to keep the same data in the databases in both master and slave servers when defining the master log positions. mysqldump command can be used to copy the master server's database to slave server.
We gave the master_log_file and master_log_pos parameters along with the master_host, master_port parameters with the CHANGE MASTER TO .. command.
After rectifying the above described issues, the replications were started correctly.
