热备
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name #指定备份的数据库，包括create database语句
-E, --events： #备份相关的所有event scheduler
-R, --routines： #备份所有存储过程和自定义函数
--triggers： #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=*]： #此选项须启用二进制日志
*=1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
*=2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，生产不使用
-d, --no-data #只备份表结构,不备份数据
-t, --no-create-info #只备份数据,不备份表结构,即create table
-n, --no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度
--------------------------------------------------------
# InnoDB建议使用的备份参数
mysqldump –uroot -p密码 –A –F –E –R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
# MyISAM建议使用的备份参数
mysqldump –uroot -p密码 –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql
--------------------------------------------------------
单表备份
"单表备份"
-------------------------------------------------------------------
# 单表备份
mysqldump -u账号 -p密码 -S [MySQL的mysql.sock文件] [数据库名] [表名] > test.sql
# 还原方法一
mysql -u账号 -p密码 [表对应的数据库名] < test.sql
# 还原方法二（建议使用这种方法）
mysql -u账号 -p密码 # 兼容到数据库执行下面命令
mysql> set sql_log_bin=0;
mysql> source /root/test.sql ; #注意 /root/test.sql 表示mysqldump导出文件存放的绝对路径
mysql> set sql_log_bin=1;
# 恢复时最好先在配置文件中加入 skip_networking=on 禁止用户在写入数据，等数据修复好了在打开
# 注意一般在恢复时都要先关闭二进制日志的记录，等恢复完数据在打开二进制日志的记录
-------------------------------------------------------------------
数据库备份
"数据库备份"
-------------------------------------------------------------------
# 把两个数据库备份备份到一起
mysqldump -u账号 -p密码 -S [MySQL的mysql.sock文件] -B [数据库名1] [数据库名2] > test_B.sql
####分库备份直接 -B 跟指定数据库名就行
# 还原方法一
mysql -u账号 -p密码 < test_B.sql
# 还原方法二（建议使用这种方法）
mysql -u账号 -p密码 # 兼容到数据库执行下面命令
mysql> set sql_log_bin=0;
mysql> source /root/test_B.sql ; #注意 /root/test_B.sql 表示mysqldump导出文件存放的绝对路径
mysql> set sql_log_bin=1;
-------------------------------------------------------------------
"全备份 及把所有数据库进行备份适用于数据量小场景"
# 注意全量不上真正意义的全量 information_schema和performance_schema这两个表不会备份
-------------------------------------------------------------------
mysqldump -u账号 -p密码 -S [MySQL的mysql.sock文件] -A > test_A.sql
# 还原方法一
mysql -u账号 -p密码 < test_B.sql
# 还原方法二（建议使用这种方法）
mysql -u账号 -p密码 # 兼容到数据库执行下面命令
mysql> set sql_log_bin=0;
mysql> source /root/test_A.sql ; #注意 /root/test_B.sql 表示mysqldump导出文件存放的绝对路径
mysql> set sql_log_bin=1;
-------------------------------------------------------------------
通过脚本实现所有数据库的备份
1）准备数据
# 准备两个数据库db1 db2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# db1和db2的表数据为
mysql> select * from db2.student2 ;
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | wangmaz | 31 | M |
+----+---------+------+--------+
mysql> select * from db2.student2 ;
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | wangmaz | 31 | M |
+----+---------+------+--------+
2）编写脚本
[root@expr01 ~]# cat mysql_backup.sh
#!/bin/bash
# ------------------------------------------
# Filename: mysql_backup.sh
# Version: 1.1
# Date: 2023/12/10
# Author: tanghao
# Email: 2826588067@qq.com
# Description: This is the script
# Copyright: 2023 tanghao
# License: GPL
# ------------------------------------------
# 备份目录
bakDir=/backup
PASSWD=123456
test ! -d $bakDir && mkdir -p $bakDir
database_num=$(mysql -uroot -p$PASSWD -e 'show databases' 2>/dev/null | grep -Ev 'Database|information_schema|performance_schema|sys')
for db in $database_num ;do
mysqldump -uroot -p$PASSWD -S /apps/mysql/data/mysql.sock -B $db > /backup/${db}_`date +%F`.sql 2>/dev/null
done
3）执行脚本实现备份
[root@expr01 ~]# bash mysql_backup.sh
[root@expr01 ~]# ls /backup/
db1_2023-12-10.sql db2_2023-12-10.sql
4）人为删除db1和db2数据库
[root@expr01 ~]# mysql -uroot -p123456 -e "drop database db1"
[root@expr01 ~]# mysql -uroot -p123456 -e "drop database db2"
[root@expr01 ~]# mysql -uroot -p123456 -e "show databases "
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5）恢复db1和db2数据
[root@expr01 ~]# mysql -uroot -p123456
# 临时关闭二进制记录
mysql> set sql_log_bin=0;
# 恢复数据
mysql> source /backup/db1_2023-12-10.sql
mysql> source /backup/db2_2023-12-10.sql
# 开启二进制记录
mysql> set sql_log_bin=1;
---------------------------------------------------------------------
#也可以这样恢复但是不建议!!!
[root@expr01 ~]# mysql -uroot -p123456 < /backup/db1_2023-12-10.sql
[root@expr01 ~]# mysql -uroot -p123456 < /backup/db2_2023-12-10.sql
---------------------------------------------------------------------
6）验证恢复的数据
# 验证db1和db2数据库恢复情况
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
# 验证db1和db2数据库的表数据恢复情况
mysql> select * from db1.student ;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xiaoming | 20 | M |
| 11 | wangwu | 21 | M |
+----+----------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from db2.student2 ;
+----+---------+------+--------+
| id | name | age | gender |
+----+---------+------+--------+
| 1 | wangmaz | 31 | M |
+----+---------+------+--------+
1 row in set (0.00 sec)
补充：备份压缩方法
# 压缩备份
mysqldump u账号 -p密码 -S [MySQL的mysql.sock文件] -B [数据库名1] [数据库名2] | gzip > test_B.sql
# 解压
gzip -d db1_B.sql.gz
以上就是通过脚本实现分库热备份的方法，注意你们的sock文件存放位置
文章评论