1. Why master slave copy
- In a system with complex business, there is a scenario in which an sql statement needs to lock the table, resulting in the temporary inability to use the read service, which will greatly affect the running business. Use master-slave replication, and let the master database be responsible for writing and the slave database be responsible for reading. In this way, even if the master database has the scenario of locking the table, the normal operation of the business can be guaranteed by reading the slave database.
- Make hot standby for data, and replace the main database in time after the main database goes down, so as to ensure business availability.
- Extension of the architecture. The business volume is increasing, and the I/O access frequency is too high, which can not be met by a single machine. At this time, do multi library storage, reduce the disk I/O access frequency and improve the I/O performance of a single machine.
2.MySQL master-slave replication process
(1) The update events (update, insert, delete) of the main database db are written to the binlog
(2) The master database creates a binlog dump thread to send the contents of binlog to the slave database
(3) Start and initiate a connection from the library to connect to the main library
(4) After starting from the library, create an I/O thread, read the binlog from the main library and write it to the relay log
(5) After starting the library, create an SQL thread to read the content from the relay log, execute the read update event from the Exec_Master_Log_Pos location, and write the update content to the slave db
Note: the above process is a relative process, not an absolute process
3. Principle of MySQL master-slave replication
MySQL master-slave replication is an asynchronous replication process. The master database sends update events to the slave database, reads the update records from the database, and executes the update records to keep the contents of the slave database consistent with the master database.
Binlog: binary log, a binary file that stores all update event logs in the main library. Binlog is a file that stores all database change records (database structure and content) from the moment the database service is started. In the master database, as long as there are update events, they will be written to binlog in turn, and then pushed to the slave database as the data source for slave database replication.
Binlog output thread: whenever a slave library is connected to the master library, the master library will create a thread and send binlog content to the slave library. For each sql event to be sent to the slave library, the binlog output thread will lock it. Once the event is read by the thread, the lock will be released, even when the event is completely sent to the slave library.
In the slave library, when replication starts, the slave library will create slave library I/O threads and slave library SQL threads for replication processing.
Slave library I/O thread: after the START SLAVE statement is executed from the library, an I/O thread is created from the library. The thread connects to the master library and requests the master library to send the update record in binlog to the slave library. Read the updates sent by the binlog output thread of the main library from the library I/O thread and copy these updates to local files, including the relay log file.
SQL thread from the Library: create an SQL thread from the library, which reads and executes the update events written to the relay log from the library I/O thread.
To sum up, it can be seen that:
For each master-slave replicated connection, there are three threads. The master library with multiple slave libraries creates a binlog output thread for each slave library connected to the master library, and each slave library has its own I/O thread and SQL thread.
By creating two independent threads from the library, the read and write from the library are separated during replication. Therefore, even if the thread responsible for execution runs slowly, the thread responsible for reading the update statement does not become slow. For example, if the slave library has not been running for some time, when it starts here, although its SQL thread is slow, its I/O thread can quickly read all binlog contents from the main library. In this way, even if the slave library stops running before the SQL thread finishes executing all the read statements, the I/O thread at least completely reads all the contents and safely backs them up in the local relay log of the slave library, ready to execute the statements at the next startup of the slave library.
4. Best practices
4.1 create configuration file and mount docker directory
mdkir -p /home/mysql/master mdkir -p /home/mysql/slave
4.2 add my CNF configuration
vim /home/mysql/master/etc/mysql/my.cnf
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL log-bin = mysql-bin server-id = 1
vim /home/mysql/slave/etc/mysql/my.cnf
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL log-bin = mysql-bin server-id = 2
4.2 start the master-slave mysql container
Main container
docker run -p 13306:3306 \ --name mysql-master \ -v /home/mysql/master/etc/mysql/my.cnf:/etc/mysql/my.cnf \ -e MYSQL_ROOT_PASSWORD=mysql \ -d mysql
From container
docker run -p 23306:3306 \ --name mysql-slave \ -v /home/mysql/slave/etc/mysql/my.cnf:/etc/mysql/my.cnf \ -e MYSQL_ROOT_PASSWORD=mysql \ -d mysql
4.3 main container configuration
mysql -uroot -pmysql
View master status
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Record the value of File and Position.
The following will be used. So far, the main library must not do any more operations to prevent the state from changing.
4.4 configuration from container
mysql -uroot -pmysql
master_host = 'x.x.x.x' / / enter the master host ip here
master_log_file=’mysql-bin. 00000 3 ', / / fill in the value of File here
master_log_pos=1338, / / fill in the value of Position here.
mysql> start slave;// Start replication from server
# 1. Stop filling in the wrong information first, and you can reset step 2 stop slave; # 2. Set the starting point for copying change master to master_host='10.6.8.227', master_user='root', master_log_file='mysql-bin.000003', master_log_pos=156, master_port=13306, master_password='mysql'; # Start slave service start slave;
4.5 check the master-slave connection status
see
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Are they all YES
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.6.8.227
Master_User: root
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 341
Relay_Log_File: 7a9c9ae49c49-relay-bin.000002
Relay_Log_Pos: 509
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 341
Relay_Log_Space: 725
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 724cdea0-0d4e-11ec-8830-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
4.6 frequently asked questions
4.6. 1. MySQL master-slave replication reports an error Authentication plugin 'caching'_ sha2_ password‘ reported error: Authentication
View main library: SELECT plugin FROM `user` where user = 'repl'; It turned out to be the main library repl of plugin yes caching_sha2_password The connection is not connected, and it is modified to mysql_native_password Can be solved. ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
reference
http://dbadiaries.com/what-is-mysql-replication-and-how-does-it-work
https://dev.mysql.com/doc/refman/5.6/en/replication-implementation-details.html