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

This article shows you how to achieve semi-synchronous replication in Mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, environmental preparation:

There are two centos system servers, one user as the Mysql master server and one for the Mysql slave server. The yum source is configured, the firewall is turned off, the clock service of each node is synchronized, and each node can communicate with each other through the host name.

192.168.41.145 master

192.168.41.137 slave

2. Preparation steps:

1. Iptables-F & & setenforce clears the firewall policy and shuts down selinux

2 、 ① vim / etc/hosts

192.168.41.145 master # nodes can communicate with each other through hostname

192.168.41.137 slave

② ntpdate 172.17.0.1 # clock service synchronization of each node

3. Use yum to install Mysql service on both servers. The version should be consistent.

4. Start the mysql service of two servers respectively to ensure that the service is normal.

Third, the implementation steps:

1. Configure the master master server

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

Vim / etc/my.cnf

Server-id=1 # configure server-id so that the primary server has a unique ID number

Log-bin=mysql-bin # Open the Mysql log in binary format

Skip-name-resolve# turns off name resolution, (optional)

2. Create a replication account

Set up a backup account in Master's database: each slave uses a standard MySQL username and password to connect to master. The user performing the replication operation is granted REPLICATION SLAVE permission.

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *. * TO slave@'192.168.%.%' IDENTIFIED BY 'ke'

3. View the status of the primary server

Execute show master status in the database of Master to check the binary log status of the primary server

4. Configure slave slave server

Configure slave, open the relay log, specify a unique servr ID, and set read-only permissions. Add the following values to the configuration file

Server-id=2 # configure server-id so that the slave server has a unique ID number

Relay_log = mysql-relay-bin # Open the Mysql log in binary format

Read_only = 1 # set read-only permission

Log_bin = mysql-bin # enable slave server binary log

Log_slave_updates = 1 # causes updated data to be written to the binary log

5. Start the replication thread from the server

① stop slave

② reset slave

③ tells slave to connect to master and starts redoing events in the master binary log.

CHANGE MASTER TO

MASTER_HOST='192.168.41.145'

MASTER_USER='slave'

MASTER_PASSWORD='ke', [master and slave passwords should be consistent]

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=278

Pay attention to

IP is the IP of master

Password and master settings are the same

POS settings check master to get: show master status

④ start slave;# starts the replication thread.

6. View the status of the slave server

You can use SHOW SLAVE STATUS\ G to view the slave server status, as shown below, or you can use show processlist\ G to view the current replication status:

Slave_IO_Running: Yes # IO thread is running normally

Slave_SQL_Running: Yes # SQL thread is running normally

[both must be YES at the same time to start successfully]

Attachment: there is an error in master-slave synchronization:

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Solution:

The main reasons why lave_IO_Running is connecting are as follows:

1. The network is not connected.

2. The password is incorrect

3. Pos is wrong.

IV. Verification

Insert data on master to see if synchronization can be done on slave

Building database by create database liuke;#

Use liuke

Create table mini_tb (id int (3), name char (10)); # create tables

Insert into mini_tb values (001); # insert data

Select*from mini_tb; # View table contents

If you can view it on slave, if you can synchronize it, you will succeed.

Add a new slave server

If master has been running for a long time, you want to synchronize the newly installed slave, or even it doesn't have master data.

At this point, there are several ways to make slave start with another service, for example, copy data from master, clone from another slave, and start a slave from the most recent backup. To speed up the synchronization of Slave and master,

Synchronize data first in the following ways:

(1) data snapshot at a certain time in master

(2) backup data of database

(3) binary log file of master.

-mysql implements database master replication-

I. principle

Master master replication means that data can be changed in both MySQL hosts, and the other host will change accordingly.

How to achieve: is to merge the two master-slave replicas organically.

What you need to pay attention to when configuring: the primary key is repeated and the server-id cannot be repeated.

Second, the experimental steps

Master:192.168.41.145 slave:192.168.41.137

