Mysql master-slave synchronization configuration and non-synchronization issues

This article describes the master-slave synchronization configuration of mysql in detail, including the master-slave configuration in linux and windows and possible solutions.

This article describes the master-slave synchronization configuration of mysql in detail, including the master-slave configuration in linux and windows and possible solutions.

Master-slave synchronization in linux
Master Server IP: 250.110.120.119
Slave Server IP: 110.120.119.250

1. Create a user on the master server for Synchronous use on the slave server.

The Code is as follows:
Grant all privileges on *. * TO 'gbu' @ '1970. 120.119.250. 'identified BY 'gbu ';

Create an account with all operation permissions with the username tongbu and password tongbu.

2. Modify the my. cnf file of the master server
In fact, this step can be omitted, because you can keep the default configuration file.
/*
Add
Server-id = 1
And enable the log-bin binary log file.
Log-bin = mysql-bin
Note: remove the default server-id = 1.
*/

3. Restart the mysql database of the master server.

4. Go to the mysql interface and execute the following command to obtain the log file name and offset.

The Code is as follows:
Show master status; Shown: Mysql> show master status; + ------ + ---- + ----- + ------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------ + ---- + ----- + ------ + | Mysql-bin.000010 | 106 | + ------ + ---- + ----- + ------ + 1 row in set (0.00 sec)

The resulting log file name is a mysql-bin.000010 offset of 106

5. modify my. cnf of the slave server
Add
Server-id = 2
Note: remove the default server-id = 1.

6. Restart the slave server mysql database

7. Set the slave database, which is the most important part.

The Code is as follows:
Change master to master_host = '192. 110.120.119 ', master_user = 'gbu', master_password = 'gbu', master_log_file = 'mysql-bin.000010', master_log_pos = 250;

8. Start the slave server

Start slave;
9. Execute the following command to view the process

Show processlistG
Shown below

The Code is as follows:

Mysql> show processlistG * *************************** 1. row *************************** Id: 4 User: root Host: localhost Db: test Command: Query Time: 0 State: NULL Info: show processlist * *************************** 2. row *************************** Id: 5 User: system user Host: Db: NULL Command: Connect Time: 1773 State: Waiting for master to send event Info: NULL * *************************** 3. row *************************** Id: 6 User: system user Host: Db: NULL Command: Connect Time: 1630 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL 3 rows in set (0.00 sec)

10. View slave server status

The Code is as follows:

Show slave statusG Shown below Mysql> show slave statusG * *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 250.110.120.119 Master_User: tongbu Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 281 Relay_Log_File: up2-relay-bin.000002 Relay_Log_Pos: 426 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_ SQL _Running: Yes Replicate_Do_DB: 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: 281 Relay_Log_Space: 579 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: 1 row in set (0.00 sec)

Test:
1. Create a user table in the master server test database

The Code is as follows:
Mysql> use test; Mysql> create table user (id int );

2. view the user table on the slave server

The Code is as follows:
Mysql> use test; Mysql> show tables like 'user '; + -------- + | Tables_in_test (user) | + -------- + | User | + -------- + 1 row in set (0.00 sec)

The Master/Slave Data is successfully synchronized.

Windows master-slave Synchronization

The media responsible for transmitting various modification actions on the master and slave servers is the binary change log of the master server, which records the various modification actions to be transmitted to the slave server. Therefore, the master server must activate the binary log function. The slave server must have the permission to connect to the master server and request the master server to transmit the binary change log to it.

Environment:
MySQL database versions A and B are 5.0.18
A:
Operating System: Windows 2003
IP Address: 10.100.0.100
B:
Operating System: Windows 2000 server
IP Address: 10.100.0.200

Configuration process:
1. Create A backup account in database A. The command is as follows:

The Code is as follows:
Grant replication slave, RELOAD, super on *.* TO backup @ '10. 100.0.200' Identified by '20140901 ';

Create an account backup and allow login only from 10.100.0.200. The password is 1234.

2. Because the new password algorithm of mysql version is different, go to mysql and enter:

The Code is as follows:
Set password for 'backup '@ '10. 100.0.200' = old_password ('123 ');

