MySQL realizes master-slave replication / master-slave synchronization

mysql Implement master-slave replication / Master slave synchronization

If a project has only one database server , And if this server goes down , It will lead to business interruption , Impact . High availability is needed at this time , Avoid a single problem .

---

One 、 What is? mysql Master-slave replication of ？

1、 brief introduction

Refers to a server acting as the primary database server , Another or more servers act as slave database servers , Data from the master server is automatically copied to the slave server . For multi-level replication , The database server can act as a host , It can also act as a slave .

MySQL The basis of master-slave replication is The master server records binary log of database modification , The slave server automatically performs the update through the binary log of the master server .
In a word, it means , What does the master database do , Do something from the database .

2、 Analysis of the working principle of master-slave replication

• Master As long as the database changes , Immediately recorded Binary log Log file
• Slave The database starts a I/O thread Connect Master database , request Master Binary log of changes
• Slave I/O Binary log obtained , Save it to your own Relay log Log file .
• Slave There is one SQL thread Timing check Realy log Change or not , Change, then update the data
  

---

Two 、 Why use mysql Principal and subordinate

1、 Achieve server load balancing

​ That is, the load of processing customer queries can be divided between the master server and the slave server , So as to get better customers and corresponding time . Usually , Database administrators have two ideas .

​ One is to only update the data on the main server . Including the update of data records 、 Delete 、 Create a new job . Query jobs that don't care about data . The database administrator will query all the data Forward to the slave server . This can be useful in some applications . For example, some applications , Like the website of fund net worth prediction . The data is updated by the administrator , That is, there are few updated users . And the number of users queried The amount will be very much . At this point, you can set up a master server , Dedicated to data updates . Set up multiple slave servers at the same time , Used to query user information

​ The second is to split the query job between the master server and the slave server . In this way of thinking , The master server does not just need to update the data 、 Delete 、 Insert and other operations , At the same time, it also needs to bear part of the query operation . And from the server , Only responsible for data query . When the primary server is busy , Some query requests are automatically sent to the slave server , To reduce the workload of the primary server .

2、 Remote backup of data is realized through replication

​ Data can be copied from the master server to the slave server on a regular basis , This is undoubtedly the first remote backup of data . Under the traditional backup system , Is to back up data locally . Backup at this time The job runs on the same device as the database server , When the backup job runs, it will affect the normal operation of the server . Sometimes it will significantly reduce the performance of the server . meanwhile , Store the backup data locally , also Not very safe . For example, the hard disk is damaged due to voltage or the server is stolen , At this time, because the backup file is still stored on the hard disk , Database administrators cannot use backup files to recover data . This will obviously give enterprises Bring relatively large losses .

3、 Improve the availability of database system

​ The database replication function realizes the data synchronization between the master server and the slave server , Increase the availability of the database system . When there is a problem with the primary server , The database administrator can immediately make the slave server the master server , Used to update and query data . Then go back and carefully check the problem of the main server . At this time, the general database administrator will also use two methods .

​ ** First, after the main server fails , Although the slave server replaces the location of the master server , But there are still some restrictions on what the primary server can do .** If you still can only query data , and Unable to update data 、 Delete and other operations . This is mainly from the perspective of data security . Such as the upgrading of some banking systems , In the process of upgrading , You can only check the balance, not withdraw money . This is the same way The reason is .

​ ** Second, from the server to the main server .** After switching from the server to the primary server , Its status is exactly the same as the original main server . At this point, you can query the data 、 to update 、 Delete and other operations do . Therefore, we need to do a good job in data security . That is, the security policy of data , Be exactly the same as the original master server . Otherwise , It may leave some potential safety hazards

---

3、 ... and 、 How to configure mysql Master slave copy

1、 Environmental preparation

Install two locally mysql, Or use a virtual machine , Need to prepare two mysql,

Environmental Science , You can make pseudo clusters , It can also be a real cluster

mysql1(master): 192.168.120.200:3306
mysql2(slave):  192.168.120.201:3306

2、mysql Configuration file configuration

mysql1(master): Profile Settings

#mysql master1 config 
[mysqld]
server-id = 1        #  node ID, Ensure uniqueness 

# log config
log-bin = master-bin     # Turn on mysql Of binlog Log function 
sync_binlog = 1         # Control of the database binlog Brush it to disk  , 0  Don't control , Best performance ,1 Every time a transaction is committed, it will be brushed into the log file , The worst performance , Safest 
binlog_format = mixed   #binlog Log format ,mysql By default statement, It is recommended to use mixed
expire_logs_days = 7                           #binlog Overdue cleaning time 
max_binlog_size = 100m                    #binlog Size of each log file 
binlog_cache_size = 4m                        #binlog Cache size 
max_binlog_cache_size= 512m              # Maximum binlog Cache size 
binlog-ignore-db=mysql # A database that does not generate log files , Multiple ignore databases can be spliced with commas , perhaps   Copy this sentence , Write multiple lines 

auto-increment-offset = 1     #  Offset of self increment 
auto-increment-increment = 1  #  Self increment of self increment 
slave-skip-errors = all        # Skip from library error 

mysql2(slave): 201 mysql.cnf To configure

[mysqld]
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

Restart both mysql, Make the configuration work

3、master database , Create a replication user and authorize

1. Get into master The database of , by master Create replication users

CREATE USER repl IDENTIFIED BY 'Root12345_';

2. Give the user the right to copy

grant replication slave on *.* to 'repl'@'192.168.120.200'  identified by 'Root12345_';
FLUSH PRIVILEGES;

3. see master The state of

show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005      120|              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4, Configure slave

mysql> CHANGE MASTER TO 
MASTER_HOST = '192.168.120.200',  
MASTER_USER = 'repl', 
MASTER_PASSWORD = 'Root12345_',
MASTER_PORT = 3306,
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=120,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000; 

# MASTER_LOG_FILE With the main library File  bring into correspondence with 
# MASTER_LOG_POS=120 , # With the main library Position  bring into correspondence with 

Start from library slave process

mysql> slave start;
Query OK, 0 rows affected (0.04 sec)

See if the configuration is successful

--  Add a master database 
--  Query from the library 

---

Four 、 Possible problems

In the configuration mysql An error occurred during master-slave copying ：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

1. The phenomenon

Slave_IO_Running:No,mysql Of IO The thread is not working properly

2. reason

The author uses two virtual machines , A master from , From library mysql It was cloned directly . stay mysql 5.6 The replication of introduces uuid The concept of , In each replication structure server_uuid Make sure it's different , But look at direct cloning data After folder server_uuid It's the same .

3. solve

find data Under folder auto.cnf file , Modify the server_uuid value , Make sure each of them db Of server_uuid Dissimilarity , restart db that will do .
find auto.cnf

 find / -name 'auto.cnf'

modify server_uuid Value

vim /var/lib/mysql/auto.cnf

Use

select uuid();

Generate a uuid that will do .

Restart the database .