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

The following content mainly brings you a brief analysis of mysql backup methods, strategies, recovery and so on. The knowledge mentioned here, which is slightly different from books, is summed up by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing, hoping to bring help to the majority of readers.

Backup of rmysql:

Misoperation, mysql crash, *, software failure, hardware failure, upgrade database, testing, etc., can cause damage to mysql data. At this time, if there is a backup, it will be embarrassing if there is no backup.

Backup type:

Full backup: backing up the entire database

Partial backup: back up only a few tables or libraries in the database

Incremental backup: only changed datasets (which can be binary logs) are backed up compared to the last full or incremental backup

Differential backup: only changed datasets are backed up compared to the last full backup

Backup method:

Physical backup: copy data files directly for backup: fast

Disadvantages: when a user accesses the updated data through the application during the backup, so that the data at that time cannot be backed up, if the database table is modified during the file system backup, the backup table file subject is inconsistent, and it will be meaningless for the later recovery table, and the backup dataset must be restored in the same way as the storage engine used by the database before the backup.

Logical backup: a backup made by "exporting" data from a database and saving it at a slower speed

Disadvantages: the speed of backup is slow. If it is a large amount of data. It takes a lot of time. If the database cloud server is in a state of providing services to users, it means locking the table (usually read lock, only read but not write data) during this long period of operation. Then the service will be affected, and the backup dataset does not have to worry about the storage engine when it is restored.

Backup strategy:

Cold backup: read and write operations cannot be performed during backup. The advantage is that the integrity of the data can be guaranteed, and there will be no requests for uncommitted transactions. The downside is that mysql is required to stop working.

Hot backup: both read and write operations can be performed during backup. The advantage is that there is no need to stop mysql.

Warm backup: read operation can be performed during backup, but write operation cannot be performed.

Considerations when backing up:

How long will it take to lock the table during backup, how long will it take to back up, how much load will be generated during backup, and how long will it take to restore?

Backup scheme:

Is it a full backup plus incremental backup or a full backup plus differential backup for the dataset

Backup method: physical backup or logical backup

Backup strategy: choose cold backup, warm backup, or hot backup

Backup tools:

Mysqldump:mysql official self-provided tool, is a logical backup tool, suitable for all storage engines, support warm backup for MyISAM engine, do not support hot backup, support full backup, partial backup, support hot backup for InnoDB storage engine

Replication and archiving tools such as cp,tar: physical backup tool that supports backup for all engines

Snapshot of lvm2: almost hot standby, need to remember with file system management tools, cp, mv, etc.

Mysqlhotcopy: cold standby tool

Quick backup tool provided by xtarbackup:percona, which can be hot standby or warm backup.

1. Mysqldump realizes data backup and restore.

Mysqldump is a client command that connects to the msql database through the mysql protocol to achieve backup. It uses the backup mechanism at the SQL level, which exports the data table to a SQL script file.

Mysqldump [option] [db_name [tbl_name..]] # mysqldump [options] db_name [tbl_name.] / / back up a single table, and you need to manually create a database during restore] # mysqldump [options]-- databases db_name. / / backup specified database, restore does not need to create database] # mysqldump [options]-- all-databases / / back up all databases, restore does not need to create database [options]-- event-E: back up all events related to specified data-- default-character-set=charset: specify which character set to use when exporting data, if the data table does not use the default latin1 character set. Then you must specify this option when you export, otherwise there will be garbled problems after importing the data again-R triggers: backing up all stored procedures and stored functions related to the specified database-- triggers: backup table-related triggers-- skip-triggers: skip backup triggers-- lock-all-tables: lock all tables in all libraries-- lock-tables: lock all tables in the specified database-- no-create-info -t: only export data, but not add CREATE TABLE statements-- no-data,-d: export no data, only database table structure. -- single-transaction: this option submits a BEGIN SQL statement before exporting the data. BEGIN does not block any applications and ensures the consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB. Note: this option and the-- lock-tables option are mutually exclusive because LOCK TABLES will implicitly commit any pending transaction-- master-data [= value]: location where binary logs and files are recorded 0: not enabled, recorded as CHANGE MASTER TO statement, this statement is not commented 2, CHANGE MASTER TO statement recorded as comments-- flush-logs: log scrolling for binary logs

Example: fully back up the database and then restore

] # mysqldump-uadmin-padmin-- databases hellodb-R-- triggers-- master-data=2 > / backup/mysqlback.sql] # service mariadb stopRedirecting to / bin/systemctl stop mariadb.service] # rm-rf / var/lib/mysql/* / / Delete mysql data] # service mariadb start Redirecting to / bin/systemctl start mariadb.service] # mysql show databases +-+ | Database | +-+ | information_schema | | hellodb | / / restore directly | mysql | | performance_schema | | test | +- -there is another way to restore + 5 rows in set (0.00 sec): MariaDB [(none)] > source / backup/mysqlback.sql / / both are provided by the mysql client.

