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

Let's talk about how MySQL5.6 implements the migration between databases. The secret of the text lies in being close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on how to achieve mutual migration between databases in MySQL5.6.

# Business description

The master-slave architecture of an MySQL within the company runs on the library of two projects of the company, where there is a shared table. Both projects can read and write to the shared table. A sub-business of Project A will have high concurrent write operations within a fixed period of time every day. The consequence is that during the working period of this sub-business, the read and write operations of the two projects will become very slow. And the external performance is that the library a used by the sub-business of project An is independent, and there is no relationship with the sub-business under project An of another project, which is used independently by this sub-business.

# requirements:

For the time being, there is no plan to separate the database of business A from the table of two shared businesses.

# solution:

Build a new master-slave master-slave environment using the latest version of 5.6. build a cascade environment old master- > old slave-> new master- > old masterxtrabackup physical backup pt-table-checksum data consistency check project A sub-business can be stopped for a period of time, because it is a period of time when the application is stopped, waiting for the independent database not to be written. Do not lock the tables above the library level. After stopping for a period of time, the application directly modifies the configuration information of the MySQL of the sub-business, and writes the new data into the architecture of the new master cascading organization without any modification, but only makes write changes at the business level. As a result, the data of the old master-slave and the new master-slave for this independent database are inconsistent. The question to be considered here is whether the read and write of the business will not be completely migrated to the new master-slave architecture because some businesses forget to modify it or other reasons, which will cause data inconsistency, and in case there is a self-added construction in the databases, there may be value conflicts. After completing the configuration changes of the business MySQL, the purpose of the four-tier cascading architecture remains intact in order to continue with the business divestiture.

# Environment:

The latest version of os system CentOS 6.9MySQL 5.6.38

# steps:

Backup:

The main reason for using xtrabackup version 2.4.4 to make a physical backup on old slave but not on old master is to avoid consuming system resources and database resources during backup to affect the normal use of online business, because all reads and writes of our online business are on the main database and do not do read-write separation. Therefore, the impact of old slave operations on the business can almost be ignored. One factor to consider when backing up is whether the disk space of the CVM where your backup is placed is sufficient. When I first backed up, I didn't use stream backup, so I backed up directly to the local server. It takes more time for network IO and disk IO during backup transfer.

Do not use stream backup: backup 77g, take 9 minutes

Innobackupex-host=127.0.0.1-user=root-password=xxxxx-port=3306 / path/BACKUP_DIR/

Use stream backup for compressed backup: backup 12g, take 40 minutes

Innobackupex-host=127.0.0.1-port=3306-user=root-password=xxxxx-stream=tar / tmpdir | gzip > / path/BACKUP_DIR/back_file.tar.gz

Because it is a 4-tier cascade replication, you only need to back up the file_name and file_position of binlog on old slave, and change master to create a master-slave replication based on these two messages. However, the files that need to pay attention to are: xtrabackup_binlog_info: this file records the information obtained by performing show master status after xtrabackup backs up non-innodb data. The information for master-slave replication comes from .xtrabackup _ info: this file records the complete backup, and some specific details are recorded after releasing MySQL resources. There is also backup binlog information here. The main source of this information is obtained from redo log. When this information is inconsistent with the above file information, it is mainly based on this file. The main reason is that it indicates that the transaction has been completed after marking the commit tag with the binlog information recorded in redo log. The parameters to be noted are:-- slave-info: perform a backup on the slave library to get the binary log information on the master library, and generate a xtrabackup_slave_info file to record this information-- safe-slave-backup: used in conjunction with slave-info, mainly pausing slave's sql_thread process when initiating a backup to ensure that there are no temporary tables open during backup Ensure data consistency-- safe-slave-backup-timeout=SECONDS: specify how long safe-slave-backup should wait

Restore: backup data to a consistent state on the new master-slave in order to apply redo log:

Innobackupex-defaults-file=/etc/my.cnf-apply-log / path/BACKUP_DIR

Restore the data to the datadir directory:

Innobackupex-defaults-file=/etc/my.cnf-copy-back / path/BACKUP_DIR

Modify the data permissions of datadir

Chown mysql.mysql / datadir-R build new master-slave: before building old slave-> new master architecture, you should pay attention to the following points: whether GTID is enabled or not. Since old master-slave is based on traditional replication, new master-slave cannot enable GTID replication server-id and does not conform to binlog log format. The four must be consistent in the configuration file my.cnf to add the parameter "replicate-ingore-db=mysql.*" in order not to copy the information of the system library mysql, because of subsequent user rights management And 5.6 does not support online modification of these replication filters, you can only restart the database after modification in the configuration file. After new master-slave backup and recovery, you can directly obtain binlog information on new master by show master status, and make master-slave synchronization according to this information. Old slave-> new master build master-slave: get the info information of slave from the first step backup and monitor whether the master-slave is built successfully. Perform show slave status information observation in old slave-> new master- > new slave

Consistency of data tested by pt-table-checksum

Command:

Pt-table-checksum-- replicate=percona.checksumss-- nocheck-replication-filters-- no-check-binlog-format hindx.x.x.rec. Uplpl pamphlet xxxxx-- Prun3306-- databases-regex=database.*-- recursion-method dsn=h=x.x.x.x,u=root,p='xxxxx',P=3306,D=zst_teach,t=dsns.

Note: the host with the first link in the command is the master instance information in the command of master instance information in master-slave to be monitored. The subsequent link information is the MySQL instance of dsn existence information, which can be stored in master or a third-party instance. At this time, the CVM where pt-table-checksum executes needs to have the permission of root users to access zst_teach.dsns table information. That is, when the permission command of select is executed, the percona.checksumss information will be generated on the master, in which the percona library will not be generated manually and need to be generated manually, but the checksums will be generated automatically, but it should be noted that since the percona.checksumss information table is generated on the master, the rpl@command_host user needs to have the permission of the select,insert,update,delete,super,process,lock tables,craete of percona.checksums You must also have select,lock tables,super,process permissions for all tables. And in order to detect in slave, the detected user must also have the permission master of the select,super,lock tables,process in the table:

Grant update,delete,insert,super,process,lock tables,create on *. * 'user'@'command_host'slave:

Grant select,process,lock tabes,super on *. * to 'user'@'master_host'

7.pt-table-sync data synchronization (can be performed in both master and slave)

Pt-table-sync-- print-- sync-to-master hobbyslavehostPrun3306, database=DB_name-- tables=table_name1,table_name2

-- print prints out the SQL executed in slave

-- sync-to-master specifies the IP address of slave, and obtains the information of master from show slave status

For the above MySQL5.6 how to achieve mutual migration between databases related to the content, is there anything you do not understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.