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

This article mainly explains "the concept of master-slave replication, semi-synchronous replication and master-master replication of MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn "the concept of master-slave replication, semi-synchronous replication and master-master replication of MySQL".

Two replication schemes supported by MySQL: statement-based replication and row-based replication

Statement-based replication is based on row replication, and both replication methods achieve data synchronization with the master server by recording any SQL statements in the binary log of the master server that may change the data in the database to the relay log, and executing the SQL statements in the following relay log on the slave server. The difference is that when a variable-based data is executed on the master server and updated to the database, such as the now () function, then statement-based replication records the entire syntax of the SQL statement, while row-based replication is to update now () to the value of the database.

For example, execute the following statement on the primary server:

Mysql > update user set createtime=now () where sid=16

If the value returned by now () at this time is: 2012-04-16 20:46:35

Statement-based replication records it as: update user set createtime=now () where sid=16

Based on the line copy, it will be recorded as: update user set createtime='2012-04-16 20 46 15 'where sid=16

Three threads started by master-slave replication

Binlog dump thread: sends the contents of the binary log to the slave server

Icano from thread: writes accepted data to the relay log

SQL thread: read one SQL statement at a time from the relay log and execute it on the slave server

1. Master-slave replication:

Preparatory work:

1. Modify the configuration file (server_id must be modified)

two。 Establish replication user

3. Start the slave service process from the server

Planning:

Master:IP address: 172.16.4.11 version: mysql-5.5.20

Slave:IP address: 172.16.4.12 version: mysql-5.5.20

It should be noted here that most mysql replication is backward compatible, so the version of the slave server must be higher than or equal to that of the master server.

1 、 Master

Modify the configuration file to make it the mysql master server

# vim / etc/my.cnf

Server_id=11 # modify server_id=11

Log_bin=mysql-bin # Open binary Log

Sync_binlog=1 # binaries written to disk immediately after any transaction is committed

Innodb_flush_logs_at_trx_commit=1 # Log file that is written to disk immediately after a transaction is submitted

Save exit

# service mysql reload # reload the configuration file of mysql

2. Create a user on Master and grant replication permission

Mysql > grant replication client,replication slave on *. * to repl@172.16.4.12 identified by '135246'

Mysql > flush privileges

3 、 Slave

Modify the configuration file to set it to an mysql slave server

# vim / etc/my.cnf

Server_id=12 # modify server_id=12

# log-bin # comment out log-bin, no binary log is needed from the slave server, so close it

Relay-log=mysql-relay # defines the relay log name and enables the relay log from the server

Relay-log-index=mysql-relay.index # defines the relay log index, which enables the relay index from the server

Read_only=1 # sets that slave servers can only read, not write.

Save exit

# service mysql reload # reload the configuration file of mysql

4. Verify whether the relay log and server_id on the Slave are valid.

Mysql > show variables like 'relay%'

+-+ +

| | Variable_name | Value |

+-+ +

| | relay_log | relay-bin |

| | relay_log_index | relay-bin.index |

| | relay_log_info_file | relay-log.info |

| | relay_log_purge | ON |

| | relay_log_recovery | OFF |

| | relay_log_space_limit | 0 | |

+-+ +

Mysql > show variables like 'server_id'

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 12 | |

+-+ +

5. Start the slave service process from the server

Scenario 1. If both the master server and slave server are newly established and no additional data is added, execute the following command:

Mysql > change master to master_host='172.16.4.11',master_user='repl',master_password='135246'

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 172.16.4.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 107

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

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: 25520

Relay_Log_Space: 2565465

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Queueing master event to the relay log

Master_Host: 172.16.4.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 107

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000001

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: 360

Relay_Log_Space: 300

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

Scenario 2. If the master server has been running for a while and the slave server is newly added, you need to import the previous data from the master server into the slave server:

Master:

# mysqldump-uroot-hlocalhost-p123456-- all-databases-- lock-all-tables-- flush-logs-- master-data=2 > / backup/alldatabase.sql

Mysql > flush tables with read lock

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 360 | |

+-+

Mysql > unlock tables

# scp / backup/alldatabase.sql 172.16.4.12:/tmp

Slave:

# mysql-uroot-p123456

< /tmp/alldatabase.sql mysql>

Change master to master_host='172.16.4.11',master_user='repl',master_password='135246',master_log_file='mysql-bin.000004',master_log_pos=360

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 172.16.4.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 360

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: No

Slave_SQL_Running: No

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: 360

Relay_Log_Space: 107

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Mysql > start slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Queueing master event to the relay log

Master_Host: 172.16.4.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 360

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000004

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: 360

Relay_Log_Space: 300

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 11

