LINUX-MySQL数据库备份与恢复

1、准备数据库和数据
mysql> create database  (abc) 库名;      

mysql> use abc (库名)    连接库

mysql> create table biao1（表名） (id int,name char(10));   创造表  

mysql> insert into biao1 values (1,'z3');

mysql> insert into biao1 values (2,'li4');

mysql> select * from biao1;   

mysql> exit

2、冷备份数据库

[root@localhost 桌面]# cd /

[root@localhost /]# mkdir  -p /backup   创建一个目录来放备份文件

[root@localhost /]# /etc/init.d/mysqld stop 关闭数据库 

[root@localhost /]# tar -zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/   
备份数据 

[root@localhost /]# ll /backup/    查看是否有文件

3、恢复冷备份数据

[root@localhost /]# /etc/init.d/mysqld stop    

[root@localhost /]# mkdir bak   创建一个恢复目录

[root@localhost /]# mv /usr/local/mysql/data/   /bak/   模拟故障

[root@localhost /]# /etc/init.d/mysqld start   重启
报错，数据库出错

[root@localhost /]# mysql -u root -p123.com 
无法登陆

[root@localhost /]# tar -zxvf /backup/mysql_all-2018-05-04.tar.gz -C /tmp  归档到/tmp下

[root@localhost tmp]# mv /tmp/usr/local/mysql/data/   /usr/local/mysql/   移动数据

[root@localhost /]# /etc/init.d/mysqld start
启动正常

二、mysqldump备份与恢复

1、mysqldump备份数据库中的表

创建一个放备份数据的目录
mkdir  /bak 

mysqldump -u 用户名 -p密码  库名 表名 > 保存路径/文件名.sql

[root@localhost /]# mysqldump -u root -p123.com abc biao1 > /bak/名字.sql

2、备份数据库

[root@localhost /]# mysqldump -u root -p123.com --databases abc > /bak/名字.sql

3、备份所有数据库

[root@localhost /]# mysqldump -u root -p123.com --all-databases > /bak/名字.sql

4、查看备份文件内容

[root@localhost /]# grep -v "^--" /bak/名字.sql | grep -v "^/" |grep -v "^$"

5、恢复备份

[root@localhost /]# mysql -u root -p123.com abc < /bak/名字.sql

[root@localhost /]# mysql -u root -p123.com -e 'show tables from abc'  -e 查看表结构
恢复所有库
mysql -u root -p123.com  < /bak/名字.sql
恢复表
mysql -u root -p123.com abc  < /bak/名字.sql

三、增量备份恢复案例

1、配置mysql，设置日志文件

[root@localhost bak]# vim /etc/my.cnf
添加：
log-bin=mysql-bin   
创建一个目录    mkdir  /bak

[root@localhost bak]# /etc/init.d/mysqld stop  

[root@localhost bak]# /etc/init.d/mysqld start
重启mysqld服务
systemctl restart mysqld

[root@localhost bak]# ll /usr/local/mysql/  查看

1、创建数据库，创建表，添加数据

[root@localhost bak]# mysql -u root -p123.com

mysql> create database abc;   创建库

mysql> use abc;

mysql> create table biao1 (id char(20),name char(20),sex char(4),user_id char(10),xiaofei int);

mysql> insert into user_info values ('001','zhang1','M','0001',120),('002','zhang2','W','0002',100),
('003','zhang3','M','0003',90),
('004','zhang4','W','0004',160),
('005','zhang5','M','0005',30),
('006','zhang6','W','0006',80);

mysql> select * from biao1;  查看表内容  

2、先进行一次完整备份

[root@localhost /]# mysqldump -u root -p123.com abc biao1 > /bak/名字-$(date +%F).sql

[root@localhost /]# ll /bak   查看日志

生成新的二进制文件
[root@localhost bak]# mysqladmin -uroot -p123.com(数据库密码） flush-logs   （flush 刷新）

[root@localhost ~]# ll /usr/local/mysql/data/  查看日志文件

3、添加新数据

[root@localhost bak]# mysql -u root -p123.com
mysql> use abc;

mysql>  insert into user_info values ('007','zhang7','M','0007',120),('008','zhang8','W','0008',100);

[root@localhost bak]# mysqladmin -uroot -p123.com flush-logs    实现日志同步 刷新

[root@localhost bak]# ll /usr/local/mysql/

4、复制日志文件
[root@localhost bak]# cp /usr/local/mysql/data/mysql-bin.000002 /bak   拷贝/usr下的目录到/bak

模拟故障删除表

[root@localhost bak]# mysql -u root -p123.com -e "drop table abc.user_info" 删除表

[root@localhost bak]# mysql -u root -p123.com -e "show tables from abc"  登录查询

5、恢复数据库中的表和数据

执行完整恢复
[root@localhost bak]# mysql -u root -p123.com abc < /bak/名字-2020-11-24.sql 

[root@localhost bak]# mysql -u root -p123.com -e "show tables from abc"

[root@localhost bak]# mysql -u root -p123.com -e "select * from abc.biao1"
缺少后添加的数据

[root@localhost bak]# mysqlbinlog --no-defaults /bak/mysql-bin.000002 |mysql -u root -p123.com    默认恢复所有的bin

[root@localhost bak]# mysql -u root -p123.com -e "select * from abc.biao1"
数据恢复完整
查看日志文件信息：
ll /usr/local/mysql/data/    查看所有日志文件
[root@localhost bak]# mysqlbinlog --no-defaults /bak/mysql-bin.00000*  查看节点
cp /usr/local/mysql/data/mysql-bin.00000*  拷贝到目录 /bak    方便调用
mysqladmin -uroot -p123.com flush-logs     刷新/结束上一步开始下一步
从日志中的指定位置开始恢复：
[root@localhost bak]# mysqlbinlog --no-defaults --start-position='操作节点' /bak/mysql-bin.000003 | mysql -uroot -p123.com    从操作节点开始恢复

恢复到日志中的指定位置：

[root@localhost bak]# mysqlbinlog --no-defaults /bak/mysql-bin.00000*  查看节点
[root@localhost bak]# mysqlbinlog --no-defaults --stop-position='操作节点' /bak/mysql-bin.000003 | mysql -uroot -p123.com        从头到操作节点开始恢复

恢复日志中介于两个位置之间的数据：

[root@localhost bak]# mysqlbinlog --no-defaults --start-position='操作节点' --stop-position='操作节点' /bak/mysql-bin.000003 | mysql -uroot -p123.com  
操作节点到操作节点的恢复

基于时间点恢复

恢复指定起始时间的数据：
[root@localhost bak]# mysqlbinlog --no-defaults --start-datetime='2018-5-4 17:27:00' /bak/mysql-bin.000003 | mysql -uroot -p123.com

恢复到指定时间之前的数据：

[root@localhost bak]# mysqlbinlog --no-defaults --stop-datetime='2018-5-4 17:27:00' /bak/mysql-bin.000003 | mysql -uroot -p123.com

恢复到指定时间范围的数据：
[root@localhost bak]# mysqlbinlog --no-defaults --start-datetime='2018-5-4 17:27:00' --stop-datetime='2018-5-5 17:27:00' /bak/mysql-bin.000003 | mysql -uroot -p123.com

恢复指定时间开始到指定位置结束的数据：

[root@localhost bak]# mysqlbinlog --no-defaults --start-datetime='2018-5-4 17:27:00' --stop-position='2315' /bak/mysql-bin.000003 | mysql -uroot -p123.com