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

I. brief introduction

Data is very important in today's Internet industry, knowing that data can find value from it, so data is more important for companies to write, when we are faced with data loss, such as accidental deletion of data, natural disasters and so on. However, data backup and recovery is particularly important.

II. Backup and recovery of MySQL/MariaDB data

Common backup types:

Depending on whether the database is online at the time of backup:

Cold backup: cold backup, server offline, online business termination, unable to perform read and write operations, but this method of backing up data is the most reliable, losing data less hot backup: hot backup, server online, online business is normal, read and write operations can be carried out, this method requires complex backup

Warm standby: warn backup, the server is online, but the global lock is imposed on the database. The read-only operation is normal and cannot be written.

Based on the interface at the time of backup:

Physical backup: physical backup, a method of cp replication and archiving directly from the data directory of the database

Logical backup: logical backup, extract the data from the library and save it as a text file. The main tool used is mysqldump.

Note: it is practical to use logical backup when the amount of data is very large. When the amount of data is less than 10G, you can consider using mysqldump to implement logical backup.

Based on the backed-up dataset:

Full backup: full backup, which means backing up the entire library

Partial backup: partial backup, which refers to backing up part of the data in the database

Depending on whether the entire data is backed up or only the changed data is backed up

Full backup: full backup, backup the entire database

Incremental backup: incremental backup, incremental backup that only backs up the data that has changed since the last full backup

Differential backup: differential backup, for example, make a backup on Monday, backup on Monday and Tuesday on Tuesday, and backup on Monday, Tuesday and Wednesday on Wednesday. This is called differential backup. It's easier to recover.

The difference between MYISAM and InnoDB:

The MYISAM storage engine does not support hot backup, but InnoDB supports InnoDB to give the data a serial number when recording data, so the backup is automatically accelerated based on the mechanism of MVCC (multi-version concurrency control). Each transaction starts a snapshot of the current set, and then each sequence number is recorded to it based on the MVCC mechanism, and only the data before the serial number or serial number is backed up during backup. What happens in the future will not be backed up, and if the isolation level of the transaction is not particularly high, it will not affect the read and write operation of the transaction, and the data backed up in this way must be consistent in time, so it is necessary to complete the hot backup. It is usually done by a transaction-based storage engine.

Innobase: the commercial backup tool is Innobackup, which can realize the hot backup of InnoDB and support incremental backup. However, MyISAM does not support incremental backup and can only achieve full backup, which belongs to physical backup, which is relatively fast.

Xtrabackup: an open source backup tool provided by the Percona organization, physical backup, fast

Mysqldump: commonly used backup tool, also a logical backup tool, for small data backup, usually small data less than 10G for backup; can use text for secondary processing; equivalent to MySQL client tool

III. Practical data backup and recovery

Common backup tools:

Mysqldump: logical backup tool, the backup and recovery process is slow

Mysqldumper: multithreaded, mysqldump, it is difficult to achieve differential or incremental backup

Lvm-snapshot:

It is close to a hot standby tool, because the global lock is requested in advance, then a snapshot is created, and the global lock is released after the snapshot is created. Use cp,tar and other tools for physical backup; backup and recovery speed is fast, it is difficult to achieve incremental backup, and it takes some time to request a global

Logical backup tool, faster than mysqldump

Innobase: commercial backup tool, innobackup

Xtrabackup: an open source backup tool provided by percona

Innodb hot backup, incremental backup

Myisam warm standby, does not support increment

Physical backup, fast

Mysqlhotcopy: almost cold standby

Mysqldump: only suitable for databases with small datasets. It is not recommended when the amount of data is more than 10 gigabytes. Backup is too slow.

Mysqldump:mysqldump [options] [db_name [tbl_name...]] Mysqldump-uroot-hlocalhost-p testdb > / tmp/testdb.sql # backup a single table mysql-uroot-hlocalhost-p testdb/tmp/dbs.sql # when using this backup method, you do not need to connect to mysql to create a database mysqldump-uroot-hlocalhost-p-all-databases > / tmp/all.sql # backup all databases, suitable for cold backup of single or multiple databases:-- databases backup all databases:-- all-databases Note: when online backup When implementing warm backup, you need to impose locks on single or multiple tables, so that users can only read but not write mysqldump-uroot-hlocalhost-- databases testdb-- lock-all-tables-- flush-logs > / tmp/testdb.sqlmysqldump-uroot-hlocalhost-- databases testdb-- single-transaction-- flush-logs > / tmp/testdb1.sql # Hot standby is implemented only for Innodb storage engine Myisam engine mysqldump-uroot-hlocalhost-- databases testdb-- lock-all-tables-- flush-logs-- master-data=2 > / tmp/testdb2.sqlmysqldump-uroot-hlocalhost-- databases testdb-- single-transaction-- flush-logs-- master-data=2 > / tmp/testdb3.sql--lock-all-tables # backup is not supported-single-transaction # enables hot backup for innodb storage engine-flush-logs # rolls on backup Log-master-data= [0 | 1 | 2] is set to 1: save as a change master statement and set to 2: commented out change master statement Set to 0: do not record data recovery: it is recommended to close binary log and other user connections: set session sql_log_bin=0

