MySQL Master/Slave replication and semi-synchronous Replication

Mysql master-slave and master-slave Replication
Mysql master-slave and master-slave Replication
I. Introduction to replication
MySQL supports one-way and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers
Act as the slave server. The master server writes updates to the binary log file and maintains an index of the file to track log loops.
These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server
Location of the last successful update read by the server in the log. The slave server receives any updates from then on, and then
Block and wait for the master server to notify you of new updates.
Note that when you perform a copy, all updates to the tables in the copy must be performed on the master server. Otherwise, you must be careful,
To avoid conflicts between updates to tables on the master server and those on the slave server.
Unidirectional replication facilitates robustness, speed, and system management:
L The master server/slave server settings increase robustness. When a problem occurs on the master server, you can switch to the slave server
Copies.
L better customer response time can be obtained by splitting the customer query load between the master server and slave server.
SELECT queries can be sent to the slave server to reduce the query processing load of the master server. However, the statement for modifying data is still
It should be sent to the master server for synchronization between the master server and the slave server. If the query is not updated, the load is
The balance policy is effective, but is generally used to update queries.
L another advantage of using replication is that one slave server can be used for backup without interfering with the master server. In the backup
During the process, the master server can continue to process updates.
MySQL provides the Database Synchronization function, which enables database redundancy, backup, recovery, and load balancing.
Very helpful
MySQL uses three threads to execute the replication function (one of them is on the master server and the other two are on the slave server. When issued
When you start slave, create an I/O thread from the server to connect to the master server and send binary logs to the master server.
The master server creates a thread to send the binary log content to the slave server. Read master services from server I/O threads
The Binlog Dump thread sends the content and copies the data to a local file in the data directory of the server.
Logs. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in the logs.
The show processlist statement can be used to query information about replication on the master server and slave server.
The default relay log uses a file name in host_name-relay-bin.nnnnnn format, where host_name is from the service
Host Name. nnnnnn is the serial number. Create a continuous relay log file with a continuous serial number, starting from 000001. Slave Server
The server tracks relay log index files to identify currently used relay logs. The default relay log index file name is
Host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. Relay day
Logs are in the same format as binary logs and can be read using mysqlbinlog. When the SQL thread completes the relay log
After all events, relay logs are automatically deleted.
The slave server creates two more state files in the data directory-master.info and relay-log.info. Save Status File
On the hard disk, the slave server will not be lost when it is disabled. When starting the next time from the server, read these files to make sure they have been
The number of binary logs read by the server and the degree to which the server processes its own relay logs.
Ii. experiment environment
Virtual Machine Operating System: Centos 5.5 64bit
Database Version: mysql 5.1.49 (refer to "install Mysql with yum in Centos 5)
A: master computer name: beijing IP Address: 192.168.20.101
B: slave computer name: shanghai IP Address: 192.168.20.102
Iii. One-way replication of mysql
Note that the mysql database version must be the same for both databases, or the slave version is lower than the master version!
1. Set a connection account for replication on the master server. This account must be granted the replication slave permission. If
An account is only used for replication (recommended), so no additional permissions are required.
# Mysql-uroot-p123456
Mysql> grant replication slave on *. *
'Replicase' @ '192. 192.% 'identified by '123 ′;
2. Create a test database test1 on the master server
Mysql> create database test1;
Mysql> use test1;
Mysql> create table user (id int (4), name varchar (20 ));
Mysql> insert into user values (1, "mary ");
Mysql> insert into user values (2, "joe ");
// Refresh the permission to make the settings take effect
Mysql> Flush privileges;
3. Configure my. cof for the master server
// Do not exit the mysql client. Add the following content to the/etc/my. cnf configuration file:
Log-bin = mysql-bin # Start the binary log system
Server-id = 1 # ID of the local database as the master server
Log-bin =/var/log/mysql/updatelog # sets the log file name to be generated. The path here does not contain mysql
The directory must be manually created and granted to its mysql users.
Binlog-do-db = test1 # Name of the binary database to be synchronized
Binlog-ignore-db = mysql, test # Avoid synchronizing mysql user configurations to avoid unnecessary troubles
// Create a directory for updating logs and grant mysql user permissions
# Mkdir/var/log/mysql
# Chown-R mysql. mysql/var/log/mysql
4. Execute the flush tables with read lock statement to clear all TABLES and block write statements, and synchronize them locally.
Package and copy the database to the slave Database
Mysql> flush tables with read lock;
// Back up the data directory of the master server on another terminal
# Cd/var/lib/mysql/
# Tar-cvf/tmp/mysqldb.tar test1/
// Remotely copy data to the slave server. You need to enter the root password of the slave server when using this copy.
# Scp/tmp/mysqldb.tar root@192.168.20.128:/var/lib/mysql
// Unlock the master server
Mysql> UNLOCK TABLES
// Restart the mysql Service
#/Etc/init. d/mysqld restart
5. Configure the slave server
// Configure the slave server/etc/my. cnf file and add the following content:
Server-id = 2 # slave server ID, do not be the same as the master id
Master-host = 192.168.0000155 # specify the IP address of the master server
Master-user = replication # specify the user name that can be synchronized on the master server
Master-password = 123456 # password
Master-port = 3306 # port used for synchronization
Master-connect-retry = 60 # time when the breakpoint is reconnected
Replicate-ignore-db = mysql # block synchronization of mysql Databases
Replicate-do-db = test1 # Name of the database to be synchronized
6. Attach the slave server to the master server database
# Cd/var/lib/mysql/
# Tar xvf mysqldb.tar
# Rm mysqldb.tar
#/Etc/init. d/mysqld restart
// Start the slave server thread:
# Mysql-uroot-p123456
Mysql> start slave;
7. Verify Configuration
// Master server:
Mysql> show master status;
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000004 | 106 | test1, netseek | mysql, test |
+ ------ + ---- + ----- + ------ +
// (If you suspect that the Master/Slave Data is not synchronized before synchronization, you can use the cold Backup Remote COPY method or the command line on the slave server.
Synchronization Method) run the MySQL command on the slave server:
Mysql> slave stop; # STOP the slave service first
Mysql> change master to MASTER_LOG_FILE = 'updatelog. 100', MASTER_LOG _
POS = 106;
// Perform the binary database record regression of the slave server based on the show master status result of the master server above,
Achieve the synchronization effect
Mysql> slave start; # START the SLAVE server Synchronization Service
// Use show slave statusG to check the synchronization status of the slave server.
Mysql> show slave statusg;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
If yes, it indicates that the synchronization is in progress.
8. Test
// Create a table on the master server
Mysql> use test1;
Mysql> create table name (id int (4), name varchar (20 ));
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
+ ------ +
| Name |
| User |
+ ------ +
2 rows in set (0.01 sec)
// Query on the slave server
Mysql> use test1;
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
+ ------ +
| Name |
| User |
+ ------ +
2 rows in set (0.00 sec)
A single copy test is successful !!!!
Iv. Bidirectional synchronization Configuration
1. modify the configuration of the original slave server
192.168.255.102
// Configure the original slave server/etc/my. cnf file and add the red content:
Server-id = 2 # slave server ID, do not be the same as the master id
Master-host = 192.168.0000155 # specify the IP address of the master server
Master-user = replication # specify the user name that can be synchronized on the master server
Master-password = 123456 # password
Master-port = 3306 # port used for synchronization
Master-connect-retry = 60 # time when the breakpoint is reconnected
Replicate-ignore-db = mysql # block synchronization of mysql Databases
Replicate-do-db = test1 # Name of the database to be synchronized
Log-bin =/var/log/mysql/updatelog # Set the log file name to be generated.
Binlog-do-db = test1 # Set the name of the synchronized Database
Binlog-ignore-db = mysql # Avoid synchronizing mysql user configurations to avoid unnecessary troubles
2. Create a directory for updating logs and grant mysql user permissions.
# Mkdir/var/log/mysql
# Chown-R mysql. mysql/var/log/mysql
3. Restart the mysql service and create a dedicated account for synchronization.
# Service mysqld restart
// Grant replication permission to slave server users
# Mysql-uroot-p123456
Mysql> grant replication slave on *. *
'Replicase' @ '192. 192.% 'identified by '123 ′;
// Refresh the permission to make the settings take effect
Mysql> Flush privileges;
4. modify the original master configuration file
192.168.20.101
// Configure the original master server/etc/my. cnf file and add the red content:
Log-bin = mysql-bin # Start the binary log system
Server-id = 1 # master database ID
Log-bin =/var/log/mysql/updatelog # sets the log file name to be generated. The path here does not contain mysql
The directory must be manually created and granted to its mysql users.
Binlog-do-db = test1 # Name of the binary database to be synchronized
Binlog-ignore-db = mysql, test # Avoid synchronizing mysql user configurations to avoid unnecessary troubles
Master-host = 192.168.20.128 # configure synchronization updates from the original slave Database
Master-user = replication # update a user
Master-password = 123456 # password
Master-port = 3306 # port
Replicate-do-db = test1 # database to be updated
// Restart the mysql Service
# Service mysqld restart
// Query on server B
192.168.255.102
# Mysql-uroot-p123456
Mysql> show master status;
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Updatelog.000001 | 106 | test1 | mysql |
+ ------ + ---- + ----- + ------ +
1 row in set (0.00 sec)
// Query on server
192.168.20.101
# Mysql-uroot-p123456
Mysql> show master status;
// Stop the slave service first
Mysql> slave stop;
Mysql> change master to MASTER_HOST = '192. 168.20.128 ', MASTER_USER = 'repli
Cation ', MASTER_PASSWORD = '000000', MASTER_PORT = 123456, MASTER_LOG_FILE = 'upda
Telog.000001', MASTER_LOG_POS = 106;
// Perform the binary database record regression of the slave server based on the show master status result of the master server above,
Achieve the synchronization effect
// Start server B Synchronization Service
192.168.255.102
Mysql> slave start;
5. Verify Configuration
// Enter the mysql command line on server
192.168.20.101
Mysql> show slave statusg;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Here, Slave_IO_Running and Slave_ SQL _Running should both be yes, indicating that the slave database's I/O and Slave_ SQL threads both
Enabled correctly. indicates that the database is being synchronized.
// Enter the mysql command line on server B
192.168.255.102
Mysql> show slave statusg;
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Here, Slave_IO_Running and Slave_ SQL _Running should both be yes, indicating that the slave database's I/O and Slave_ SQL threads both
Enabled correctly. indicates that the database is being synchronized.
6. Test
// Create A table on server
192.168.20.101
Mysql> use test1;
Mysql> create table test1 (id int (4), name varchar (20 ));
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
+ ------ +
| Name |
| Test1 |
| User |
+ ------ +
3 rows in set (0.00 sec)
// Query on server B
192.168.255.102
Mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-
Database changed
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
+ ------ +
| Name |
| Test1 |
| User |
+ ------ +
3 rows in set (0.00 sec)
// Create a table on server B
192.168.255.102
Mysql> create table test2 (id int (4), name varchar (20 ));
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
+ ------ +
| Name |
| Test1 |
| Test2 |
| User |
+ ------ +
4 rows in set (0.00 sec) | user |
+ ------ +
3 rows in set (0.00 sec)
// Query on server
192.168.20.101
Mysql> show tables;
+ ------ +
| Tables_in_test1 |
| Name |
| Test1 |
| Test2 |
| User |
+ ------ +
4 rows in set (0.00 sec)
Bidirectional replication test successful !!!
 
Deployment of the semi-synchronous replication function of mysql5.5.9

1. mysql5.5.9 semi-synchronous replication:

MySQL supports semi-synchronous Replication (Semisynchronous Replication),

It is not yet native support. It is supported through plugin, and this plug-in is not installed by default.

This plug-in is generated by default for both binary release and source code compilation,
One is for the master instance and the other is for the slave instance. before using the plug-ins, install the two plug-ins first.

First, check whether mysql supports dynamic addition of plug-ins,

Mysql. sock @ mysql> select @ have_dynamic_loading;
+ ------------------------ +
| @ Have_dynamic_loading |
+ ------------------------ +
| YES |
+ ------------------------ +
1 row in set (0.00 sec)

Supports dynamic addition and deletion of plug-ins,

Add plug-ins:

Mysql. sock @ (none)> install plugin rpl_semi_sync_master soname

Semisync_master.so;
Query OK, 0 rows affected (0.00 sec)

Mysql. sock @ (none)> install plugin rpl_semi_sync_slave soname

Semisync_slave.so;
Query OK, 0 rows affected (0.00 sec)

After the plug-in is added, several system parameters are added by default.
Mysql. sock @ (none)> show global variables like rpl_semi_sync %;
+ ------------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------------ + ------- +
| Rpl_semi_sync_master_enabled | OFF |
| Rpl_semi_sync_master_timeout | 10000 |
| Rpl_semi_sync_master_trace_level | 32 |
| Rpl_semi_sync_master_wait_no_slave | ON |
| Rpl_semi_sync_slave_enabled | OFF |
| Rpl_semi_sync_slave_trace_level | 32 |
+ ------------------------------------ + ------- +
6 rows in set (0.00 sec)

These parameters can be dynamically modified.

Rpl_semi_sync_master_enabled:
Starting the master node supports semi-synchronous replication.

Rpl_semi_sync_master_timeout:
Timeout interval returned by the master database waiting for semi-synchronous replication information. The default value is 10 seconds.

Rpl_semi_sync_master_trace_level:
Monitoring level:
1 = general level (for example, time function failures)

16 = detail level (more verbose information)

32 = net wait level (more information about network waits)

64 = function level (information about function entry and exit)

Rpl_semi_sync_master_wait_no_slave:

Whether to allow the master to wait for the slave receipt signal after each transaction is submitted.
The default value is on, and every transaction will wait. If the slave fails, when the slave catches up with the master log

, You can automatically switch to the semi-Sync Mode. If it is off, the slave will not be synchronized after it is caught up.

The method is copied and needs to be manually started.

Rpl_semi_sync_slave_enabled:
Start slave to support semi-synchronous replication.

Rpl_semi_sync_slave_trace_level:
Monitoring level, which is the same as the preceding rpl_semi_sync_master_trace_leve.

 

Corresponding system status variables:

Mysql. sock @ mysql> show global status like rpl_semi_sync %;
+ ------------------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------------------ + ------- +
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 902 |
| Rpl_semi_sync_master_net_wait_time | 902 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 501 |
| Rpl_semi_sync_master_tx_wait_time | 501 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_semi_sync_slave_status | OFF |
+ ------------------------------------------ + ------- +
15 rows in set (0.00 sec)

Rpl_semi_sync_master_clients:
Record the number of slave instances that support semi-synchronization.

Rpl_semi_sync_master_net_avg_wait_time:
Average waiting time for the master to wait for the slave reply. Unit: milliseconds.

| Rpl_semi_sync_master_net_wait_time:
Total master wait time.

Rpl_semi_sync_master_net_waits:
The total number of waits for the master to wait for the slave reply.

Rpl_semi_sync_master_no_times:
The number of times the master disables semi-synchronous replication.

Rpl_semi_sync_master_no_tx:
The number of times that the master node has not received a response from the server Load balancer instance.

Number)

