Asynchronous Replication

Asynchronous Replication

MySQL Clustering

Asynchronous Replication Objective Setup a master-slave relationship between four MySQL servers.

Pre-requisites 1. Install the necessary Operating Systems and install MySQL server on all of them. 2. All the Operating Systems should be in a network with every Operating System having its own IP address.

Assumptions 1. The master is on Windows Vista and the slaves are on – Windows Server 2003, Fedora 10 and OpenSolaris 2008.11. 2. The replication process is done only for the new data so existing data will not be synchronized.

Procedure 1. The primary pre-requisite for asynchronous replication to be established, is that all the Operating Systems should be connected in a network and every slave should be able to ping the master. If there is a problem in pinging, the fire-walls at the master and slave’s end should be either disabled or properly configured to allow the slaves to communicate with master and viceversa.

Asynchronous Replication

MySQL Clustering

2. Open C:\Program Files\MySQL\MySQL Server 5.1\my.ini using a text editor in Windows Vista (the master). In the [mysqld] section, add the following two lines – log-bin=mysql-bin server-id=1 In certain situations, Vista may not allow us to change the file. So copy the file somewhere, make the change sand replace the original one.

Asynchronous Replication

MySQL Clustering

3. Right Click Computer, select Manage and the “Services and Applications” tab, move to Services and restart the MySQL service in Vista.

Asynchronous Replication

MySQL Clustering

4. Open the MySQL client and type the following command to check the status of the master – show master status

Asynchronous Replication

MySQL Clustering

5. Every slave should have privilege on the master to read the replication logs. Grant the appropriate privilege using the following command for each of the slave using their IP addresses – grant replication slave on *.* to ‘user_name’@’IP address’ identified by ‘password’

Asynchronous Replication

MySQL Clustering

6. That is it on the masters’ side. 7. Moving to the slave side, open C:\Program Files\MySQL\MySQL Server 5.1\my.ini in Windows Server 2003 (a slave). Change the server-id in the [mysqld] section. Every system in the network needs to have a unique server-id. server-id=some_unique_number

Asynchronous Replication

MySQL Clustering

8. Open the MySQL client in the slave. Stop the slave if it is running. If it is not running, a warning will appear, ignore it and move ahead.

Asynchronous Replication

MySQL Clustering

9. Change the master using the following command and syntax and then start the slave – change master to master_host = ‘IP_address of master’, master_user = ‘user_name_given_in_grant’, master_password = ‘password_given_in_grant’, master_port = default is 3306 or the port specified in the master master_log_file = ‘log_file_name_in_show_master_status’, master_log_pos = position of log file in show master status;

Asynchronous Replication

MySQL Clustering

10. Use the following command to check the status of the slave (Both the I/O and SQL should be running) – show slave status\G;

Asynchronous Replication

MySQL Clustering

11. In Fedora 10 copy my-huge.cnf from the /usr/share/mysql directory to /etc/my.cnf. Open it with a text editor and change the server-id. Either restart the mysqld process or restart the system for the effect to be seen.

Asynchronous Replication

12. Stop the slave, change the master and start the slave again.

MySQL Clustering

Asynchronous Replication

MySQL Clustering

13. Check the slave status to see if everything went well.

14. In the OpenSolaris 2008.11 slave, copy my-huge.cnf from the /opt/mysql/mysql/support-files directory to any preferred directory.

Asynchronous Replication

15. Open it with a text editor and change the server-id.

MySQL Clustering

Asynchronous Replication

MySQL Clustering

16. Start mysqld_safe as the mysql user specifying the my.cnf as the defaults file – ./mysqld_safe –defaults-file=’path_to_my.cnf’

17. Stop the slave, change the master and start the slave again.

Asynchronous Replication

18. Check the slave status to see if everything went well.

MySQL Clustering

Asynchronous Replication

MySQL Clustering

19. To check the slaves, create a database and a few tables. Add data to them.

20. If everything went correctly, the same data would be available in the master as well as the other three slaves.

Asynchronous Replication

MySQL Clustering