Synchronous Replication

Synchronous Replication

MySQL Clustering

Synchronous Replication Objective Setup a cluster involving two MySQL servers. Connect two MySQL servers as slaves to one of the MySQL server in the cluster, thereby making it a master. The slaves are connected using Asynchronous replication.

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 cluster is setup using MySQL servers installed on – Windows Vista and Windows Server 2003. 2. The MySQL Server on Windows Vista acts as a master and the slaves are installed on – Fedora 10 and OpenSolaris 2008.11 3. The replication process is done only for the new data so existing data will not be synchronized.

Procedure 1. The primary pre-requisite for synchronous 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 vice-versa.

Synchronous Replication

MySQL Clustering

2. Create a directory in Windows Vista to place the configuration file for the cluster. The following steps use the C:\mysql\mysql-cluster directory. 3. MySQL Cluster consists of four major components – Management Servers, Management Clients, Data Nodes and MySQL Servers. Except Management Clients, all the others have to be declared in a configuration file. 4. Create a file named config.ini in the C:\mysql\mysql-cluster directory. There are four main sections – [ndbd default] NoOfReplicas =

 Indicates no. of replicas in the cluster. Optimum value is 2.

[ndb_mgmd]

 Declare the hostname and data directory of the management server here

[ndbd]

 Declare the hostname and data directory of the data node here

[mysqld]

 Declare the hostname of the MySQL server here

Synchronous Replication

MySQL Clustering

5. Open C:\Program Files\MySQL\MySQL Server 7.0\my.ini using a text editor. In the [mysqld] section, add the following two lines – ndbcluster ndb-connectstring = At the end of the file create three new sections and declare the connect strings and the path of the configuration file. [ndbd] connect-string = [ndb_mgm] connect-string = [ndb_mgmd] config-file = C:\mysql\mysql-cluster\config.ini 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.

Synchronous Replication

MySQL Clustering

Synchronous Replication

MySQL Clustering

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

7. Start the management server using the following command in command prompt – ndb_mgmd This application is present in the bin directory of the MySQL install path. Either run this from that directory, or include the directory in the system path.

Synchronous Replication

8. Start the data node using the following command in command prompt – ndbd

MySQL Clustering

Synchronous Replication

MySQL Clustering

9. Start the management client using the following command in command prompt – ndb_mgm

Synchronous Replication

MySQL Clustering

10. Though the MySQL Server is running on the system, it is not connected to the cluster as all the data nodes have not been started. 11. Moving to Windows 2003, open C:\Program Files\MySQL\MySQL Server 7.0\my.ini. In the [mysqld] section, add the following two lines – ndbcluster ndb-connectstring = At the end of the file create two new sections and declare the connect strings. [ndbd] connect-string = [ndb_mgm] connect-string =

Synchronous Replication

MySQL Clustering

Synchronous Replication

MySQL Clustering

12. Create an empty directory with the same path as mentioned as the data directory in config.ini of Vista.

13. Restart the MySQL service in Windows 2003.

Synchronous Replication

14. Start the data node using the following command in command prompt – ndbd

MySQL Clustering

Synchronous Replication

MySQL Clustering

15. Check the management client in Windows Vista to check if the data node has connected properly. Notice that the MySQL Servers now become a part of the cluster.

Synchronous Replication

MySQL Clustering

16. The cluster is now setup. 17. For connecting the slaves to the Windows Vista master, the procedure is similar to that of the Asynchronous replication procedure. 18. 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 ndb-log-update-as-write = 1

Synchronous Replication

MySQL Clustering

19. Restart the MySQL service. 20. Open the MySQL console client and check the master status to make sure the master is running.

Synchronous Replication

MySQL Clustering

21. 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’ 22. That’s it on the master’s side. 23. In Fedora 10 (the slave) copy my-huge.cnf from the /usr/share/mysql directory to /etc/my.cnf. Open it with a text editor and make the following changes slave-exec-mode = IDEMPOTENT server-id=some_unique_number

Synchronous Replication

MySQL Clustering

24. Either restart the mysqld process or restart the system for the effect to be seen. 25. Open the MySQL console client, stop the slave, change the master and restart the slave using the following commands in the MySQL console – stop 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; start slave;

Synchronous Replication

MySQL Clustering

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

Synchronous Replication

MySQL Clustering

27. Repeat the same procedure in the OpenSolaris 2008.11 slave. 28. In the OpenSolaris 2008.11 slave, copy my-huge.cnf from the /opt/mysql/mysql/support-files directory to any preferred directory. 29. Open it with a text editor and make the following changes slave-exec-mode = IDEMPOTENT server-id = some_unique_number

Synchronous Replication

MySQL Clustering

30. Open the MySQL console client, stop the slave, change the master and restart the slave.

Synchronous Replication

MySQL Clustering

31. Check the status of the slave. Both the I/O and SQL should be running.

32. Though both the slaves are now connected to the master, the replication would not work. This is because the MySQL cluster expects a table named ndb_apply_status in the mysql database. 33. Create the table ndb_apply_status in the mysql database using the following command in the MySQL console – create table ndb_apply_status ( server_id int(10) unsigned not null, epoch bigint(20) unsigned not null, log_name varchar(255) character set latin1 collate latin1_bin not null, start_pos bigint(20) unsigned not null, end_pos bigint(20) unsigned not null, primary key (server_id) using hash) engine=innodb default charset=latin1; 34. Create the above table in both the slaves.

Synchronous Replication

MySQL Clustering

Synchronous Replication

MySQL Clustering

35. One of the major drawbacks of connecting slaves asynchronously to a MySQL Cluster server is that table creation will create inconsistency problems between the master and the slave. This is because tables in a cluster are created using the NDB engine and on the slave the tables are created using either the MyISAM engine or the InnoDB engine. So before the replication is started, the required databases and tables should be setup. 36. Stop the slave; create the database and the tables.

Synchronous Replication

37. Create the databases and the tables in the MySQL cluster.

MySQL Clustering

Synchronous Replication 38. Change the master properties in the slaves and restart them.

MySQL Clustering

Synchronous Replication

MySQL Clustering

39. Insert data at the master MySQL server using the MySQL console.

40. If the entire setup was configured properly, the same data would be available in the cluster as well as the slaves.

Synchronous Replication

MySQL Clustering