Notes on one-way and two-way synchronization of Mysql Data in llinux

Document category: Database

Keyword: MySQL master slave

Recently, due to project requirements, Mysql Data Synchronization has been set on Linux servers. Fortunately, there are a lot of network resources, and Google has all of them, but the actual operation is troublesome.
Yes, I encountered a lot of problems, but I fixed it by Google,

At the same time, I have learned a lot of relevant knowledge and will summarize it for future use. I hope it will be helpful to je people who need it;

I. Prepare the environment
There are few hardware resources for doing exercises by yourself. You can only operate on the Virtual Machine. Haha!

Linux: Red Hat Enterprise Linux Server Release 5.1
MySQL version: MySQL-server-community-5.0.67-0.rhel5.i386.rpm
MySQL-client-community-5.0.67-0.rhel5.i386.rpm

(PS: the synchronization mechanism of MySQL is based on binary log BINLOG. Different MySQL versions may lead to different formats, resulting in Data Synchronization failure. Therefore, the best combination is
MySQL version of master and slave

The same version or slave version is later than the master version. because MySQL is backward compatible, the master version cannot be later than the slave version. Otherwise, it may not be implemented.
Function; To achieve bidirectional synchronization, it is best that MySQL is the same

Version 1)

Virtual Machine version: vmware6.0

Install two Linux systems on the Virtual Machine and install MySQL separately. (For details about installing MySQL on the blog of MySQL, see the following section)

The default files installed in MySQL are:

/Var/lib/MySQL/(the database directory where all the database files created are located)

/Usr/share/MySQL (MySQL-related configuration files)

/Usr/bin (MySQL-related commands, such as MySQL mysqladmin)

To distinguish systems, name the system to facilitate the following:

Linuxa system: IP: 192.168.59.123 (master)

Linuxb system: IP: 192.168.59.124 (slave)

Disable the firewall of linuxa and linuxb # service iptables stop (ensure that the system can ping each other)

2. Set one-way Mysql Data Synchronization (linuxa (master)-> linuxb (slave ))

1. linuxa is the master

Linuxb is slave (synchronize data in the test database specified by MySQL on linuxa );

As MySQL is just installed, create the same database on MySQL of linuxa and linuxb, for example, test (the table has Stu,
Class, teacher) to ensure that the database names on the two MySQL databases are consistent with their table names and

The structure is consistent. If there is a difference, synchronization will fail;
(PS: To synchronize data, ensure that the database name is consistent with the table name and the table structure are consistent)

2. Create an account for slave (that is, linuxb) for data synchronization on MySQL as the master (that is, linuxa ).

# MySQL (Default User, such as setting the root password, use MySQL-u root-P to log on to MySQL)
Mysql> grant file on *. * to backa @ '192. 168.59.124 'identified
By 'backa'
Mysql> flush privileges;

(PS: This backa account can only be accessed through IP address 192.168.59.124)

On linuxb, test whether account backa can access MySQL on linxua:
# Mysql-u backa-p-h 192.168.59.123 (enter the password backa. The access instructions are correct)

3. Modify the my. CNF File

Because the newly installed MySQL
, The my. CNF file may not exist under the/etc directory, copy the my-medium.cnf from the/user/share/MySQL directory
To/etc and change it to my. CNF (same as that on linuxa and linuxb)
Such as # cp/user/share/MySQL/my-medium.cnf/etc/My. CNF

Modify/etc/My. CNF on linuxa (master)

Log-bin = mysql-bin (the log file exists by default. The log file in/var/lib/MySQL starts with MySQL-bin)
# Log_bin =/var/log/MySQL/mysql-bin.log)
BINLOG-do-DB = test (name of the database to be synchronized. To synchronize multiple databases, you can start another operation, such as BINLOG-do-DB = test1.
)
Server-id = 1 (MySQL ID)

Save and exit after modification, and restart MySQL such as # service MySQL restart (restart normal, indicating no configuration error)

Modify/etc/My. CNF on linxub (slave)