3. Shut down server A, copy the data in server A to server B, synchronize the data in server A and server B, and ensure that before all the settings are completed, do not perform write operations on server A and server B so that the data in the two databases must be the same!

4. modify the configuration of server A, open the mysql/my. ini file, and add the following content under [mysqld:

The Code is as follows:
Server-id = 1 Log-bin = c: log-bin.log

Server-id: ID value of master server
Log-bin: Binary Change Daily Value

4. Restart server A. From now on, it will record the database changes related to the customer heap into the binary change log.

5. Shut down server B and configure Server B's Jinxi so that it can know its image ID, where to find the master server, and how to connect to the server. The simplest case is that the master and slave servers run on different hosts respectively and use the default TCP/IP ports as long as the mysql/my. add the following commands to the INI file.

The Code is as follows:
[Mysqld] Server-id = 2 Master-host = 10.100.0.100 Master-user = backup Masters-password = 1234 // Optional Replicate-do-db = backup

Server-id: ID of slave server B. Note that it cannot be the same as the ID of the master server.
Master-host: the IP address of the master server.
Master-user: The account connecting the slave server to the master server.
Master-password: the account and password used to connect the slave server to the master server.
Replicate-do-db: tells the master server to only synchronize images for the specified database.

6. Restart slave server B. Now all settings are complete. Update the data in A, and B will also perform synchronization updates immediately. If the slave server is not synchronously updated, you can view the mysql_error.log file on the slave server to troubleshoot the error.

8. view some log commands
1. show master statusG;
Here, we mainly check whether the log-bin files are the same.
Show slave statusG;
Here we mainly look:
Slave_IO_Running = Yes
Slave_ SQL _Running = Yes
If Yes, the configuration is successful.

2. Enter show processlistG on the master;

The Code is as follows:
Mysql> SHOW PROCESSLISTG * *************************** 1. row *************************** Id: 2 User: root Host: localhost: 32931 Db: NULL Command: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog Be updated Info: NULL

If Command: Binlog Dump appears, the configuration is successful.

Solve the Problem of master-slave synchronization in MySQL

1. Stop the Slave service first:

Mysql> slave stop2. check the host status on the master server:
Record the value corresponding to File and Position.

The Code is as follows:

Mysql> show master status; + ------------------ + ----------- + -------------- + ------------------ + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------ + ----------- + -------------- + ------------------ + | Mysql-bin.000020 | 135617781 | + ------------------ + ----------- + -------------- + ------------------ + 1 row in set (0.00 sec) 3. perform manual synchronization on the slave server: Mysql> change master > Master_host = 'master _ ip ', > Master_user = 'user ', > Master_password = 'pwd ', > Master_port = 3307, > Master_log_file = 'mysql-bin.20.20 ', > Master_log_pos = 135617781; 1 row in set (0.00 sec) Mysql> slave start; 1 row in set (0.00 sec) Check the slave status again and find: Slave_IO_Running: Yes Slave_ SQL _Running: Yes

...
Seconds_Behind_Master: 0 as we know, because the data replication mechanism is completely based on the incremental SQL statements executed on the master to be propagated to the secondary server and run successfully. This requires that data in the primary and secondary databases be consistent before this mechanism is run. In addition, when this mechanism is run, the secondary database prohibits other SQL statements from being transmitted from the primary database). However, inconsistency may still occur during running, which may cause the communication to fail. Therefore, if a master-slave problem occurs, you must first solve the synchronization location problem and repair the lost data.

Solve the Problem of master-slave synchronization in MySQL

Problem description: master-slave replication fails to be synchronized from the server at a specific time and an error message is returned.

Message content: a table in a database is missing.

Cause: the Master/Slave service is not synchronized for a long time. The master database has generated many new tables or databases, but the slave database does not.

Procedure:
(1) log on to the master server and check the status of the master server.

The Code is as follows:
Mysql> show master status;

(2) log on to the slave server and perform the synchronization operation. [Code]

The Code is as follows:
Mysql> stop slave; mysql> change master to... (omitted here); mysql> start slave;

(3) view the status on the server [code]

The Code is as follows:
Mysql> show slave statusG

If the error message is returned, the table or database is missing. If the error message is missing, it is copied directly from the master server through scp, and then the process is repeated (1 )~ (3) until no error is reported