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

This article introduces what is master-slave replication in Mysql MHA deployment, the content is very detailed, interested friends can refer to, I hope it can be helpful to you.

Mysql MHA deployment-Master-Slave replication

Architecture description:

Reference: http://www.zhaibibei.cn/mysql/mha/

Build master-slave replication

1 Mysql installation

2 rac1 (187master), rac3 (223Slave) configure asynchronous replication

3 rac1 (187master), rac2 (188Slave) configuration semi-synchronous replication

We establish the master-slave relationship according to the above topology. 192.168.2.223 is semi-synchronous and 192.168.2.223 is asynchronous.

Use GTID-based replication, otherwise it is recommended to turn off the GTID feature

I won't make any more introductions here. For details, see the previous topic.

Note that when doing master-slave synchronization, it is recommended to clean up the relevant information of the slave library.

Reset master; reset slave all

Time synchronization:

[root@rac1 ~] # ntpdate cn.pool.ntp.org

14 Mar 15:37:01 ntpdate [31863]: step time server 203.107.6.88 offset 2.987670 sec

[root@rac1 ~] # date

Sat Mar 14 15:37:17 CST 2020

1 Mysql installation

-rac1 (master 187), rac2 (slave 188), rac3 (slave 223) install Mysql respectively

1.1 configure Limits

[root@rac1 package] # cat > > / etc/security/limits.conf/etc/sysctl.conf alter user 'root'@'localhost' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

[mysql@rac1 mysql] $mysql-S / data/mysql/data/mysql.sock-uroot-p123456

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

+-+

4 rows in set (0.00 sec)

2 rac1 (187master), rac3 (223Slave) configure asynchronous replication

In this section, our content is MySQL replication, and there are two forms of MySQL replication.

Based on binary log file location

Based on GTID

This section is the first based on the binary log file location

2.1 enable binary logging function

No matter which way we use, we need to enable binary logging.

If it is not enabled, you need to add the following parameters to the my.cnf file, and restart the database to take effect

-main library 187rac1

[mysqld]

Server-id = 1

Binlog_format = row

Log_bin = / datalog/mysql/binlog/mysql-bin.log

Expire_logs_days = 14

Log-slave-updates=ON

-from library 223 rac3

[mysqld]

Server-id = 3

Binlog_format = row

Log_bin = / datalog/mysql/binlog/mysql-bin.log

Expire_logs_days = 14

Log-slave-updates=ON

Read_only=1

-restart mysql

[mysql@rac1 ~] $service mysqld stop

Shutting down MySQL. [OK]

[mysql@rac1 ~] $service mysqld start

Starting MySQL. [OK]

2.2 check whether the UUID is consistent

It should be noted that if the slave library is cloned from the master library, the uuid will be the same, and an error will be reported.

The file is located in the auto.cnf file of daadir

Vim / data/mysql/data/auto.cnf

If you can delete the file and restart the database, a new file will be generated.

2.3 establish a replication account

Next, let's set up a separate account for replication.

Master library and slave library

[mysql@rac1] $mysql-S / data/mysql/data/mysql.sock-uroot-p

Mysql > CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY' rpl'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2.187'

Mysql > CREATE USER 'repl'@'192.168.2.223' IDENTIFIED BY' rpl'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2.223'

Mysql > flush privileges

Mysql > select host,user from user

+-+ +

| | host | user |

+-+ +

| | 192.168.2.187 | repl | |

| | 192.168.2.223 | repl | |

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

+-+ +

5 rows in set (0.00 sec)

Here we restrict that the account can only connect from two synchronous servers.

2.4 back up the main library

-create test data (simulated production data)

Mysql > CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Query OK, 1 row affected (0.00 sec)

Mysql > use jumptest

Database changed

Mysql > source / package/mysql/jumpserver_bak_2020_03_13_22_00_01.sql

-backup

[mysql@rac1] $mysqldump-S / data/mysql/data/mysql.sock-uroot-p-- databases jumptest-- single-transaction-- master-data=2-- set-gtid-purged=off-- triggers-- events-- routines > / tmp/dumpmaster.sql

Enter password:

[mysql@rac1 ~] $ll-rth / tmp/dumpmaster.sql

-rw-r--r-- 1 mysql mysql 19m Mar 14 17:04 / tmp/dumpmaster.sql

2.5 File transfer

Next, transfer the dump file of the main library to the backup, and then change the file permissions of the standby library.

Main library

[mysql@rac1 ~] $scp / tmp/dumpmaster.sql root@192.168.2.223:/tmp

Slave library

[root@rac3 ~] # chown mysql:mysql / tmp/dumpmaster.sql

2.6 prepare the library to import data

Next, we import the backed-up data into the backup

[mysql@rac3] $mysql-S / data/mysql/data/mysql.sock-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Query OK, 1 row affected (0.01sec)

Mysql > use jumptest

Database changed

Mysql > source / tmp/dumpmaster.sql

2.7 start synchronization

Next, let's start synchronization.

First, let's look at the information of master in the dumpmaster.sql file.

Mysql > show master status

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | mysql-bin.000004 | 34751569 | c1227971-65b3-11ea-bf67-080027839e5c:1-297 |

+-- +

1 row in set (0.00 sec)

-start synchronization command 2.223 from the library

Mysql > change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34751569

