Master and slave replication details of centos7 under mysql5.6
- 2020-05-17 07:30:27
- OfStack
1. Introduction to master and slave replication of mysql
The master-slave copy of mysql is not a direct copy of the files on the database disk, but a logical copy of the binlog log to the local server to be synchronized, and then the local thread reads the sql statements in the log and reapplies them to the mysql database.
mysql database support unidirectional and bidirectional, chain, cascade ring of different business scenarios, such as copying, 1 server ACTS as the primary server master, receive updates from users, and one or more other server ACTS as slave from the server, and receives binlog independent server log file, parse the sql, updates from the server.
1 master 1 slave (A -) > B, A mainly,B from)
1 master many follow (A -) > B, A - > C, A mainly,B and C are from)
Dual master bidirectional synchronization (A -) > B , B - > A, A and B are the same, backup each other)
Linear cascade (A -) > B - > C, A and B are interchangeable, C is slave)
Loop cascade (A -) > B - > C - > A, A, B, C are all dominant, and each node can write data.)
2. Implementation of mysql master-slave read-write separation scheme
1, through the program to achieve read and write separation (judge the statement keywords, to connect the master and slave database)
2. Separate reading and writing through open source software (mysql-proxy,amoeba, stability and function 1, not recommended for production)
3. Independently developed DAL layer software
3. Introduction to the master slave replication principle of mysql
mysql master slave replication is an asynchronous replication process in which one master library is copied to one slave library. The entire process between master and slave is implemented by three threads. The sql thread and I/O thread are at the slave end, and the other I/O thread is at the master end.
Replication principle process
1. Execute start slave command on slave, turn on master-slave copy switch and start master-slave copy.
2. The slave I/O thread requests master through an authorized replication user on master to specify the specified location of the binlog log.
3. After receiving the request from slave's I/O thread, the I/O thread responsible for replication will read the log information after the specified location of the binlog log in batches according to the request information of slave, and then return it to slave's I/O thread. In addition to the binlog log, master's new binlog file name will also be returned. And the next update location specified in the new binlog.
4. slave gets the binlog log content sent by I/O thread on master. After the log file and location point, binlog content will be successively written to the end of relay log(relay log) file of slave itself, and the new binlog file name and location will be recorded in master-info file, so that the next time the new binlog log is read from master, Can tell master to read from the new binlog location.
5. The sql thread of slave will detect the newly added log contents of the local relay log I/O thread in real time, parse the contents of relay log file into sql statements in a timely manner, and execute these sql statements in the order in which sql statements are parsed. The file name and location point in relay-log.info that records the current application relay log.
4. mysql master-slave copy operation
I have here mysql single machine multiple instances, 3306, 3308, 3309
The master library is 3306, and the slave library is 3308,3309
(1) on the master main library
1. Set server-id value and enable binlog function
> vi /etc/my.cnf
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
2. Restart the main library
> service mysqld restart 3. Log in the main library and check server-id
> mysql -uroot -p
> show variables like 'server_id';
4. Set up accounts on the master library for replication from the slave library
> grant replication slave on *.* to "rep"@"%" identified by "123456";
> flush privileges;
> select user,host from mysql.user;
> show grants for rep@"%";
5. Read only the lock table of the main database (do not close the current window)
> flush table with read lock; View the status of the main library
> show master status; 6. Backup 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 database data, unlock it
> unlock tables; 8. Migrate the data exported from the master library to the slave library
(2) on slave slave library
1. Set server-id value and turn off binlog function
There are two conditions to open binlog
Cascade synchronization A- > B- > B in the middle of C, you turn on binlog
Full backup and binlog logs are required for database backup from the slave database.
> 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
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
3, login from the library to check the parameters
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
4. Restore the data exported from the master library mysqldump to the slave library
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
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 the slave library and configure the replication parameters
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
Note that MASTER_LOG_FILE and MASTER_LOG_POS above are show master status in the main library; View the information.
See the master.info file
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
6. Start the slave synchronization switch to test the master slave replication
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
7. Test master-slave replication
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
5. Master slave copy thread status description and purpose of mysql
1. Synchronization state of main library thread
> 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
Indicates that the master library thread has read the update from binlog and sent it to the slave library. The thread processes the idle state and waits for the event update of binlog.
2, the same frequency state of slave library thread
[mysqld]
# Each machine used for synchronization server-id They can't be the same
server-id = 10
log-bin = /data/mysql56/data/mysql-bin
Indicates that all relay logs have been read from the slave library and are waiting for updates from the slave library I/O threads.
6. Master slave replication failure
If I create a library on the slave library and go to the master library to create a library with the same name, then this will conflict.
> 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'
For this conflict resolution method
Method 1
> stop slave;
# Move the synchronization pointer down 1 Can be repeated if out of sync multiple times
> set global sql_slave_skip_counter = 1;
> start slave;
Method 2
> vi /mysql-instance/3308/my.cnf
# Configure the negligible error number in the configuration file beforehand
slave-skip-errors = 1002,1007,1032