MySql Replication configuration, mysqlreplication

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.