As Previous document(http://conheotiensinh.blogspot.com/2009/09/cluster-linux-mail-server.html) I showed you how to do replicate mysql.In Document I will intro to you how to do setup multi master replicate mysql
II/SETUP
Step 1: Install 3 Databases with info:
Server 1:192.168.124.128
Server 2:192.168.124.136
Server 3:192.168.124.137(root Cluster)
Step 2:Install some packages all servers
[root@centos ~] yum install gcc44 gcc44-c++ rsync -y
Step 2:Install some packages all servers
[root@centos ~] yum install gcc44 gcc44-c++ rsync -y
Step 3:Lets check what kind of MySQL applications installed in the box and Remove it
[root@centos ~] rpm -qa | grep mysql
[root@centos ~] rpm -qa | grep mysql
Step 4:Following files should exist if you download the correct version:
galera-23.2.2-1.rhel5.i386.rpm
MySQL-client-5.5.28-1.rhel5.i386.rpm
MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
MySQL-shared-5.5.28-1.rhel5.i386.rpm
Step 5:Now, we need to install all files based on following sequence:
[root@centos galera] rpm -UVh galera-23.2.2-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-client-5.5.28-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-shared-5.5.28-1.rhel5.i386.rpm
Step 6:Lets reset/create MySQL root password. In this example we will use simple root password as above, but I recommend you to use strong password:
[root@centos ~] mysqladmin -u root password 'root#@pass'
Step 7:To ease up MySQL administration, we will need to create /root/.my.cnf files and put following contents. This will automate your login as client:
galera-23.2.2-1.rhel5.i386.rpm
MySQL-client-5.5.28-1.rhel5.i386.rpm
MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
MySQL-shared-5.5.28-1.rhel5.i386.rpm
Step 5:Now, we need to install all files based on following sequence:
[root@centos galera] rpm -UVh galera-23.2.2-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-client-5.5.28-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-shared-5.5.28-1.rhel5.i386.rpm
Step 6:Lets reset/create MySQL root password. In this example we will use simple root password as above, but I recommend you to use strong password:
[root@centos ~] mysqladmin -u root password 'root#@pass'
Step 7:To ease up MySQL administration, we will need to create /root/.my.cnf files and put following contents. This will automate your login as client:
[client]
user=root
password='root#@pass'
Step 8:Change the permission to 600 to make sure no one can open it other than root:
[root@centos ~] chmod 0600 /root/.my.cnf
Step 9:Run following commands inside MySQL. First command will delete empty username, 2nd is to create root@% user, 3rd is update root password for all hosts, 4th is create SST user which we will use to communicate between nodes and last command will grant SST user to dump databases:
mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO root@'%' IDENTIFIED BY 'root#@pass';
mysql> UPDATE mysql.user SET Password=PASSWORD('root#@pass') WHERE User='root';
mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';
Step 10: Lets locate libgalera and take note of the full path. We will use this as the communication provider for MySQL to communicate:
[root@centos galera] updatedb
[root@centos galera] locate libgalera
/usr/lib/galera/libgalera_smm.so
Step 11:Lets configure MySQL so it will know where and how to communicate with other nodes. Open /etc/mysql/conf.d/wsrep.conf (copy the directory /usr/share/doc/MySQL-server-5.5.28_wsrep_23.7/wsrep.cnf) with text editor and change value for following variables:
[root@centos galera] mkdir -p /etc/mysql/conf.d/
[root@centos galera] vi /etc/mysql/conf.d/wsrep.cnf
//change line 22 to
wsrep_provider=/usr/lib/galera/libgalera_smm.so
//line 50 change to
wsrep_cluster_address="gcomm://" //if it cluster root wsrep_cluster_address="gcomm://192.168.124.137:4567" // if it is other server
//change line 112 to
wsrep_sst_method=rsync
//change 121 to
wsrep_sst_auth=sst:sstpass123
Step 12:Make sure in your /etc/my.cnf (create this file it is not exist) to add following line:
!includedir /etc/mysql/conf.d/
Step 13:Restart the MySQL so it will run the latest configuration:
[root@centos galera] service mysql restart
Step 14:Make sure MySQL is run on port 3306 and wsrep is run on port 4567
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LIST EN 4787/mysqld
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LIST EN 4787/mysqld
Step 15:To check whether Galera is installed correctly and running properly, login into mysql and execute following command:
mysql> show status like 'wsrep%';
You will see similar output as below in all servers:
Server root:
mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 12 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_received | 11 |
| wsrep_received_bytes | 1520 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.111111 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 3 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 2 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------------+
40 rows in set (0.03 sec)
2 Server 192.168.124.128 and 192.168.124.136
+----------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 12 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 156 |
| wsrep_received | 7 |
| wsrep_received_bytes | 1108 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 3 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 1 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON |
+----------------------------+----------------------------------------------------------------+
40 rows in set (0.01 sec)
+----------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_protocol_version | 4 |
| wsrep_last_committed | 12 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 148 |
| wsrep_received | 3 |
| wsrep_received_bytes | 436 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 3 |
| wsrep_causal_reads | 0 |
| wsrep_incoming_addresses | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id | 5 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | e0a16aa3-663e-11e2-0800-df7a96683b0b |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy |
| wsrep_provider_version | 23.2.2(r137) |
| wsrep_ready | ON
P/S;If you add more Server Mysql Server to cluster .This Server, just login into the box and repeat step 1 to 15.and register This server to cluster with command:
mysql> SET GLOBAL wsrep_cluster_address='gcomm://';
Thanks and Best Regards
