Principle and Practice of MySQL master-slave Replication

Principle and Practice of MySQL master-slave Replication

MySQL master-slave Replication
MySQL master-slave replication copies the SQL command set originally created and modified databases to the slave database, and re-executes these SQL commands locally to create the same data as the master database. After the slave database is built, it includes copying all the data at the current cutoff position of the master database, and the data that continues to grow in the next step. As a matter of fact, it's okay to stop the database and back up all the data and then copy it again. The question is, how can we copy the increasing data of the database in real time? This requires the use of the binlog function...

Master-slave replication Principle

1) enable the log-bin function of the master database. During database writing, MySQL creates a binlog to record the addition, deletion, modification, and other operations performed by the database; you also need to create an account rep for the verification relationship between the slave database and the master database, and authorize it;

2) Lock the table and perform full-backup operations on the database to maximize data consistency. The most important thing is to obtain the binlog log demarcation point of the current database;
3) restore the database after database shutdown to the slave Database
4) run the change master to... command in the slave database TO verify the user, tell the user where the binlog log of the database currently backed up from the slave database is located, and generate the master.info file;
5) when the slave database is enabled, the mysql replication function will actually play A role. Slave database B will use its own IO thread to constantly ask the master database A: Buddy, I have A password that is my own, er... I have these products locally. Do you have any new products (new data. Then, the master database A will view its binlog and find some new products. Then, it will send these new products to slave database B and A list will be provided, tell the slave database B what goods he has in his current Warehouse. Next time I come back from the slave database B to ask for new goods. As long as I come up with this list A, I will know if the goods there are the same as those of myself, if it is the same, it will not be sent. If it is different, the latest goods will be sent according to the goods cutoff position on this list. The new goods here can be understood as the binlog file content, the list shows the binlog position information post;
Every time you get the new goods from the database, you will be very happy. Start your own SQL thread to resolve the new goods to your database to ensure that it is exactly the same as master database.

Master-slave replication case practices

Use/data/3306/mysql. sock as the master database and/data/3307/mysql. sock as the slave database in multiple local mysql instances.

Master Database

1) enable the binlog function of the master database and ensure that the server id is different
[Root @ db02 ~] # Egrep "log-bin | server-id" log-bin/data/3306/my. cnf/data/3307/my. cnf
/Data/3306/my. cnf: log-bin =/data/3306/mysql-bin
/Data/3306/my. cnf: server-id = 1
/Data/3307/my. cnf: # log-bin =/data/3307/mysql-bin
/Data/3307/my. cnf: server-id = 3

2) master database authorization slave database account rep
Mysql> grant replication slave on *. * to rep @ '192. 172.% 'identified by 'oldboy123 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;

3) Lock table (the current mysql control window cannot be exited after the lock table)
Mysql> flush table with read lock;

4) view the binlog critical point
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000015 | 3204 |
+ ------------------ + ---------- + -------------- + ------------------ +

5) Full backup

[Root @ db02 ~] # Mysqldump-uroot-poldboy1234-S/data/3306/mysql. sock-A-B -- events | gzip>/server/backup/mysql _ $ (date ready before f2.16. SQL .gz
[Root @ db02 backup] # ll mysql_2016-07-07. SQL .gz
-Rw-r -- 1 root 144620 Jul 7 mysql_2016-07-07. SQL .gz

6) unlock the table
1 mysql> unlock table;

Slave Database

1) Ensure that the server ID is different
[Root @ db02 ~] # Grep server-id/data/3307/my. cnf
Server-id = 4

2) Restore full backup

[Root @ db02 backup] # mysql-uroot-poldboy456-S/data/3307/mysql. sock </server/backup/mysql_2016-07-07. SQL

3) configure the master and enter the correct binlog location

Mysql> change master to MASTER_HOST = '192. 16.2.10 ', MASTER_PORT = 172, MASTER_USER = 'rep ',
MASTER_PASSWORD = 'oldboy123 ', MASTER_LOG_FILE = 'mysql-bin.000015', MASTER_LOG_POS = 3204;
# In this case, if the master password configuration is incorrect, the master.info file will not take effect. You need to execute reset slave all;
[Root @ db02 data] # cat master.info
18
Mysql-bin.000015
3204
172.16.2.10
Rep
Oldboy123
3306
60
0

4) Enable slave database replication to verify data

Mysql> start slave;
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.10
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 3289
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 338
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes # copy the binlog log process from the master database
Slave_ SQL _Running: Yes # convert the relay log read from the database into an SQL statement applied to the database Process
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 3289
Relay_Log_Space: 488
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 # The slave database delays the master database in seconds during the replication process.
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

Verification completed.

This article permanently updates the link address: