Shulou(Shulou.com)06/01 Report--

Editor to share with you how to achieve the MySQL database "but" HA, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Implementation of MySQL database "accessible" HA 1. Database "accessible" analysis

The yardstick of "can"

The database realizes the "kind" type of "available".

MySQL database can be realized.

2. Disaster recovery of MySQL master-slave replication

Replication Analysis of MySQL holddown

Switch between master and slave scenes

How to realize disaster recovery with Master-Slave structure

1. What is the database "can" 1.1. What is "Ke" cluster?

A cluster is a cluster, 1 is a cluster, and the core of a cluster is redundancy, and the cluster is guaranteed to have a minimum service standard.

1.2. The measure of "Ke" cluster

It is measured by the reliability and maintainability of the system.

MTTF: average failure time, which is a measure of reliability

MTTR: measure the maintainability of the system

HA=MTTF/ (MTTF+MTTR) * 100%

SLA:99.999%: said that the annual downtime / downtime is no more than 6 minutes.

1.3. There are three ways to realize "can be".

Master-slave formula (symmetry)

This classic organization is usually organized through two nodes and two or more servers, with the platform as the primary node

(active), as the backup node (standy), the backup node should check the health status of the primary node at any time, when

If the primary node fails, the service will switch to the backup node to ensure the normal operation of the service.

Symmetrical type

Two nodes, both running different services and backing up each other, check the health of each other. When any node fails, this

The services on each node will be switched to another node.

Multi-machine type

It contains multiple nodes and multiple services, and each node needs to back up and operate different services. If there are problems, it will be migrated.

1.4. The "accessible" implementation of MySQL data is 1.4.1. Master-slave formula (symmetry)

Resources: two MySQL databases of the same version

Internal operation principle and mechanism of master-slave implementation

First Step: the master database server records the changes to the data in the binlog blog, and the binlog account must be opened.

Second Step: enter the Relay Log relay log from the library's Ibank O to read the binlog content of the main library and save it to the user's Relay Log relay log.

A single I / O thread will establish a normal client connection with the main library, and then the main library will start a binary dump thread, I / O line

The program reads the binlog update event through the dump thread. After the synchronization is completed, the program enters the sleep, and a new update will wake up again.

The format of Relay Log and Binlog is similar, which can be read by mysqlbinlog or show.

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

There are two types of replication in the front database.

Binlog blog position

The GTID style also depends on binlog.

Step 3: the SQL process from the server reads the event from the Relay Log and replays it from the library

When performing a replay operation from the server, you can configure the binlog to declare whether or not to write to the server.

1.4.2. Configure master-slave service step 1.4.2.1. Master-slave synchronization of Binlog's "log point" configuration

Configure master-slave server parameters

Create a database account that can be replicated and authorized on the Master server

Back up the Master database and initialize the Slave server data

Initiate replication link

Master server configuration chown-R mysql:mysql / usr/local/binlog/# configuration piece server_id=163log_bin=/usr/local/binlog/mysql-bin12345Slave server configuration server_id=196log_bin=/usr/local/binlog/mysql-binrelay_log=/usr/local/relaylog/relay-bin# when the slave is down, if the relaylog is corrupted, resulting in the partial relay log not being processed, then discard all unfinished items and reacquire the server Ensure integrity relay_log_recovery=1 # Let the slave database data read-only, super subscriber, super_read_only=onread_only=on# slave database replication link service will not restart with the database restart, need to "start skip_slave_start=on#" to ensure data integrity, through innoDB crash recovery mechanism to protect oh master_info_repository=TABLErelay_log_info_repository=TABLE#select * from mysql.slave_master_info # select * from mysql.slave_relay_log_info; main library authorizes mysql > use msyql;mysql > grant replication slave on *. * to 'syncuser'@'192.168.0.103'identified by' 123456 authorization MySQL > flush privileges;set global validate_password_policy=LOW;set global validate_password_length=6 Initialize data mysqldump-uroot-p123456-- master-data=2-- single-transaction-- routines-- triggers-- events-- databases mydb > mydb.sql create replication link mysql > CHANGE MASTER TOMASTER_HOST='192.168.0.102',MASTER_PORT=3306,MASTER_USER='syncuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=8122;mysql > start slave;mysql > show slave status\ G. Do you want to write the binlog of the slave library?

Writes are not written by default: because writing binlog consumes Icano, performance degrades, if you need to restore the number from the library

According to the Relay Log "import" export processing

Operating the update statement directly on the slave library will write the slave binlog.

What if you just need to write? Start synchronization and write binlog in my.cnf: log_slave_updates=on # of the slave library

Turn on synchronization and write "binlog should be dependent on follower to follower"

Problem: synchronize only three of these tables # Master configuration artifacts # which databases are not synchronized binlog-ignore-db=mysqlbinlog-ignore-db=testbinlog-ignore-db=information_schema# synchronized which libraries binlog-do-db=gamebinlog-do-db=mydb#Slave configuration artifacts # replicate which databases replicate-do-db=mydbreplicate-do-db=game# does not replicate which databases replicate-ignore-db=mysqlreplicate-ignore-db=test--replicate-wild-ignore-table=foo%.bar% does not replicate so that the name of the replication table Update bar1.4.2.1 for tables that start with foo and whose name begins with a table. The parameters of master-slave replication are different in different points of GTID. # on the basis of the previous version, you need to add gtid_mode=onenforce_gtid_consistency=on # to both master and slave servers to enable the causality of forced GTID to ensure that the replication link under the transaction GTID starts mysql > CHANGE MASTER TOMASTER_HOST='192.168.0.102',MASTER_PORT=3306,MASTER_USER='syncuser',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1. The following database operations cannot be create table tableName.... after starting GTID Select creates temporary tables in several transactions and updates innoDB and myisam tables in two transaction. Data master-slave replication disaster recovery processing 2.1. The copy format held by MySQL is 2.1.1. Statement-based replication (statement)

Advantages: few records, only record the statement, easy to understand

Disadvantages: insert into table1 (create_time) values (now ()), this now is not the time at that time.

2.1.2. Based on replication (row)

Advantages: there are almost no scenarios based on the "copy" method.

Disadvantages: the amount of data is too small

2.1.3. Mixed type replication (MIXED)

The mixed format defaults to statement, such as UUID () and ROW_COUNT ().

2.1. Asynchronous replication in MySQL master-slave replication mode: MySQL defaults to asynchronous replication with the best performance, but the data copied from master and slave has the best probability of synchronous replication: when the client sends a request, it will reply to the front-end transaction completion only when all slave libraries are written to Relay Log, resulting in the worst performance. But "very strong semi-synchronous replication: after" less "slave library completes Relay Log write, the transaction is returned to the front end master and slave to install mysql > install plugin rpl_semi_sync_master soname='semisync_master.so'rpl_semi_sync_master_enabledrpl_semi_sync_master_timeout # units in milliseconds If the master library waits for a reply from the slave library for more than this time, it will be switched to asynchronous. The above is all the contents of the article "how to achieve HA in MySQL database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.