MySQL high availability architecture design

Posted by haku on Mon, 31 Jan 2022 00:41:02 +0100

What problems does mysql replication solve

Realize data distribution on different servers
Load balancing of data reading
Enhanced data security
Achieve high database availability and failover
Realize online database upgrade

mysql binary log

Service layer log

Binary log
Slow check log
General log
Storage engine tier log
innodb redo log
innodb rollback log

Binary log

MySQL binary log is a binary file, which is mainly used to record MySQL statements that modify data or may cause data changes. The binary log records all operations that change the MySQL database and other additional information such as statement occurrence time, execution time, operation data, etc., but it does not record SQL statements that do not modify data such as SELECT and SHOW. Binary log is mainly used for database recovery, master-slave replication, and audit operations.

Format of binary log (segment)

Segment based format binlog_format=STATEMENT

Advantages: the amount of log recording is relatively small, saving disk and network I/O
Disadvantages: the data of the primary and standby servers replicated by MySQL may be inconsistent

Check whether it is on
SHOW VARIABLES LIKE 'log_bin';
Can be in my.ini Adding a configuration to the file requires a reboot
[mysqld]
log_bin = mysql_bin #Log file name
 Modify to segment log
set session binlog_format=statement;

Format of binary log (line)

Line based log format binlog_ format= ROW
, row format can avoid the inconsistency between master and slave in MySQL replication

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.02 sec)

same SQL Statement modifies 10000 pieces of data
 The segment based log format will only record this SQL sentence
 There will be 10000 records in the row based log, recording the data modification of each row separately

The data in the database is modified due to misoperation,At the same time, there is no backup
 Recovery time,We can analyze binary logs,Record in log
 The data modification operation is reverse processed to achieve the purpose of restoring data

If binary log is enabled, you can view the contents of binary log records.

mysqlbinlog --vv mysql-bin.000003 | more

shortcoming

The amount of logs is relatively large

Binary log format (mixed)

Mixed log format binlog_format= MIXED
characteristic

According to the SQL statement, the system decides on segment based and row based
Select from the log format
The amount of data executed by the SQL statement determines the size

Influence of mysql binary log format on replication

SQL name based replication (SBR)

The binary log format uses the statement format

The SQL segment based log is the SQL that is re executed from the database

advantage

The amount of logs generated is small, which saves network transfer I/O
It is not mandatory that the table definitions of the master and slave databases are exactly the same
It is more flexible than row based replication

shortcoming

For non deterministic events, the consistency of master-slave replicated data cannot be guaranteed
For stored procedures, contactors and custom functions, you can also modify them
Can cause data inconsistency
Compared with row based replication, more row locks are required when executing from

Row based replication (RBR)

The binary log format uses the line based log format

The row based log is to directly apply the changes to the database rows from the database

advantage

It can be applied to any SQL replication, including non deterministic functions that have been stored
Cheng et al
You can reduce the use of database locks

shortcoming

The table structure of the master-slave database is required to be the same, otherwise the replication may be interrupted
It is impossible to execute the trigger on the slave server alone (row based replication is applied directly on the slave server, and the master database modifies the row instead of re executing sql on the slave server)

Mixed mode

Switch between the above two according to the actual content

How mysql replication works

1. The master writes the changes to the binary log
2. Read the binary log changes from the master and write them to the relay_log (log point based replication, GTID based replication)
3. Replay relay on_ Log in log

Log point based replication

Log point based replication configuration steps

Establish a replication account on the primary DB server

CREATE USER 'repl' @' IP paragraph' identified by 'PassWord';
GRANT REPLICATION SLAVE ON *.* TO 'repl' @ 'IP paragraph';

Configure primary database server

log_bin = mysql-bin
server_id = 1

Configure slave database server

log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_update = on [Optional]
read_only = on [Optional]

