Master-slave synchronous replication configuration of MySQL database under Linux

The advantage of the master-slave synchronization configuration of the MySQL database under Linux is that this method can be used as a backup method to achieve read-write separation and relieve the pressure on a database. In order to greatly improve the speed and efficiency when running massive amounts of data, MySQL's master-slave replication requires at least two MySQL services. Of course, MySQL services can be distributed on different servers, or multiple services can be started on one server. This is the principle of MySQL master-slave backup. Let's take a look at the specific synchronization configuration process.

Let's first look at the environment in which I tested it:

CentOS 6.5 MySQL master-slave synchronization, MySQL version 5.6.25

Main server: centos6.5 IP:192.168.1.101

From server: centos6.5 IP:192.168.1.102

1. Main server related configuration

1. Create a synchronization account and specify the server address

[root@localhost ~]mysql -uroot -p
mysql>use mysql
mysql>grant replication slave on *.* to 'testuser'@'192.168.1.102' identified by '12345678';
mysql>flush privileges #Refresh privileges

Authorized user testuser can only access the database of the primary server 192.168.1.101 from the address 192.168.1.102, and only has the permission to back up the database

2. Modify the /etc/my.cnf configuration file vi /etc/my.cnf

Add the following parameters under [mysqld]. If the file already exists, you do not need to add it.

server-id=1 
log-bin=mysql-bin #Start the MySQL binary log system,
binlog-do-db=ourneeddb #Database to be synchronized binlog-ignore-db=mysql #Do not synchronize the mysql system database. If there are other databases that you do not want to synchronize, continue to add [root@localhost ~]/etc/init.d/mysqld restart #Restart the service

3. Check the master status of the master server (note the File and Position items, the slave server requires these two parameters)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 | 120 | ourneeddb| mysql |
+------------------+----------+--------------+------------------+

4. Export database

Lock the database before exporting it

flush tables with read lock; #Database read-only lock command to prevent data from being written when exporting the database

unlock tables; #Unlock

Export database structure and data: mysqldump -uroot -p ourneeddb > /home/ourneeddb.sql

Export stored procedures and functions: mysqldump -uroot -p -ntd -R ourneeddb > ourneeddb_func.sql

Tips: -ntd export stored procedures, -R export functions

2. Slave server related configuration

1. Modify the /etc/my.cnf configuration file vi /etc/my.cnf

Add the following parameters under [mysqld]. If the file already exists, you do not need to add it.

server-id=2 #Set the slave server id, which must be different from the master server

log-bin=mysql-bin #Start the MySQL binary log system

replicate-do-db=ourneeddb #The name of the database that needs to be synchronized

replicate-ignore-db=mysql #Do not synchronize the mysql system database

[root@localhost~ ]/etc/init.d/mysqld restart #Restart the service

2. Import database

The import process is not described here.

3. Configure master-slave synchronization

[root@localhost~ ]mysql -uroot -p
mysql>use mysql 
mysql>stop slave;
mysql>change master to
   master_host='192.168.1.101',
   master_user='testuser',
   master_password='12345678',
   master_log_file='mysql-bin.000012',
   master_log_pos=120; #log_file and log_pos are the File and Position of the master server in the master state
mysql>start slave;
mysql>show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: testuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000012
Read_Master_Log_Pos: 120
Relay_Log_File: orange-2-relay-bin.000003
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: orange
Replicate_Ignore_DB: mysql,test,information_schema,performance_schema
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: 120
Relay_Log_Space: 1320
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: 773d2987-6821-11e6-b9e0-00163f0004f9
Master_Info_File: /home/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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

Note that Slave_IO_Running: Yes Slave_SQL_Running: Yes must be Yes and Log_File and Log_Pos must be the same as File and Position in the master state.

If they are all correct, the configuration is successful!

The above is all the content we have compiled for you about the master-slave synchronous replication configuration of MySQL database under Linux. I hope it can help you.