MySQL enables database master-slave replication, master-master replication, and semi-synchronous replication

--------------MySQL to implement the database master-slave replication Architecture----------------

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

192.168.41.145 Master

192.168.41.137 slave

Second, the preparation steps:

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

2, ①vim/etc/hosts

192.168.41.145 Master #各节点之间可以通过主机名互相通信

192.168.41.137 slave

②ntpdate 172.17.0.1 #各节点时钟服务同步

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

4. Start the two server MySQL service separately to ensure the service is normal.

Third, the implementation of the steps:

1. Configure Master master server

Configure master, including opening a binary log, specifying a unique SERVR ID. For example, add the following value to the configuration file.

Vim/etc/my.cnf

Server-id=1 #配置server-id, let the primary server have a unique ID number

Log-bin=mysql-bin #打开Mysql日志, the log format is in binary

skip-name-resolve# Turn off name resolution (not required)

2. Create a copy Account

Establish 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] ' 192.168.%.% ' identified by ' Ke ';

3. View Primary Server Status

Perform the show master status on the master database to view the primary server binary log status

4. Configure slave from the server

Configure slave, turn on the trunk log, specify a unique Servr ID, and set read-only permissions. Add the following values to the configuration file

server-id=2 #配置server-id to have a unique ID number from the server

Relay_log = Mysql-relay-bin #打开Mysql日志, log format is binary

Read_Only = 1 #设置只读权限

Log_bin = Mysql-bin #开启从服务器二进制日志

Log_slave_updates = 1 #使得更新的数据写进二进制日志中

5. To start a replication thread from the server

①stop slave;

②reset slave;

③ let slave connect to master and begin to redo the events in the master binary log.

Change MASTER to

Master_host= ' 192.168.41.145 ',

Master_user= ' slave ',

Master_password= ' Ke ', "Master and slave password to be consistent"

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=278;

※ Note

IP is the IP of master;

Password and master settings are consistent;

POS Settings View master get: Show Master Status

④start slave;# initiates the replication thread.

6. View from server Status

You can use show SLAVE status\g to view the status from the server, as shown below, or show processlist \g to view the current replication status:

Slave_io_running:yes #IO线程正常运行

Slave_sql_running:yes #SQL线程正常运行

"Both must be yes at the same time to start successfully"

attached: master-Slave synchronization error occurred:

Slave_io_running:connecting

Slave_sql_running:yes

Workaround:

There are 3 main reasons that lead to lave_io_running as connecting:

1, the network does not pass

2. Incorrect password

3, POS is not correct

Iv. Verification

Insert data on Master to see if synchronization is possible on the slave

Create database Liuke; #建数据库

Use Liuke;

CREATE TABLE MINI_TB (ID int (3), name char (10)); #建表

INSERT into MINI_TB values (001, ' Candy '); #插入数据

Select*from MINI_TB; #查看表内容

When viewed on slave, you can synchronize and then succeed.

V. Add a new slave server

If master has been running for a long time, you want to synchronize data with the newly installed slave, even if it does not have master data.

At this point, there are several ways to get slave to start from another service, such as copying data from Master, cloning from another slave, and starting a slave from the most recent backup. To speed up slave synchronization with master, you can

Data synchronization is performed in the following ways:

(1) Data snapshot at a certain time of master;

(2) Backup data of the database

(3) The binary log file of master.

-------------- MySQL implements the primary master copy of the database----------------

First, the principle

Primary master replication can change the data within both MySQL hosts, and the other host will make changes accordingly.

How to achieve: it is to combine two master-slave replication organically.

A problem to be aware of when configuring: primary key duplication, Server-id cannot be duplicated.

Second, the experimental steps

master:192.168.41.145 slave:192.168.41.137

1. Configuration files

Define Master with Odd ID

Vim/etc/my.cnf

auto_increment_offset=1 #起始值. Generally fill the nth master MySQL. This is the first master MySQL

auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n

Slave using even IDs

auto_increment_offset=2

auto_increment_increment=2

2, because the primary master replication is a combination of two master-slave replication, so the above master-slave replication is then configured.

(1)

① creates a MySQL user on slave that the 192.168.41.145 host can log on to.

User: Bubu Password: 123

② created: Create user ' Bubu ' @ ' 192.168.41.145 ' identified by "123";

Authorization: Grant replication Slave on * * to ' bubu ' @ ' 192.168.41.145 ' identified by "123";