relay_log: The default is the host name. If it is not specified, there will be problems in modifying the host name in the future
log_slave_update: (Connected replication (if the slave server needs to be replicated as the master server, it needs to be configured)
read_only: Avoid modifying the slave server data due to the configuration error of the slave server.

Initialize slave data

mysqldump backs up the main database. This method needs to lock the database. If the business traffic is very frequent, using this method will cause blocking. It will affect the concurrency of the database. Master data records the offset information of binary log files-- The single transaction hybrid storage engine needs to use other parameters.

xtrabackup is a database hot backup software. If the storage engines are innodb, the backup will not produce blocking. A hybrid storage engine will.

mysqldump --master-data=2 --single-transaction
xtrabackup --slave-info

Start the replication connection and use it in the slave library

CHANGE MASTER TO MASTER_HOST= 'master_host_ip',
MASTER_USER= 'repl'
MASTER_PASSWORD= 'PassWord'
MASTER_LOG_FILE= 'mysql_log_file_name',
MASTER_LOG_POS=4;

Actual operation (MySQL 5.7 installed using yum)

Creating replication users and authorizations in the master library

create user repl@'192.168.233.%' identified by '123456';
grant replication slave on *.* to repl@' 192.168.233.%';

Modify my CNF configuration file, in the / etc / directory
[the external chain picture transfer fails. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-kaxeq6ue-1623547394676) (15a27aaeb9884646a5362f19e1e0560)]

Restart database

Before backing up the database, you need to lock the main database, and then back up. Otherwise, inserting data in the time period before the backup has been imported will cause the problem of data inconsistency.

# Lock
flush tables with read lock;
# Unlock
unlock tables;

Back up the main database and use mysqldump

If the version of the master-slave database is inconsistent, it is best to back up only the business database. If the system database is also backed up, there may be problems due to the inconsistent version.

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p >> /opt/all.sql;

mysql5.6 when using mysqldump for backup, specify the specific database to be backed up, and use – database
mysql5.7 when using mysqldump for backup, specify the specific database to be backed up, and use – databases

All. From server 129 Send SQL to 130

scp -r all.sql root@192.168.233.130:/opt/

Modify restart from server
[the external chain picture transfer fails. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-nIu2S58i-1623547394678)(9970044E4BC44AC3804B0E2243640C89)]

Import the slave library to make the master-slave data consistent.

[root@hadoop130 opt]# mysql -uroot -p<all.sql

Log in to MySQL, configure replication connectivity, MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154, file and offset can be from all Check in the SQL file.

Or use the mysql command line to view

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |     154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql> CHANGE MASTER TO MASTER_HOST= '192.168.233.129',
    -> MASTER_USER= 'repl',
    -> MASTER_PASSWORD= '123456',
    -> MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

start-up

mysql> start slave;

mysql> show slave status \G

[the external chain picture transfer fails. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-wau27Ijj-1623547394679)(6BD592B857624192AD499AAD70AC2DFF)]

advantage

It is the earliest replication technology supported by MySQL, and there are relatively few bugs
There are no restrictions on SQL queries
Fault handling is relatively easy

shortcoming

It is difficult to retrieve the log point information of the new master during failover

GTID based replication

Since version 5.6

If the offset of log point based replication is wrong, duplicate replication or missing replication may occur, while the same transaction of GTID based replication is only executed once from the library.

GTID based configuration steps

GTID is the global transaction ID, which is guaranteed for each transaction submitted on the master database
A unique ID can be generated in the replication cluster
GTID=source_id:transaction_id

Configure primary database server

#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
    
#binlog
log_bin = mysql-bin
log-slave-updates = on
binlog_format = row
sync-master-info = 1
sync_binlog = 1

enforce-gtid-consiste(force gtid uniformity)
Impact: 1.out of commission create table ... select Such a statement
     2.Use in transactions Create temporar table Creating a temporary table and updating the transaction table and non transaction table with association will report an error
log-slave-updates = on(Record the modification log from the master server from the slave server)
be careful: mysql>=5.7 You don't need this parameter.
reference resources: https://blog.51cto.com/hcymysql/1579197

