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

This article mainly introduces the master-slave principle, configuration and testing handouts of MySQL 5.7.I hope to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

First, prepare two CVMs, one master server (Master) and the other slave server (Slave), and then make sure that the version of Master and Slave is the same and that Master cannot be higher than the version of Slave. The general robust approach is to make the version the same, because the binlog (binary log) format may be different between different versions of MySQL, which will eventually lead to synchronization exception.

Environment: software environment

System: CentOS 6. Or 7.

MySQL version: 5.7

Host settings: IP hostname role 192.168.1.101MySQL-001master192.168.1.102MySQL-002slave II, Mysql master-slave basic principles

The master-slave synchronization of Mysql is that when the data of master (master library) changes, it will synchronize to slave (slave library) in real time.

Master-slave replication can horizontally expand the load capacity of the database, fault tolerance, high availability, data backup.

Whether it is delete, update, insert, or creating functions or stored procedures, they are all on master. When master has operations, slace will quickly accept these operations and synchronize.

The main implementation principle is on the master machine, the master-slave synchronization time will be written in a special log file (binary-log); on the slave machine, slave reads the master-slave synchronization events and makes corresponding changes in the slave library according to the read event changes. There are three main forms of detailed master-slave synchronization: statement, row, mixedstatement: sql statements for database operations will be written to row in binlog: changes in each piece of data will be written to binlog. The mixture of mixed:statement and row. Mysql decides when to write statement format and when to write row format binlog. Operation on the master machine:

When the data on the master changes, the event change is written sequentially to the binlog. When slave links to master, the master machine starts the binlog dunp thread for slave. When the binlog of the master changes, the binlog dump thread notifies the slave and sends the corresponding binlog content to the slave.

Operate on the slave machine:

When master-slave synchronization is turned on, two threads are created on the slave: I\ O thread. The thread connects to the master machine, and the binlog dump thread on the master machine sends the contents of the binlog to the I\ O thread. The I / O thread receives the binlog content and then writes the content to the local relay log;sql thread. The thread reads the ralay log written by the I / O thread. And according to relay log. And do the corresponding operation to the slave database according to the content of relay log.

Master-Slave configuration 1. Master configuration file settings

The general mysql configuration file is in / etc/my.cnf

