Implementation of master-slave replication experiment steps and semi-synchronization

Lab environment:

Master node IP Address: 172.16.2.16

Slave node IP: 172.16.2.17

The mariadb version is 5.5.36 and is installed in binary mode. The installation is complete.

Purpose:

Learn about master-slave replication and how each node works.

Procedure:

1. The master server needs to enable binary logging and authorize a user to the slave node.

The main configuration file [mysqld] of the master server is as follows.

[Mysqld] Port = 3306 socket =/tmp/MySQL. sockskip-external-lockingkey_buffer_size = bytes = 1mtable_open_cache = bytes = 64mthread_cache_size = 8query_cache_size = 16mthread_concurrency = 8log-bin =/mydata/BINLOG/MySQL-bin defines the location of binary logs, the production environment should not be on the same disk as the database file. Binlog_format = mixedserver-id = 1 server-ID: use the default 1 datadir =/mydata/datasync_binlog = 1 to generate binary logs and write them to the disk.

Authorize a copy user to the slave Node

MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO [email protected]‘172.16.2.17‘ IDENTIFIED BY ‘slavepass‘;MariaDB [(none)]> FLUSH PRIVILEGES;

Slave node configuration:

The main configuration file [mysqld] of MySQL is as follows:

[Mysqld] Port = 3306 socket =/tmp/MySQL. sockskip-external-lockingkey_buffer_size = bytes = 1mtable_open_cache = 256sort_buffer_size = bytes = 64mthread_cache_size = 8query_cache_size = 16mthread_concurrency = 8 # log-bin = mysql-bin because nodes do not need binary logs, therefore, relay-log =/mydata/relaylog/relay-log is commented out to define the relay log binlog_format = mixedserver-id = 2 server-ID is defined as 2 datadir =/mydata/dataread_only = 1. The database is set to read-only, but is not valid for Super Users. Only for common users.

Run

CHANGE MASTER TO MASTER_HOST=‘172.16.2.15‘, MASTER_USER=‘realuser‘, MASTER_PASSWORD=‘passwd‘, MASTER_LOG_FILE=‘master_bin.000001‘, MASTER_LOG_POS=495;    START SLAVE;

After the configuration is complete, create a database and a table in the master database and view the table in the slave database. If yes, the configuration is successful.

The command to view the slave node status is: Show slave status. other meanings are as follows.

Mariadb [(none)]> show slave status \ G ***************************** 1. row ************************** slave_io_state: Current slave node Io status master_host: 172.16.2.16 master node IP master_user: slave use that user connection master node master_port: 3306 master node use TCP port connect_retry: 60 how long to read master_log_file: mysql-bin.000005 current master node binary log file read_master_log_pos: 581 current master node binary log file location relay_log_file: relay-log.000001 current slave node relay Log File relay_log_pos: 4 Current slave node relay Log File Location relay_master_log_file: the log file that the mysql-bin.000005 is replaying the master node is the slave_io_running: no slave node io_thread is started, if it is no, no binary log file has been requested from the master node. This thread is used to synchronize the binary log of the master node one by one to the relay log of the slave node. Slave_ SQL _running: whether to enable the SQL thread of no slave node. The task of this thread is to execute the SQL statements in the relay log one by one in read MySQL to write data. Master_server_id: ID of the 0 master node

Bytes -------------------------------------------------------------------------------------------

After the database has been running for a period of time, the master node has a large amount of data. If you add a slave node, You 'd better back up the master node to the slave node. Then configure data replication from the node to the master node, so that the data of the master and slave nodes can be synchronized and consistent quickly.

1. perform a full backup on the master server. Then, the backed up data is sent to the slave node.

# Mysqldump -- lock-all-tables -- all-databases -- master-Data = 2> master. SQL

# SCP master. SQL 172.16.2.17:/root

You need to create a database from the slave node to connect to the master node.

Mysql> grant replication slave, replication client on *. * to [email protected] '2017. 16.2.13 'identified by 'passwd'

Mysql> flush privileges;

2. Define the operating characteristics of slave nodes. Then recover the data.

In the configuration file, add the following sections to [mysqld] and comment out the binary log. This is a waste of space because binary logs are useless for slave nodes.

