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

A new job-hopping company came up with database tuning and data migration. We'll make up later on the tuning part. The company currently uses a 5.1 database, which is very old, and with the development of the business, it needs to do read-write separation and master-slave replication. I want to move all the data to the new database. But this library has about 60G of data.

Main ideas:

1. Create a user and authorize 2. Build master-slave to achieve master-slave synchronization. Data is kept consistent at present. IpMysql5.1 already has data volume when switching master-slave synchronization, in order to ensure data consistency. Therefore, it is necessary to lock the library and unlock 3. 3 after the data is imported. You need to pay attention to the slave library and the main library engine, the character set show variables like 'character_set%';-- view the current environment's character_set show variables like' collation%';-- view the current collation4. After the data is moved, the data is checked and the consistency is verified by pt-table-checksum. The consistency is verified by pt-table-sync recovery. Zabbix regularly monitors whether the mysql is consistent. If it is inconsistent, call the script to repair it automatically.

Statistics are made in several ways:

A database of the same version

1. If the version is the same, stop the library directly, and copy (cp, tar,gzip,cpio) package the entire directory of mysql to the new host. This is the simplest and most effective, and the data is very consistent. You can also use percona-xtrabackup physical backup + binlog with the help of third-party software backup, the benefit: it can back up the database of InnoDB and XtraDB storage engine non-blocking. It does not suspend service to create Innodb hot backups; do incremental backups for mysql; do online table migration between mysql servers; make it easier to create replication; back up mysql without increasing the load on the server. 3.mysqldump score a. Host An is backed up as a sql file, which takes a long time to lock the table when imported into the host B database, and takes a lot of time to mysqldump-uvnum-pvnum@123-- force data_base_name > / data/data_base_name.sql or directly to the sql file under the corresponding host. If 60 gigabytes are used, the export and import time is about five hours. Too slow b.tar backup backup and compress mysqldump-uusername-ppassword dbname | gzip > / data/wwwroot/www/dbname.sql.gz decompress and restore gunzip < / root/dbname.sql.gz | mysql-uusernameb-ppasswordb dbnamenew time relative reduction is the best way to introduce another way to directly transfer database A to database B. Mode (executed in server A), but the network speed must be good mysqldump-default-character-set=utf8mb4-host=127.0.0.1-uusername-ppassword 1. You must ensure that the database of server B can be accessed by server A, and that there is an optional library 2.--opt command for server B. it is recommended to add. Is equivalent to specifying-- add-drop-tables--add-locking-- create-option-- disable-keys--extended-insert-- lock-tables-- quick-- set-charset. It can give a quick dump operation and produce a dump file that can be quickly loaded into the MySQL server. 3.--default-character-set=utf8mb4 specifies the character type of the database connection. If the server does not utf8 by default, the exported data may lose four bytes of unicode information (emoticons, etc.) 4. Enable compression to transmit all information between the Mutual C client and the server. Of course there is. Mysql transfers the database quickly through the command line, and one command (mysqldump) completes the data preparation.

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.