(if you can't find it, it could be in these directories: / etc/mysql/my.cnf,/usr/local/mysql/etc/my.cnf,~/.my.cnf)

[root@MySQL-001 ~] # vim / etc/my.cnf [mysqld] basedir=/usr/local/mysqldatadir=/data/mysqldatasocket=/tmp/mysql.sockuser=mysqlport=3306# master configuration server-id=1 # Server id (set unique identity) binlog-do-db=employees # Library to be synchronized to the slave binlog-ignore-db=mysql # not to the library synchronized by the slave (multiple writes and multiple lines) binlog-ignore-db=information_schemabinlog-ignore-db=performance_schemabinlog-ignore-db=syslog -bin=mysql-bin # Open log (the host needs to open it) You can specify an absolute path Expire_logs_days=90 # automatically cleans up the log files from 90 days ago. You can modify and restart the database as needed to make the configuration effective: CentOS 6. Update: [root @ MySQL-001 ~] # service mysqld restart [root@MySQL-001 ~] # service mysqld status SUCCESS! MySQL running (15607) CentOS 7.9: [root@MySQL-001 ~] # systemctl restart mysqld.service [root@MySQL-001 ~] # systemctl status mysqld.service mysqld.service-MySQL Server Loaded: loaded (/ usr/lib/systemd/system/mysqld.service; enabled) Active: active (running) since four 2018-05-17 11:42:02 CST 2h 5min ago Docs: man:mysqld (8) http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 29959 (mysqld) CGroup: / system.slice/mysqld.service └─ 29959 / opt/mysql/bin/mysqld-- defaults-file=/etc/my.cnf 17 11:42:02 tcloud-118 systemd [1]: Started MySQL Server. Test whether log_bin successfully opens [root@MySQL-001 ~] # mysql-uroot-pmysql > show variables like'% log_bin%' +-- +-- + | Variable_name | Value | +-- -+ | log_bin | ON | | log_bin_basename | / opt/mysql/logs/mysql-bin | | log_bin_index | / Opt/mysql/logs/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +- -+-- + 6 rows in set (0.00 sec) mysql >

You can see that log_bin is ON

2. Create a master-slave synchronization account backup in master's database:

Backup is the user name, 192.168.1% means that only the client connection of the 192.168.1 network segment is allowed, and 123456 is the password

Mysql > grant replication slave on *. * to 'backup'@'192.168.1.%' identified by' 123456 accounts; # create synchronization account mysql > flush privileges; # Refresh permissions mysql > select Host,User,authentication_string from mysql.user # check whether to create +-+ | Host | User | authentication_string | | +-+ | localhost | root | * 6C362347EBEAA7DF44F6D34884615A35095E80EB | | localhost | mysql.session | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | localhost | mysql.sys | * THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | 192.168.1% | | | backup | * 9BB58B7F11A03B83C396FF506F3DF45727E79614 | +-+ 5 rows in set (0.00 sec) mysql > |

Backup@192.168.1.% account has been established

3. Master lock table

Restart the MySQL service and set a read lock, which means that it can only be read, not updated, in order to get a consistent snapshot

Mysql > flush table with read lock; # main library lock table; default is 28800 seconds, that is, 8 hours automatically unlock Mysql > show master status\ gateway * 1. Row * * File: mysql-bin.000002 Position: 1621 Binlog_Do_DB: Binlog_Ignore_DB: mysqlExecuted_Gtid_Set: 1d3d078c-59a7-11e8-9a08-00163e000b3f:1-71 row in set (0.00 sec) mysql >

Check the current binary log name and offset values on the primary server. The file and position here should be the same as above.

Export the data on master (192.168.1.101) and import it into slave

4. Copy database to slave library (slave) master:

Format: mysqldump-uUSER-pPASSWORD DATABASE TABLE > NAME.sql

[root@MySQL-001 ~] # mysqldump-uroot-p employees > / opt/employees.sql # if employees is the existing library of the main library [root@MySQL-001 ~] # yum install openssh-clients-y # optional [root@MySQL-001 ~] # scp / opt/employees.sql root@192.168.1.102:/opt/slave: [root @ MySQL-002 ~] # yum install openssh-clients-y # optional 5. Configure slave (192.168.1.102) [root@MySQL-002 ~] # vim / etc/ my.cnf [mysqld] basedir=/usr/local/mysql # mysql program path datadir=/data/mysqldata # mysql data directory socket=/tmp/mysql.sockuser=mysqlport=3306# slave configuration server-id=2 # MySQLid the next two slave servers need to set different skip_slave_start=1 # replication process will not start with the startup of the database You need to start manually after restarting the database # add the following parameters to avoid master-slave replication errors caused by untimely updates and SLAVE restart. Read_only=1 # slave database ordinary account is read-only; master_info_repository=TABLErelay_log_info_repository=TABLE#relay_log_recovery=1 # slave forbids writing # super_read_only=1 # slave forbids writing and restarting database

CentOS 6. 1:

[root@MySQL-002 ~] # service mysqld restart [root@MySQL-002 ~] # service mysqld status SUCCESS! MySQL running (15604)

CentOS 7.9:

[root@MySQL-002 ~] # systemctl restart mysqld.service [root @ MySQL-002 ~] # systemctl status mysqld.servicemysqld.service-MySQL Server Loaded: loaded (/ usr/lib/systemd/system/mysqld.service; enabled) Active: active (running) since four 2018-05-17 11:42:02 CST 2h 5min ago Docs: man:mysqld (8) http://dev.mysql.com/doc/refman/en/using-systemd.html Main PID: 29959 (mysqld) CGroup: / system.slice/mysqld.service └─ 29959 / opt/mysql/bin/mysqld-- defaults-file=/etc/my.cnf5 17 11:42:02 tcloud-118 systemd [1]: Started MySQL Server.

Then import it into the mysql database. If the employees database on slave does not exist, create it first, and then import it.

[root@MySQL-002 ~] # mysql-uroot-pmysql > create database employees; # create this library [root@MySQL-002 ~] # mysql-uroot-p employees

Stop slave; # disables slave synchronization, which can be skipped for the first time Mysql > change master to- > master_host='192.168.1.101', # master ip- > master_user='backup', # backup username-> master_password='123456', # password-> master_log_file='mysql-bin.000002', # III. 3, and must be consistent with the parameters of master-> master_log_pos=1621; # III. 3, and consistent with the parameters of master

It is written as follows:

Mysql > change master to master_host='192.168.1.101', master_user='backup', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=1621;mysql > start slave # start synchronization mysql > show slave status\ G # View the status of the slave slave * * 1. Row * * Slave_IO_State: Waiting for master to send event Master _ Host: 192.168.1.101 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1621 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File : mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_ Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1621 Relay_Log_Space: 521 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101Master_UUID: 1d3d078c-59a7-11e8-9a08-00163e000b3f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 5037e479-59a7-11e8-a35b-00163e000402:1-3 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec) mysql >

The following corresponding parameters are the same: the setting is successful. Delay is 0.

Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0 Parameter description: the name of the primary server binary log file that is currently being read by the Master_Log_File: SLAVE thread Read_Master_Log_Pos: in the current primary server binary log The location where the Relay_Log_File O thread in SLAVE has read: the name of the relay log file that the SQL thread is currently reading and executing Relay_Log_Pos: in the current relay log Location where the SQL thread has been read and executed: the name of the primary server binary log file containing most recent events executed by the SQL thread Slave_IO_Running: whether the Slave_SQL_Running O thread is started and successfully connected to the primary server: whether the SQL thread is started Seconds_Behind_Master: the time gap between the slave server SQL thread and the slave server ICPO thread Units are measured in seconds. Close the read lock mysql > unlock tables; 4 of the master database, test master-slave replication 1, create a new table in marster and see if there is data master:mysql > use employees;mysql > create table test001 (id int auto_increment primary key,name varchar (20) not null) in slave; mysql > insert into test001 values (null,'will'); mysql > insert into test001 values (null,'jim'); mysql > insert into test001 values (null,'tom'); mysql > select * from employees.test001 +-+ | id | name | +-+-+ | 1 | will | | 2 | jim | | 3 | tom | +-+-- + 3 rows in set (0.01sec) slave:mysql > use employees;mysql > select * from employees.test001 +-restart the slave database to see if it is normal master:mysql > drop table employees.test001;slave:mysql > use employees;mysql > show tables.

The above experiments prove that the master-slave synchronization is successful!

Fifth, the delay of mysql database synchronization from the database 1. The general situation of synchronization delay from the database

1. Show slave status shows that the parameter Seconds_Behind_Master is not 0, which may be very large.

2. The show slave status display parameters Relay_Master_Log_File and Master_Log_File show a great difference in the number of the bin-log, indicating that the bin-log is not synchronized in time on the slave database, so the recently executed bin-log is very different from the bin-log read by the current IO thread.

3. There are a large number of mysql-relay-log logs in the slave database data directory of mysql, which will be automatically deleted by the system after the synchronization is completed, and there are a large number of logs, indicating that the master-slave synchronization delay is very serious.

2. Principle of master-slave synchronization delay in MySQL database.

Mysql master-slave synchronization principle:

For the write operation, the master library writes binlog sequentially, reads the "binlog of write operation" sequentially from the single thread of the library to the master database, and fetches from the library to the binlog to execute locally as is (random write) to ensure the logical consistency of master-slave data.

The master-slave replication of mysql is a single-threaded operation, and the master library writes binlog,binlog sequentially to all DDL and DML, so it is very efficient. The Slave_IO_Running thread of slave takes logs to the master database, which is relatively efficient. Next, the problem is that the Slave_SQL_ running thread of slave implements the DDL and DML operations of the master library in slave. The IO operation of DML and DDL is random, not sequential, and the cost is much higher, and other queries on slave may also generate lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes, then all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay.

A friend will ask: "the same DDL on the main library also needs to execute 10 points. Why is the slave delayed?" The answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

3. How is the master-slave synchronization delay in MySQL database generated?

When the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave.

The first reason is that the database has too much reading and writing pressure on the business, the CPU computing load is heavy, the network card load is heavy, and the hard disk random IO is too high.

Secondary reasons: the performance impact of reading and writing binlog, network transmission delay.

You can check which statement is stuck via mysqlbinlog. Mysqlbinlog-- no-defaults-v-- base64-output=DECODE-ROWS / usr/local/mysql/log/relay-bin.004764-- start-position=94813395 | more

Where:

The Relay_Log_File in the master-slave status query "show slave status\ G;" displays the log file executed from the library: / usr/local/mysql/log/relay-bin.004764 master-slave status query "show slave status\ G" The Relay_Log_Pos in the statement shows the log statement executed from the database: 94813395 query which table in the currently written binlog calls the most mysqlbinlog mysql-bin.001314 | awk'/ Table_map/ {print $11}'| sort | uniq-c | sort-N4, MySQL database master-slave synchronization delay solution. Architectural aspect

1. The implementation of the persistence layer of the business adopts a sub-library architecture, and mysql services can be expanded in parallel to disperse the pressure.

two。 A single library is separated from reading and writing, with one master and multiple followers, and the main writer is read from each other, dispersing the pressure. In this way, the pressure of the slave library is higher than that of the master library, which protects the master library.

3. The infrastructure of the service adds the cache layer of memcache or redis between the business and the mysql. Reduce the reading pressure of mysql.

4. The mysql of different businesses is physically placed on different machines to disperse the pressure.

5. Use a better hardware device than the main library as the slave

To sum up, if the mysql pressure is small, the latency will naturally become smaller.

Hardware aspect

1. Using a good server, for example, the performance of 4U is obviously better than that of 2U, and the performance of 2U is better than that of 1U.

two。 Storage uses ssd or disk array or san to improve the performance of random writes.

3. The master and slave are guaranteed to be under the same switch and in a 10-gigabit environment.

In conclusion, with strong hardware, latency will naturally be reduced. In a word, the solution to reducing delays is to spend money and time.

5. Mysql master-slave synchronous acceleration method

1. Sync_binlog is set to 0 on the server.

2.-logs-slave-updates updates received from the server from the master server are not recorded in its binary log.

3. Disable binlog on Slave directly.

4. Server, if the storage engine used is innodb,innodb_flush_log_at_trx_commit = 2

6. Sync_binlog and innodb_flush_log_at_trx_commit parameters

The main library is written, which is highly secure for data. Settings such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1 are required.

However, slave does not need such a high level of data security. It can be said that sync_binlog is set to 0 or binlog,innodb_flushlog can be turned off to improve the efficiency of sql execution.

Sync_binlog

MySQL provides a sync_binlog parameter to control the binlog of the database to be flushed to disk.

The default, sync_binlog=0, means that MySQL does not control the refresh of binlog, and the file system controls the refresh of its cache. At this time, the performance is the best, but the risk is also the greatest. Once the system Crash, all binlog information in the binlog_cache will be lost.

If sync_binlog > 0, it means that every sync_binlog transaction commits, the refresh operation of the file system called by MySQL brushes the cache. The safest is sync_binlog=1, which means that MySQL will brush down binlog every time a transaction is committed, which is the safest setting with the greatest performance loss. In this way, if the host operating system where the database is located is damaged or suddenly powered off, it is possible for the system to lose the data of a transaction.

However, although binlog is a sequential IO, setting sync_binlog=1 and multiple transactions commit at the same time also greatly affect the performance of MySQL and IO.

Although it can be mitigated by patches in group commit, the high frequency of refreshes can also have a great impact on IO. For systems with high concurrent transactions

The write performance gap between systems with "sync_binlog" set to 0 and 1 can be as high as 5x or more.

So the sync_binlog set by many MySQL DBA is not the safest 1, but 2 or 0. At the expense of a certain amount of consistency, higher concurrency and performance can be achieved.

By default, the binlog is not synchronized with the hard drive every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the binlog is lost. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binlog with the hard drive after every N binlog writes. Even if sync_binlog is set to 1, it is possible that there is an inconsistency between the table content and the binlog content in the event of a crash.

Innodb_flush_log_at_trx_commit

Complain that Innodb is 100 times slower than MyISAM? So you probably forgot to adjust this value.

The default value of 1 means that every transaction commit or instruction outside a transaction needs to be written to (flush) the hard disk, which is time-consuming. Especially when using battery powered cache (Battery backed up cache).

Setting to 2 is OK for many applications, especially those transferred from the MyISAM table, which means writing to the system cache instead of writing to the hard disk. Logs are still flush to the hard drive every second, so you don't usually lose updates for more than 1-2 seconds.

Setting it to 0 is faster, but the security aspect is poor, and even if the MySQL is down, the transaction data may be lost. A value of 2 will only lose data when the entire operating system is down.

7. Optimize from the point of view of the attributes of the file system

Master end

Modify the etime attribute of files in linux and Unix file systems. Because OS will write back the time of read operations to disk every time you read files, this is not necessary for database files with frequent read operations, and will only increase the burden on the disk system and affect Ilinux O performance. You can organize the operating system to write atime information by setting the mount attribute of the file system. The operation on linux is as follows:

Open / etc/fstab, plus the noatime parameter

/ dev/sdb1 / data reiserfs noatime 1 2

Then re-mount the file system

Mount-oremount / data

I have read the master-slave principle, configuration and testing handouts of MySQL 5.7.I hope it can bring some help to you 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.