Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to achieve master-slave replication and semi-synchronous replication in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. introduction of replication

MySQL supports one-way, asynchronous replication, in which one server acts as the primary server and one or more other servers

Act as a slave server. The primary server writes updates to the binary log file and maintains an index of the file to track the log cycle.

These logs can record updates sent to the slave server. When a slave server connects to the master server, it informs the master server that the

The location of the last successful update read by the server in the log. Receive any updates that have occurred since then from the server, and then

Block and wait for the primary server to notify the new update.

Please note that when you replicate, all updates to the replicated tables must be made on the primary server. Otherwise, you have to be careful.

To avoid conflicts between user updates to the tables on the master server and updates to the tables on the slave server.

One-way replication is good for robustness, speed, and system management:

L Master / Slave server settings add robustness. When there is a problem with the master server, you can switch to the slave server as the

A copy.

Better customer response time can be achieved by splitting the load of processing customer queries between the master server and the slave server.

SELECT queries can be sent to the slave server to reduce the query processing load on the master server. But the statement that modifies the data is still

Should be sent to the master server so that the master server and the slave server are synchronized. If the non-update query is dominant, the load is all

The balance strategy is effective, but it is usually to update the query.

Another benefit of using replication is that you can use a slave server to perform backups without interfering with the master server. In backup

The primary server can continue to process updates during the process.

MySQL provides the function of database synchronization, which is important for us to realize database redundancy, backup, recovery, load balancing and so on.

It's very helpful.

MySQL uses three threads to perform the replication function (one on the master server and the other two on the slave server. When issued

In case of START SLAVE, create an Icano thread from the server to connect to the master server and have the master server send binary logs.

The master server creates a thread to send the contents of the binary log to the slave server. Read the main service from the server Iswap O thread

The Binlog Dump thread sends the content and copies the data to the local file in the data directory of the slave server, that is, relay

Journal. The third thread is the SQL thread, which is used from the server to read the relay log and perform updates contained in the log.

The SHOW PROCESSLIST statement can query information about replication that occurs on the master and slave servers.

The default relay log uses a file name in the form of host_name-relay-bin.nnnnnn, where host_name is from the service

Device hostname, nnnnnn is the serial number. Create a continuous relay log file with a sequential serial number, starting at 000001. Obedience

The server tracks the relay log index file to identify the relay log currently in use. 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

The log is in the same format as the binary log and can be read with mysqlbinlog. When the SQL thread finishes executing the

After all events, the relay log will be deleted automatically.

From the server, create two additional state files-master.info and relay-log.info-in the data directory. State file saving

On the hard drive, it will not be lost when the slave server is turned off. The next time you boot from the server, read these files to make sure that it has started from the master server

How many binary logs are read by the server, and the extent to which it handles its own relay logs.

Second, the experimental environment

Operating system: Centos 5.5 64bit

Version: 5.1.49 (refer to the "Centos 5 install Mysql using yum" document)

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

Pay attention to the version of the mysql database, the two database versions should be the same, or slave is lower than the master version!

1. Set up a connection account for replication on the primary server. The account must be granted REPLICATION SLAVE permission. If

If the account is used for replication only (recommended), no other permissions need to be granted.

# mysql-uroot-p123456

Mysql > GRANT REPLICATION SLAVE ON *. * TO

BY '123456'

2. Set up the 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 permissions to make the settings take effect

Mysql > Flush privileges

3. Configure the my.cof of the master server

/ / do not quit the mysql client program, add the following to the / etc/my.cnf configuration file

Log-bin=mysql-bin # starts the binary logging system

Server-id=1 # Native database ID marked as the primary server

Log-bin=/var/log/mysql/updatelog # sets the name of the generated log file. There is no mysql in the path here.

The directory is created manually and given the permissions of the mysql user.

Binlog-do-db=test1 # database name that needs to be synchronized in the binary

Binlog-ignore-db=mysql,test # avoid synchronizing mysql user configuration to avoid unnecessary hassle

/ / create a directory for update logs and give permissions to mysql users

# mkdir / var/log/mysql

# chown-R mysql.mysql / var/log/mysql

4. Execute the FLUSH TABLES WITH READ LOCK statement to clear all table and block write statements and synchronize the local needs

Package and copy the database to the slave database

Mysql > FLUSH TABLES WITH READ LOCK

/ / back up the main server data directory at another terminal

# cd / var/lib/mysql/

# tar-cvf / tmp/mysqldb.tar test1/

/ / remote copy to the slave server. You need to enter the slave server root password when passing this copy.

# scp / tmp/mysqldb.tar

/ / unlock the master server

Mysql > UNLOCK TABLES

/ / restart the mysql service

# / etc/init.d/mysqld restart

5. Configure slave server

/ / configure the slave server / etc/my.cnf file and add the following:

Server-id=2 # Slave server ID number, not the same as the master ID

Master-host=192.168.20.155 # specify the IP address of the primary server

Master-user=replication # specifies the user name that can be synchronized on the primary server

Master-password=123456 # password

Master-port=3306 # Port used for synchronization

Master-connect-retry=60 # breakpoint reconnection time

Replicate-ignore-db=mysql # masks synchronization of mysql libraries

Replicate-do-db=test1 # name of the synchronized database

6. Install the slave server in 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 the 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 and slave data are out of sync before synchronization, you can take the method of cold backup remote copy above or order the line on the slave server.

Synchronization method) when executing the MySQL command from the server:

Mysql > SLAVE STOP; # stop the slave service first

Mysql > CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000004',MASTER_LOG_

POS=106

/ / regress the binary database records of the slave server according to the results of the show master status of the master server above

Achieve the effect of synchronization

Mysql > SLAVE START; # start synchronization service from server

/ / use show slave statusG; to see the synchronization of the slave server.

Mysql > SHOW SLAVE STATUSG

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If it's all yes, it means it's already in sync.

8. Testing

/ / create a table on the primary 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.01sec)

/ / query from the server

Mysql > use test1

Mysql > show tables

+-+

| | Tables_in_test1 |

+-+

| | name |

| | user |

+-+

2 rows in set (0.00 sec)

The single copy experiment is successful!

Fourth, two-way synchronous configuration

1. Modify the original slave server configuration

192.168.20.102

/ / configure the original slave server / etc/my.cnf file and add the scarlet letter:

Server-id=2 # Slave server ID number, not the same as the master ID

Master-host=192.168.20.155 # specify the IP address of the primary server

Master-user=replication # specifies the user name that can be synchronized on the primary server

Master-password=123456 # password

Master-port=3306 # Port used for synchronization

Master-connect-retry=60 # breakpoint reconnection time

Replicate-ignore-db=mysql # masks synchronization of mysql libraries

Replicate-do-db=test1 # name of the synchronized database

Log-bin=/var/log/mysql/updatelog # sets the name of the generated log file

Binlog-do-db=test1 # set the synchronization database name

Binlog-ignore-db=mysql # avoid synchronizing mysql user configuration to avoid unnecessary hassle

2. Create a directory to update the log and give permissions to the mysql user

# 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

/ / give user replication synchronization permission from the slave server

# mysql-uroot-p123456

Mysql > GRANT REPLICATION SLAVE ON *. * TO

BY '123456'

/ / refresh permissions to make the settings take effect

Mysql > Flush privileges

4. Modify the original master configuration file

192.168.20.101

/ / configure the original masterer / etc/my.cnf file and add the scarlet letter:

Log-bin=mysql-bin # starts the binary logging system

Server-id=1 # the native database is marked mainly by ID.

Log-bin=/var/log/mysql/updatelog # sets the name of the generated log file. There is no mysql in the path.

The directory is created manually and given the permissions of the mysql user.

Binlog-do-db=test1 # database name that needs to be synchronized in the binary

Binlog-ignore-db=mysql,test # avoid synchronizing mysql user configuration to avoid unnecessary hassle

Master-host=192.168.20.128 # settings synchronize updates from the original slave database

Master-user=replication # Update user

Master-password=123456 # password

Master-port=3306 # port

Replicate-do-db=test1 # requires updated libraries

/ / restart the mysql service

# service mysqld restart

/ / query on server B

192.168.20.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 A

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='123456',MASTER_PORT=3306,MASTER_LOG_FILE='upda

Telog.000001',MASTER_LOG_POS=106

/ / regress the binary database records of the slave server according to the results of the show master status of the master server above

Achieve the effect of synchronization

/ / start B server synchronization service

192.168.20.102

Mysql > SLAVE START

5. Verify the configuration

/ / enter the mysql command line on server A

192.168.20.101

Mysql > SHOW SLAVE STATUSG

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Here both Slave_IO_Running and Slave_SQL_Running should be yes, indicating that all the SQL threads from the database