After the backup, the database crash needs to be restored before the next backup, then the data generated in the middle needs to be restored according to the point in time through the binary log.

Binary log: records SQL statements that cause or potentially cause data changes.

] # mysqldump-uroot-databases hellodb-R-triggers-master-data=2 > / backup/mysqlbackv2.sqlMariaDB [hellodb] > insert into students (Name,Age) values ("Hello", 20) Query OK, 1 row affected (0.01sec) write operation is done after backup, and the data is changed. Need to replay through binary log] # less / backup/mysqlbackv2.sql-- Host: localhost Database: hellodb-- Server version 5.5.44 Mia DBMI log bank / 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /; / *! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /; / *! 40101 SET NAMES utf8 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * / / *! 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /; /! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /;-Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='master.000003', MASTER_LOG_POS=7925 / / display the binary files at the time of backup and the location where the binary events started] # mysqlbinlog-- start-position=245 / root/master.000003 > / backup/mysqlbin.sql / / Import the binary log of the recorded subsequent events into sql statements] # mysql select * from students | | 26 | Hello | 20 | F | NULL | NULL | / / the newly added data is also restored +-+-+ |

2. Lvm2 realizes data backup and recovery.

Create a lvm logical volume and mount the mysql data storage directory to the logical volume

] # pvcreate / dev/sdb1 Physical volume "/ dev/sdb1" successfully created] # vgcreate myvg/ dev/sdb1 Volume group "myvg" successfully created] # lvcreate-L 1G-n mysql_lvm / dev/mmapper/ mcelog mem midi mqueue/] # lvcreate-L 1G-n mysql_lvm / dev/mmapper/ mcelog mem midi mqueue/] # lvcreate-L 1G-n mysql_lvm / dev/myvg Logical volume "mysql_lvm" created.] # mke2fs-t ext4 / dev/myvg/ Mysql_lvm mke2fs 1.42.9 (28-Dec-2013) Filesystem label=OS type: LinuxBlock size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks Stripe width=0 blocks65536 inodes, 262144 blocks13107 blocks (5.00%) reserved for the super userFirst data block=0Maximum filesystem blocks=2684354568 block groups32768 blocks per group, 32768 fragments per group8192 inodes per groupSuperblock backups stored on blocks: 3276898304,163840 229376Allocating group tables: doneWriting inode tables: done Creating journal (8192 blocks): doneWriting superblocks and filesystem accounting information: done] # mkdir / data/mysql] # mount / dev/myvg/mysql_lvm / data/mysql/] # chown-R mysql.mysql / data/mysql/] # service mariadb start / / launch mysql error Redirecting to / bin / systemctl start mariadb.service Job for mariadb.service failed. See 'systemctl status mariadb.service' and' journalctl-xn' for details. [root@localhost /] # tail-f / var/log/mariadb/tail: error reading'/ var/log/mariadb/': Is a directorytail: / var/log/mariadb/: cannot follow end of this type of file Giving up on this nametail: no files remaining [root@localhost /] # tail-f / var/log/mariadb/mariadb.log 160609 18:14:55 mysqld_safe mysqld from pid file / var/run/mariadb/mariadb.pid ended160609 18:18:21 mysqld_safe Starting mysqld daemon with databases from / data/mysql160609 18:18:21 [Note] / usr/libexec/mysqld (mysqld 5.5.44-MariaDB-log) starting as process 5585... 160609 18:18:21 [Warning] Can't create test file / data/mysql/localhost.lower-test160609 18:18:21 [ERROR] mysqld: File'. / master-bin.index' not found (Errcode: 13) this may be due to a problem with the directory / data/mysql But I have already set it up and reported an error] # getenforce / / selinux. Just close Enforcing] # setenforce 0] # service mariadb startRedirecting to / bin/systemctl start mariadb.service

Back up the database:

MariaDB [hellodb] > flush tables with read lock; / / first lock table MariaDB [hellodb] > flsuh logs; / / scrolling binary log] # mysql-e "show master status "> / root/mysqlbin.date+"% F "/ / record binary log file and location] # lvcreate-L 500m-s-p r-n mysql_lvm_snap / dev/myvg/mysql_lvm / / Snapshot Logical volume" mysql_lvm_snap "created.] # mount / dev/myvg/mysql_lvm_snap / backup/] # cp-r * / data/mysqlback/ data backup succeeded

Restore data:

] # cp-r / data/mysqlback/* / data/mysql/] # chown-R mysql.mysql. / *] # service mariadb startMariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | / / No data problem | mysql | | performance_schema | | test | +-+ 5 rows In set (0.01 sec) and then recover the data lost after the full backup based on the binary log

Third, xtrabackup realizes data backup and recovery.

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Features:

(1) the backup process is fast and reliable