1. Configuration file

Define master to use odd id

Vim / etc/my.cnf

Auto_increment_offset=1 # starting value. Generally fill in the nth main MySQL. This is the first master MySQL.

Auto_increment_increment=2 # step value auto_imcrement. Usually fill in n if there are n master MySQL

Slave uses even id

Auto_increment_offset=2

Auto_increment_increment=2

2. Because the master-master replication is a combination of two master-slave replicas, the above master-slave replication is then configured.

(1)

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

User: bubu password: 123

② creation: 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

③ looks at the binary name and location on 192.168.41.137: show master status

(3) tell the binary file name and location

Executed 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 master replication configuration is complete.

III. Testing

(1)

The two hosts start slave:start slave respectively

Check the slave status separately:

192.168.41.145 [replicate-do-db=aa # databases to be synchronized, default all libraries]

192.168.41.137

When Slave_IO_Running: YES and Slave_SQL_Running: YES are seen, 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: the data results of the two hosts are the same!

"Note:

1. Auto_increment_increment and auto_increment_offset in the master replication configuration file can only ensure that the primary key does not repeat, but not the order of the primary key.

2. When the configuration is completed with Slave_IO_Running and Slave_SQL_Running is not all YES, there is an error message in the show slave status\ G message, which can be corrected according to the error prompt.

Common mistakes:

1. There is a db database in both databases, but if there is tab1 in the first MySQL db and tab1 in the second MySQL db, it will definitely not be successful.

2. The binary log name and location of the data have been obtained, and the data operation has been carried out, which leads to the change of POS. The previous POS is still used when configuring CHANGE MASTER.

3. After stop slave, the data changes, and then start slave. Something went wrong.

Ultimate correction: just execute the CHANGE MASTER again

-MySQL to achieve semi-synchronous database replication-

Semi-synchronous replication mode must be turned on in both the master and slave servers, otherwise it will default to asynchronous replication mode.

I. Environmental preparation

① needs to be installed to use:

Mysql > INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

Semi-synchronous replication:

Semisync_master.so

Semisync_slave.so

② checks whether there is an automatic loading function.

MariaDB [(none)] > show variables like 'have_dynamic_loading'

③ master-slave replication is configured and 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%';# View semi-synchronous replication Files

+-+ +

| | 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: on]

# enable semi-synchronous replication, which is disabled by default.

2. Set the slave node:

① INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

② MariaDB [mydb] > SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';# to see if the load is successful.

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | OFF |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

③ restarts the IO thread from the server and manually changes 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 check the error log from the library to see if it takes effect.

3. Configuration file modification

Both master and slave configuration files are added:

Vim / etc/my.cnf

Rpl_semi_sync_master_enabled=1 # indicates that starting MySQL in the future will automatically enable semi-synchronous replication.

III. Testing

If the slave hangs up and the master waits for 10 seconds and still does not receive the feedback signal, it will switch to asynchronous replication mode and continue to execute.

First create the database aa synchronously

1. Slave executes stop slave; to shut down master-slave replication

2. Master creates the table aihao in the aa database and does not receive a feedback signal. After waiting for ten seconds (Rpl_semi_sync_master_timeout=1000 wait for timeout), continue to execute.

Master:

Slave:

3. Master creates a tab2 in the database and executes it without waiting for feedback.

[when feedback times out, master switches to asynchronous replication mode. It is in asynchronous mode at this time, there is no need to wait]

4. Slave executes start slave, starts data synchronization, establishes aihao and tab2, feeds back to master, and switches to semi-synchronous replication.

5. Slave executes stop slave; to shut down master-slave replication

6. When master creates the table tab3 in the database, you need to wait 10 seconds to receive the slave feedback signal; wait for the timeout, switch to asynchronous replication mode, and continue to execute [when step 4, data synchronization has been fed back to master, when master is already in semi-synchronous replication mode]

The above is how to achieve semi-synchronous replication in Mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.