Configure slave database server

#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on

#binlog
log_bin = mysql-bin
log-slave-updates = on
binlog_format = row
sync-master-info = 1
sync_binlog = 1

#relay log
skip_slave_start = 1
read_only = on[proposal]

master_info_repository =TABLE [proposal]
relay_log_info_repository =TABLE [proposal]


The latter two configurations specify the information of connecting from the server to the master server and the storage method of the intermediate log. By default, it is stored in the file. Through the above configuration, the information can be stored in the table of the database. In case of database crash, it can be saved through the innodb Restore the characteristics of the transaction to ensure that the slave server can replicate in the correct location.

skip_slave_start = 1 [proposal]
reference resources: https://www.cnblogs.com/liang545621/p/9400031.html

Create a copy account

Initialize the slave server database to keep the master-slave consistency

Start GTID based replication

CHANGE MASTER TO MASTER_HOST= 'master_host_ip' ,
MASTER_USER= 'repl',
MASTER_PASSWORD= 'PassWord',
MASTER_AUTO_POSITION = 1;

Case operation

1. Create a user for replication in the main database of 129, which has been created above.

2. Add two parameters to the main library compared with the previous one

Restart mysql

3. Add parameters from library

4. Initialize the data and restore the data from 129 server to 130 server

mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p >> /opt/all_gtid.sql;

Send the sql file to 130

scp -r all_gtid.sql root@192.168.233.130:/opt/

Import the slave library to make the master-slave data consistent.

[root@hadoop130 opt]# mysql -uroot -p123456<all_gtid.sql

5. Configure master-slave on the slave Library of 130, which needs to be closed first

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO MASTER_HOST= '192.168.233.129',
    -> MASTER_USER= 'repl',
    -> MASTER_PASSWORD= '123456',
    -> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

advantage

It is convenient to perform failover (globally unique transaction id)
The slave library will not lose any changes on the master library

shortcoming

Fault handling is complicated
There are certain restrictions on the SQL executed

Issues to consider when selecting replication mode

MySQL version used
Replication architecture and master-slave switching mode
High availability management components used
Support for applications

Replication architecture

MySQL5. Before 7, a slave library could only have one master library

MySQL5. After 7, it supports one slave multi master architecture

One master multi slave architecture

advantage

Simple configuration
Multiple slave libraries can be used to share the read load

purpose

Use different slave libraries for different businesses
Put one from the library to the remote IDC for disaster recovery
Share the read load of the main library

Primary primary replication topology with standby database

Handling of common problems in Mysql replication

Master slave replication error caused by data corruption or loss

Error caused by unexpected downtime of primary or secondary Library

Skip binary log events using
 Inject empty transactions to restore the interrupted replication link first
 Then use other methods to compare the data on the master-slave server

The binary log on the primary library is corrupted
The relay log on the standby database is damaged
Master slave replication error caused by data modification on slave Library
Not a unique server_id or server_uuid

server_uuid Is recorded in the data directory auto.cnf In the file

max_ allow_ Master slave copy error caused by packet setting

Topics: MySQL binlog

Programmer Think

MySQL high availability architecture design

What problems does mysql replication solve

mysql binary log

Binary log

Format of binary log (segment)

Format of binary log (line)

Binary log format (mixed)

Influence of mysql binary log format on replication

SQL name based replication (SBR)

advantage

shortcoming

Row based replication (RBR)

advantage

shortcoming

Mixed mode

How mysql replication works

Log point based replication

Log point based replication configuration steps

Actual operation (MySQL 5.7 installed using yum)

advantage

shortcoming

GTID based replication

GTID based configuration steps

Case operation

advantage

shortcoming

Issues to consider when selecting replication mode

Replication architecture

One master multi slave architecture

Primary primary replication topology with standby database

Handling of common problems in Mysql replication

Master slave replication error caused by data corruption or loss

Hot Topics