MySQL Series 12 Backup and Recovery

usage:
           shell> mysqldump [options] db_name [tbl_name ...]
           shell> mysqldump [options] --databases db_name ...
           shell> mysqldump [options] --all-databases

Options:
	-A: Back up all libraries -B db_name1,[db_name2,...]: Back up the specified library -E: Back up all related event schedulers
	-R: Back up all stored procedures and stored functions --triggers: Back up table-related triggers, enabled by default, use --skip-triggers to not back up triggers --master-data={1|2}:
		 1: Add a record before the backed up data as the CHANGE MASTER TO statement, non-comment, and the default value is 1 if not specified.
		 2: CHANGE MASTER TO statement recorded as a comment. Note: This option automatically turns off the --lock-tables feature and automatically turns on the --lock-all-tables feature (unless --single-transaction is turned on)
	-F: Roll the log before backup. After locking the table, execute the flush logs command to generate a new binary log file. When used with -A, it will cause multiple database refreshes. If you perform dump and log refresh at the same time, you should use --flush-logs and -x, --master-data or --single-transaction at the same time. In this case, only refresh once. Recommendation: Use with -x, --master-data or --single-transaction. --compact Remove comments, suitable for debugging, not used in production. -d: Only back up the table structure. -t: Only back up the data, not the create table.
	-n: Do not back up create database, can be overwritten by -A or -B --flush-privileges: Refresh the authorization table before backup, required for backing up MySQL database or related -f: Ignore SQL errors and continue execution --hex-blob: Use hexadecimal notation to dump binary columns (for example, "abc" becomes 0x616263), affected data types include BINARY, VARBINARY, BLOB, BIT
	-q: Do not cache queries, output directly, speed up backup

InnoDB recommends the following backup strategy:
	mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM recommended backup strategy:
	mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

usage:
	innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/

Options:
    --user: This option indicates the backup account --password: This option indicates the backup password --host: This option indicates the address of the backup database --databases: The parameter accepted by this option is the data name. If you want to specify multiple databases, they need to be separated by spaces; for example: "xtra_test dba_test". At the same time, when specifying a database, you can also specify only a table in it. For example: "mydatabase.mytable". This option is invalid for innodb engine tables, but all innodb tables will be backed up. --defaults-file: This option specifies the file from which to read the MySQL configuration. It must be placed in the first option position on the command line. --incremental: This option means creating an incremental backup. You need to specify --incremental-basedir
    --incremental-basedir: This option specifies the directory of the previous full backup or incremental backup, and is used together with --incremental. --incremental-dir: This option indicates the directory of the incremental backup during restore. --include=name: Specify the table name, format: databasename.tablename
    --apply-log: Generally, after the backup is completed, the data cannot be used for recovery operations because the backed-up data may contain transactions that have not yet been committed or transactions that have been committed but not yet synchronized to the data file. Therefore, the data file is still in an inconsistent state at this time. This option is used to make the data file consistent by rolling back uncommitted transactions and synchronizing committed transactions to the data file. --use-memory: This option is used together with the --apply-log option. When preparing a backup, the memory size allocated by xtrabackup for crash recovery is in bytes. Also available (1MB, 1M, 1G, 1GB), 1G is recommended
	--export: Indicates that a separate table can be exported and then imported into other MySQLs. --redo-only: This option is used when preparing a base full backup and merging incremental backups into it. --copy-back: When restoring data, copy the backup data file to the datadir of the MySQL server.
	--move-back: This option is similar to --copy-back, the only difference is that it does not copy the file, but moves the file to the destination. This option removes the backup file and should be used with caution. Usage scenario: There is not enough disk space to keep both data files and backup copies

~]# mkdir /backup
~]# systemctl stop mariadb #Stop service~]# tar Jcf /backup/mariadb_all.tar.xz /var/lib/mysql/ #Package and compress backup]# systemctl start mariadb

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf #Delete the damaged library~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz #Unzip the packaged database file backup]# cp -av var/lib/mysql/ /var/lib/ #Restore backup]# systemctl start mariadb #Start the service and restore successfully

