Master-slave replication of mysql5.6 under Centos7

First, MySQL master-slave Replication Introduction

The master-slave copy of MySQL is not a direct copy of the file on the database disk, but is replicated to the server to be synchronized by the logical Binlog log.
The local thread then reads the SQL statement inside the log and re-applies it to the MySQL database.

MySQL database supports one-way, bidirectional, chain cascade, ring and other different business scenarios of replication, a server acting as Master server master, receive updates from users,
While one or more other servers act as slave from the server, receive log content from the primary server Binlog file, parse out the SQL, and update to the slave server.

One Master one from (A-B, a-based, b is from) a master and many from (A-B, a-C, a-based, B and C are from) two-way synchronization (A, B, B, A, A and B with the main, backup each other) linear cascade (A-B C, A and B master mutual preparation, C is from the ring cascade (A, B, C-A, A, B, C are mainly, each node can write data)

Second, the realization of MySQL master-slave reading and writing separation scheme

1, through the program to achieve read and write separation (judgment statement keyword, to connect master-slave database) 2, through the open source software to achieve read and write separation (Mysql-proxy,amoeba, stability and function general, not recommended production use) 3, the development of the DAL layer software independently

Three, MySQL master-slave Replication Principle Introduction
MySQL master-slave replication is an asynchronous replication process that copies a master library to a library, and the entire process between master and slave is performed by three threads.
where the SQL thread and the I/O thread are on the slave side, another I/O thread is on the master side.

Replication principle Process

1, execute the start slave command on the slave, turn on the master-slave copy switch, start the master-slave copy. 2. The slave I/O thread requests master through the authorized replication user on Master, requesting the specified location of the Binlog log. 3. After master receives the slave I/O thread's request, its own I/O thread responsible for replication reads the log information after the specified Binlog log in batches according to the slave request information, and then returns the I/O thread to slave. Returns information in addition to the Binlog log, there is a new Binlog file name for master and the next specified update location in the new Binlog. 4, slave Gets the Binlog log content sent from the I/O thread on master, the log file and the location point, the Binlog content will be written to the slave itself relay log (trunk log) file tail, The new Binlog file name and location are recorded in the Master-info file so that the next time you read the new Binlog log from master, you can tell master to read from the new Binlog location. 5. Slave's SQL thread detects the added log contents of the I/O line assigns the local relay log in real time, parses the contents of the relay log file into SQL statements, and executes the SQL statements in the order in which they were parsed. The file name and location point of the current application relay log are recorded in Relay-log.info.

Iv. MySQL master-slave copy operation
I am here MySQL standalone multi-instance, 3306, 3308, 3309
Main library is 3306, from library to 3308,3309

(1), on the Master Master Library
1. Set Server-id value and turn on Binlog function

    > vi/etc/my.cnf

[Mysqld]
#用于同步的每台机器server-id are not the same.

    Server-id = ten    Log-bin =/data/mysql56/data/mysql-bin

2. Restart the main library

    > Service mysqld Restart

3. Log in to the main library to view Server-id

    > Mysql-uroot-p    > Show variables like ' server_id ';

4. Create an account for copying from the library on the main library

    > Grant replication Slave on * * to ' rep ' @ "%" identified by "123456";    > Flush Privileges;    > select User,host from Mysql.user;    > show grants for [email protected] "%";

5. The lock table is read-only for the main library database (do not turn off the current window)

    > Flush table with read lock;

View the main library status

    > Show master Status;

6. Back up all data files of 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. Migrating the data exported from the main library to the library

(2), in slave from the library
1. Set Server-id value and turn off Binlog function

There are two cases in which you need to open Binlog
Cascade Sync A->b->c in the middle of the B, you need to open Binlog
To make a database backup from a library, you must have full and Binlog logs to be a full backup.

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

    [Mysqld]    Server-id =    Relay-log =/mysql-instance/3308/relay-bin    relay-log-info-file =/mysql-instance/3308/ Relay-log.info

2. Reboot from library

    >/mysql-instance/3308/mysql Restart

3. Log in from the 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 main library mysqldump exported data to the slave library

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

Restore the Master library data to the slave library

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

5. Log in from the library, 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 Master_log_file and Master_log_pos above are in the main library with show master status;

View Master.info File

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

6. Start the sync switch from the library, 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 Master-slave replication

    > Mysql-uroot-p-E "CREATE database wohehe;"    > Mysql-uroot-p-s/mysql-instance/3308/mysql.sock-e "show databases;"

V. MySQL master-slave replication Thread status description and purpose
1. Synchronization state of the main library thread

> Show processlist\g;

1. Row ***************************     id:5   user:rep   host:localhost:47605     db:NULLCommand:Binlog Dump   time:4728  State:master have sent all binlog to slave; Waiting for Binlog to be updated   Info:null

Explains that the main library thread has read updates from Binlog, sent to the thread from the library, and waits for Binlog events to update.

2. The same frequency state from the library thread

> Show processlist\g;

2. Row ***************************     id:6   user:system User   Host:     db:NULLCommand:Connect   time:5305  State:slave have read all relay log; Waiting for the Slave I/O thread to update it   Info:null

Indicates that all trunk logs have been read from the library, waiting for updates from the library I/O thread.

VI. master-slave replication failure
If I create a library from the library and then go to the main library to create a library with the same name, then this will be a conflict.

> Show slave status;

Slave_IO_Running:YesSlave_SQL_Running:NoSeconds_Behind_Master:NULLLast_Error:Error ' Can ' t create database ' xxxxx '; Database exists ' on query. Default database: ' xxxxx '. Query: ' Create database xxxxx '

For this conflict resolution
Method One

> Stop slave; #将同步指针移动下一个, if multiple steps are different, repeatable operation > Set global sql_slave_skip_counter = 1;> start slave;

Method Two

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

#把可以忽略的错误号事先在配置文件中配置slave-skip-errors = 1002,1007,1032

Master-slave replication of mysql5.6 under Centos7