MySQL master-slave and master-master replication. Step by step configuration instructions.
If you are looking for the options to csale your MySQL installation you may be also interested in MySQL partitioning and subpartitioning. It may improve each node speed and capacity parameters.One may say that there are a lot of MySQL replication manuals, but latest versions of MySQL server have changed the way how configuration should be applied. Most of the manuals do not reflect these changes. I want to describe some other aspects of configurations also. As far as there are a lot of good manuals about replication, I think there is no need to dove into details what is the replication. Just want to mention that this technique is usually used for load balancing on database servers. If you have a lot of read requests (most common for web applications) master-slave replication should suit your needs well. In this case you will do write transactions on master host and read requests on slave hosts, because data is populated from master to slave much faster than from slaves to master and to other slaves.
Master 1/Slave 2 ip: 192.168.16.4
Master 2/Slave 1 ip : 192.168.16.5
Iptables rules for MySQL replication
It will be good practice to allow connections only from other nodes envolved into the replication and deny from other. By the way this will work good for some other services that are allowed to communicate nly with the known hosts. You can define port range like 1025:3306 (I am going to write more about iptables soon, so follow my blog on twitter).iptables -A INPUT -p tcp -s 192.168.16.4 --sport 3306 -d 192.168.16.5 --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT iptables -A OUTPUT -p tcp -s 192.168.16.5 --sport 3306 -d 192.168.16.4 --dport 3306 -m state --state ESTABLISHED -j ACCEPT
MySQL master-slave replication
Basically master-master replication consists of two master-slave replications. Now we will configure master-slave replication from the first server to the second one.Create relication user on Master 1:
mysql> grant replication slave on *.* to 'replication'@192.168.16.5 identified by 'slave';And start master:
mysql> start master;Master 1 changes to /etc/my.cnf:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin binlog-do-db=<database name> # 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.pidSlave 1 changes to /etc/my.cnf:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pidImportant! Pay attention that you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.
On Master 1:
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ |mysqld-bin.000012 | 106 | adam | | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)On Slave 1:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.4', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;Attention! This will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.
Start slave on Slave 1:
mysql> start slave;
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.5
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysqld-bin.000012
Read_Master_Log_Pos: 1368129
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 605530
Relay_Master_Log_File: mysqld-bin.000012
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: 1368129
Relay_Log_Space: 1367083
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.02 sec)
Above highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.
MySQL master-master replication
Master-master replication is actually two master-slave replications. This allows to make read and write transactions on both servers, as data propagation from master to slave goes very fast oposit to data propagation from slave to master which requires much more time. So, to create master-master replication we should now configure Master 2 - Slave 2 replication.Create a replication slave account on Master 2 for Master 1/Slave 2:
mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave';And start master:
mysql> start master;Master 2 changes to /etc/my.cnf:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin binlog-do-db=<database name> # 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=2 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pidSlave 2 / Master 1 changes to /etc/my.cnf:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin binlog-do-db=<database name> # 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.pidImportant! And again you should not configure master-host, master-user, master-password, master-port via my.cnf on slave server now.
On Master 2:
mysql> show master status; +------------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ |mysqld-bin.000012 | 106 | adam | | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)On Slave 2:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.16.5', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_PORT=3306,MASTER_LOG_FILE='mysqld-bin.000012', MASTER_LOG_POS=106, MASTER_CONNECT_RETRY=10;Attention! As I have already mentioned in the previous section this will configure slave and server will remember settings, so this replaces my.cnf settings in latest versions of MySQL server.
MySQL master-master replication and autoincrement indexes
If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.On the Master 1/Slave 2 add to /etc/my.cnf:
auto_increment_increment= 2 auto_increment_offset = 1On the Master 2/Slave 1 add to /etc/my.cnf:
auto_increment_increment= 2 auto_increment_offset = 2
--- The End ---
No comments:
Post a Comment