~]# systemctl stop mariadb
~]# rm /var/lib/mysql/ -rf #Delete the damaged library~]# cd /backup/
backup]# tar xf mariadb_all.tar.xz #Unzip the packaged database file backup]# cp -av var/lib/mysql/ /var/lib/ #Restore backup]# systemctl start mariadb #Start the service and restore successfully

Prepare the lvm environment:
~]# pvcreate /dev/sda5
~]# vgcreate vg0 /dev/sda5
~]# lvcreate -n lv_data -L 10G vg0
~]# lvcreate -n lv_binlog -L 10G vg0
~]# mkfs.xfs /dev/vg0/lv_data
~]# mkfs.xfs /dev/vg0/lv_binlog
~]# mkdir -pv /data/{mysqldb,binlog} #Create data directory and binary log storage directory~]# chown -R mysql:mysql /data/
~]# vim /etc/fstab
	UUID=4e3d726a-d420-4c1e-812b-da315012ba86 /data/mysqldb xfs defaults 0 0
	UUID=6dd98866-769f-4369-8738-291fbcc94ca1 /data/binlog xfs defaults 0 0

Configure the database and simulate the generation of large amounts of data:
~]# yum install mariadb-server -y
~]# vim /etc/my.cnf
    [mysqld]
    datadir = /data/mysqldb #Specify the database storage path log_bin = /data/binlog/mariadb-bin #Enable binary logging and store it in the specified path innodb_file_per_table = ON #Enable a separate tablespace for each table~]# systemctl start mariadb
~]# mysql #Connect to the database. The username and password are omitted here. The following are the same. MariaDB [(none)]> CREATE DATABASE school; #Create a test library MariaDB [(none)]> use school
MariaDB [school]> CREATE TABLE testtb (id int auto_increment primary key,name char(30),age int default 20); #Create a data table MariaDB [school]> DELIMITER // #Change the statement terminator to “//”
MariaDB [school]> CREATE PROCEDURE pro_testtb() #Write a stored procedure to generate 100,000 records for testing-> BEGIN
    -> declare i int;
    -> set i = 1;
    -> while i < 100000
    -> do INSERT INTO testtb(name,age) VALUES (CONCAT('testuser',i),i);
    -> SET i = i + 1;
    -> END while;
    -> END//
MariaDB [school]> DELIMITER ; #Remember to change the statement terminator back MariaDB [school]> CALL pro_testtb; #Call the stored procedure MariaDB [school]> SELECT COUNT(*) FROM testtb; #Check that there are 100,000 records in the table +----------+
| COUNT(*) |
+----------+
| 99999 |
+----------+

Start backup:
MariaDB [school]> FLUSH TABLES WITH READ LOCK; #Remember to lock the table before backing up to prevent users from continuing to write MariaDB [school]> FLUSH LOGS; #Scroll the binary log MariaDB [school]> SHOW MASTER LOGS; #Check the location of the binary log +--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 30334 |
| mariadb-bin.000002 | 1038814 |
| mariadb-bin.000003 | 29178309 |
| mariadb-bin.000004 | 528 |
| mariadb-bin.000005 | 245 | #Record this output, we will need it later+--------------------+-----------+
~]# lvcreate -L 5G -n lv_mysql_snap -s -pr /dev/vg0/lv_data #You need to open another terminal to create a snapshot. Do not exit the mysql terminalMariaDB [school]> UNLOCK TABLES; #Unlock the snapshot as soon as possible after creating it. Be careful of user complaints~]# mount -o nouuid,norecovery /dev/vg0/lv_mysql_snap /mnt/ #Mount the snapshot to /mnt
~]# cp -av /mnt/ /backup #Copy data to the backup directory~]# umount /mnt/
~]# lvremove /dev/vg0/lv_mysql_snap #After the copy is completed, delete the snapshot immediately to affect the server performance. The full backup is now complete~

Some more data:
MariaDB [school]> CALL pro_testtb; #Let's simulate inserting 100,000 records MariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
| 199998 | #Now there are 200,000 records+----------+

Simulate database corruption:
~]# rm -rf /data/mysqldb/* #Server crashes, no more BB, just clear the library~]# systemctl stop mariadb #Stop service

