Mariadb备份恢复
杂项
1、mariadb默认引擎其实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 |
XtraBackup
1、安装XtraBackup
yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm -y
1.1 查看安装生成的文件
[root@master2 ~]# 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 准备工作
1.2.1 确保所有表都是InnoDB
1.2.2 确认一个参数，该参数实现很多高级功能（单独的表空间功能）
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'innodb%';
| innodb_file_per_table | OFF
1.2.3 因为已经有数据库了。需要删除多有数据库，开启参数，重新生成；
[root@master2 ~]# rm -rf /data/mysql/*
[root@master2 ~]# rm -rf /data/binlogs/*
[root@master2 ~]# vim /etc/my.cnf
innodb_file_per_table=ON
[root@master2 ~]# systemctl start mariadb.service
1.2.4 还原数据库
[root@master2 ~]# mysql < all.sql
MariaDB [hellodb]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30349 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 522279 |
2、备份
2.1 创建备份目录
[root@master2 ~]# mkdir /backups/
2.2 备份
2.1 全数据库备份
[root@master2 ~]# innobackupex --user=root /backups/
2.2 将数据库恢复至master1
2.2.1 master1准备：
[root@master1 data]# mkdir -pv /data/mysql
mkdir: created directory ‘/data’
mkdir: created directory ‘/data/mysql’
[root@master1 data]# chown mysql.mysql /data/mysql/
[root@master1 data]# service mysqld start
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
2.2.2 恢复备份
拷贝数据库倒要恢复的机子：
[root@master2 ~]# scp /backups/2017-01-28_03-19-23/* root@master1:/tmp/backups
目录整理：
[root@master1 ~]# innobackupex --apply-log /backups/2017-01-28_03-19-23/
恢复备份：
[root@master1 ~]# service mysqld stop
[root@master1 ~]# rm -rf /data/mysql/*
[root@master1 ~]# innobackupex --copy-back /backups/2017-01-28_03-19-23/
[root@master1 mysql]# chown -R mysql:mysql /data/mysql/*
[root@master1 mysql]# rm -rf ib_logfile*
[root@master1 mysql]# service mysqld start
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
3、增量备份
3.1 准备
创建表：
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 重新做完全备份
删除之前的备份
[root@master1 ~]# rm -rf /backups/*
完全备份
[root@master1 ~]# innobackupex /backups/
查看备份类型：
[root@master1 ~]# cat /backups/2017-01-28_05-05-39/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1645521
last_lsn = 1645521
compact = 0
recover_binlog_info = 0
3.3 数据修改，准备实验增量备份
删除表：
MariaDB [hellodb]> DROP TABLE coc;
MariaDB [hellodb]> INSERT INTO testtb VALUES (44),(32);
插入数据
3.4 增量备份
innobackupex --incremental /backups/ --incremental-basedir=/backups/2017-01-28_05-05-39
[root@master1 ~]# cat /backups/2017-01-28_08-02-15/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 1645521
to_lsn = 1647443
last_lsn = 1647443
compact = 0
recover_binlog_info = 0
3.5 备份还原
停止数据库
3.5.1 整理完全备份
[root@master1 ~]# innobackupex --apply-log --redo-only /backups/2017-01-28_05-05-39/
3.5.2 整理增量备份
[root@master1 ~]# innobackupex --apply-log --redo-only /backups/2017-01-28_05-05-39/ --incremental-dir=/backups/2017-01-28_08-02-15/
3.5.3 删除数据
[root@master1 ~]# rm -rf /data/mysql/*
3.5.4 恢复备份
[root@master1 ~]# innobackupex --copy-back /backups/2017-01-28_05-05-39/
3.5.5 更改权限
[root@master1 mysql]# chown -R mysql.mysql ./*
3.5.6 启动数据库
MariaDB [hellodb]> SELECT * FROM testtb;
+------+
| id |
+------+
| 1 |
| 10 |
| 99 |
| 44 |
| 32 |
