MySQL-运维

mysql备份和恢复

• 导出数据库

# 导出某个数据库
mysqldump -u root -p test_db > /root/test_db.sql

# 导出某个数据库，包含所有函数和存储过程需要添加 --routines 参数
mysqldump -u root -p test_db --routines > /root/test_db.sql

# 导出所有数据库
mysqldump -u root -p -A > /root/all_db.sql

# 导出一张表
mysqldump -u root -p test_db t_service > /root/service.sql

# 导出数据库结构  -d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
mysqldump -u root -h localhost -p -d --add-drop-table bss >/root/test_db.sql

• 导入数据库

# 需要提前创建好数据名
mysql -u root -p
use bss
source /root/bss_db.sql

• 导入部分数据

有时候我们需要往数据库里面添加一些不常改动的数据，我们一些写一些脚本程序，但前提服务器得有脚本运行环境和依赖。如果单独编写一个项目，显得有点 杀鸡用牛刀 的感觉， 此时，我们可以在本地操作数据，然后将查询结果导出 sql 文件，最后去线上数据库导入数据

sql文件内容如下：

INSERT INTO `t_user`(`id`, `name`) VALUES (521, "asd");
INSERT INTO `t_user`(`id`, `name`) VALUES (522, "zxc");
...
INSERT INTO `t_user`(`id`, `name`) VALUES (1520, "ttt");

指定数据库并运行脚本文件：

# -D 指定数据库名， 并运行sql文件 命令
mysql -u root -p -D t_user < user_data.sql

# 修改密码
update user set password=password("123") where user="root";
# 刷新权限
flush privileges;

binlog进行数据恢复

相信后端开发的同学在开发过程经常会遇到产品临时修改线上数据的需求，如果手法很稳那么很庆幸可以很快完成任务，很不幸某一天突然手一抖把表里的数据修改错误或者误删了，这个时候你会发现各种问题反馈接踵而来。 此时，需要进行数据恢复。可进行如下操作进行数据恢复

查看是否开启了binlog

# 查看是否开启了 binlog 日志功能
show variables like '%log_bin%';

# 新建binlog日志文件
flush logs;

# 查看当前 binlog 文件名字
show master status;

# 查看binlog文件事件
show binlog events in 'binlog.000098';

mariadb开启binlog

vim /etc/my.cnf

# 在[mysqld]下添加
log-bin=mysql-bin
max-binlog-size=1G
expire_logs_days=90
binlog_format=row

查询binlog日志

show master status;

# 查看 binlog 进行日志分片的开始时间，并查询最近的5个日志分片
ll -lt /var/lib/mysql/mysql-bin* | head -n 5;

# 导出2023-06-11 -- 2023-06-12 时间段之间 mysql-bin.000001 文件中 的操作数据  
# （--start-datetime 可以通过通过上面分片文件信息，填写开始时间， --stop-datetime 可以填今天，也可以是下一个分片文件的开始时间）
# 文件中数据内容默认是加密的， --base64-output=decode-rows 可以配置解密； 文件中默认是不展示数据的， 可以通过 --verbose 显示数据修改信息；
mysqlbinlog --base64-output=decode-rows --verbose --database=test --start-datetime="2023-06-11 00:00:00" --stop-datetime="2023-06-12 00:00:00" /var/lib/mysql/mysql-bin.000001 > mysql-binlog.sql


# row 方式进行数据查询

# 查询 t_person 表的插入语句
cat mysql-binlog.sql | grep -A 10 'INSERT INTO `test`.`t_person`'
# 查询 t_person 表的删除语句
cat mysql-binlog.sql | grep -A 10 'INSERT INTO `test`.`t_person`'
# 查询 t_person 表的更新语句
cat mysql-binlog.sql | grep -A 10 'UPDATE `test`.`t_person`'

# statement 方式进行数据查询

# 查询 t_person 表的插入语句
cat mysql-binlog.sql | grep 'insert into t_person'
# 查询 t_person 表的删除语句
cat mysql-binlog5.sql | grep 'delete from t_person' 
# 查询 t_person 表的更新语句
cat mysql-binlog5.sql | grep 'delete from t_person'