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

This article mainly introduces the basic essentials and summary of MySQL master-slave replication, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

1.1 introduction to MySQL master-slave replication

The master-slave replication scheme of MySQL database is similar to the file-level replication using commands such as scp/rsync, which is the remote transfer of data, except that the master-slave replication of MySQL is its own function and does not need the help of third-party tools. Moreover, the master-slave replication of MySQL is not a direct copy of the files on the database disk, but is copied to the local cloud server to be synchronized through logical binlog logs. Then the local thread reads the SQL statements in the log and reapplies them to the MySQL database.

MySQL database supports replication of different business scenarios such as one-way, two-way, chain cascade, ring, and so on. In the process of replication, one server acts as a master server (Master), receiving content updates from users, while one or more other servers act as slave servers (Slave), receiving log contents from binlog files of autonomous servers, parsing SQL and updating them to slave servers, so that the master and slave servers are consistent.

If chain-level replication is set, the slave server itself acts not only as a slave server, but also as a master server for the slave server below it. The form of chain cascade replication is similar to that of Amure-> Bmure-> C.

1.2 Enterprise Application scenario of MySQL Master-Slave replication

The MySQL master-slave replication cluster function makes it possible for MySQL databases to support large-scale and highly concurrent reads and writes, and effectively protects the data backup in physical server downtime scenarios.

Application scenario 1: real-time data backup of slave server as master server

The setting of master-slave server architecture can greatly enhance the robustness of MySQL database architecture. For example, when there is a problem with the master server, we can manually or set to automatically switch to the slave server to continue to provide services, and the data of the slave server is almost the same as the master database at the time of downtime.

This is similar to the NFS storage data synchronized to the backup NFS server through inotify+rsync, except that MySQL's replication scheme is its own tool.

When using the replication function of MySQL to do data backup, in the case of hardware failure or software failure, the data backup is effective, but in the case of artificially executing drop, delete and other statements to delete data, the backup function from the library is useless, because the deleted statement will also be executed from the server.

Application scenario 2: master-slave server to achieve read-write separation, slave server to achieve load balancing

The master-slave server architecture can separate the read and write of the request of the user (client) through the program (PHP, java, etc.) or agent software (mysql-proxy, Amoeba), that is, the slave server can only process the user's select query request, reducing the user query response time and the access pressure brought by reading and writing on the master server at the same time. The updated data (such as uodate, insert, delete statements) is still left to the master server to ensure that the master server and slave server are synchronized in real time.

Application scenario 3: split access to multiple servers according to business importance

Several different slave servers can be split according to the company's business. For example, there are slave servers that provide query services for external users, slave servers that internal DBA uses for data backup, and background servers that provide access to internal staff, scripts, log analysis and slave servers for developers to query. This split not only reduces the pressure on the master server, but also makes the database not affect each other, such as external user browsing, internal user business processing and DBA personnel backup.

1.3 introduction to the principle of MySQL master-slave replication

The master-slave replication of MySQL is an asynchronous replication process (although it generally feels real-time). The data will be replicated from one MySQL database (we call it Master) to another MySQL database (we call it Slave). The whole process of master-slave replication between Master and Slave is completed by three threads. There are two threads (the SQL thread and the IO thread) on the Slave side and the other thread (the Icano thread) on the Master side.

In order to realize the master-slave replication of MySQL, the Binlog recording function on Masterside must be turned on first, otherwise it cannot be realized. Because the whole replication process is actually that Slave takes the Binlog log from the Master side, and then performs the various SQL operations recorded in the acquired Binlog log on the Slave in the same order.

The following is a summary of the key points of the principle of MySQL master-slave replication.

◆ master-slave replication is asynchronous logical SQL statement-level replication.

When ◆ is replicated, the master library has one I / O thread, and the slave library has two threads, Igamot O and SQL threads.

The necessary condition for ◆ to realize master-slave replication is that the master library should turn on the record binlog function.

The server-id of ◆ as a replicated MySQL node cannot be the same.

The ◆ binlog file records only SQL statements that make changes to the data (changes to the contents of the autonomous database), not any query (select,show) statements.

Forget the database password

Mysqld_safe--defaults-file=/data/3306/my.cnf-skip-grant-table-user=mysql &

Then log in without entering a password

Mysql-uroot-p-S / data/3306/mysql.sock

Set the password after entering the database

Updatemysql.user set password=password ('oldboy123') where user='root' andhost='localhost'

Refresh permissions

Flushprivileges

1.4 MySQL master-slave replication operation steps

Architectural practice:

3306Mutual talk-> 3307

Master-> slave

1. Open the main library binlog and configure server-id

[root@db02~] # egrep-I "server-id | log-bin" / data/3306/my.cnf

Log-bin= / data/3306/mysql-bin

Server-id= 6

Restart the service

/ data/3306/mysqlrestart

Slave library

[root@db02~] # egrep-I "server-id | log-bin" / data/3307/my.cnf

# log-bin= / data/3307/mysql-bin

Server-id= 7

two。 The main library creates users

Grantreplication slave on *. * to 'rep'@'172.16.1.%' identified by' oldboy123'

Mysql > grant replication slave on *. * to 'rep'@'172.16.1.%' identified by' oldboy123'

Query OK, 0rows affected (0.04 sec)

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | root | 127.0.0.1 | |

| | rep | 172.16.1% | |

3. Export data from the main library

According to what we said, we can just take the backup at 00:00 today.

Lock the table flush table with read lock first

Mysql > flush table with read lock

Query OK, 0rows affected (0.00 sec)

Mysql > showmaster status

+-+

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

+-+

| | mysql-bin.000001 | 120 | |

+-+

1 row in set (0.00 sec)

Mysqldump

Cp/tar

Xtrabackup

Getting the location point is the key [A1] sed-n '22p'all_2017-06-28.sql

The main library is complete.

[root@db02] # mysqldump-B-- master-data=2-- single-transaction-S / data/3306/mysql.sock-A | gzip > / data/backup/all_$ (date+%F) .sql.gz

[root@db02 ~] # ls-l / data/backup/

Total dosage 228

-rw-r--r-- 1root root 178468 June 28 11:11 all_2017-06-28.sql.gz

Unlock the main library:

Mysql > unlock table

Query OK, 0rows affected (0.00 sec)

4. Import full data from the library

[root@db02scripts] # cd / data/backup/

[root@db02backup] # gzip-d all_2017-06-28.sql.gz

[root@db02backup] # mysql-S / data/3307/mysql.sock start slave

Query OK, 0rows affected (0.03 sec)

Mysql > show slave status\ G

The following shows the success.

[root@db02backup] # mysql-S / data/3307/mysql.sock-e "show slave status\ G" | egrep "_ Running | Behind_Master" | head-3

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

Seconds_Behind_Master:0

3306 View managed hosts

Mysql > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | Slave_UUID | |

+-+

| | 7 | | 3307 | 6 | 295750c8-54c1-11e7-80dd-000c29fc02ee | |

| | 8 | | 3308 | 6 | 328e8c80-54c1-11e7-80dd-000c29fc02ee | |

+-+

2 rows in set (0.00 sec)

Read the above about the basic essentials and summary of MySQL master-slave replication, hoping to bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.