Detailed explanation of the sample code sharing of master-slave replication of mysql5.6 under centos7

This article mainly introduces the detailed explanation of master-slave replication in mysql5.6 under centos7. The editor thinks it is quite good, so I will share it with you now and give it as a reference. Let’s follow the editor and take a look.

1. Introduction to mysql master-slave replication

Mysql’s master-slave replication is not on the database disk The file is copied directly, but copied to the local server to be synchronized through the logical binlog log, and then the local thread reads the sql statement in the log and re-applies it to the mysql database.

Mysql database supports replication in different business scenarios such as one-way, two-way, chain cascade, and ring. One server acts as the main server master and receives updates from users, while one or more other servers act as The slave server receives the log content from the master server's binlog file, parses the sql, and updates it to the slave server.

One master and one slave (A -> B, A is the master, B is the slave)

One master and multiple slaves (A -> B, A -> C, A is the slave Master, B and C are slaves)

Double-master bidirectional synchronization (A -> B, B -> A, A and B are both masters and backup each other)

Linear cascade (A -> B -> C , A and B are masters and slaves, and C is slave)

Ring cascade (A -> B -> C -> A, A, B and C are both masters, and each node can write data)

2. Solution to realize mysql master-slave read and write separation

1. Realize reading and writing separation through programs (judgment statementskeywords to connect the master-slave database)

2. Realize reading and writing separation through open source software (mysql-proxy, amoeba, stable The performance and function are average, not recommended for production use)

3. Independent development of DAL layer software

3. Introduction to the principle of mysql master-slave replication

Mysql master-slave replication is an asynchronous replication process, which copies a master library to a slave library. The entire process between master and slave is completed by three threads. The sql thread and I/O thread are on the slave side, and the other I/O thread is on the master side.

Copying Principle and Process

1. Execute the start slave command on the slave, turn on the master-slave replication switch, and start master-slave replication.

2. The I/O thread of the slave requests the master through the authorized replication user on the master and requests the specified location of the specified binlog log.

3. After the master receives the request from the slave's I/O thread, its own I/O thread responsible for copying will read the log information after the specified position of the specified binlog log in batches based on the slave's request information. Then it is returned to the slave's I/O thread. In addition to the binlog log, the returned information also includes the master's new binlog file name and the next specified update position in the new binlog.

4. The slave obtains the binlog log content sent from the I/O thread on the master. After the log file and the location point, the binlog content will be written to the end of the slave's own relay log (relay log) file in turn. And record the new binlog file name and location into the master-info file, so that the next time the new binlog log is read from the master, the master can be told to read from the new location of the new binlog.

5. The slave's sql thread will detect the newly added log content of the I/O thread in the local relay log in real time, parse the content in the relay log file into sql statements in a timely manner, and parse the sql statements in the order of their positions. Execute these sql statements. Relay-log.info records the file name and location of the current application relay log.

4. Mysql master-slave replication operation

I have mysql single-machine multiple instances, 3306, 3308, 3309

The master library is 3306, the slave library is 3308,3309

(1), on the master library

1, set the server-id value and open it Binlog function

> vi /etc/my.cnf

 [mysqld]
 #用于同步的每台机器server-id都不能相同

server-id = 10

log-bin = /data/mysql56/data/mysql-bin

2. Restart the main library

> service mysqld restart

3. Log in to the main library and check the server-id

> mysql -uroot -p

> show variables like 'server_id';

4. Create a database on the main library for copying from the library Account

> grant replication slave on *.* to "rep"@"%" identified by "123456";

> flush privileges;

> select user,host from mysql.user;

> show grants for rep@"%";

5. Read-only lock table of main database database (do not close the current window)

> flush table with read lock;

View main database status

> show master status;

6 , Back up all data files in the main library

> mysqldump -uroot -p -A -B | gzip > /data/mysql_bak.$(date +%F).sql.gz

7. After backing up the main library data, unlock

> unlock tables;

8. Migrate the data exported from the main library to the slave library

( 2) On the slave library

1, set the server-id value and turn off the binlog function

①There are two situations where binlog needs to be turned on

② To synchronize B in the middle of A->B->C in cascade, you need to enable binlog

③When doing database backup in the slave database, you must have full backup and binlog log to be a complete backup.

> vi /mysql-instance/3308/my.cnf 

[mysqld]

server-id = 11

relay-log = /mysql-instance/3308/relay-bin

relay-log-info-file = /mysql-instance/3308/relay-log.info

2. Restart the slave library

> /mysql-instance/3308/mysql restart

3. Log in to the slave library to check the parameters

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock

> show variables like 'log_bin';

> show variables like 'server_id';

4. Restore the data exported by mysqldump from the main library to the slave library

> gzip -d /data/mysql_bak.2017-01-15.sql.gz

Restore the master database data to the slave database

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock < /data/mysql_bak.2017-01-15.sql

5. Log in to the slave database and configure the replication parameters

CHANGE MASTER TO

MASTER_HOST='127.0.0.1',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='123456',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=396;

Note that the above MASTER_LOG_FILE and MASTER_LOG_POS are used in the master database using show master status; View information.

View the master.info file

> cat /mysql-instance/3308/data/master.info

6. Start the slave library synchronization switch and test the master-slave replication situation

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "start slave;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show slave status\G;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show slave status\G" | egrep "IO_Running|SQL_Running|_Behind_Master"

7. Test the master-slave replication

> mysql -uroot -p -e "create database wohehe;"

> mysql -uroot -p -S /mysql-instance/3308/mysql.sock -e "show databases;"

五、mysql主从复制线程状态说明及用途

1、主库线程的同步状态

> show processlist\G; 


*************************** 1. row ***************************

   Id: 5

  User: rep

  Host: localhost:47605

   db: NULL

Command: Binlog Dump

  Time: 4728

 State: Master has sent all binlog to slave; waiting for binlog to be updated

  Info: NULL

说明主库线程已从binlog读取更新，发送到了从库，线程处理空闲状态，等待binlog的事件更新。

2、从库线程的同频状态

> show processlist\G; 

*************************** 2. row ***************************

   Id: 6

  User: system user

  Host:

   db: NULL

Command: Connect

  Time: 5305

 State: Slave has read all relay log; waiting for the slave I/O thread to update it

  Info: NULL

说明从库已读取所有中继日志，等待从库I/O线程的更新。

六、主从复制故障

如果我在从库上创建了一个库，然后去主库创建同名的库，那么这就会冲突了。

> show slave status; 

Slave_IO_Running: Yes

Slave_SQL_Running: No

Seconds_Behind_Master: NULL

Last_Error: Error 'Can't create database 'xxxxx'; database exists' on query. Default database: 'xxxxx'. Query: 'create database xxxxx'

对于该冲突解决方法

方法一

> stop slave;

#将同步指针移动下一个，如果多次不同步，可重复操作

> set global sql_slave_skip_counter = 1;

> start slave;

方法二

> vi /mysql-instance/3308/my.cnf 

#把可以忽略的错误号事先在配置文件中配置

slave-skip-errors = 1002,1007,1032

The above is the detailed content of Detailed explanation of the sample code sharing of master-slave replication of mysql5.6 under centos7. For more information, please follow other related articles on the PHP Chinese website!