Implement master-slave synchronization between two MySQL Databases

Implement master-slave synchronization between two MySQL Databases

I. Overview
MySQL versions later than 3.23.15 provide the database replication function, which enables the synchronization, master-slave mode, and mutual backup of two databases.
II. Environment
Operating System: Linux 2.6.23.1-42. fc8 # SMP (XEN is not installed)
Mysql version: 5.0.45-4. fc8
Device environment: two PCs (or virtual machines)
Iii. Configuration
The settings of the Database Synchronization replication function are reflected in the MySQL configuration file. The MySQL configuration file (generally my. cnf) is/etc/my. cnf in the current environment.
3.1 set the environment:
IP settings:
Host a ip Address: 10.10.0.119
Mask: 255.255.0.0
Host B IP: 10.10.8.112
Mask: 255.255.0.0
After setting the IP address, make sure that the firewall of the two hosts is disabled. You can run the service iptables status Command to view the Firewall status. If the firewall is still running. Use service iptables stop to stop the firewall. If you want to enable or disable the firewall, you can use the setup command to disable or customize the firewall.
In the end, it is better for two hosts to ping each other.
3.2 configure master A (master B slave) Mode
3.2.1 configure A as master
1. Add an account used by the user for synchronization:
Grant file on *. * TO 'backup '@ '10. 10.8.112 'identified BY '123 ';
Grantreplication slave on *. * TO 'backup '@ '10. 10.8.112 'identified BY '123 ';
Assign the File Permission to 10.10.8.112, that is, the Server Load balancer instance. You cannot grant the File Permission to the Server Load balancer instance, but also the REPLICATION Server Load balancer permission to the Server Load balancer instance.
2. Add a database as the synchronization database:
Create database test;
3. Create a table structure:
Create table mytest (username varchar (20), password varchar (20 ));
4. modify the configuration file:
Modify the/etc/my. cnf file of A and add the following configuration in the my. cnf configuration item:
Server-id = 1 # Server id
Log-bin
Binlog-do-db = test # specify the database for logs

5. Restart the Database Service:
Service mysqld restart
View server-id:
Show variable like 'server _ id ';
Instance:
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 1 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
6. Run the show master status/G command to view the logs.
Normal:
Mysql> show master status/G
* *************************** 1. row ***************************
File: mysqld-bin.000002
Position: 198
Binlog_Do_DB: test, test
Binlog_Ignore_DB:
1 row in set (0.08 sec)
3.2.2 configure B as slave
1. Add a database as the synchronization database:
Create database test;
2. Create a table structure:
Create table mytest (username varchar (20), password varchar (20 ));
3. modify the configuration file:
Modify the/etc/my. cnf file of B and add the following configuration to the configuration items of my. cnf:
Server-id = 2
Master-host = 10.10. 0.119
Master-user = backup # synchronize user accounts
Masters-password = 1234
Master-port = 3306
Master-connect-retry = 60 # preset retry Interval 60 seconds
Replicate-do-db = test # Tell slave to only update the backup database

5. Restart the Database Service:
Service mysqld restart
View server-id:
Show variables like 'server _ id ';
Instance:
Mysql> show variables like 'server _ id ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 2 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
6. Run the show slave status/G command to view logs.
Normal:
Mysql> show slave status/G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.0.119
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-bin.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: test, test
Replicate_Ignore_DB:
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: 98
Relay_Log_Space: 236
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
1 row in set (0.01 sec)
 
 
3.2.3 Verify Configuration
Use insert, delete, and update to add, delete, modify, and query databases on host A respectively. Check whether the database on host B is consistent with that on host A. If yes, the configuration is successful.
3.3 dual-host mutual standby mode
If you add slave settings to host A and master settings to host B, you can synchronize data between host B and host.
1. Add the following settings to the mysqld configuration item in the configuration file of host:
Master-host = 10.10.8.112
Master-user = backup
Masters-password = 1234
Replicate-do-db = test
Master-connect-retry = 10
 
2. Add the following settings to the mysqld configuration item in the configuration file of B:
Log-bin
Binlog-do-db = test
 
Note: When an error occurs, the *. err log file synchronization thread exits. After the error is corrected, the synchronization mechanism should be executed to run slave start.
 
Two-way hot backup can be implemented when the server A and server B are restarted.
Iv. FAQs and Solutions
1. the permissions of the Server Load balancer instance should not only grant the File Permission to the Server Load balancer instance, but also the REPLICATION Server Load balancer permission to the Server Load balancer instance.
2. After modifying the Slave machine/etc/my. cnf, remember to delete master.info before the mysql service of the slave machine starts.
3. When show master status or show slave status is abnormal, check what. err says.
4. Mysql Replication on Slave has two threads: I/O thread and SQL thread. I/O is used to retrieve its binlog from the master port 3306 (after the master has modified anything, write the modified information to your binlog for slave update) and write it to the local relay-log, while the SQL thread reads the local relay-log, the conversion cost is what Mysql can understand, So synchronization is completed step by step. decide I/O thread is/var/lib/mysql/master.info, and decide the SQL thread is/var/lib/mysql/relay-log.info.
5. start slave. Run start slave to restart slave.

This article permanently updates the link address: