Semi-synchronous master-slave replication for database based on MySQL

First, let's look at the problems that the database often encounters:

The first is the performance problem.

1, upward expansion (hardware) scale up the individual itself easily reached the limit

2, outward expansion scale out

The second is the question of usability.

1. Database service Interruption

2. damage to the wrong operation data

3. Hardware Failure

4. Database Upgrade test encountered bug

5. Hacker attacks

Based on the above problems, there is a better solution, that is, to implement the master-slave replication of the database. MySQL master-slave replication technology can improve server performance. In addition to MySQL replication can also solve the following issues:

1. Data distribution (distribution)

2, load balancing (load Balancing)

3. Backup (Backups)

4. High availability and fault tolerance hi availability and failover

Before we start the experiment, let's look at several concepts: asynchronous replication, full synchronous replication, and asynchronous replication.

Asynchronous replication

MySQL default is asynchronous replication, that is, the main library after executing the client committed transactions immediately return the results to the client, it does not matter whether the library has been accepted processing, so that if the Lord hangs, the transaction may not be fully uploaded from the master, if this time forcibly from the promotion of the main, May cause the new Lord's data to be incomplete.

Full synchronous replication

When the main library finishes executing a transaction, the result is returned to the client until all the transactions have been executed from the library. Because it is necessary to wait for all the returns from the library to be completed, the performance of full synchronous replication will inevitably be affected and the time-out required.

Semi-synchronous replication

between asynchronous replication and full-synchronous replication, the main library does not return to the client immediately after executing a client-submitted transaction, but waits until at least one is received from the library and written to realy log to return to the client. Compared to asynchronous replication, semi-synchronous replication improves the security of the database and also causes a certain amount of latency, which is at least a TCP/IP round-trip time. Therefore, semi-synchronous replication is best used in low latency networks.

If you want to better realize the master-slave copy of MySQL, you must first think clearly its replication principle, so that in the work can be skilled operation and application.

How MySQL replication works:

1. The master server (master) writes the change record to the binary log (the binary log events, binary logs event), and the master notifies the storage engine to commit the transaction after the event is written to the binary log. You can then receive slave requests.

2. Copy the binary log events from the primary server master to its own trunk log (relay log) from the server (slave). The slave I/O thread opens a normal connection on master and writes these binary events to the relay log.

3, the SQL thread reads the event from the log and replays the event in it to update the slave data so that it is consistent with the data in master.

MySQL also provides a replication filter: that is, in the process of database replication, copying only one or several database-related data, not all, provides two ways to replicate: one is to add a configuration file to the primary server's configuration file, and the other is to add a configuration file from the server.

1. In the master server

The primary server records only write operations related to a particular database to the binary log.

binlog_do_db= #要复制的数据库

binlog_ignore_db= #要忽略的数据库

2. In the slave server

The SQL thread from the server only replays events related to the database or table concerned and applies them locally;

replicate_do_db= #要复制过来的数据库

replicate_ignore_db= #不要复制过来的数据库

Now we're going to do our experiment based on the theory above.

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/21/4531414b9e233caa9da7e07459f99756.png "title=" Picture 1.png "alt=" 4531414b9e233caa9da7e07459f99756.png "/>

MySQL master-slave architecture diagram

First, the environment preparation:

CentOS System server 2, a user to do MySQL master server, one for MySQL from the server, configured yum source, firewall shutdown, each node clock service synchronization, each node can communicate with each other through the host name

Second, the preparation steps:

1, iptables-f && setenforce empty firewall policy, turn off SELinux

2, take two servers all use the Yum mode to install the MySQL service, requires the same version

3. Start the MySQL service of two servers separately to ensure the service is normal.

Third, the implementation of the steps :

Configuration Master Primary server:

1, Configure the master, including open binary log, specify a unique Servr ID. Add the following values to the configuration file:

Server-id=1 #配置server-id, let the primary server have a unique ID number log-bin=mysql-bin #打开Mysql日志, log format is binary skip-name-resolve# turn name resolution off, (not required)

2. Create a copy account set up a backup account in Master's database: Each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions.

Grant replication Slave,replication Client on * * to [e-mail protected] ' 172.17.%.% ' identified by ' 111111 ';

3. View the primary server status, perform the show master status in the master's database, and view the primary server binary log status

   mariadb [(none)]> show master  status;   +------------------+----------+--------------+------------------+   |  file             | position |  binlog_do_db | binlog_ignore_db |   +------------------+----------+------- -------+------------------+   | mysql-bin.000001 |       414 |              |                   |    +------------------+----------+--------------+------------------+   1 row  in set  (0.00 sec) 

Configure slave from the server:

1, Configure the slave, open the relay log, specify a unique Servr ID, set the read-only permission. Add the following values to the configuration file

server-id=2 #配置server-id, let the server have a unique ID number Relay_log = Mysql-relay-bin #打开Mysql日志, the log format is binary read_only = 1 #设置只读权限 Log_bin = Mysql-bin #开启从服务器二进制日志 log_slave_updates = 1 #使得更新的数据写进二进制日志中

2. start the replication thread from the server to let slave connect to master and begin to redo the events in the master binary log.

MariaDB [(none)]> change master to master_host= ' 172.17.253.210 ', master_user= ' slave ', master_password= ' 111111 ', MA       Ster_log_file= ' mysql-bin.000001 ', master_log_pos=245; Query OK, 0 rows affected (0.01 sec) executes the start slave;# start replication thread.

       

3, view from the server state can use show SLAVE status\g View from the server state, as shown below, also can be used show processlist \g View the current replication status:

Slave_io_running:yes #IO线程正常运行

Slave_sql_running:yes #SQL线程正常运行

To achieve semi-synchronous replication:

Master node:

    mariadb [(None)]> install plugin rpl_semi_sync_master  soname  ' semisync_master.so ';      query ok, 0 rows affected   (0.00 sec)      mariadb [(none)]> show global  variables like  ' rpl_semi% ';     +------------------------------------+-------+     | 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    |    +-------------------------------- ----+-------+    4 rows in set  (0.01 sec)       mariadb [(None)]> set global rpl_semi_sync_master_enabled=on;     Query OK, 0 rows affected  (0.00 SEC)

    mariadb [(None)]> install plugin rpl_semi_sync_slave  soname  ' semisync_slave.so ';          query ok, 0  rows affected  (0.01 sec)          mariadb  [(none)]> show global variables like  ' rpl_semi% ';     +-- -------------------------------+-------+    | variable_name                    | value |     +---------------------------------+-------+    | rpl_semi_ sync_slave_enabled     | off   |    |  rpl_semi_sync_slave_trace_level | 32    |    +-------------- -------------------+-------+&Nbsp;   2 rows in set  (0.01 sec)            mariadb [(None)]> stop slave io_thread;     query ok, 0 rows affected  (0.00 sec)           mariadb [(None)]> set global rpl_semi_sync_slave_enabled =on;     Query OK, 0 rows affected  (0.01 sec)           mariadb [(none)]> show global variables like  ' rpl_semi% ';         +---------------------------------+-------+     | Variable_name                    | value |    +------------------------ ---------+-------+    | rpl_semi_sync_slave_enabled     | on     |    | rpl_semi_sync_slave_trace_level | 32    |     +---------------------------------+-------+    2 rows in  set  (0.00 sec)          mariadb [(none)]>  start slave io_thread;                   Query OK, 0 rows affected  (0.00 SEC)

Iv. Testing

Create a database and data table on master, observe the data in the slave database, and synchronize with master.

Semi-synchronous master-slave replication for database based on MySQL