Build MySQL master-slave, semi-synchronous, primary master replication architecture

The ultimate goal of replication is to keep the data of one server in sync with the data of another server, which has reached data redundancy or service load balancing. A primary server can connect to multiple slave servers, and from the server it can also be the primary server. The master and slave servers can be located in different network topologies, and because of the powerful replication capabilities of MySQL, the replication target can be all databases, or some databases, or even some tables in a database, for replication.

two replication scenarios supported by MySQL: statement-based replication, row-based replication
Statement-based replication is based on row replication, which synchronizes data with the primary server by recording any SQL statements in the primary server's binary log that could cause changes in the data in the database to the trunk log, and executing the SQL statements from the following trunk logs from the server. The difference is that when a variable-based data is executed on the primary server and updated to the database, such as the Now () function, the entire syntax of the SQL statement is recorded when the statement is copied, and row-based replication is a record of the value of the update to the database.
For example, execute the following statement on the primary server:
Mysql>update user Set Createtime=now () where sid=16;
If now () the value returned is: 2012-04-16 20:46:35
Statement-based replication records it as: Update user set Createtime=now () where sid=16;
Row-based replication will record it as: Update user set createtime= ' 2012-04-16 20:46:35 ' where sid=16;

Three threads for master-slave replication initiation
Binlog Dump Thread: sends the contents of the binary log to the slave server
I/o slave thread: writes the accepted data to the relay log
SQL Thread: reads an SQL statement from a log at a time from the server execution

First, master-slave replication:
Preparatory work:
1. Modify the configuration file (server_id must be modified)
2. Create a 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 is important to note that MySQL replication is mostly back-compatible, so the version from the server must be higher or equal to the primary server version.
1. Master
Modify the configuration file to set it as the MySQL master server
#vim/etc/my.cnf
server_id=11 #修改server_id =11
Log_bin=mysql-bin #开启二进制日志
Sync_binlog=1 #任何一个事务提交之后就立即写入到磁盘中的二进制文件
Innodb_flush_logs_at_trx_commit=1 #任何一个事物提交之后就立即写入到磁盘中的日志文件

Save exit
#service MySQL Reload #重新载入mysql的配置文件

2. Create user on Master, grant copy permission
Mysql>grant replication Client,replication Slave on * * to [e-mail protected] identified by ' 135246 ';
Mysql>flush privileges;

3, Slave
Modify the configuration file to set it to a MySQL slave server
#vim/etc/my.cnf
Server_id=12 #修改server_id =12
#log-bin #注释掉log-bin, the binary log is not required from the server, so it is turned off
Relay-log=mysql-relay #定义中继日志名, turn on relay log from server
Relay-log-index=mysql-relay.index #定义中继日志索引名, turn on the relay index from the server
Read_only=1 #设定从服务器只能进行读操作, no write operation

Save exit
#service MySQL Reload #重新载入mysql的配置文件

4. Verify that the relay logs on the slave and the server_id are in effect
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. Starting from the server from the service process
scenario One, if both the master and slave servers are newly created 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 Two, if the primary server has been running for a while, from the server is newly added, you need to import data from the primary server to 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 |       |              |                   |
+------------------+----------+--------------+------------------+
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

Illustrates the success of MySQL's master-slave replication architecture

Note 1:mysql replication can be replicated based on a database or a table of memories in a library, to implement this feature, simply add the following configuration to its configuration file:
Master:
Binlog-do-db=db_name Copy only db_name database
Binlog-ignore-db=db_name do not replicate db_name database

NOTE 2: Defining a filter rule on Master means that any writes that are not related to the database are not recorded in the binary log and are therefore not recommended The filter rule is defined on master, and Binlog-do-db is not recommended to be defined at the same time as binlog-ignore-db.

Slave:
Replicate_do_db=db_name Copy only db_name database
Replicate_ignore_db=db_name do not replicate db_name database
Replicate_do_table=tb_name Copy only Tb_name table
Replicate_ignore_table=tb_name Copy only Tb_name table
replicate_wild_do_table=test% Copy only the table with the name beginning with test and followed by any character
Replicate_wild_ignore_table=test_ Copy only the table with the name beginning with test and followed by any single character

Note 3: If you need to specify more than one DB or table, you can simply write the command multiple times

=============================================================================================

Second, semi-synchronous replication

Because MySQL replication is based on asynchronous, in special cases can not guarantee the successful replication of data, so after MySQL 5.5 used from Google patch, MySQL replication can be implemented in semi-synchronous mode. Therefore, you need to load the corresponding plug-in for the primary server. There is a corresponding plugin in the lib/plugin/directory in the MySQL installation directory semisync_master.so,semisync_slave.so

Run the following command on the MySQL command line in 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 that the semi-sync 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 |
+--------------------------------------------+-------+
describes the semi-synchronous success.

Let the semi-sync function automatically take effect every time the MySQL starts, edit in master and slave my.cnf:
Master:
[Mysqld]
Rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 #1秒

Slave:
[Mysqld]
Rpl_semi_sync_slave_enabled=1

You can also set whether to start a semi-synchronous plug-in by setting a global variable:
Master:
Mysql> Set Global rpl_semi_sync_master_enabled=1
Cancel loading plug-ins
mysql> Uninstall plugin Rpl_semi_sync_master;

Slave:
mysql> Set Global rpl_semi_sync_slave_enabled = 1;
mysql> Uninstall plugin Rpl_semi_sync_slave;

=============================================================================================

Third, the primary master replication schema
1. Establish a user with copy permission on both servers;
Master:
Mysql>grant replication Client,replication Slave on * * to [e-mail protected] identified by ' 135246 ';
Mysql>flush privileges;

Slave:
Mysql>grant replication Client,replication Slave on * * to [e-mail protected] 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 no other write operations, each server only need to record the current binary log files and the location of the event, as a separate starting location of the server replication can be
Master:
mysql> Show master status;
+------------------+----------+--------------+------------------+
| file             | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |       |              |                   |
+------------------+----------+--------------+------------------+

Slave:
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000005 |              107 |                  | |
+------------------+----------+--------------+------------------+

4, each server next specify to another server as its own master server can:
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 thread from the server:
Master:
Mysql>start slave;

Slave:
Mysql>start slave;

To this main master architecture has been successful!

Build MySQL master-slave, semi-synchronous, primary master replication architecture