MySQL数据备份

#### mysqldump备份

> mysqldump -uroot -p"password" -h"ipaddress" -database "DatabaseName" > /mysql_backup/bak.sql

```shell
##全量备份
[root@mysql_master ~]# mkdir /var/mysql_backup
[root@mysql_master ~]# mysqldump -uroot -p --all-databases > /var/mysql_backup/20190415.sql
Enter password:
[root@mysql_master ~]# ls /var/mysql_backup/
20190415101054.sql

##备份恢复
[root@mysql_master ~]# mysql -uroot -p </var/mysql_backup/20190415101054.sql
Enter password:
[root@mysql_master ~]# mysql -uroot -p"(Fcuk..0411)"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.24-log Source distribution

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| Book_Class              |
| Cloud_Class_Grade       |
| Cloud_Class_Information |
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
7 rows in set (0.00 sec)

mysql>
```

```shell
##指定备份
[root@mysql_master ~]# mysqldump -uroot -p --databases Book_Class > /var/mysql_backup/Book_Class.sql

##备份恢复
[root@mysql_master ~]# mysql -uroot -p"(Fcuk..0411)"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.24-log Source distribution

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| Book_Class              |
| Cloud_Class_Grade       |
| Cloud_Class_Information |
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
7 rows in set (0.00 sec)

mysql> drop database Book_Class;
Query OK, 3 rows affected (0.02 sec)

mysql> source /var/mysql_backup/Book_Class.sql
Query OK, 3 rows affected (0.02 sec)
```

```shell
##增量备份
[root@mysql_master ~]# mysqldump -uroot -p --single-transaction --all-databases >/var/mysql_backup/alldatabases.sql
Enter password:

#实验操作:把数据库中的数据增加一些,在mysql命令行中运行“flush logs;”刷新binlog日志会产生新的position截点数,后续删除几个表数据, 在binlog中找到删除前的end-log-pos对应的number,恢复时指定pos截点即可.

##恢复
mysqlbinlog mysqld-bin.000008 --stop-position=154 | mysql -uroot -p
Enter password:
```



####Xtrpbackup备份

特点：
​        (1)备份过程快速、可靠；
​        (2)备份过程不会打断正在执行的事务；
​        (3)能够基于压缩等功能节约磁盘空间和流量；
​        (4)自动实现备份检验；
​        (5)还原速度快；

```shell
##工具安装
[root@mysql_master ~]# yum install -y perl-DBD-MySQL perl-DBI perl-Time-HiRes libaio*
[root@mysql_master ~]# yum -y install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
```



```shell
##全量备份
[root@mysql_master ~]# innobackupex --user root --password "(Fcuk..0411)" --port 3306 --socket /usr/local/mysqld/tmp/mysql.sock /var/mysql_backup/xtracebackup/

##恢复
[root@mysql_master ~]# ls /var/mysql_backup/xtracebackup/
2019-04-14_19-00-20
[root@mysql_master ~]# innobackupex --apply-log /var/mysql_backup/xtracebackup/2019-04-14_19-00-20/
[root@mysql_master ~]# mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@mysql_master ~]# rm -rf /usr/local/mysqld/data/*
[root@mysql_master ~]# innobackupex --copy-back /var/mysql_backup/xtracebackup/2019-04-14_19-00-20/
[root@mysql_master ~]# chown -R mysql:mysql /usr/local/mysqld/*
[root@mysql_master ~]# mysql.server start
Starting MySQL. SUCCESS!
```

```shell
##增量备份(在全量备份的基础之上)
[root@mysql_master ~]# mkdir /var/mysql_backup/xtracebackup/{incramental_backup,full_backup}
[root@mysql_master ~]# innobackupex --user=root --password="(Fcuk..0411)" --port=3306 --socket=/usr/local/mysqld/tmp/mysql.sock /var/mysql_backup/xtracebackup/full_backup/

[root@mysql_master ~]# mysql -uroot -p"(Fcuk..0411)"
mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| Book_Class              |
| Cloud_Class_Grade       |
| Cloud_Class_Information |
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
7 rows in set (0.00 sec)

mysql> create database Test;
Query OK, 1 row affected (0.00 sec)

mysql> use Test;
Database changed
mysql> create table backup(id int, name varchar(200))charset=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into backup (id,name) values(1,"bavduer001");
Query OK, 1 row affected (0.01 sec)

mysql> insert into backup (id,name) values(1,"bavduer002");
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye
[root@mysql_master ~]#


[root@mysql_master ~]# innobackupex --user=root --password="(Fcuk..0411)" --port=3306 --socket=/usr/local/mysqld/tmp/mysql.sock --incremental /var/mysql_backup/xtracebackup/incramental_backup/ --incremental-basedir=/var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/
```

```shell
##恢复
[root@mysql_master ~]# innobackupex --apply-log --redo-only --use-memory=1G /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/

[root@mysql_master ~]# innobackupex --apply-log --redo-only /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/ --incremental-dir=/var/mysql_backup/xtracebackup/incramental_backup/2019-04-14_20-31-30/

[root@mysql_master ~]# mysql.server stop
Shutting down MySQL.. SUCCESS!
[root@mysql_master ~]# rm -rf /usr/local/mysqld/data/*
[root@mysql_master ~]# innobackupex --copy-back /var/mysql_backup/xtracebackup/full_backup/2019-04-14_19-24-48/

[root@mysql_master ~]# chown -R mysql:mysql /usr/local/mysqld/*
[root@mysql_master ~]# mysql.server start
Starting MySQL.. SUCCESS!
```

我在社区摸爬滚打这么多年，所谓阅人无数，就算没有见过猪走路，也总明白猪肉是啥味道的。一看到楼主的气势，我就觉得楼主同在社区里灌水的那帮小混子有着本质的差别，你一定就是传说中的最强技术牛。

感谢大佬精彩分享，每日打卡。

楼主的文章图文并茂，清晰易懂，看完这波操作可以轻松上手了，如遇到问题再向楼主请教~