Server-id = 2 (MySQL ID, unique; otherwise, an error occurs during startup)
Master-host = 192.168.59.123 (synchronize the IP address of the master)
Master-user = backa (account required for synchronization)
Master-Password = backa (synchronization account password)
Master-Port = 3306 (MySQL access port in linuxa)
Replicate-do-DB = test (name of the database to be synchronized. If you want to synchronize multiple databases, you can start another row, as shown in figure
Replicate-do-DB = test1)
# Replicate-do-table = test. Stu (if you only need to synchronize the stu table in the test database)
# Replicate-do-table = test. Teacher (if you only need to synchronize the stu table in the test database)
Master-connect-retry = 60
(The number of seconds of sleep before the server (slave) thread retries to connect to the master server when the master server (master) is down or the connection is lost)

Save and exit after modification, and restart MySQL such as # service MySQL restart (restart normal, indicating no configuration error)

4. Start Synchronization

Go to MySQL of linuxa (master:

Mysql> show Master Status/g (view the current status of the master)
After running the preceding command, the result is as follows:
 

File: mysql-bin.000001 (current BINLOG log file)
Position: 98 (but the location of the former BINLOG file)
Binlog_do_db: Test (synchronize database)
Binlog_ignore_db: (data that does not need to be synchronized, which is currently set)

-------------------------------------------------------------
Go to MySQL of linuxb (slave:

Mysql> show slave status/g (view slave synchronization status)

After running the preceding command, the result is as follows:

Slave_io_state: Waiting for Master to send event (slave
To obtain the log Content of the BINLOG of the master, which is in its current status)
Master_host: 192.168.59.123 (IP address of the master to be synchronized)
Master_user: backa (account required for synchronization)
Master_port: 3306 (MySQL port number required for synchronization)
Connect_retry: 60
Master_log_file: mysql-bin.000001
(Binlong log files in the master node are invalid if they are different from the log file names in the master node)
Read_master_log_pos: 98
(The location of the binlong log file in the master, that is, the 98th location in the mysql-bin.000001 file to start synchronization, if it is different from the location on the master
Step failure)
Relay_log_file: localhost-relay-bin.000001
Relay_log_pos: 235
Relay_master_log_file: mysql-bin.000001
Slave_io_running: Yes (whether the slave Io thread is running)
Slave_ SQL _running: Yes)
Replicate_do_db: 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: 235
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

If slave_io_state: it is not waiting for Master to send event, it indicates that the actual synchronization is not performed.

There may be many reasons:
1, master_log_file: mysql-bin.000001
Read_master_log_pos: 98
The log file and location are inconsistent with the current status of linuxa (master ).
Solution: (MySQL operations on linuxb)
Mysql> slave stop; (disable slave before modification)
Mysql> change master
> Master_log_file = 'mysql-bin.000001'
(PS: Same as the log file name of the master in linuxa)
> Master_log_pso = 98 (PS: the current location of the master log file in linuxa is the same)
> Master_user = 'backa' (if the account to be synchronized is inconsistent, you can modify it here)
> Master_password = 'backa' (if the account password to be synchronized is inconsistent, you can change it here)
>;

(PS: If you re-Modify the slave configuration information in/etc/My. CNF, restart to view
The slave status still displays unmodified data. It should be the first time to read my. CNF, and then under/var/lib/MySQL/

Generate a master.info
So the second time will not read the content of my. CNF, but read the content in master.info. To make the modified my. CNF take effect, delete it.
Master.info file, restart MySQL

)
Mysql> slave start; (restart slave)

Mysql> show slave status/g (view slave status)

If the displayed slave_io_state: Waiting for Master to send event
Slave_io_running: Yes
Slave_ SQL _running: Yes
Congratulations on the success of one-way synchronization, that is, linuxa --> linuxb;
On MySQL of linuxa, insert, delete, update, and modify the table structure of test are all synchronized
On the test database on MySQL of linuxb
(PS: If you execute insert into to Stu (UUID (), 'yushan ') on the master, it will be synchronized
The IDS on slave are different because they are generated with UUID, so they are inconsistent.

Delete and update are not synchronized)

3. Bidirectional synchronization (linuxa <---> linuxb)


The best MySQL version for two-way synchronization must be consistent to ensure that the binary log BINLOG format is the same.
Insert, update, delete, alter, and other operations will affect the other MySQL.

Because one-way synchronization has been successfully set up, on this basis, add the linuxb (slave) configuration to linuxa (master)
Add the linuxa (master) configuration to the linuxb (slave), and then start

Set the binlong file to be synchronized and the current location as follows:

1. Add slave to the original master of linuxa (synchronize data in the test database specified by MySQL on linuxb)

Linuxb adds an mmaster on the basis of the original slave (synchronize the data in the test database specified on MySQL on linuxa );

2. Create an account on MySQL as the master (that is, linuxb) for slave (that is, linuxa) for data synchronization.

# MySQL (Default User, such as setting the root password, use MySQL-u root-P to log on to MySQL)
Mysql> grant file on *. * To backb @ '192. 168.59.123 'identified
By 'backb'
Mysql> flush privileges;

(PS: This backb account can only be accessed through IP address 192.168.59.123)

On linuxa, test whether account backb can access MySQL on linxub:
# Mysql-u backb-p-h 192.168.59.124 (enter the password backb. The access instructions are correct)

3. Modify the my. CNF File
 
Add the slave configuration to the/etc/My. CNF file on linuxa

# Server-id = 2
(MySQL ID. Otherwise, an error occurs during startup. Because it is already marked as 1, you can comment it out here. Only one ID is required for a MySQL instance)
Master-host = 192.168.59.124 (synchronize the IP address of linuxb (master)
Master-user = backb (account required for synchronization)
Master-Password = backb (synchronization account password)
Master-Port = 3306 (MySQL access port in linuxb)
Replicate-do-DB = test (name of the database to be synchronized. If you want to synchronize multiple databases, you can start another row, as shown in figure
Replicate-do-DB = test1)
# Replicate-do-table = test. Stu (if you only need to synchronize the stu table in the test database)
# Replicate-do-table = test. Teacher (if you only need to synchronize the stu table in the test database)
Master-connect-retry = 60
(The number of seconds of sleep before the server (slave) thread retries to connect to the master server when the master server (master) is down or the connection is lost)

Save and exit after modification, and restart MySQL such as # service MySQL restart (restart normal, indicating no configuration error)

Add the master configuration to the/etc/My. CNF file on linxub.

# Server-id = 1
(MySQL ID. Otherwise, an error occurs during startup. Because it is already marked as 2, you can comment it out here. Only one ID is required for a MySQL instance)
Log-bin = mysql-bin (the log file exists by default. The log file in/var/lib/MySQL starts with MySQL-bin)
# Log_bin =/var/log/MySQL/mysql-bin.log)
BINLOG-do-DB = test (name of the database to be synchronized. To synchronize multiple databases, you can start another operation, such as BINLOG-do-DB = test1)
 
Save and exit after modification, and restart MySQL such as # service MySQL restart (restart normal, indicating no configuration error)

4. Start Synchronization

Go to MySQL of linuxb (master:

Mysql> show Master Status/g (view the current status of the master)
After running the preceding command, the result is as follows:

File: mysql-bin.000003 (current BINLOG log file)
Position: 231 (but the location of the former BINLOG file)
Binlog_do_db: Test (synchronize database)
Binlog_ignore_db: (data that does not need to be synchronized, which is currently set)

-------------------------------------------------------------
Go to MySQL of linuxa (slave:

Mysql> show slave status/g (view slave synchronization status)

After running the preceding command, the result is as follows:

Slave_io_state: Waiting for Master to send event (slave
To obtain the log Content of the BINLOG of the master, which is in its current status)
Master_host: 192.168.59.124 (the IP address of the master to be synchronized)
Master_user: backb (account required for synchronization)
Master_port: 3306 (MySQL port number required for synchronization)
Connect_retry: 60
Master_log_file: mysql-bin.000003
(Binlong log files in the master node are invalid if they are different from the log file names in the master node)
Read_master_log_pos: 98
(The location of the binlong log file in the master, that is, the 98th location in the mysql-bin.000001 file to start synchronization, if it is different from the location on the master
Step failure)
Relay_log_file: localhost-relay-bin.000001
Relay_log_pos: 231
Relay_master_log_file: mysql-bin.000001
Slave_io_running: Yes (whether the slave Io thread is running)
Slave_ SQL _running: Yes)
Replicate_do_db: 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: 235
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

The preceding figure shows correct synchronization. If the displayed data is not
Slave_io_state: Waiting for Master to send event
Slave_io_running: Yes
Slave_ SQL _running: Yes

If it is different from the preceding three states, no synchronization is implemented. You can use the change master in the preceding one-way synchronization to reset the settings (PS: the firewall needs to be disabled)

If the preceding configurations are correct and the displayed status is the same, congratulations on the success of Bidirectional synchronization, that is, linuxa <--> linuxb synchronization;

Iv. MySQL
Data Synchronization instructions (reposted on the Internet, a lot of Google)

1. Overview of synchronization mechanism implementation

The MySQL synchronization mechanism updates and deletes all database operations based on the master node.
Are recorded in binary logs. Therefore, to enable the synchronization mechanism, binary logs must be enabled on the master node.

Each slave receives an update operation recorded in the binary log on the master. Therefore, a copy of this operation is executed on the slave. It should be very important to realize that binary day
Logs only start from enabling binary logs.

Update operations are recorded at all times. All slave data must be copied from the master when binary logs are enabled. If the Slave Data and
When binary logs are enabled on the master node

Then the slave synchronization will fail.

2. Synchronization Implementation Details

The MySQL synchronization function is implemented by three threads (one on the master node and two on the slave node. Run start slave.
Then, slave creates an I/O thread. The I/O thread connects to the master and requests the master to send binary logs.

. The master creates a thread to send the log content to the slave. This thread runs show processlist on the master.
The BINLOG dump thread in the result after the statement is. I/O thread reading on slave

The BINLOG dump thread of the master node sends the statements and copies them to the relay log (
Logs. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data.

As mentioned above, each mster/slave has three threads. Each master has multiple threads. It creates a thread for each slave connection, and each slave has only
/O and SQL threads.

In MySQL
Before 4.0.2, only two threads are required for synchronization (one for Master and one for slave ). The I/O and SQL threads on the slave are merged into one. It does not use relay logs.

The advantage of using two threads on slave is to split the read log and execution into two independent tasks. If the task is executed slowly, the log reading task will not be slowed down. For example, if
Slave stopped for a period of time, then the I/O thread

You can quickly read all the logs from the master after the slave is started, although the SQL thread may lag behind the I/O thread for several hours. If slave stops running all the SQL threads
But the I/O thread has updated all

Logs are read and saved in the local relay log. Therefore, after the slave is started again, it will continue to run them. This allows you to clear binary logs on the master, because slave has no
You need to go to the master to read the Update log.

Executing the show processlist statement in MySQL will tell us what happens on the master and slave.

On the master, the result of show processlist is as follows:

Mysql> show processlist/g

* *************************** 1. row ***************************

ID: 21

User: Root

HOST: localhost

DB: MySQL

Command: Query

Time: 0

State: NULL

Info: Show processlist

* *************************** 2. Row
(MASTER thread )***************************

ID: 25

User: Back

HOST: 192.168.1.125: 34085

DB: NULL

Command: BINLOG dump

Time: 746

State: has sent all BINLOG to slave; waiting for BINLOG to be updated

Info: NULL

2 rows in SET (0.01 Sec)

Here, thread 2 is created for an slave connection. The results show that all the unfinished update logs have been sent to slave, and the master is waiting for the new update log to happen.

On slave, the result of show processlist is as follows:

Mysql> show processlist/g

* *************************** 1. row ***************************

ID: 2

User: system user

Host:

DB: NULL

Command: connect

Time: 70479

State: Waiting for Master to send event

Info: NULL

* *************************** 2. row (I/O thread )***************************

ID: 3

User: system user

Host:

DB: NULL

Command: connect

Time: 16971

State: has read all relay log; waiting for the slave I/O thread
Update it

Info: NULL

* *************************** 3. row (SQL thread )***************************

ID: 8

User: Root

HOST: localhost

DB: NULL

Command: Query

Time: 0

State: NULL

Info: Show processlist

3 rows in SET (0.00 Sec)

This indicates that thread 2 is an I/O thread and is being connected to the master; thread 3 is an SQL thread that performs the update operation in the relay log. Now, both threads are idle and waiting for new threads
.

Note that the value of the time field tells us how long the log on the slave is later than the master.

3. Master synchronization thread status

The following lists the most common statuses in the BINLOG dump thread state field of the master. If there is no BINLOG dump on the master
The synchronization is not running. That is to say, there is no slave connection.

.

Sending BINLOG event to slave

An event is composed of binary logs. An event is usually composed of an update statement and other information. The thread reads an event and sends it to slave.

Finished reading one BINLOG; switching to next BINLOG

After reading a binary log, switch to the next one.

Has sent all BINLOG to slave; waiting for BINLOG to be updated

You have read all the incomplete update logs and sent them to slave. It is idle and is waiting to execute new update operations on the master to generate new things in binary logs.
And then read them.

Waiting to finalize termination

The current thread has stopped. This time is short.

4. Slave I/O thread status

The following lists the most common states in the slave I/O thread state field. Starting from MySQL 4.1.1, this status is running show slave
The slave_io_state field of the status statement result also appears. This means

You can only execute the show slave status statement to learn more.

Connecting to master

This thread certificate tries to connect to the master.

Checking master version

Determine a transient status after the master node is connected.

Registering slave on Master

Determine a transient status after the master node is connected.

Requesting BINLOG dump

Determine a transient status after the master node is connected. This thread sends a request to the master to inform it of the binary file to be requested and the starting position.

Waiting to reconnect after a failed BINLOG Dump Request

If a binary log Dump Request fails (due to disconnection), the thread enters this state during sleep and reconnects regularly. The reconnection interval is

-- Master-connect-retry option.

Reconnecting after a failed BINLOG Dump Request

This thread is trying to reconnect to the master.

Waiting for Master to send event

Connected to the master, waiting for it to send binary logs. If the master node is idle, the status may last for a long time.
Slave_read_timeout seconds. Then, it will

Consider disconnecting and try again.

Queueing master event to the relay log

An event has been read and is being copied to the relay log for processing by the SQL thread.

Waiting to reconnect after a failed master event read

An error occurred while reading the log (due to disconnection ). This thread sleep for Master-connect-retry seconds before re-connection.

Reconnecting after a failed master event read

Attempting to reconnect to the master. After the connection is confirmed, the status changes to waiting for Master to send event.

Waiting for the slave SQL thread to free enough relay log Space

Relay_log_space_limit
The value is not zero. The total size of the relay log exceeds this value. The I/o thread waits for the SQL thread to process the relay logs and then delete them to free up enough space.

Waiting for slave mutex on exit

The current thread has stopped. This time is short.

5. Slave SQL thread status

The following lists the most common statuses in the slave SQL thread state field:

Reading event from the relay log

Read an event from the relay log for execution.

Has read all relay log; waiting for the slave I/O thread to update it

All the events in the relay log have been processed and are waiting for the I/O thread to write the updated log.

Waiting for slave mutex on exit

The current thread has stopped. This time is short.

The state field of the SQL thread may also be an SQL statement. This means that it reads an event from the relay log, extracts the SQL statement from it, and runs it.


(MySQL synchronization schematic drawn by myself)

 

 

Connection to related Mysql Data Synchronization data:

 

Http://imysql.cn/node/58

Http://deidara.blog.51cto.com/400447/122039

Http://www.phpx.com/tech/database/2009-01-15/65.html

Http://www.cnscn.org/htm_data/288/0811/17463.html

Http://www.koven.org/archives/203.html

Http://www.cnblogs.com/harryguo/archive/2008/03/10/1099223.html

Http://www.blogjava.net/lzj520/archive/2008/02/27/182485.html