从零开始的MySQL数据库三部曲（三、MySQL数据库的3种备份和5种还原篇）

数据库冷备份及恢复：

数据库冷备份：

#mkdir /backup
# /etc/init.d/mysqld stop
#tar  -zcf  /backup/mysql_all-$(date +%F).tar.gz  /usr/local/mysql/data/
# ll  /backup

模拟数据库损坏：

mkdir  /bak
mv /usr/local/mysql/data/ /bak/
/etc/init.d/mysqld start→报错
mysql   -u  root  -p→无法登陆
恢复冷备份数据：
# cd /bak/
# tar zxf /backup/mysql_all-2020-11-24.tar.gz -C   /bak/
# mv /bak/usr/local/mysql/data/   /usr/local/mysql
# /etc/init.d/mysqld start（成功）

mysqldump备份与恢复：
mysqldump -u 用户名 -p  密码  选项  数据库名  表名 > /备份路径/备份文件名
常见选项：--all-databases、--opt
备份：
备份数据表
# mysqldump  -u  root  -p123.com  abc  biao1  >  /bak/abc.biao1.sql  将abc库中的表1备份到/bak下，起名为abc.biao1.sql
备份数据库
# mysqldump  -u  root  -p123.com  --database  abc  >  /bak/abc.sql 将abc库备份到/bak下
备份所有库
# mysqldump  -u  root  -p123.com  --all-databases  >  /bak/all.sql 将所有库备份到/bak下 
查看备份文件内容：
# grep  -v   “^--”  /bak/abc.sql  |  grep  -v  “^/”  | grep  -v  “^$”
模拟故障：
drop  database  abc  删除库  
恢复：
mysql  -u 用户名  -p  密码  选项  数据库名 < /备份路径/备份文件名
# mysql  -u  root  -p123.com   < /bak/abc.sql 将abc库备份从/bak中恢复
 # mysql  -u  root  -p123.com  -e  “show  databases; ” 查看库
增量备份与恢复
开启二进制日志
vim /etc/my.cnf
log-bin=/usr/local/mysql/mysql-bin 日志文件储存位置
重启数据库
/etc/init.d/mysqld restart
查看二进制日志文件
mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000001
创建库abc和表biao1
mysql> create database abc
mysql> use abc
mysql> create table biao1(id char(10) not null,name char(20) not null)
mysql> insert into biao1 values('1','z1');
mysql> insert into biao1 values('2','z2');
mysql> select * from biao1;
+----+------+
| 1  | z1   |
| 2  | z2   |
+----+------+
先进行一次完整备份：
#mkdir  /bak
# mysqldump -u root -p123.com abc biao1 > /bak/abc.biao1.sql
生成新的二进制文件（刷新）：
# mysqladmin  -u  root  -p123.com  flush-logs
在添加用户做增量备份:
mysql> use abc; 
mysql> insert into biao1 values('3','z3');
mysql> insert into biao1 values('4','z4');
mysql> select * from biao1;
+----+------+
| id | name |
+----+------+
| 1  | z1   |
| 2  | z2   |
| 3  | z3   |
| 4  | z4   |
+----+------+
刷新，同步：
# mysqladmin -u root -p123.com  flush-logs
#ll  /usr/local/mysql
#cp  /usr/local/mysql/mysql-bin.000002  /bak
模拟误删除：
# mysql -u root -p123.com -e 'drop table abc.biao1;'
# mysql -u root -p123.com -e 'select * from abc.biao1;' 
恢复（完整→增量）：
# mysql -u root -p123.com abc < /bak/abc.biao1.sql 
# mysql -u root -p123.com -e 'select * from abc.biao1;'
+----+------+
| 1  | z1   |
| 2  | z2   |
+----+------+
恢复缺少部分：
# mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u  root  -p123.com
# mysql -u root -p123.com -e 'select * from abc.biao1;'
+----+------+
| id | name |
+----+------+
| 1  | z1   |
| 2  | z2   |
| 3  | z3   |
| 4  | z4   |
+----+------+