Rpl_semi_sync_master_status:
Mark whether the master is in the semi-synchronous replication status.

Rpl_semi_sync_master_timefunc_failures:
The number of times the master failed when calling time functions such

Gettimeofday ().

Rpl_semi_sync_master_tx_avg_wait_time:
The average wait time that the master spends on each transaction.

Rpl_semi_sync_master_tx_wait_time:
Total number of master waits.

Rpl_semi_sync_master_wait_pos_backtraverse:
What I understand is the number of times that I come first, but not yet.
The total number of times the master waited for an event with binary

Coordinates lower than events waited for previusly. This can occur when

The order in which transactions start waiting for a reply is different from

The order in which their binary log events are written.

Rpl_semi_sync_master_wait_sessions:
The number of sessions currently waiting due to the slave reply.

 

Rpl_semi_sync_master_yes_tx:
The number of times the master successfully received the slave reply.

Rpl_semi_sync_slave_status:
Indicates whether the slave is in the semi-sync status.

 

========================================================== ======================
The procedure for configuring semi-sync is simple:

1. Set up the replication according to the general configuration of asynchronous replication.

2. Start asynchronous replication.

3. When the slave catches up with the master Status, stop the slave:

4. Modify the semi-sync parameters of the Master/Slave database:
 
Master database execution:
Set global rpl_semi_master_enabled = 1;

Set global rpl_semi_sync_master_timeout = 1000;

Slave database execution;
Set global rpl_semi_slave_enabled = 1;

5. Start slave from the database:

Start slave;

6. Check the parameters and adjust the value of global rpl_semi_sync_master_timeout Based on the replication status.