Master slave synchronous replication configuration of MySQL database under Linux

The advantage of master-slave synchronization configuration of MySQL database under Linux is that this method can be regarded as a backup method to realize the separation of reading and writing and relieve the pressure of one database. The speed and efficiency of running massive data are greatly improved. The master-slave replication of Mysql requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, and multiple services can also be started on one server. This is the principle of MySQL master-slave backup. Let's look at the specific synchronization configuration process.

Let's first look at the environment in which this site is tested:

CentOS 6.5 MySQL Master-Slave Synchronization, MySQL Version 5.6. 25

Primary Server: centos6.5 IP: 192.168. 1.101

Slave server: centos6.5 IP: 192.168. 1.102

1. Primary 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 permissions 

Authorized user testuser can only access the database of Master Server 192.168. 1.101 from the address 192.168. 1.102, and only have database backup rights

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

[mysqld], without adding the following parameters if they already exist in the file

server-id=1 
log-bin=mysql-bin # Start MySQL2 Binary log system, 
binlog-do-db=ourneeddb # Databases that need to be synchronized 
binlog-ignore-db=mysql # Asynchronous mysql System database, if there are other ones that do not want to synchronize, continue to add them 
[root@localhost ~]/etc/init.d/mysqld restart # Restart service 

3. Check the master status of the master server (note the File and Position items, which are required by the slave server)

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

4. Export the 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

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

tips:-ntd exports stored procedures,-R exports functions

2. Slave server-related configuration

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

[mysqld], without adding the following parameters if they already exist in the file

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

log-bin=mysql-bin # Start MySQ2 Binary Log System

replicate-do-db=ourneeddb # Database name to be synchronized

replicate-ignore-db=mysql # Asynchronous mysql System Database

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

2. Import the 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 Is the primary server master State of File And Position
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 master state

If they are all correct, the configuration is successful!

The above is all the contents of the master-slave synchronous replication configuration of MySQL database sorted out by us, hoping to help you.