zhaojp0 2020-01-10
实操
创建数据库、表、插入数据
mysqldump命令对单个库进行完全备份
添加多个数据库
[ mysql]# mysqldump -u root -p school > /opt/school.sql Enter password: [ mysql]# ls /opt/ dir_SC_UTF8 mysql-5.7.20 php-7.1.10 school.sql mysql-2020-01-07.tar.xz nginx-1.12.2 rh 说明.htm [ mysql]# cd /opt [ opt]# ls school.sql school.sql [ opt]# vim school.sql [ opt]#
mysqldump命令对多个库进行完全备份
[ opt]# mysqldump -uroot -p12341234 school info > /opt/school_info.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [ opt]# ls /opt/school_info.sql /opt/school_info.sql [ opt]# vim /opt/school_info.sql
基于表结构的备份
[ opt]# mysqldump -uroot -p12341234 -d school info > /opt/school_infod.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [ opt]# vim /opt/school_infod.sql
恢复数据库
使用musqldump命令导出的SQL备份脚本,在进行数据恢复时可使用以下方法导入
mysql [(none)] > source /backup/all-data.sql
示例:
备注:备份sql脚本的路径写绝对路径
恢复数据库,是恢复数据库里面的表,如果此时数据库也被删除,需要创建一个同名的数据库————仔细查看school.sql脚本,可以发现没有school数据库的操作
[ opt]# mysql -uroot -p Enter password: Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bbs | | mysql | | performance_schema | | school | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use school; Database changed mysql> drop table info; Query OK, 1 row affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql> source /opt/school.sql Query OK, 0 rows affected (0.00 sec) ...... Query OK, 0 rows affected (0.00 sec) mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec)
使用mysql命令恢复数据
mysql -u -p [密码] < 库备份脚本的路径
mysql> drop table info; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec) mysql> quit Bye [ opt]# [ opt]# mysql -uroot -p12341234 school < /opt/school.sql mysql: [Warning] Using a password on the command line interface can be insecure. [ opt]# mysql -uroot -p Enter password: mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> select * from info; +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +----+-------+-------+ 2 rows in set (0.00 sec)
mysql 命令恢复举例
恢复表的操作
mysql -u 用户名 -p [密码] < 表备份脚本的路径 mysql -u -p mysql < /backup/mysql-user.sql
mysql备份思路:
定期实施备份,制定备份计划或者策略,并严格遵守
除了进行完全备份,开启mysql服务器的日志功能是很重要的
完全备份加上日志,可以对mysql进行最大化还原
使用统一的和易理解的备份文件名称
不要使用backup1/2这样没有意义的名字
推荐使用库名或者表名加上时间的命名规则
备份文件名使用时间+业务名+库名
要开启服务器的日志功能
mysql增量备份
诞生增量备份的原因
解决使用mysqldump进行完全备份时的存在的问题
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
基于位置恢复
就是将某个起始时间的二进制日志导入到数据库中,从而跳过某个发生错误的时间点实现数据的恢复
命令:
#恢复数据到指定位置 mysqlbinlog --stop-position=‘操作 id’ 二进制日志 | mysql -u 用户名 -p 密码 #从指定的位置开始恢复数据 mysqlbinlog --start-position=‘操作 id’ 二进制日志 | mysql -u 用户名 -p 密码
基于时间点恢复
使用基于时间点的恢复,可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作,所以我们需要一种更为精确的恢复方式
针对过程中的误操作备份,如何跳过误操作的方式————可以进行断点恢复
语法:
#从日志开头截止到某个时间点的恢复 mysqlbinlog [–no-defaults] --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码 #从某个时间点到日志结尾的恢复 mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码 #从某个时间点到某个时间点的恢复 mysqlbinlog [–no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码
实操
[ opt]# ls all.sql mysql-5.7.20 school_infod.sql db_school_mysql.sql nginx-1.12.2 school_info.sql dir_SC_UTF8 php-7.1.10 school.sql mysql-2020-01-07.tar.xz rh 说明.htm [ opt]# rm -rf *.sql [ opt]# ls dir_SC_UTF8 mysql-5.7.20 php-7.1.10 说明.htm mysql-2020-01-07.tar.xz nginx-1.12.2 rh
开启二进制日志功能,修改/etc/my.cnf文件,然后重启服务
[client] port = 3306 default-character-set=utf8 socket = /usr/local/mysql/mysql.sock [mysql] port = 3306 default-character-set=utf8 socket = /usr/local/mysql/mysql.sock [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock ‘log-bin=mysql-bin server-id = 1 default-storage-engine=Myisam sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES [ opt]# systemctl restart mysqld
查看二进制日志文件
[ opt]# cd /usr/local/mysql/ [ mysql]# cd data/ [ data]# ls auto.cnf ibdata1 ibtmp1 mysql-bin.index sys bbs ib_logfile0 mysql performance_schema ib_buffer_pool ib_logfile1 ‘mysql-bin.000001‘ school [ data]#
做增量备份前,要先进行一次完全备份
[ data]# mysqldump -uroot -p12341234 school > /opt/school.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [ data]# cd /opt [ opt]# ls dir_SC_UTF8 mysql-5.7.20 php-7.1.10 school.sql mysql-2020-01-07.tar.xz nginx-1.12.2 rh 说明.htm [ opt]# vim school.sql
接下来做增量备份,此时,之前的操作被存放到001当中,接下来的操作会被存放到002当中
[ opt]# mysqladmin -uroot -p12341234 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [ opt]# ls /usr/local/mysql/data/ auto.cnf ibdata1 ibtmp1 mysql-bin.000002 school bbs ib_logfile0 mysql mysql-bin.index sys ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema
mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +----+-------+-------+ 2 rows in set (0.00 sec) mysql> insert into info (name,score) values (‘test01‘,66); //这个是正常操作 Query OK, 1 row affected (0.00 sec) mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +----+--------+-------+ 3 rows in set (0.00 sec) mysql> delete from info where name=‘stu01‘; //误操作 Query OK, 1 row affected (0.01 sec) mysql> insert into info (name,score) values (‘test02‘,99); //正常操作 Query OK, 1 row affected (0.01 sec) mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 4 | test02 | 99.0 | | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +----+--------+-------+ 3 rows in set (0.01 sec) mysql> quit Bye
此时在不知情的情况下,进行增量备份,此时误操作写在了002中
[ opt]# mysqladmin -uroot -p12341234 flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [ opt]# cd /usr/local/mysql/data/ [ data]# ls auto.cnf ib_logfile0 mysql-bin.000001 performance_schema bbs ib_logfile1 mysql-bin.000002 school ib_buffer_pool ibtmp1 mysql-bin.000003 sys ibdata1 mysql mysql-bin.index
查看日志文件:-v 显示内容在界面,–base64解码器 output输出 decode-rows 读取按行读取
[ data]# mysqlbinlog --no-defaults mysql-bin.000002 //查看二进制日志文件,不过可以发现看不懂 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 //这个是位置点 #200107 16:54:11 server id 1 end_log_pos 123 CRC32 0x76a9dc26 Start: binlog v 4, server v 5.7.20-log created 200107 16:54:11 //这个是时间点 BINLOG ‘ M0cUXg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA //像这样的就是被加密的命令 [ data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/back.txt //-v 显示内容在界面,--base64解码器 output输出 decode-rows 读取按行读取 [ data]# cd /opt [ opt]# vim back.txt //下面的截图就是bak.txt中的数据信息
错误操作截图
200107 16:57:56 --stop-datetime /指从这个日志文件开始,执行到这个时间点时就停止
200107 16:58:46 --start-datetime /指这个日志文件中,从这个时间点开始向后面执行
先完全备份恢复,source /opt/school.sql
[ opt]# mysql -uroot -p12341234 mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table info; Query OK, 0 rows affected (0.01 sec) mysql> select * from info; ERROR 1146 (42S02): Table ‘school.info‘ doesn‘t exist mysql> show tables; Empty set (0.00 sec) mysql> source /opt/school.sql; Query OK, 0 rows affected (0.00 sec) 。。。。。。 mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> select * from info; +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +----+-------+-------+ 2 rows in set (0.00 sec) mysql>
然后再增量恢复,即时间上的断点恢复
[ opt]# mysqlbinlog --no-defaults --stop-datetime=‘2020-01-07 16:57:56‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p Enter password: [ opt]# mysql -uroot -p12341234 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +----+--------+-------+ 3 rows in set (0.00 sec) mysql> quit Bye [ opt]# mysqlbinlog --no-defaults --start-datetime=‘2020-01-07 16:58:46‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p Enter password: [ opt]# mysql -uroot -p12341234 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | | 4 | test02 | 99.0 | +----+--------+-------+ 4 rows in set (0.00 sec) //误操作删除的stu01 没有被删掉 mysql>
基于位置的恢复
错误操作的日志文件
[ opt]# mysqlbinlog --no-defaults --stop-postion=‘612‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p mysqlbinlog: [ERROR] unknown variable ‘stop-postion=612‘ Enter password: ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES) [ opt]# mysqlbinlog --no-defaults --stop-position=‘612‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p Enter password: [ opt]# mysql -uroot -p12341234 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | | 3 | test01 | 66.0 | +----+--------+-------+ 3 rows in set (0.00 sec) mysql> quit Bye [ opt]# mysqlbinlog --no-defaults --start-position=‘716‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p Enter password: [ opt]# mysql -uroot -p12341234mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> use school; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from info; +----+--------+-------+ | id | name | score | +----+--------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | | 4 | test02 | 99.0 | | 3 | test01 | 66.0 | +----+--------+-------+ 4 rows in set (0.00 sec) mysql> desc info; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(4,1) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> quit Bye [ opt]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //这个是增量恢复,将日志文件内的所有操作全部执行