全量备份
1 创建mysqldump脚本
先了解一下mysqldump的基本参数
1. 全备数据库(innodb Engine)
mysqldump -uroot -pmysql123 --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
2. 全备(Myisam Engine)
mysqldump -uroot -pmysql123 --lock-all-tables --master-data=2 --flush-logs --flush-privileges --routines --events --all-databases > /backup/db_bak01.sql
3. 只备份数据库db1和db2
mysqldump -uroot -pmysql123 --single-transaction --master-data=2 --flush-logs --routines --events --databases db1 db2 > /backup/db_bak01.sql
#innodb事务
--single-transaction
#在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
#0: 不记录
#1：记录CHANGE MASTER语句
#2：记录但注释CHANGE MASTER语句
#例子 CHANGE MASTER TO MASTER_LOG_FILE=’MySQL-bin.000002′, MASTER_LOG_POS=106;
--master-data=[0][1][2]
#刷新binlog日志,比如之前是mysql-bin.000004执行完flush-logs后创建mysql-bin.000005,重启数据库也会执行flush-logs
-F 或 --flush-logs
#指定数据库,加了这个会生成建库的语句
-B
#备份存储过程等
-R
#锁表
-x
1.1 找一个目录,这里选择放到/data/mysqlbackup下,创建shell脚本
vim /data/mysqlbackup/mysqlBackuoShell.sh
1.2 添加内容
#!/bin/bash
#保存备份个数,31条
number=31
#备份保存路径
backup_dir=/data/mysqlbackup/mysqlbackupdata
#日期
dd=`date +%Y-%m-%d`
#备份工具
tool=mysqldump
#用户名
username=用户名
#密码
password=密码
#将要备份的数据库
database_name=city_challenge
#运行内容,结果用gzip压缩下
$tool -u $username -p$password --single-transaction --master-data=2 $database_name|gzip > $backup_dir/$database_name-$dd.sql.gz
#写创建备份日志
echo "create $backup_dir/$database_name-$dd.sql.gz" >> $backup_dir/log.txt
#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql.gz | awk '{print $9 }' | head -1`
#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql.gz | awk '{print $9 }' | wc -l`
if [ $count -gt $number ]
then
rm $delfile //删除最早生成的备份，只保留number数量的备份
#写删除文件日志
echo "delete $delfile" >> $backup_dir/log.txt
fi
1.3给脚本赋予执行权限
chmod +x mysqlBackuoShell.sh
2.crontab
crontab是一个 Liunx 下 的定时执行工具，可以在无需人工干预的情况下运行作业。
//启动服务
service crond start
//关闭服务
service crond stop
//重启服务
service crond restart
//重新载入配置
service crond reload
//查看服务状态
service crond status
crontab语法
crontab命令用于安装、删除或者列出用于驱动cron后台进程的表格。用户把需要执行的命令序列放到crontab文件中以获得执行。每个用户都可以有自己的crontab文件。/var/spool/cron下的crontab文件不可以直接创建或者直接修改。该crontab文件是通过crontab命令创建的。
在crontab文件中如何输入需要执行的命令和时间。该文件中每行都包括六个域，其中前五个域是指定命令被执行的时间，最后一个域是要被执行的命令。每个域之间使用空格或者制表符分隔。
2.1查看cron状态
service crond status
没启动就
service crond start
2.1创建crontab脚本
位置随便,这里和shell脚本放在一块了
vim /data/mysqlbackup/mysqlRollBack.cron
2.2 编辑,每天凌晨3点执行一次
crontab在线工具~~注意和java的cron不一样
0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh
3.3 执行定时任务命令
crontab mysqlRollBack.cron
2.4 查看定时任务是否成功或者检测/var/spool/cron下是否生成对应cron脚本
crontab -l
[root@node-test01 /data 18:04:43]#crontab -l
0 3 * * * /data/mysqlbackup/mysqlBackuoShell.sh
2.5 删除定时任务
如果不想执行定时任务,直接删除就行了
crontab -r
2.6编辑定时任务
如果想要修改执行时间,可以编辑
crontab -e
3.数据恢复
注意,这里只是做了全量备份
3.1删除旧数据库
mysql> drop database 数据库名
3.2 解压备份的文件
gzip -d city_challenge-2022-03-05.sql.gz
3.3 恢复数据
1.在系统命令行中，输入如下实现还原：
mysql -uroot -p12345678 < /data/mysqlbackup/city_challenge-2022-03-05.sql
2.在登录进入mysql系统中,通过source指令找到对应系统中的文件进行还原：
mysql>use city_challenge
mysql> source /data/mysqlbackup/city_challenge-2022-03-05.sql
注意一个细节:
若是mysqldump导出一个库的数据，导出文件为a.sql，然后mysql导入这个数据到新的空库下。
如果新库名和老库名不一致，那么需要将a.sql文件里的老库名改为新库名，
这样才能顺利使用mysql命令导入数据（如果使用source命令导入就不需要修改a.sql文件了）。
增量备份
1.开启mysql的binlog日志(增量备份)
1.1 查看binlog是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.01 sec)
如果没开启
1.2 编辑my.cnf
vim /etc/my.cnf
增加
#节点Id,注意集群中不能重复,单节点不配置也可以
server-id=123
#开启binlog日志,指定其存放位置
log-bin=/var/lib/mysql/mysql-bin
#开启binlog自动过期
expire_logs_days=3
1.3 重启数据库
service mysqld restart
然后
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
发现已开启binlog
/var/lib/mysql/mysql-bin是日志文件
/var/lib/mysql/mysql-bin.index是索引
1.4 查看日志索引和事件位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql-bin是日志文件
000004是索引
pos=154是事件位置也可以叫偏移值
1.5如何实现增量备份
比如上面的每天凌晨3点执行一次全量备份,并且刷新一次binlog(–flush-logs),之前是 mysql-bin.000004,执行
flush-logs后变成 mysql-bin.000005,那么mysql-bin.000005,中之中就是当日新增的数据
2.一次增量备份
2.1凌晨3点进行一次全量备份
查询binlog
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 7083 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 467 |
| mysql-bin.000005 | 1265 |
| mysql-bin.000006 | 201 |
+------------------+-----------+
6 rows in set (0.00 sec)
#全量备份test库,并刷新binlog
mysqldump -u root -p --master-data=2 --single-transaction -F -B test > /data/mysqlbackup/test5.sql;
再次查询binlog
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 7083 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 467 |
| mysql-bin.000005 | 1265 |
| mysql-bin.000006 | 201 |
| mysql-bin.000007 | 154 |
+------------------+-----------+
7 rows in set (0.00 sec)
多出了一条
mysql-bin.000007
2.2 早上9点向test.user中插入三条数据
mysql>INSERT INTO user ( id, age,name ) VALUES ( 5, 5, '5' );
mysql>INSERT INTO user ( id, age,name ) VALUES ( 6, 6, '6' );
mysql>INSERT INTO user ( id, age,name ) VALUES ( 7, 7, '7' );
2.3 下午5点把数据库删了>
mysql> drop database test;
2.4 如何恢复数据库?
查看最新的mysql-bin.00000?*
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 1109 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
日志是mysql-bin.000007,pos是1109
接着刷新日志 flush logs;
mysql> flush logs;
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 7083 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 467 |
| mysql-bin.000005 | 1265 |
| mysql-bin.000006 | 201 |
| mysql-bin.000007 | 1156 |
| mysql-bin.000008 | 154 |
+------------------+-----------+
8 rows in set (0.00 sec)
接着新的日志就会输出到mysql-bin.000008上,以便我们分析mysql-bin.000007的数据
查看mysql-bin.000007
方式1
#-vvv :查看sql,方便判断pos点
#--base64-output=decode-rows : base64编码的数据转成row(没看到有啥用)
mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000007
部分
/*!*/;
# at 823
#220307 14:32:40 server id 1 end_log_pos 874 CRC32 0x6a5153c0 Table_map: `test`.`user` mapped to number 156
# at 874
#220307 14:32:40 server id 1 end_log_pos 921 CRC32 0x9df18a8f Write_rows: table id 156 flags: STMT_END_F
### INSERT INTO `test`.`user`
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2=7 /* INT meta=0 nullable=1 is_null=0 */
### @3='7' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
# at 921
#220307 14:32:40 server id 1 end_log_pos 952 CRC32 0xaa3f5c7b Xid = 7046
COMMIT/*!*/;
# at 952
#220307 14:37:44 server id 1 end_log_pos 1017 CRC32 0x8914e925 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1017
#220307 14:37:44 server id 1 end_log_pos 1109 CRC32 0xb100a121 Query thread_id=124 exec_time=0 error_code=0
SET TIMESTAMP=1646635064/*!*/;
drop database test
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
方式2
mysql>show binlog events in 'mysql-bin.000007'\G;
mysql> show binlog events in 'mysql-bin.000007';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000007 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql-bin.000007 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000007 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000007 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000007 | 291 | Table_map | 1 | 342 | table_id: 156 (test.user) |
| mysql-bin.000007 | 342 | Write_rows | 1 | 389 | table_id: 156 flags: STMT_END_F |
| mysql-bin.000007 | 389 | Xid | 1 | 420 | COMMIT /* xid=7033 */ |
| mysql-bin.000007 | 420 | Anonymous_Gtid | 1 | 485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000007 | 485 | Query | 1 | 557 | BEGIN |
| mysql-bin.000007 | 557 | Table_map | 1 | 608 | table_id: 156 (test.user) |
| mysql-bin.000007 | 608 | Write_rows | 1 | 655 | table_id: 156 flags: STMT_END_F |
| mysql-bin.000007 | 655 | Xid | 1 | 686 | COMMIT /* xid=7043 */ |
| mysql-bin.000007 | 686 | Anonymous_Gtid | 1 | 751 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000007 | 751 | Query | 1 | 823 | BEGIN |
| mysql-bin.000007 | 823 | Table_map | 1 | 874 | table_id: 156 (test.user) |
| mysql-bin.000007 | 874 | Write_rows | 1 | 921 | table_id: 156 flags: STMT_END_F |
| mysql-bin.000007 | 921 | Xid | 1 | 952 | COMMIT /* xid=7046 */ |
| mysql-bin.000007 | 952 | Anonymous_Gtid | 1 | 1017 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000007 | 1017 | Query | 1 | 1109 | drop database test |
| mysql-bin.000007 | 1109 | Rotate | 1 | 1156 | mysql-bin.000008;pos=4 |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
20 rows in set (0.00 sec)
找到问题pos在1017~1109之间
所以将数据恢复到1017之前就可以了
1.先将昨天的全量备份恢复
mysql -uroot -p -v <test5.sql
查看数据库,发现test已经恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| city_challenge |
| ds_0 |
| ds_1 |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
8 rows in set (0.00 sec)
2.恢复今天删库之前的数据
方式一:
mysqlbinlog --stop-position=1017 -d test mysql-bin.000007 | mysql -uroot -p12345678
mysqlbinlog恢复数据常用参数:
常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--skip-gtids是忽略GTIDs报错
-d 指定数据库
原理:实际是将读出的binlog日志内容，通过管道符传递给mysql命令
方式二:
mysqlbinlog --stop-position=1017 -d test mysql-bin.000007 >testa.sql
mysql -uroot -p12345678 < /data/mysqlbackup/testa.sql
原理:参照全量备份