Open it correctly. Indicates that the database is synchronizing.

/ / enter the mysql command line on server B

192.168.20.102

Mysql > SHOW SLAVE STATUSG

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Here both Slave_IO_Running and Slave_SQL_Running should be yes, indicating that all the SQL threads from the database

Open it correctly. Indicates that the database is synchronizing.

6. Testing

/ / create a table on server A

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 B server

192.168.20.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-A

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.20.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 A

192.168.20.101

Mysql > show tables

+-+

| | Tables_in_test1 |

| | name |

| | test1 |

| | test2 |

| | user |

+-+

4 rows in set (0.00 sec)

The two-way copy experiment is successful!

Deployment of mysql5.5.9 semi-synchronous replication function

1.mysql5.5.9 semi-synchronous replication function:

The mysql5.5 version supports semi-synchronous replication (Semisynchronous Replication), but

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

Whether it is released in binary or compiled by your own source code, this plug-in will be generated by default.

One is for master and the other is for slave. You need to install these two plugins before using it.

First check whether mysql supports dynamically adding plug-ins.

> select @ @ have_dynamic_loading

+-- +

| | @ @ have_dynamic_loading |

+-- +

| | YES |

+-- +

1 row in set (0.00 sec)

Support dynamic add and subtract plug-ins

Add a plug-in:

) > install plugin rpl_semi_sync_master soname

Semisync_master.so

Query OK, 0 rows affected (0.00 sec)

) > install plugin rpl_semi_sync_slave soname

Semisync_slave.so

Query OK, 0 rows affected (0.00 sec)

After adding the plug-in, the system will add several system parameters by default

) > 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 modified dynamically.

Rpl_semi_sync_master_enabled:

Starting master supports semi-synchronous replication.

Rpl_semi_sync_master_timeout:

Timeout interval for the main database to wait for semi-synchronous replication information to be returned. Default 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 master to wait for the receipt signal of slave after everything is submitted.

The default is on, and every transaction will wait. If slave fails, when slave catches up with master logs,

It can be automatically switched to semi-synchronous mode. If it is off, it will not be semi-synchronous after slave catches up.

The mode is copied and needs to be started by hand.

Rpl_semi_sync_slave_enabled:

Starting slave supports semi-synchronous replication.

Rpl_semi_sync_slave_trace_level:

Monitoring level, same as rpl_semi_sync_master_trace_leve above.

State variables of the corresponding system:

> 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 that supports semi-synchronization.

Rpl_semi_sync_master_net_avg_wait_time:

The average wait time for master to wait for a reply from slave. Unit millisecond.

| Rpl_semi_sync_master_net_wait_time:

The total wait time for the master.

Rpl_semi_sync_master_net_waits:

The total number of waits that master waited for slave to reply.

Rpl_semi_sync_master_no_times:

The number of times master turned off semi-synchronous replication.

Rpl_semi_sync_master_no_tx:

The number of times master submitted without receiving a reply from slave, (it should be understood as the number of times master waited for a timeout

Number)

Rpl_semi_sync_master_status:

Marks whether master is now in a semi-synchronous replication state.

Rpl_semi_sync_master_timefunc_failures:

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

Gettimeofday ().

Rpl_semi_sync_master_tx_avg_wait_time:

The average wait time that master spends on each transaction.

Rpl_semi_sync_master_tx_wait_time:

Total number of waits for the master.

Rpl_semi_sync_master_wait_pos_backtraverse:

What I understand means that the latter arrives first, while the first comer hasn't arrived yet.

The total number of times the master waited for an event with binary

Coordinates lower than events waited for previously. 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:

How many session are currently waiting due to slave's reply?

Rpl_semi_sync_master_yes_tx:

The number of times master successfully received a reply from slave.

Rpl_semi_sync_slave_status:

Marks whether the slave is in a semi-synchronous state.

=

The steps to configure semi-synchronization are simple:

1. First set up the replication in the way we generally configure asynchronous replication.

two。 Start replication in asynchronous mode.

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

4. Modify the semi-synchronous parameters of the master-slave library:

The main library executes:

Set global rpl_semi_master_enabled=1

Set global rpl_semi_sync_master_timeout=1000

Execute from the library

Set global rpl_semi_slave_enabled=1

5. Launch slave from the library:

Start slave

6. View the parameters and adjust the value of global rpl_semi_sync_master_timeout according to the status of the replication.

On how to achieve master-slave replication and semi-synchronous replication in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.