Start restoration:
~]# cp -av /backup/* /data/mysqldb/ #cp the backed up files to the corresponding library directory. Add skip_networking under [mysqld] in /etc/my.cnf to prohibit users from using the database and prevent data from being written during the recovery process. ~]# systemctl start mariadb #Start the service ~]# ls -1 /data/binlog/ #View the number of binary log files mariadb-bin.000001
    mariadb-bin.000002
    mariadb-bin.000003
    mariadb-bin.000004
    mariadb-bin.000005
    mariadb-bin.000006
    mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000005 > binlog.sql #Data after the full backup time point~]# mysqlbinlog /data/binlog/mariadb-bin.000006 >> binlog.sql #Append all subsequent data to the same sql file~]# mysql < binlog.sql #Use binary log to restore incrementally from the point of our previous full backup~]# mysql -e 'SELECT COUNT(*) FROM school.testtb' #Check it out, 200,000 records are all there, nice
+----------+
| COUNT(*) |
+----------+
| 199998 |
+----------+
Go to /etc/my.cnf and delete skip_networking under [mysqld], restart the service, and the restoration is complete.

~]# mysqldump -A -F -E -R --single-transaction --master-data=2 --flush-privileges > /backup/full-`date +%F-%T`.sql #Full database backup

MariaDB [(none)]> CREATE DATABASE db1; #Create a database MariaDB [(none)]> CREATE DATABASE db2; #Create another database MariaDB [school]> use school;
MariaDB [school]> DROP TABLE testtb; #Due to an error, our table with 200,000 records was deletedMariaDB [school]> CREATE TABLE students (id INT(4) AUTO_INCREMENT PRIMARY KEY,name CHAR(30),age TINYINT); #Subsequently, other users created other tablesMariaDB [school]> INSERT INTO students(name,age) VALUES ('user1',20); #And also added data

At this point, we found that a table is missing and needs to be restored urgently. Let's get started. MariaDB [(none)]> FLUSH TABLES WITH READ LOCK; #Lock table MariaDB [(none)]> FLUSH LOGS; #Flush and roll binary log file MariaDB [(none)]> SHOW MASTER LOGS; #View the current log status +--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 30334 |
| mariadb-bin.000002 | 1038814 |
| mariadb-bin.000003 | 29178309 |
| mariadb-bin.000004 | 528 |
| mariadb-bin.000005 | 29177760 |
| mariadb-bin.000006 | 29177786 |
| mariadb-bin.000007 | 953 |
| mariadb-bin.000008 | 245 |
+--------------------+-----------+
~]# systemctl stop mariadb #Stop service and prepare for repair~]# head -30 /backup/full-2018-06-14-05\:33\:47.sql |grep "CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000007', MASTER_LOG_POS=245; #Find the log point of the full backup, at 245 of mariadb-bin.000007
~]# ls -1 /data/binlog/
mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.index
~]# mysqlbinlog --start-position=245 /data/binlog/mariadb-bin.000007 > /backup/binlog.sql #Export the binary log after the full backup~]# mysqlbinlog /data/binlog/mariadb-bin.000008 >> /backup/binlog.sql
~]# vim /backup/binlog.sql #Modify the exported sql file and delete the incorrect SQL statement. Delete the line "DROP TABLE `testtb` /* generated by server */"

Import the backup:
~]# rm -rf /data/mysqldb/* #Clear the fault database first~]# vim /etc/my.cnf #Edit the configuration file. Add skip_networking to [mysqld] to prevent users from writing data~]# systemctl start mariadb #Start the service~]# mysql < /backup/full-2018-06-14-05\:33\:47.sql #Import full backup~]# mysql < /backup/binlog.sql #Import incremental backupMariaDB [(none)]> show databases; #Check if our data is successfully restored+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 | #Recovered | db2 | #Recovered | mysql |
| performance_schema |
| school |
| test |
+--------------------+
MariaDB [(none)]> SELECT COUNT(*) FROM school.testtb;
+----------+
| COUNT(*) |
+----------+
| 199999 | #Recovered+----------+
MariaDB [(none)]> SELECT * FROM school.students;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 20 | #Recovered+----+-------+------+
So far, the recovery has been completed. Delete skip_networking in the configuration file, restart the service, and you are done~

~]# innobackupex --user=root /backup/ #The password is omitted here