Relay-log =/mydata/relaylog/relay-Log

Server-id = 2 serviceid must not be the same as the ID of the master node.

Read_only = 1 set the database to read-only mode, but it is invalid for Super Users. A common user can only read and cannot modify it.

# Mysql </root/master. SQL restores data backed up by the master node to the database

3. You need to find the data in the backup file when restoring the data. When backing up the master node, the binary log file uses that file, and the location of the event ID in the binary log file. Probably as follows:

-- Change master to master_log_file = 'mysql-bin.000009 ', master_log_pos = 7925;

Then, connect to the master server in the database.

Change master to master_host = '192. 16.2.15 ', master_user = 'realuser', master_password = 'passwd', master_log_file = 'master _ bin.000001', master_log_pos = 172;

Start io_thread and SQL _thread

Start slave;

Bytes ------------------------------------------------------------------------------------------

Copy summary:

1. For the binary log that the master needs to start, there is a unique server-ID

2. BINLOG dump: send data from binary log event requests sent from the Service's Io thread to the other party.

3. Slave must enable relay logs, disable binary logs (recommended), and have a unique server-id.

Io thread: Requests binary logs from the master and saves them to the local relay logs.

SQL thread: read and execute SQL statements in the current relay log one by one.

4. There is usually only one master server for the slave server. Otherwise, the relay logs may be disordered.

5. mariadb 10 supports multi-source replication. The slave server supports multiple master servers but multiple databases.

6. Multi-level replication is supported. However, if the slave server is the master server of another slave server, you need to enable binary logs.

7. There can be multiple slave servers.

8. Because it is an asynchronous replication, the data on the slave server may lag behind the master server. This problem is hard to avoid.

Bytes -------------------------------------------------------------------------------------------

How to enable semi-sync? Functions available only after mysql5.5.8. Patches contributed by Google to MySQL are installed as plug-ins. They are not installed by default. Semisync_master.so and semisync_slave.so in lib/plugin the MySQL installation directory are dedicated to the master node and dedicated to slave nodes.

What is semi-sync? The master must wait for at least one slave node to completely copy the data.

Run the install command. Plugin_dir = in the configuration file [MySQL] is used to specify the location where the plug-in files are saved. If other plug-ins are stored in other locations, You need to define them here, so that the install command can be found.

How to install MySQL

MariaDB> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;

Several new server variables will be introduced after installation: show global variables like '% semi % ';

| Rpl_semi_sync_master_enabled | off | whether to enable this plug-in | rpl_semi_sync_master_timeout | 10000 | maximum waiting time for slave nodes, in milliseconds. If the slave node does not respond within the waiting time, the slave node does not wait. The slave node changes from semi-synchronous mode to asynchronous mode. | Rpl_semi_sync_master_trace_level | 32 | rpl_semi_sync_master_wait_no_slave | on does not need to wait for slave nodes.

You need to write data in the [MySQL] section of the configuration file.

 rpl_semi_sync_master_enabled=1 rpl_semi_sync_master_timeout=1000

After the settings, restart the service.

Settings on slave nodes

Mariadb> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so '; mariadb> show global variables like 'rpl _ semi %'; view the server variable generated after the plug-in is installed | rpl_semi_sync_slave_enabled | off whether the plug-in is enabled, on is enabled. | Rpl_semi_sync_slave_trace_level | 32 this item must be consistent with the master node.

Write Data in the [MySQL] section of the main configuration file

rpl_semi_sync_slave_enabled=1

Restart the IO thread on the slave node.

MariaDB> STOP SLAVE IO_THREAD;MariaDB> START SLAVE IO_THREAD;

To check whether semi-Sync Mode is enabled, you need to check it on the master.

Mariadb> show global status like '% semi % '; rpl_semi_sync_master_clients: clients that work in semi-synchronization have several primary nodes: average waiting time for io_thread; master node: Waiting For io_thread transactions

This article from the "red face easily die lofty sentiments long" blog, please be sure to keep this source http://wukui.blog.51cto.com/1080241/1554977

Implementation of master-slave replication experiment steps and semi-synchronization