Mysql>flush privileges;

③ view binary name and location on 192.168.41.137: Show Master status;

(3) Tell the binary file name and location

Execute in 192.168.41.145:

Change Master to master_host= ' 192.168.41.137 ',

Master_user= ' Bubu ',

Master_password= ' 123 ',

Master_log_file= ' mysql-bin.000004 ',

master_log_pos=601;

The primary master replication configuration is complete.

Third, testing

(1)

Two hosts start slave:start slave respectively;

View slave status separately:

192.168.41.145 "Replicate-do-db=aa #要同步的数据库, default all Libraries"

192.168.41.137

When you see Slave_io_running:yes and Slave_sql_running:yes, it shows the state is normal.

(2)

①192.168.41.145

MariaDB [mysql]> use Jesper;

CREATE table TAB1 (id int);

INSERT into TAB1 values (' 66 ');

Select*from Tab1;

②192.168.41.145

MariaDB [mysql]> use Jesper;

INSERT into TAB1 values (' 99 ');

Select*from Tab1;

View data: Two host data results as well!

※ Note:

1, the primary master replication configuration file Auto_increment_increment and Auto_increment_offset can only guarantee that the primary key is not duplicated, but does not guarantee the primary key order.

2, when the configuration is complete slave_io_running, slave_sql_running is not all Yes, show Slave status\g information, there are errors, can be corrected according to the error prompt.

Common error points:

1, both databases exist DB database, and the first MySQL DB has tab1, the second MySQL DB does not have TAB1, that certainly cannot succeed.

2, has obtained the data binary log name and the location, but also carries on the data operation, causes the POS to change. The previous POS was used when configuring change master.

3, stop slave, data changes, and then start slave. Error.

The ultimate correction: Re-execute the change master again.

-------------- MySQL enables database semi-synchronous replication -------------

The semi-synchronous replication mode must be turned on both the primary and slave servers, otherwise the asynchronous replication mode will default.

First, the Environment preparation

① need to be installed to use:

mysql> INSTALL PLUGIN plugin_name SONAME ' shared_library_name ';

Semi-synchronous replication:

Semisync_master.so

Semisync_slave.so

② Check to see if there is an automatic loading function

MariaDB [(None)]> show variables like ' have_dynamic_loading ';

③ Master-slave replication is already configured and is already working.

Second, the experimental steps

1. Master Node settings:

① Installation: Install PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

② View: MariaDB [mydb]> 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 |

+------------------------------------+-------+

③MARIADB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=on; "0: Off; 1: Open"

#开启半同步复制, the default is off.

2, from the node settings:

①install PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

②MARIADB [mydb]> SHOW GLOBAL VARIABLES like ' rpl_semi% '; #查看是否加载成功

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | OFF |

| Rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

③ restart from the server IO thread, manually change the asynchronous mode to semi-synchronous mode

MariaDB [mydb]> STOP SLAVE io_thread;

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_slave_enabled = on;

MariaDB [mydb]> SHOW GLOBAL VARIABLES like ' rpl_semi% ';

MariaDB [mydb]> START SLAVE io_thread;

※ You can view the error log from the library to see if it takes effect

3. Configuration file Modification

The master and slave configuration files are added:

Vim/etc/my.cnf

Rpl_semi_sync_master_enabled=1 #表示以后启动MySQL将会自动开启半同步复制

Third, testing

Touch slave hang off, master waits for 10s still not received the feedback signal, then to asynchronous replication mode, continue to execute.

First synchronize the creation of the database AA

1, slave execution stop slave; turn off master-slave replication

2, Master in the AA database to create the table Aihao, did not receive the feedback signal, wait 10 seconds (rpl_semi_sync_master_timeout=1000 wait timeout), continue to execute

Master

Slave

3, Master in the database re-create TAB2, do not need to wait for feedback, direct execution

"When feedback times out, master switches to asynchronous replication mode. This is the asynchronous mode and does not need to wait "

4, slave execution start slave, data start synchronization, establish Aihao, tab2, feedback to master, and switch to semi-synchronous replication

5, slave execution stop slave; turn off master-slave replication

6, Master in the database to create table TAB3, at this time need to wait 10s, receive slave feedback signal; Wait timeout, switch to asynchronous replication mode, continue to "Step 4 o'clock, data synchronization has been fed back to master, when Master is already semi-synchronous replication Mode"

MySQL enables database master-slave replication, master-master replication, and semi-synchronous replication