It indicates that the master-slave replication architecture of MySQL is successful.

Note replication of 1:MySQL can be based on a database or a tacit table in a library. To achieve this function, simply add the following configuration to its configuration file:

Master:

Binlog-do-db=db_name replicates only db_name databases

Binlog-ignore-db=db_name does not replicate db_name databases

Note 2: defining filtering rules on Master means that any writes that are not related to the database will not be recorded in the binary log, so defining filtering rules on Master is not recommended, and it is not recommended that binlog-do-db and binlog-ignore-db be defined at the same time.

Slave:

Replicate_do_db=db_name replicates only db_name databases

Replicate_ignore_db=db_name does not replicate db_name databases

Replicate_do_table=tb_name only copies the tb_ name table

Replicate_ignore_table=tb_name only copies the tb_ name table

Replicate_wild_do_table=test% only copies tables that begin with test and are followed by any character.

Replicate_wild_ignore_table=test_ only copies tables that begin with test and are followed by any single character name

Note 3: if you need to specify multiple db or table, you only need to write the command multiple times

Second, semi-synchronous replication

Since the replication of Mysql is based on asynchronism, the successful replication of data can not be guaranteed under special circumstances, so a patch from google is used after mysql 5.5.The replication of Mysql can be implemented in semi-synchronous mode. So you need to load the corresponding plug-in for the primary server. There is a corresponding plug-in semisync_master.so,semisync_slave.so in the lib/plugin/ directory under the installation directory of Mysql

Run the following command on the mysql command line of Master and Slave:

Master:

Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so'

Mysql > set global rpl_semi_sync_master_enabled = 1

Mysql > set global rpl_semi_sync_master_timeout = 1000

Mysql > show variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 1000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

Slave:

Mysql > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Mysql > set global rpl_semi_sync_slave_enabled = 1

Mysql > stop slave

Mysql > start slave

Mysql > show variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | ON |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

Check whether semi-synchronization is in effect:

Master:

Mysql > show global status like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 0 | |

| | Rpl_semi_sync_master_net_wait_time | 0 | |

| | Rpl_semi_sync_master_net_waits | 0 | |

| | 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 | 0 | |

| | Rpl_semi_sync_master_tx_wait_time | 0 | |

| | Rpl_semi_sync_master_tx_waits | 0 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 0 | |

+-+ +

Indicates that the semi-synchronization is successful.

Let the semi-synchronization feature take effect automatically every time MySQL starts, and edit it in the my.cnf of Master and Slave:

Master:

[mysqld]

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

Slave:

[mysqld]

Rpl_semi_sync_slave_enabled=1

You can also set whether to start the semi-synchronous plug-in by setting the global variable:

Master:

Mysql > set global rpl_semi_sync_master_enabled=1

Unload the plug-in

Mysql > uninstall plugin rpl_semi_sync_master

Slave:

Mysql > set global rpl_semi_sync_slave_enabled = 1

Mysql > uninstall plugin rpl_semi_sync_slave

III. Master master replication architecture

1. Set up a user with replication rights on each of the two servers

Master:

Mysql > grant replication client,replication slave on *. * to repl@172.16.4.12 identified by '135246'

Mysql > flush privileges

Slave:

Mysql > grant replication client,replication slave on *. * to repl@172.16.4.11 identified by '135246'

Mysql > flush privileges

2. Modify the configuration file:

Master:

[mysqld]

Server-id = 11

Log-bin = mysql-bin

Auto-increment-increment = 2

Auto-increment-offset = 1

Relay-log=mysql-relay

Relay-log-index=mysql-relay.index

Slave:

[mysqld]

Server-id = 12

Log-bin = mysql-bin

Auto-increment-increment = 2

Auto-increment-offset = 2

Relay-log=mysql-relay

Relay-log-index=mysql-relay.index

3. If both servers are newly established and there are no other write operations, each server only needs to record its own binary log file and event location as the starting point of server replication.

Master:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 360 | |

+-+

Slave:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000005 | 107 |

+-+

4. Each server can then designate another server as its own master server:

Master:

Mysql > change master to master_host='172.16.4.12',master_user='repl',master_password='135246',master_log_file='mysql-bin.000005'

Master_log_pos=107

Slave:

Mysql > change master to master_host='172.16.4.11',master_user='repl',master_password='135246'

Master_log_file='mysql-bin.000004',master_log_pos=360

5. Start the slave server thread:

Master:

Mysql > start slave

Slave:

Mysql > start slave

At this point, the main main architecture has been successful!

At this point, I believe you have a deeper understanding of "the concept of master-slave replication, semi-synchronous replication and master-master replication of MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.