cyhgogogo 2019-12-13
mysqldump -u root -p auth > /backup/auth.sql
mysqldump -u 用户名 -p [密码] [选项] --databases 库名 1 [库名2] ... > /备份路径/备份文件名
mysqldump -u root -p --databases autth mysql > /backup/databases-auth-mysql.sql
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
mysqldump -u root -p --opt --all-databases > /backup/all-data.sql
mysqldump -u 用户名 -p [密码] [选项] 数据库名 表命 > /备份路径/备份文件名
mysqldump -u root -p mysql user > /backup/mysql-user.sql
MySQL[(none)]>source /backup/all-data.sql
mysql -u 用户名 -p [密码] < 库备份脚本的路径
mysql -u root -p < /backup/all-data.sql
mysq| -u 用户名 -p [密码] < 表备份脚本的路径
mysql -u root -P mysq| < /backup/mysql-user.sql
一般恢复
基于时间点的恢复
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 密码
[ ~]# mysql -uroot -p //进入数据库 Enter password: mysql> create database school; //创建数据库 Query OK, 1 row affected (0.01 sec) mysql> use school; //使用数据库 Database changed mysql> create table info( //创建表 -> id int(3) not null primary key auto_increment, -> name varchar(10) not null, -> score decimal(4,1) not null); Query OK, 0 rows affected (0.02 sec) mysql> desc info; //查看表结构 +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(3) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | | NULL | | | score | decimal(4,1) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into info (name,score) values (‘stu01‘,88),(‘stu02‘,77); //插入表数据 Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from info; //查看表内容 +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | stu01 | 88.0 | | 2 | stu02 | 77.0 | +----+-------+-------+ 2 rows in set (0.01 sec) mysql> select * from info limit 1; //只显示表中的前1行 +----+-------+-------+ | id | name | score | +----+-------+-------+ | 1 | stu01 | 88.0 | +----+-------+-------+ 1 row in set (0.00 sec)
[ ~]# cd /usr/local/mysql/data/ //切换到数据库的数据目录下 [ data]# ls auto.cnf ibdata1 ib_logfile1 mysql school test ib_buffer_pool ib_logfile0 ibtmp1 performance_schema sys [ data]# cd school/ [ school]# ls //数据中的文件 db.opt info.frm info.ibd [ school]# cd .. [ data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/ //用xz格式压缩 [ data]# cd /opt/ [ opt]# ls mysql-2019-11-26.tar.xz mysql-5.7.20 rh
[ opt]# mysqldump -uroot -p school > /opt/school.sql //逻辑备份单个数据库 Enter password: [ opt]# ls mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql [ opt]# vim school.sql //查看备份数据库脚本 ... CREATE TABLE `info` ( `id` int(3) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, `score` decimal(4,1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; ... LOCK TABLES `info` WRITE; /*!40000 ALTER TABLE `info` DISABLE KEYS */; INSERT INTO `info` VALUES (1,‘stu01‘,88.0),(2,‘stu02‘,77.0); ...
[ opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql //备份多个数据库 Enter password: [ opt]# ls db_school_mysql.sql mysql-2019-11-26.tar.xz mysql-5.7.20 rh school.sql
[ opt]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql //完全备份 Enter password: [ opt]# ls all.sql mysql-2019-11-26.tar.xz rh db_school_mysql.sql mysql-5.7.20 school.sql
[ opt]# mysqldump -uroot -p school info > /opt/school_info.sql //对数据库中的表进行备份 Enter password: [ opt]# ls all.sql mysql-2019-11-26.tar.xz rh school.sql db_school_mysql.sql mysql-5.7.20 school_info.sql
[ opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql //对表结构进行备份 Enter password: [ opt]# ls all.sql mysql-5.7.20 school_info.sql db_school_mysql.sql rh school.sql mysql-2019-11-26.tar.xz school_info_desc.sql
[ opt]# mysql -uroot -p //进入数据库 Enter password: mysql> show databases; //查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> use school; //使用数据库 Database changed mysql> show tables; //查看表 +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> drop table info; //删除表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; //查看表 Empty set (0.00 sec) mysql> source /opt/school.sql //恢复数据库脚本文件 mysql> show tables; //查看表 +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec)
mysql> drop table info; //删除表 Query OK, 0 rows affected (0.01 sec) mysql> show tables; //查看表 Empty set (0.00 sec) mysql> quit //退出 Bye [ opt]# mysql -uroot -p123123 school < /opt/school.sql //利用mysql命令进行恢复 mysql: [Warning] Using a password on the command line interface can be insecure. [ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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)
[ opt]# vim /etc/my.cnf //开启二进制日志文件 [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 pid-file = /usr/local/mysql/mysql.pid socket = /usr/local/mysql/mysql.sock log-bin=mysql-bin //开启二进制日志文件 server-id = 1 [ opt]# systemctl restart mysqld.service //重启mysql服务 [ opt]# cd /usr/local/mysql/data/ //切换到mysql站点 [ data]# ls //查看二进制日志文件 auto.cnf ib_logfile0 mysql performance_schema test ib_buffer_pool ib_logfile1 mysql-bin.000001 school ibdata1 ibtmp1 mysql-bin.index sys
[ data]# mysqldump -uroot -p123123 school > /opt/school.sql //一次完全备份 mysqldump: [Warning] Using a password on the command line interface can be insecure. [ data]# ls auto.cnf ib_logfile0 mysql performance_schema test ib_buffer_pool ib_logfile1 mysql-bin.000001 school ibdata1 ibtmp1 mysql-bin.index sys [ data]# mysqladmin -uroot -p123123 flush-logs //刷新二进制日志文件 mysqladmin: [Warning] Using a password on the command line interface can be insecure. [ data]# ls //生成新的二进制日志文件,接下来的操作会保存在mysql-bin.000002中 auto.cnf ib_logfile0 mysql mysql-bin.index sys ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema test ibdata1 ibtmp1 mysql-bin.000002 school
[ data]# mysql -uroot -p123123 ##进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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 | st01 | 88.0 | | 2 | st02 | 77.0 | +----+------+-------+ 2 rows in set (0.00 sec) mysql> insert into info (name,score) values (‘by01‘,66); //正确操作 Query OK, 1 row affected (0.00 sec) mysql> select * from info; +----+------+-------+ | id | name | score | +----+------+-------+ | 1 | st01 | 88.0 | | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +----+------+-------+ 3 rows in set (0.00 sec) mysql> delete from info where name=‘st01‘; //错误操作 Query OK, 1 row affected (0.00 sec) mysql> insert into info (name,score) values (‘by02‘,99); //正确操作 Query OK, 1 row affected (0.00 sec) mysql> select * from info; +----+------+-------+ | id | name | score | +----+------+-------+ | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | +----+------+-------+ 3 rows in set (0.00 sec) [ data]# mysqladmin -uroot -p123123 flush-logs //刷新二进制日志文件 mysqladmin: [Warning] Using a password on the command line interface can be insecure. [ data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt //用64位解码器查看二进制日志文件,并生成一个文件 [ data]# cd /opt/ [ opt]# ls bak.txt mysql-5.7.20 rh school.sql [ opt]# vim bak.txt //查看二进制日志文件 # at 1084 #191127 20:14:01 server id 1 end_log_pos 1132 CRC32 0xdcc90eb5 Write_rows: table id 221 flags: STMT_END_F ### INSERT INTO `school`.`info` //第一次正确操作的时间和位置 ### SET ### @1=3 ### @2=‘by01‘ ### @3=66.0 ... # at 1302 //停止位置点 #191127 20:14:46 server id 1 end_log_pos 1357 CRC32 0x6648509a Table_map: `school`.`info` mapped to number 221 # at 1357 #191127 20:14:46 server id 1 end_log_pos 1405 CRC32 0x1eeb752b Delete_rows: table id 221 flags: STMT_END_F ### DELETE FROM `school`.`info` //第二次执行错误操作的时间和位置191127 20:14:46 ### WHERE ### @1=1 ### @2=‘st01‘ ### @3=88.0 # at 1405 //开始位置点 #191127 20:14:46 server id 1 end_log_pos 1436 CRC32 0xf1c8d903 Xid = 54 ... # at 1630 #191127 20:15:16 server id 1 end_log_pos 1678 CRC32 0x08d9b0f4 Write_rows: table id 221 flags: STMT_END_F ### INSERT INTO `school`.`info` //第二次正确操作的时间和位置191127 20:15:16 ### SET ### @1=4 ### @2=‘by02‘ ### @3=99.0
[ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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> source /opt/school.sql //恢复完全备份数据库脚本 ... mysql> show tables; //查看表 +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> select * from info; //查看表数据 +----+------+-------+ | id | name | score | +----+------+-------+ | 1 | st01 | 88.0 | | 2 | st02 | 77.0 | +----+------+-------+ 2 rows in set (0.00 sec) [ opt]# mysqlbinlog --no-defaults --stop-datetime=‘2019-11-27 20:14:46‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //恢复bin.000002中前一个正确的执行语句(从第二个错误语句时间点停止) mysql: [Warning] Using a password on the command line interface can be insecure. [ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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 | st01 | 88.0 | | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +----+------+-------+ 3 rows in set (0.00 sec) [ opt]# mysqlbinlog --no-defaults --start-datetime=‘2019-11-27 20:15:16‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //跳过错误节点,恢复最后一个正确的操作(从最后一个正确的操作时间点开始) mysql: [Warning] Using a password on the command line interface can be insecure. [ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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 | st01 | 88.0 | | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | +----+------+-------+ 4 rows in set (0.00 sec)
mysql> delete from info where name=‘by01‘; //为实验方便直接删除 Query OK, 1 row affected (0.01 sec) mysql> delete from info where name=‘by02‘; //删除 Query OK, 1 row affected (0.00 sec) mysql> select * from info; //完全备份的初始状态 +----+------+-------+ | id | name | score | +----+------+-------+ | 1 | st01 | 88.0 | | 2 | st02 | 77.0 | +----+------+-------+ 2 rows in set (0.00 sec) mysql> quit Bye [ opt]# mysqlbinlog --no-defaults --stop-position=‘1302‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //跳过错误操作的位置点从上一个位置点开始 [ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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 | st01 | 88.0 | | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | +----+------+-------+ 3 rows in set (0.00 sec) mysql> quit Bye [ opt]# mysqlbinlog --no-defaults --start-position=‘1405‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //从错误的位置后一个位置点开始,跳过错误操作的位置点 [ opt]# mysql -uroot -p123123 //进入数据库 mysql: [Warning] Using a password on the command line interface can be insecure. 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 | st01 | 88.0 | | 2 | st02 | 77.0 | | 3 | by01 | 66.0 | | 4 | by02 | 99.0 | +----+------+-------+ 4 rows in set (0.00 sec)
[ opt]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //全部增量恢复