MySql Replication configuration, mysqlreplication
I. Preface
As a cheap solution for read/write splitting, Mysql Replication supports one master node and multiple slave nodes for data storage and binary log transmission. Currently, Mysql Replication is widely used and has many online concepts, I will not repeat it. Reference an official Replication Application Scenario image (http://dev.mysql.com/doc/refman/5.1/en/replication-solutions.html ). This document describes how to configure Mysql Replication in Linux.
II. Environment preparation
|
IP_PORT |
OS |
Mysql |
Master |
10.129.221.14: 3306 |
CentOS 1, 6.5 |
5.1.73 |
Slave |
10.129.221.16: 3306 |
CentOS 1, 6.5 |
5.1.73 |
Slave |
... |
|
|
3. enable remote access for the root user on the Master and Slave respectively, and set the root user password. The default value is null.
mysql> use mysql;mysql> update user set host = '%' where host ='127.0.0.1' and user = 'root';mysql> update user set password = PASSWORD('glf_123') where user = 'root';mysql> flush privileges;
4. Create a repl user on the Master database and grant permissions for the Server Load balancer instance to access the master database.
mysql> create user 'repl'@'%' identified by 'glf_123';mysql> grant replication slave on *.* to 'repl'@'%' identified by 'glf_123';mysql> flush privileges;
The modified user table data is as follows:
5. Create a TestDB database on the Master server for Replication
6. Modify the database configuration of the Master database (/etc/my. cnf) and enable the binary log record of the database.
[Mysqld] server-id = 1 # unique IDlog-bin = mysql-bin # specify the log file binlog-do-db = TestDB # datadir =/var/lib/ mysqlsocket =/var/lib/mysql. sockuser = mysql # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links = 0 [mysqld_safe] log-error =/var/log/mysqld. logpid-file =/var/run/mysqld. pid
7. Restart the Master database
[root@VM-CentOS /]# service mysqld restar
8. Check the Master Status of the Master database
mysql> SHOW MASTER STATUS;
The result is as follows:
9. Modify the Slave database configuration (/etc/my. cnf)
[Mysqld] server-id = 2 # unique IDread_only = 1 # Set Slave database read-only datadir =/var/lib/mysqlsocket =/var/lib/mysql. sockuser = mysql # Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links = 0 [mysqld_safe] log-error =/var/log/mysqld. logpid-file =/var/run/mysqld. pid
10. Restart the Slave Database
[root@VM-CentOS /]# service mysqld restart
11. Set the Server Load balancer node to direct replication to the master node and execute the change master command in the Server Load balancer database.
change master to master_host='10.129.221.14',master_user='repl', master_password='glf_123',master_port=3306, master_log_file='mysql-bin.000001',master_log_pos=0, master_connect_retry=10;
Master_log_file indicates the log file name in the Master database, which can be queried through show master status;
Master_log_pos indicates the Binary Offset to be synchronized, and 0 indicates all are re-synchronized;
In step 8, the show master status query displays the current Position;
12. Enable the slave mode on the Salve Server
mysql> start slave;
XIII. view the execution of replication and related processes
mysql> SHOW SLAVE STATUS \G;mysql> show processlist \G;
14. At this point, MySQL Replication-related configuration has been completed. Any modifications made to the Master database will be committed to Slave through the transaction log.