This comprehensive Linux guide expects that you run the following commands as root user but if you decide to run the commands as a different user then ensure that the user has
sudoaccess and that you precede each of the privileged commands withsudo
MySQL Master-Master replication MySQL servers which increases speed and reduces latency for a website which require high availability with high traffic. Using replication, Two separate MySQL servers act as a cluster. The servers are synchronized with each other so that in case of failure,
other could take over and no data is lost.
Consider two separate systems. Lets call the first system as Master and Second as Slave. In the opposite case, when the first system shuts down or is unavailable, the second becomes the master and first becomes the slave. We will call the Server 1 as Master A and Slave B while the Server 2 will be called Master B and Slave A.
Install MySQL
Use the following command to install MySQL on both servers
apt-get update
apt-get upgrade -y
apt-get install mysql-server mysql-clientRun the MySQL secure installation command. You will be asked to create a root password. It is recommended you select yes to all of the questions
mysql_secure_installationEdit MySQL Configuration
Open the /etc/mysql/my.cnf file on each server using nano and add/modify the following lines:
nano /etc/mysql/my.cnfServer 1
- /etc/mysql/my.cnf
-
# The MySQL database server configuration file. # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin binlog-do-db= # input the database which should be replicated binlog-ignore-db=mysql # input the database that should be ignored for replication binlog-ignore-db=test server-id=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
On Server 1, create a replication slave account in MySQL which is “replication11” in my case and the IP Address of this slave is 172.168.1.101 in my case.
mysql> grant replication slave on *.* to 'replication11'@172.168.1.101 \
identified by 'slave'; and then restart MySQL Master A.
Server 2
Open the /etc/mysql/my.cnf file on System 2 using nano and add/modify the following lines keeping in mind the Master system’s IP Address which is 172.168.1.100 in my case.
- /etc/mysql/my.cnf
-
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host = 172.168.1.100 master-user = replication master-password = slave master-port = 3306 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Restart System B and run the following command
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.168.1.100
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: MASTERMYSQL02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187
1 row in set (0.00 sec)Master Master Configuration
Run the Following command on Server 1:
mysql> show master status;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 | 410 | adam | |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)This shows that master-slave configuration has been created. The System 1 is acting like a master server and System 2 is a slave to that server. Now we need to vice-versa i.e. make a Master-Master Configuration so that The system 2 can act as a master and system 1 as a slave when system 1 shuts down due to any reason.
Server 2
On Server 2 open the file /etc/mysql/my.conf and edit it:
- /etc/mysql/my.cnf
-
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 server-id=2 master-host = 172.168.1.100 master-user = replication master-password = slave master-port = 3306 log-bin #information for becoming master added binlog-do-db=adam [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Create a replication slave account on Server 2 using
mysql> grant replication slave on *.* to 'replication'@172.168.1.100 identified by 'slave2';Server 1
On System 1 (Master A) open the file /etc/mysql/my.conf and edit it:
- /etc/mysql/my.cnf
-
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-bin binlog-do-db=adam binlog-ignore-db=mysql binlog-ignore-db=test server-id=1 #information for becoming slave. master-host = 172.168.1.101 master-user = replication master-password = slave2 master-port = 3306 [mysql.server]user=mysqlbasedir=/var/lib
Restart both MySQL Server 1 and Server 2
On MySQL Server 1:
mysql> start slave;On MySQL Server 2:
mysql > show master status;On MySQL Server 1:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.168.1.101
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000008
Read_Master_Log_Pos: 410
Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
Relay_Log_Pos: 445
Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 410
Relay_Log_Space: 445
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 103799
1 row in set (0.00 sec)
ERROR:
No query specifiedConfigure Replication
Log into the Server 1 and type the following command to show its status
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 277 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)Now open up the Server 2 and log into MySQL. Run the following command keeping in mind to replace 172.168.1.100 with the private IP from the first server. Also replace the value for master_log_file with the file value from the previous step, and the value for master_log_pos with the position value.
STOP SLAVE;
CHANGE MASTER TO master_host='172.168.1.100', master_port=3306, master_user='replication11', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=106;
START SLAVE;Run the status command again for Server 2
SHOW MASTER STATUS;Set the slave database status on Server 1, replacing the same values swapped in step 2 with those from the Server 2.
STOP SLAVE;
CHANGE MASTER TO master_host='172.168.1.100', master_port=3306, master_user='replication11', master_password='password', master_log_file='mysql-bin.000001', master_log_pos=277;
START SLAVE;Test if the configuration is fine and working by creating row in the database using the following query
Server 1:
create database test;
create table test.flowers (`id` varchar(10));Server 2:
show tables in test;If this goes well, you should see that the tables from Server 1 replicated on Server 2. You have configured the Master Master Replication!
