MARIADB Restore Backup notes (2)

MARIADB Backup Recovery Misc 1, mariadb default engine in fact XTRADB

MariaDB [(None)]> SHOW engines;+--------------------+---------+----------------------------------------------- -----------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | savepoints |+--------------------+---------+-------------------------------------------------------------------- --------+--------------+------+------------+| CSV | YES | CSV Storage Engine | NO | NO | NO | | Mrg_myisam | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | Blackhole | YES |       /dev/null Storage Engine (anything you write to it disappears)      | NO | NO | NO | | MyISAM | YES | MyISAM Storage Engine | NO | NO | NO | | InnoDB | DEFAULT | PERCONA-XTRADB, Supports transactions, Row-level locking, and foreign keys | YES | YES | YES |

XtraBackup1, installation Xtrabackup

yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y

1.1 Viewing installation-generated files

[[email protected] ~]# rpm -ql percona-xtrabackup/usr/bin/innobackupex/usr/bin/xbcloud/usr/bin/xbcloud_osenv/usr/bin/xbcrypt/usr/bin/xbstream/usr/bin/xtrabackup/usr/share/doc/percona-xtrabackup-2.3.2/usr/share/doc/percona-xtrabackup-2.3.2/COPYING/usr/share/man/man1/innobackupex.1.gz/usr/share/man/man1/xbcrypt.1.gz/usr/share/man/man1/xbstream.1.gz

/usr/share/man/man1/xtrabackup.1.gz

1.2 Preparatory work

1.2.1 确保所有表都是InnoDB1.2.2 确认一个参数，该参数实现很多高级功能（单独的表空间功能）MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE ‘innodb%‘;| innodb_file_per_table                     | OFF1.2.3 因为已经有数据库了。需要删除多有数据库，开启参数，重新生成；[[email protected] ~]# rm -rf /data/mysql/*[[email protected] ~]# rm -rf /data/binlogs/*[[email protected] ~]# vim /etc/my.cnfinnodb_file_per_table=ON[[email protected] ~]# systemctl start mariadb.service1.2.4 还原数据库[[email protected] ~]# mysql < all.sqlMariaDB [hellodb]> SHOW BINARY LOGS;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |     30349 || mysql-bin.000002 |   1038814 || mysql-bin.000003 |    522279 |

2. Backup 2.1 Create backup directory

[[email protected] ~]# mkdir /backups/

2.2 Backup

2.1 Full database backup [[email protected] ~]# innobackupex--user=root/backups/2.2 Restore the database to master12.2.1 Master1 prepare: [[email  protected] data]# mkdir-pv/data/mysqlmkdir:created directory '/data ' mkdir:created directory '/data/mysql ' [[ Email protected] data]# chown mysql.mysql/data/mysql/[[email protected] data]# service mysqld StartMariaDB [ (none)] > SHOW databases;+--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema | | Test |+--------------------+2.2.2 Restore backup copy database back to the machine: [[email protected] ~]# scp/backups/2017-01-28_03-19 -23/* [email protected]:/tmp/backups Directory collation: [[email protected] ~]# Innobackupex--apply-log/backups/ 2017-01-28_03-19-23/Restore Backup: [[email protected] ~]# service mysqld stop[[email protected] ~]# rm-rf/data/mysql /*[[email protected] ~]# Innobackupex--copy-back/backups/2017-01-28_03-19-23/[[email protected] mysql]# Chown-r Mysql:mysql/data/mysql/*[[email protected] mysql]# rm-rf ib_logfile*[[email protected] mysql]# service mysqld StartMariaDB [ (none)] > SHOW databases;+--------------------+| Database |+--------------------+| Information_schema | | Hellodb |

3, incremental backup 3.1 preparation

创建表：MariaDB [hellodb]> CREATE TABLE testtb (id int);插入数据：MariaDB [hellodb]> INSERT INTO testtb VALUES (1),(10),(99);MariaDB [hellodb]> SELECT * FROM testtb;+------+| id   |+------+|    1 ||   10 ||   99 |+------+

3.2 Redo a full backup

删除之前的备份[[email protected] ~]# rm -rf /backups/*完全备份[[email protected] ~]# innobackupex /backups/查看备份类型：[[email protected] ~]# cat /backups/2017-01-28_05-05-39/xtrabackup_checkpoints backup_type = full-backupedfrom_lsn = 0to_lsn = 1645521last_lsn = 1645521compact = 0recover_binlog_info = 0

3.3 Data modification, ready for experimental incremental backup

删除表：MariaDB [hellodb]> DROP TABLE coc;MariaDB [hellodb]> INSERT INTO testtb VALUES (44),(32);插入数据

3.4 Incremental Backup

innobackupex --incremental /backups/ --incremental-basedir=/backups/2017-01-28_05-05-39[[email protected] ~]# cat /backups/2017-01-28_08-02-15/xtrabackup_checkpoints backup_type = incrementalfrom_lsn = 1645521to_lsn = 1647443last_lsn = 1647443compact = 0recover_binlog_info = 0

3.5 Backup Restore

停止数据库3.5.1 整理完全备份[[email protected] ~]# innobackupex --apply-log --redo-only /backups/2017-01-28_05-05-39/3.5.2 整理增量备份[[email protected] ~]# innobackupex --apply-log --redo-only /backups/2017-01-28_05-05-39/ --incremental-dir=/backups/2017-01-28_08-02-15/3.5.3 删除数据[[email protected] ~]# rm -rf /data/mysql/*3.5.4 恢复备份[[email protected] ~]# innobackupex --copy-back /backups/2017-01-28_05-05-39/3.5.5 更改权限[[email protected] mysql]# chown -R mysql.mysql ./*3.5.6 启动数据库MariaDB [hellodb]> SELECT * FROM testtb;+------+| id   |+------+|    1 ||   10 ||   99 ||   44 ||   32 |

4. Backup single Sheet

Mariadb Restore Backup notes (2)