MariaDB [school]> CALL pro_testtb; #Add some dataMariaDB [school]> SELECT COUNT(*) FROM testtb; #Now there are 300,000 records+----------+
| COUNT(*) |
+----------+
| 299998 |
+----------+
MariaDB [school]> INSERT INTO students VALUES (2,'user2',21);
MariaDB [school]> UPDATE students SET age=19 WHERE id=1;
MariaDB [school]> SELECT * FROM students;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 19 |
| 2 | user2 | 21 |
+----+-------+------+

~]# mkdir /backup/inc{1,2} #Create an incremental backup directory~]# innobackupex --incremental /backup/inc1/ --incremental-basedir=/backup/2018-06-14_10-44-57/ #Specify incremental backup based on full backup

MariaDB [(none)]> CREATE DATABASE db3; 
MariaDB [(none)]> DROP TABLE school.students; #The table was deleted by mistake MariaDB [(none)]> use school
MariaDB [school]> CALL pro_testtb; #Subsequently, more data will be generatedMariaDB [school]> SELECT COUNT(*) FROM testtb;
+----------+
| COUNT(*) |
+----------+
| 399997 |
+----------+
MariaDB [school]> SELECT * FROM students; #At this point, I found that the students table is gone. What should I do?
ERROR 1146 (42S02): Table 'school.students' doesn't exist

~]# rm -rf /data/mysqldb/* #Clear the data directory before restoring MariaDB [(none)]> show databases; #The database is gone now+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+

To restore full and incremental backups:
: : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : : :

Relying on the binary log, restore the latest incremental backup to now:
~]# cat /backup/2018-06-14_10-44-57/xtrabackup_binlog_info #Check the binary log record point during backup mariadb-bin.000011 35740416
~]# ls -1 /data/binlog/ #See where our binary log file is recorded mariadb-bin.000001
mariadb-bin.000002
mariadb-bin.000003
mariadb-bin.000004
mariadb-bin.000005
mariadb-bin.000006
mariadb-bin.000007
mariadb-bin.000008
mariadb-bin.000009
mariadb-bin.000010
mariadb-bin.000011
mariadb-bin.000012
mariadb-bin.000013
mariadb-bin.index
~]# mysqlbinlog --start-position=35740416 /data/binlog/mariadb-bin.000011 > /backup/binlog.sql #Export the binary log data after the latest incremental backup~]# mysqlbinlog /data/binlog/mariadb-bin.000012 >> /backup/binlog.sql
~]# mysqlbinlog /data/binlog/mariadb-bin.000013 >> /backup/binlog.sql
Edit the /backup/binlog.sql file and delete "DROP TABLE `school`.`students` /* generated by server */" to undo the accidental deletion. MariaDB [(none)]> SET sql_log_bin=0; #Temporarily turn off the binary log function. MariaDB [(none)]> source /backup/binlog.sql #Import the latest data after the incremental backup to check whether the data has been restored completely. Delete skip_networking in my.cnf and restart the service. Now the data has been restored to the latest state.

~]# innobackupex --include="testdb.testlog" /backup #Backup table data~]# mysql -e 'SHOW CREATE TABLE testdb.testlog' > /backup/desc_testdb_testlog.sql #Backup table space~]# mysql -e 'DROP TABLE testdb.testlog' #Simulate failure and delete the testlog table

~]# innobackupex --apply-log --export /backup/2018-06-14_17-47-02/ #Arrange table data~]# vim /backup/desc_testdb_testlog.sql #Edit the statement to create the tablespace and delete the following fields Table Create Table
    testlog
~]# mysql testdb < /backup/desc_testdb_testlog.sql #Import tablespace~]# mysql testdb -e 'DESC testlog' #Check whether the import is successful+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(30) | YES | | NULL | |
| age | int(11) | YES | | 20 | |
+-------+----------+------+-----+---------+----------------+
~]# mysql -e 'ALTER TABLE testdb.testlog DISCARD TABLESPACE' # Clear tablespace~]# cd /backup/2018-06-14_17-47-02/testdb/
testdb]# cp testlog.cfg testlog.exp testlog.ibd /var/lib/mysql/testdb/ #Copy the table data to the library directory~]# chown -R mysql:mysql /var/lib/mysql/testdb/ #Modify the owner and group~]# mysql -e 'ALTER TABLE testdb.testlog IMPORT TABLESPACE' #Import tablespace