MySql Master slave copy

1. Why master-slave replication is needed

• In complex business systems , There is such a scene , There is a sentence. sql Statement needs lock table , Resulting in temporary inability to use the read service , Then it will affect the running business , Use master-slave replication , Let the main database be responsible for writing , Read from the library , such , Even if the main database appears to lock the table , The normal operation of the business can also be ensured by reading from the database .
• Do hot data backup , After the main database is down, it can replace the main database in time , Ensure business availability .
• The extension of Architecture . More and more business ,I/O Access frequency is too high , Click not satisfied , At this time, do multi library storage , Lower the disk I/O Frequency of visits , Improve single machine I/O performance .

2. What is? Mysql Master slave copy

​ MySql Master-slave replication means that data can be copied from one MySql The database server master node is replicated to one or more slave nodes .MySql Asynchronous replication is adopted by default , In this way, the slave node does not need to access the master server uniformly to update its own data , Data can be updated on a remote connection , The slave node can copy all databases in the master database or specific databases , Or a specific watch .

3. Master slave copy form

mysql Master slave copy flexible

• A master from
• Master master copy
• One master, many followers --- Extended system read performance , Because reading is reading from the library ;
• Multi master and one slave ---5.7 Start supporting
• Co replication ---

4. Necessary conditions for master-slave replication

• Main library opens binlog journal （ Set up log-bin Parameters ）
• Master-slave server-id Different
• The slave server can connect to the master database

5. Mysql The principle of replication

principle ：

• Generate two threads from the library , One I/O Threads , One SQL Threads ;
• i/o Thread to request main library Of binlog, And will get binlog The log says relay log（ relay logs ） In file ;
• The main library will generate a log dump Threads , Used to give to the slave i/o Thread transfer binlog;
• SQL Threads , Will read relay log Log in file , And parse it into concrete operation , To achieve master-slave operation consistency , And the final data is consistent ;

Principle that

MySQL Master-slave replication is an asynchronous replication process , The master database sends the update event to the slave database , Read update records from the library , And perform update records , Keep the content of the slave library consistent with the main library .

binlog：binary log, The binary file that stores all update event logs in the main library .binlog From the moment the database service starts , Save all changes to the database （ Database structure and content ） The file of . In the main library , As long as there are update Events , Will be written to in turn binlog in , It is then pushed to the slave library as a data source for replication from the library .

binlog Output thread ： Whenever there is a slave connection to the master , The main library will create a thread and send binlog Content to from library . For every sql event ,binlog The output thread will lock it . Once the event has been read by the thread , The lock will be released , Even when the event is completely sent to the slave Library , The lock will also be released .

From the library , When replication starts , From the library will create a slave Library I/O Thread and slave library SQL Thread for replication processing .

Slave Library I/O Threads ： When START SLAVE Statement is executed from the library , Create a... From the library I/O Threads , This thread connects to the main database and requests the main database to send binlog The update records in it are recorded on the slave database . Slave Library I/O The thread reads the main library binlog The output thread sends the updates and copies them to the local file , These include relay log file .

From library SQL Threads ： Create a... From the library SQL Threads , This thread reads from the library I/O The thread writes relay log Update event and execute .

in summary , You know ：

For each master-slave replication connection , There are three threads . The master database with multiple slave databases creates one for each slave database connected to the master database binlog Output thread , Each slave has its own I/O Threads and SQL Threads .

From the library by creating two separate threads , So that when copying , Read and write are separated from the library . therefore , Even if the thread responsible for execution runs slower , The thread responsible for reading the update statement does not slow down . for instance , If the slave library has not been running for a period of time , When it starts here , Even though it's SQL Thread execution is slow , its I/O Threads can quickly read all the... From the main library binlog Content . thus , Even from the library SQL The thread stops running before executing all the read statements ,I/O The thread at least reads everything completely , And back it up safely in the local of the slave library relay log, Be ready to execute statements the next time you start from the library .

7. MySql 5.7 install （ be based on cent os 6.5 above ）

Reference documents 《MySQL 5.7 install 》

8. Master and slave copy build

1） Create two chalk databases in the same server

# test_syn_db  Name the database 
create database test_syn_db

2) Configure the main server as follows

#  Modify the configuration file , Execute the following command to open mysql The configuration file 
vim /etc/my.cnf
#  stay mysqld Add the following configuration information to the module 
# master-bin  For the custom binary file name 
log-bin=master-bin 
#  Set binary log format 
#  There are three binary formats : ROW、statement、mixed.
# ROW:  Copy the changes , Instead of executing the command from the server 
# stastment:  Executed on the primary server sql sentence , Execute the same statement on the slave server .MySQL Statement based replication is used by default .
# mixed:  Statement based replication is used by default , Once it is found that statement based replication cannot be accurate , You'll use row based replication 
binlog-format=ROW 
#  Require each server's id It has to be different 
server-id=1 
#  The name of the synchronized database 
binlog_do_db=test_syn_db

After the configuration , Restart by executing the following command mysql service ：

service mysqld restart

After restart , Sign in mysql database , see master state ：

show master status;

3) Configure the authorization to log in from the server to the master server

--  Authorized operation 
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--  Refresh the permissions 
flush privileges;

4) Configuration from server

#  Modify the configuration file , Execute the following command to open mysql The configuration file 
vim /etc/my.cnf
#  stay mysqld Add the following configuration information to the module 
# master-bin  For the custom binary file name 
log-bin=master-bin 
binlog-format=ROW 
#  Require each server's id It has to be different 
server-id=2

After configuring from the server , Also restart mysql service , And log in and configure it ：

#  restart mysql service 
service mysqld restart
#  Sign in mysql
mysql -uroot -p
#  Connect to the main server  
# master_host：  master server IP
# master_user：  Primary server user 
# master_password：  Master server user password 
# master_port：  Primary server port 
# master_log_file:  master server bin-log Log file name , That is, execute... On the primary server show master status What you see after the command File field value 
# master_log_pos:  master server bin-log Log location , That is, execute... On the primary server show master status What you see after the command Position field value 
change master to master_host='192.168.145.129', master_user='root', master_password='123456', master_port=3306,master_log_file='master-bin.000002', master_log_pos=154;
#  start-up slave
start slave;
#  see slave state 
show slave status\G #  Notice there's no semicolon 

show slave status when , If the connection fails , Make sure master_password Whether it is consistent with the authorized password , If it's not the same , Judge whether the access rights of the two servers are enabled , You can use telnet Command test verification , If the firewall is not open , Execute the following command to open the firewall port ：

#  Turn on the firewall 
firewall-cmd --permanent --zone=public --add-port=3306/tcp
#  Make the firewall work 
firewall-cmd --state
firewall-cmd --reload

Read / write separation

Read write separation depends on master-slave replication , And master-slave replication is for the purpose of read-write separation service .MySql Separation of reading and writing refers to making master Handle write operations , Give Way slave Processing read operations , It is very suitable for scenarios with large amount of read operations , Can reduce master The pressure of the .

6