基于位置恢复
模拟误删除（只能删除表内容，不能删除表或库）
# mysql -u root -p123.com -e 'delete from abc.biao1 where id=2;' 删除id为2的内容
查看日志：
# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000001

日志内容：
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201125 15:56:22 server id 1  end_log_pos 120 CRC32 0xbd3267d4     Start: binlog v 4, server v 5.6.36-log created 201125 15:56:22 at startup
ROLLBACK/*!*/;
BINLOG '
Jg6+Xw8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAmDr5fEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAdRn
Mr0=
'/*!*/;
# at 120
#201125 15:57:37 server id 1  end_log_pos 211 CRC32 0xa2b4f2e1     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1606291057/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database abc
/*!*/;
# at 211
#201125 15:57:56 server id 1  end_log_pos 346 CRC32 0xc5886149     Query    thread_id=1    exec_time=0    error_code=0
use `abc`/*!*/;
SET TIMESTAMP=1606291076/*!*/;
create table biao1(id char(10) not null,name char(20) not null)
/*!*/;
# at 346
#201125 15:58:09 server id 1  end_log_pos 423 CRC32 0x2c86d1f6     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1606291089/*!*/;
BEGIN
/*!*/;
# at 423
#201125 15:58:09 server id 1  end_log_pos 529 CRC32 0x5dc5a878     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1606291089/*!*/;
insert into biao1 values('1','z1')
/*!*/;
# at 529
#201125 15:58:09 server id 1  end_log_pos 560 CRC32 0x3e449cc5     Xid = 11
COMMIT/*!*/;
# at 560
#201125 15:58:17 server id 1  end_log_pos 637 CRC32 0x44b947fc     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1606291097/*!*/;
BEGIN
/*!*/;
# at 637
#201125 15:58:17 server id 1  end_log_pos 743 CRC32 0x847621a8     Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1606291097/*!*/;
insert into biao1 values('2','z2')
/*!*/;
# at 743
#201125 15:58:17 server id 1  end_log_pos 774 CRC32 0x788195d1     Xid = 12
COMMIT/*!*/;
# at 774
#201125 15:59:04 server id 1  end_log_pos 821 CRC32 0x4a61157e     Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
恢复：
# mysqlbinlog --no-defaults --start-position='637' /usr/local/mysql/mysql-bin.000001 | mysql -u root -p123.com从637位置开始恢复
#mysqlbinlog --no-defaults --stop-position='774' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p123.com恢复到774位置停止（位置的选择：恢复的位置区域里要有遗失的内容，日置文件可见id=2在637区域里）
查看：
#mysql -u root -p123.com -e 'select * from abc.biao1;'
+----+------+
| id | name |
+----+------+
| 1  | z1   |
| 3  | z3   |
| 4  | z4   
| 2  | z2   |
| 3  | z3   |
| 4  | z4   |
+----+------+
基于时间点恢复：
模拟误删除（只能删除表内容，不能删除表或库）
# mysql -u root -p123.com -e 'delete from abc.biao1 where id=2;' 删除id为2的内容
查看日志：
# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000001
恢复：
#mysqlbinlog --no-defaults --start-datetime='2020-11-25 15:58:17' /usr/local/mysql/mysql-bin.000001 | mysql -uroot -p123.com 从2020-11-25  15时58分17秒开始恢复
#mysqlbinlog --no-defaults --stop-datetime='2020-11-25 15:59:04' /usr/local/mysql/mysql-bin.000002 | mysql -uroot -p123.com恢复到2020-11-25  15时59分04秒停止（时间的选择：恢复的时间区域里要有遗失的内容，日置文件可见id=2在2020-11-25  15时58分17秒区域里）
查看：
 #mysql -u root -p123.com -e 'select * from abc.biao1;'
+----+------+
| id | name |
+----+------+
| 1  | z1   |
| 3  | z3   |
| 4  | z4   
| 2  | z2   |
| 3  | z3   |
| 4  | z4   |
+----+------+