Use mysqldump for backup and binary log for recovery:

Use mysqldump to back up and use binary logs to recover data. Here we take the database hellodb on the current system as an example:

Step 1: first, make a full backup of the hellodb database. If the data in the database is very large, for example, if it is larger than 10 gigabytes, it is not recommended to use mysqldump as a backup tool, but it is more reasonable to use physical backup:

Mysqldump-uroot-hlocalhost-p-databases hellodb-- lock-all-tables-- flush-logs-- master-data=2 > / tmp/hellodb.sql

Step 2: modify or create some tables or data in the database hellodb to make the previously backed up data different from the data in the existing database, so that the data can be recovered later through binary logs:

MariaDB [(none)] > use hellodb;Database changedMariaDB [hellodb] > show tables +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | T1 | | teachers | | toc | +- -+ 8 rows in set (0.00 sec) MariaDB [hellodb] > drop table T1 Query OK, 0 rows affected (0. 01 sec) MariaDB [hellodb] > create table tb1 (id int,name varchar (20)); Query OK, 0 rows affected (0. 02 sec) MariaDB [hellodb] > insert into tb1 values. (2 rows affected sec) Records: 2 Duplicates: 0 Warnings: 0MariaDB [hellodb] > show tables +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | tb1 | | teachers | | toc | +- -+ 8 rows in set (0.00 sec) MariaDB [hellodb] >\ qBye

Step 3: use another server for recovery testing, import hellodb.sql and changed binary logs and copy them to another host

[root@centos6 binlog] # mysqlbinlog-- start-position=245 mastercopyright bin.000005Universe 50530 SET @ @ SESSION.PSEUDOSLAVEMODE1 / session # SET @ @ session.Max.insertinsertdelayedthreads0Universe SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / *! / / # at 4 "161117 6:43:00 server id 1 end_log_pos 245 Start: binlog v 4, server v5.5.32-MariaDB-log created 161117 6 end_log_pos 43 Start 0such Warning: this binlog is either in use or was not closed properly.BINLOG '9OAsWA8BAAAA8QAAPUAAAABAAQANS41LjMyLU1hcmlhREItG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlcards increasing numbers off set @ @ session.auto_increment_increment=1, @ @ session.session .autovariation increments offsetbacks @ @ session. Utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.lctimestamp timekeeper namespace 0qqqxxx session.session. Collationalization databaseDeFAULTGER / at @ @ session.collationalization databaseDROP TABLE `t1` / * generated by server * / *! * /; # at 352 "161117 7:01:51 server id 1 end_log_pos 459 Querythread_id=23exec_time=0error_code=0SET TimESTAMP" 1479337311) table tb1 (id int,name varchar (20)) / * *; # at 459 "161117" 7:02:43 server id 1 end_log_pos 530 Querythread_id=23exec_time=0error_code=0SET Timestampin 1479337363 BEGINActionAccording to at 530,161117 7:02:43 server id 1 end_log_pos 642 Querythread_id=23exec_time=0error_code=0SET timestamp 1479337363), (2) / *! /; # at 642 / 161117 7:02:43 server id 1 end_log_pos 669 Xid = 1515COMMITGUR / DELIMITER; # End of log fileROLLBACK / * added by mysqlbinlog * /; / *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/ / *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/; [root@centos6 binlog] # mysqlbinlog-- start-position=245 master-bin.000005 > inre.sql

Step 4: restore the data, enter the mysql command line mode or on the command line, turn off the binary log, and there is no need to record the recovery information in the binary log when restoring the data.

MariaDB [(none)] > set sql_log_bin=0MariaDB [hellodb] > source / root/hellodb1.sqlMariaDB [hellodb] > source / root/inre.sqlMariaDB [hellodb] > set sql_log_bin=1MariaDB [hellodb] > show tables +-+ | Tables_in_hellodb | +-+ | classes | | coc | | courses | | scores | | students | | tb1 | | teachers | | toc | +- -+ 8 rows in set (0.00 sec)

If the result of the appeal shows that the data has been restored to the original initial state, the backup and recovery based on the mysqldump+ binary log is completed.

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.