(2) the backup process will not interrupt the transaction in progress.

(3) it can save disk space and traffic based on compression and other functions.

(4) automatic backup verification

(5) Fast reduction speed.

] # lspercona-xtrabackup-2.3.2-1.el7.x86_64.rpm] # yum-y install * .rpm

Xtrabackup full backup:

] # innobackupex-- user=root / backup/ add-- password160609 19:08:43 innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully if the database has a password. At the end of a successful backup run innobackupex prints "completed OK!". 160609 19:08:55 Executing UNLOCK TABLES160609 19:08:55 All tables unlocked160609 19:08:55 Backup created in directory'/ data/mysql//2016-06-09pm 19-08-43'MySQL binlog position: filename 'master-bin.000005' Position '245160609 19:08:55 [00] Writing backup-my.cnf160609 19:08:55 [00]... done160609 19:08:56 [00] Writing xtrabackup_info160609 19:08:56 [00]... donextrabackup: Transaction log of lsn (1597945) to (1597945) was copied.160609 19:08:56 completed OK!

Data restore:

Restore preparation: after the backup is complete, the data cannot be used for the restore operation because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. The main function of "preparation" is to make the data file consistent by rolling back the uncommitted transaction and synchronizing the committed transaction to the data file. ] # innobackupex-- apply-log / backup/2016-06-09 September 19-16-30/InnoDB: Setting logfile. / ib_logfile101 size to 48 MBInnoDB: Setting logfile. / ib_logfile1 size to 48 MBInnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0 / / when a transaction merge, innobackupex sets the transaction log file to 48m, and the transaction log defaults to 5m This will cause mariadb to fail to start xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown...InnoDB: Shutdown completed Log sequence number 1598486160609 19:17:40 completed OK! Delete dataset Restore] # innobackupex-- copy-back / backup/2016-06-09 September 19-16-30 / 160609 19:22:27 [01]... done160609 19:22:28 [01] Copying. / performance_schema/threads.frm to / data/mysql/performance_schema/threads.frm160609 19:22:28 [01]... done160609 19:22:28 [01] Copying. / xtrabackup_info to / data/mysql/xtrabackup_info160609 19:22:28 [01] ]... done160609 19:22:28 completed OK!] # chown-R mysql.mysql. / *] # service mariadb startMariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | hellodb | | mysql | | performance_schema | | test | +-+ 5 rows in set (0.00 sec)

Xtrabackup for incremental backup:

Insert data: MariaDB [hellodb] > insert into students (Name,Age) values ("Hello", 21); Query OK, 1 row affected (0.01 sec)] # innobackupex-- incremental / backup-- incremental-basedir=/backup/2016-06-09backup 19-16-30 / / basedir is followed by the directory of the last full backup. If the last backup is incremental, point to the last incremental backup.

Note: mysql cannot be started during recovery, incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

There are some differences between incremental backups and full backups when integrating transactions, which should be noted:

1. On each backup (incremental backup and full backup), the committed transaction is "replayed". After "replay", all backup data is merged into the full backup.

2. After merging, "rollback" some uncommitted transactions based on all backups.

Why can't uncommitted transactions on incremental backups be "rolled back"?

Because the transaction has not been committed on this incremental backup, the transaction may have been committed on the next incremental backup, and all transactions cannot be rolled back, only after all the backed up transactions have been "replayed". "rollback" all outstanding transactions based on all backups.

] #] # innobackupex-- apply-log-- redo-only / backup/2016-06-09 / 19-55-38 / / first full backup transaction integration] #] # innobackupex-- apply-log-- redo-only / backup/2016-06-09 / 19-55-38-- incremental=dir=/backup/2016-06-09 19-57-43 / / the first incremental backup if there are multiple Only change the directory after incremental to the second incremental backup] # rm-rf / data/mysql/*] # innobackupex-- copy-back / backup/2016-06-09 / 19-55-38 /] # service mariadb startMariaDB [hellodb] > select * from students | | 26 | Hello | 21 | F | NULL | NULL | +-+-+ 26 rows in set (0.01sec) |

There are some xtarbackup files in each backup directory:

] # cd / backup/2016-06-09 / 19-55-38 / [root@localhost 2016-06-09 / 19-55-38] # lsxtrabackup_binlog_info: binary log files currently in use by the mysql server and the location of binary log events up to the moment of backup xtrabackup_checkpoints: backup type (such as full or incremental), backup status (such as whether it is already prepared status) and LSN (log serial number) range information An incremental backup is to back up modified data by looking at what LSN has changed: xtrabackup backup is the current position of log xtrabackup _ binlog_pos_innodb: binary log files and binary log files for InnoDB or XtraDB tables.

Xtrabackup_info: xtrabackup backup database of all kinds of information

For the above brief analysis of mysql backup methods, strategies, recovery, etc., if you need to know more, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sales and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.