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

MySQL master-slave replication how to achieve high availability, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

1. MySQL master-slave replication to achieve high availability 1.1. Database is always the most fragile link in the system.

80% are queries.

Cache to protect the database: Redis, ElasticSearch

The database system should have certain disaster recovery ability.

The data state is obvious.

1.2. The HA implementation of the database is 1.2.1. Highly calculable mode

MTTF: mean time between failures

MTTR: average maintenance time

HA=MTTF/ (MTTF+MTTR) * 100% (this is commonly known as 99.95%)

1.2.2. Three ways of high availability of database

Master-slave (asymmetric)

Master-slave, hot data backup, slave database is generally used for real-time backup, slave computer can be quickly switched to master database, read-write separation (data consistency), slave library is generally set to read-only

The front-end application is cached, and the back-end application Report is checked from the database.

Symmetrical mode

Two nodes, master and slave to each other, double master mode (if the primary key of the two databases is automatically generated, it needs to be initially set to different)

Alysee 1001, 1003, 1005, Bly2, 1004, 1006.

Dual-master data synchronization must be real-time

Multi-machine mode

MHA

MMM

MyCat (can achieve dual host eagerness)

1.2.3. The logic of master-slave implementation

Resources: two databases

Master will record changes in binlog

Slave reads data from the main library binlog and writes it to relaylog

Relaylog has the same format as binlog, and you can read relaylog with mysqlbinlog

Mysql > show relaylog events in 'relay-bin.000001'

Postition mode of binlog

GTID method: MySQL version > = 5.7

1.3. Configure master-slave replication

Master server configuration

# slow query switch slow_query_log=ONslow_query_log_file=/usr/local/slowlog/slow.logslow_launch_time=4long_query_time=3# chown-R mysql:mysql / usr/local/slowlog/

Binlog Settin

Server_id=213log_bin=/usr/local/binlog/mysql-bin # just need to prefix the name with binlog_format=rowexpire_logs_days=3max_binlog_size=200m

Slave server configuration

# slow query switch slow_query_log=ONslow_query_log_file=/usr/local/slowlog/slow.logslow_launch_time=4long_query_time=3# chown-R mysql:mysql / usr/local/slowlog/

Master-slave settings of slave library

Server_id=214relay_log=/usr/local/relaylog/relay-binrelay_log_recovery=1 # ensure integrity read_only=on # read-only skip_slave_start=on # slave from the server will not start the replication link by itself after restart. It needs to be started manually. It is recommended to start it manually. The advantage is to start master_info_repository=TABLE # after checking correctly. Save the information of the master-slave service in the table relay_log_info_repository=TABLE # save the information of the master-slave service in the table.

Master-slave indicates out-of-sync database

# Master# out of Sync Database binlog-ignore-db=mysqlbinlog-ignore-db=information_schema# only synchronizes certain databases binlog-do-db=icoding_admin# Slave# replicates which databases replicate-do-db=icoding_adminreplicate-do-db=game# does not replicate which databases replicate-ignore-db=mysqlreplicate-ignore-db=information_schema

First set up an authorized account on Master

# Master is authorized to slavemysql > use mysql;mysql > grant replication slave on *. * to 'synuser'@'192.168.0.214' identified by' 123456 authorization MySQL > flush privileges

Initialize Slave data

# if the database version of Master and Slave is the same, Master mydb.sql#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154; uses the exported log pos

Initiate replication link

# execute mysql > change master to master_host='192.168.0.213',master_port=3306,master_user='synuser',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=154 on slave

Matters needing attention

When master-slave replication occurs, the UUID of the master-slave database cannot be the same. If it is mirrored, delete the UUID file before starting, it will re-create cd / var/lib/mysqlcat auto.cnf # delete it server-uuid=890980d9-b072-11ea-8ab8-00163e03668d

Start master-slave replication

# show slave status\ G can view the status of master-slave replication and log errors # start slavemysql > start slave;# stop slavemysql > stop slave

Assignment 1: build a two-master structure

Use GTID for replication links

# default GTID is not enabled # if the following operations are not available # create table. Select * from xxx; not available # cannot create and delete tables in a transaction # update innodb engine and myisam are not available in a transaction

Inadvertently deleted a data, can do delay from the library

# it is impossible to restore the data in time when backing up from the library # Let my slave achieve delayed synchronization mysql > stop slave;mysql > change master to master_delay=10; # delay 10 seconds mysql > start slave;mysql > show slave status\ G

Master (real-time slave, delay slave)

After reading the above, have you mastered how to achieve high availability of MySQL master-slave replication? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.