Query OK, 0 rows affected, 2 warnings (0.03 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

2.8 View synchronization status from the library

Use the following command to see if the synchronization is normal

The main concerns are as follows

Slave_IO_Running needs to be YES

Slave_SQL_Running needs to be YES

Seconds_Behind_Master needs to be 0

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.187

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 34751569

Relay_Log_File: mysql-relay.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

.

Seconds_Behind_Master: 0

.

2.9 restart and reset replication

Use the following command to shut down and restart

Mysql > stop slave

Mysql > start slave

We can restart the IO process or the SQL process independently

Mysql > stop slave sql_thread

Mysql > stop slave io_thread

Mysql > start slave io_thread

Mysql > start slave sql_thread

Reset replication using the following command

Mysql > reset slave all

3 rac1 (187master), rac3 (188Slave) configuration semi-synchronous replication

3.1 introduction of semi-synchronization

In asynchronous replication, after the master sends the binary log to the slave library, there is no need to confirm whether the slave library is accepted and applied, which may result in data loss.

MySQL has introduced the semi-synchronous function since version 5.5, which is equivalent to the maximum protection mode of Oracle DG. It requires the slave database to receive and apply the logs before the master database is submitted, which ensures the data.

Enabling semi-synchronization requires the following requirements:

MySQL 5.5 and above

Variable have_dynamic_loading is YES

3.2 188 initialize master database data from the library

188:

Mysql > CREATE DATABASE jumptest DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Query OK, 1 row affected (0.01sec)

Mysql > use jumptest

Database changed

Mysql > source / tmp/dumpmaster.sql

3.3 create a replication account

[mysql@rac1] $mysql-S / data/mysql/data/mysql.sock-uroot-p

Mysql > CREATE USER 'repl'@'192.168.2.187' IDENTIFIED BY' rpl'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2.187'

Mysql > CREATE USER 'repl'@'192.168.2.188' IDENTIFIED BY' rpl'

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.2.188'

Mysql > flush privileges

Mysql > use mysql

-from the library 188

Mysql > select host,user from user

+-+ +

| | host | user |

+-+ +

| | 192.168.2.187 | repl | |

| | 192.168.2.188 | repl | |

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

+-+ +

5 rows in set (0.00 sec)

-main library 187

Mysql > select host,user from user

+-+ +

| | host | user |

+-+ +

| | 192.168.2.187 | repl | |

| | 192.168.2.188 | repl | |

| | 192.168.2.223 | repl | |

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

+-+ +

6 rows in set (0.00 sec)

3.4 load semi-synchronous plug-in

Users need to have SUPER permission because they need to perform INSTALL PLUGIN, SET GLOBAL, STOP SLAVE and START SLAVE operations.

Master library and slave library

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

Semi-synchronous master and slave plug-ins are installed here

Considering that it may be necessary to switch between master and slave, both master and slave libraries are installed here.

Confirm whether the load is successful

Mysql > show plugins

.

| | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | |

| | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | |

+-+

Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%'

+-+ +

| | PLUGIN_NAME | PLUGIN_STATUS |

+-+ +

| | rpl_semi_sync_master | ACTIVE |

| | rpl_semi_sync_slave | ACTIVE |

+-+ +

2 rows in set (0.00 sec)

3.5 enable semi-synchronization

First, we enable the semi-synchronous plug-in.

Main library 187

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

From library 188

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

Mysql > change master to master_host='192.168.2.187', master_user='repl', master_password='rpl',master_log_file='mysql-bin.000004',master_log_pos= 34752186

Mysql > start slave

Mysql > show slave status\ G

After that, you need to restart the IO thread for semi-synchronization to take effect, or you can restart replication directly.

Slave library

Mysql > STOP SLAVE IO_THREAD

Mysql > START SLAVE IO_THREAD

If the slave library cannot communicate with the master library for more than a certain period of time, it will be automatically reduced to asynchronous mode.

The time is controlled by the rpl_semi_sync_master_timeout parameter, in milliseconds

3.6 View semi-synchronous status

We use the following command to see if semi-synchronization is working properly

Both the master of the master library and the slave of the slave library need to be ON

Main library

Mysql > show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | ON |

+-+ +

1 row in set (0.02 sec)

Mysql > show status like 'Rpl_semi_sync_slave_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | OFF |

+-+ +

1 row in set (0.00 sec)

Slave library

Mysql > show status like 'Rpl_semi_sync_slave_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > show status like 'Rpl_semi_sync_master_status'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_status | OFF |

+-+ +

1 row in set (0.01 sec)

3.7 write to configuration file

Next we write the command in the configuration file so that it starts automatically after reboot

Considering that the master-slave switch may be needed later, here the semi-synchronous master and slave on the master-slave library are set to start.

Master library 187 and slave library 188

[mysql@rac1 ~] $vim / etc/my.cnf

#-MySQL semi Replication Setting-#

Plugin_dir = / usr/local/mysql/lib/plugin

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_slave_enabled = 1

Rpl_semi_sync_master_timeout = 5000

-- from library 188223

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

The restart of master 187 and slave library 188 takes effect:

[mysql@rac1 ~] $service mysqld restart

Shutting down MySQL.... [OK]

Starting MySQL. [OK]

Test data synchronization

Main library 187

Mysql > create table cjc01 (id int)

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into cjc01 values (1)

Query OK, 1 row affected (0.02 sec)

Mysql > insert into cjc01 values (2)

Query OK, 1 row affected (0.00 sec)

Mysql > insert into cjc01 values (3)

Query OK, 1 row affected (0.01sec)

Mysql > update cjc01 set id=100 where id=1

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > delete from cjc01 where id=2

Query OK, 1 row affected (0.00 sec)

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

+-+

2 rows in set (0.00 sec)

From library 188

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

+-+

2 rows in set (0.00 sec)

From library 223

Mysql > select * from cjc01

+-+

| | id |

+-+

| | 100 |

| | 3 |

+-+

2 rows in set (0.00 sec)

What is master-slave replication in Mysql MHA deployment is shared here. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

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.