mysql8 mysqldump

1、创建备份用户

create user backup@'%' identified with mysql_native_password by '123456';
grant select,reload,process,lock tables,replication client,replication_slave_admin,show view,show_routine,trigger,event,file on *.* to backup@'%';
flush privileges;


select:			备份表数据
show view:   	备份试图
trigger:        备份触发器
lock tables:    没有使用--single-transaction
process:		没有使用 --no-tablespaces
reload：		备份前刷新mysql日志文件
super、replication_client：使用--master-data记录点位信息，需要执行show master status
super、replication_slave_admin: 使用了--dump-slave （要执行 stop slave sql_thread）
show_routine:     备份存储过程#8.0.20 新增
event： event事件
file:   导出文本文件

2、备分举例及参数注释

--只备份表结构
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF --databases mydb --no-data >mydb_desc_`date +%Y%m%d`.sql


--只备份表结构，不生成drop table，以免误操作
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF --skip-add-drop-table --databases mydb --no-data >mydb_desc_`date +%Y%m%d`.sql

--只备份数据
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 -q --set-gtid-purged=OFF --databases mydb --no-create-info >mydb_data_`date +%Y%m%d`.sql


--将某个表导出为insert语句
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF --no-create-info --complete-insert --skip-extended-insert  mydb t1 >mydb_t1_`date +%Y%m%d`.sql

--备份某个数据库的多张表
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF -q --single-transaction --hex-blob --triggers --routines --events mydb  t1 t2 t3 >mydb_`date +%Y%m%d`.sql

--备份某个数据库
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF  -q --single-transaction  --hex-blob --triggers --routines --events --databases  test >test_`date +%Y%m%d`.sql

********************************************
--新建测试库
mysql> create database test;
mysql> use test
mysql> create table t1 (id bigint not null auto_increment primary key,row_create_time datetime default current_timestamp,row_lastupdate_time datetime default current_timestamp) engine=innodb default charset=utf8mb4 collate = utf8mb4_bin;
mysql> insert into t1 (id) select null;
mysql> insert into t1 (id) select null;

--查看备份test数据库信息
[root@mysql1 ~]# egrep -v '^$|^--|^/' test_20221211.sql 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `test`;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `row_create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `row_lastupdate_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1,'2022-12-11 20:31:24','2022-12-11 20:31:24'),(2,'2022-12-11 20:31:25','2022-12-11 20:31:25');
UNLOCK TABLES;


********************************************

--备份所有数据库，8.0.23之后使用--source-data=2，之前使用--master-data=2
mysqldump -ubackup -p123456 -h192.168.100.82 -P3306 -q --single-transaction --set-gtid-purged=on --all-databases --hex-blob --triggers --routines --events --source-data=2 >alldata_`date +%Y%m%d`.sql 


--备份数据库排除某张表
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF  -q --single-transaction   --hex-blob --triggers --routines --events  --databases mydb  --ignore-table=mydb.t1  --ignore-table=mydb.t2  >mydb_`date +%Y%m%d`.sql 


--备份多个数据库
mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF  -q --single-transaction  --hex-blob --triggers --routines --events --databases  mydb1 mydb2 mydb3  >mydb1_2_3_`date +%Y%m%d`.sql 

----hex-blob

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB--where条件备份

mysqldump -ubackup -p123456 -h192.168.100.81 -P3306 --set-gtid-purged=OFF  -q --single-transaction --no-create-info --complete-insert --skip-extended-insert  mydb t1  --where="id=1"  >mydb_t1_`date +%Y%m%d`.sql 


********************************************
[root@mysql2 ~]# egrep -v '^--|^/|^$' mydb_t1_20221211.sql 
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` (`id`, `row_create_time`, `row_lastupdate_time`) VALUES (1,'2022-12-09 22:29:00','2022-12-09 22:29:00');
UNLOCK TABLES;

********************************************


-u:			指定连接mysql的用户名
-p:			指定连接mysql的密码
-h:			指定mysql服务器地址
-P:			指定mysql端口  
--databases： 指定备份的数据库
--no-data：   只备份标结构，不备份数据
--no-create-info：  只备份数据，不备份标结构
--skip-add-drop-table：不添加drop table语句
-q: 不要缓冲查询，直接转储到stdout，大数据量加速备份
--single-transaction: 将事务隔离级别设置为RR，并在备份数据之前向服务器发送SQL语句，显示开启一个事务快照
--hex-blob: 备份 BINARY, VARBINARY, BLOB 以十六进制导出
--triggers: 备份trigger 
--routines：备份存储过程 
--events：  导出数据库内的计划事件（定时任务）
--ignore-table=mydb.t1: 备份数据库的时候忽略某张表
--where： where条件备份
--flush-logs    Flush logs file in server before starting dump

--dump-replica=2|--dump-slave=2: 在从库做一个完整备份，用于主库的新从库
--apply-replica-statements: 在备份文件中增加：开头：stop slave; 末尾：start slave;
--include-source-host-port：记录MASTER_HOST，MASTER_PORT 

********************************************
mysqldump -ubackup -p123456 -h192.168.100.83 -P3306 -q --single-transaction --set-gtid-purged=on --all-databases --hex-blob --triggers --routines --events --dump-replica=2 --include-source-host-port >alldata_`date +%Y%m%d`.sql 

********************************************



--set-gtid-purged=OFF: 备份部分数据是需要设置，导入主库时需要设置，不会在备份文件中记录SET @@GLOBAL.GTID_PURGED的值,不需要reset master可直接导入
--set-gtid-purged=ON:  全库备份需要设置
--set-gtid-purged=AUTO: 默认是auto模式
--set-gtid-purged=commented: commented模式，开启gtid模式，如果gtid_purged不为空，在备份内容中以注释的形式增加 SET @@GLOBAL.GTID_PURGED，同时在备份内容中增加SET @@SESSION.SQL_LOG_BIN= 0;

********************************************
SET @@SESSION.SQL_LOG_BIN= 0;
/* SET @@GLOBAL.GTID_PURGED='+0ea02cd8-787f-11ed-9873-000c29c85cd0:1-19';*/
********************************************




----set-gtid-purged 参数设置对SQL_LOG_BIN=0的影响
--set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.
--set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.
--set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you are backing up (that is, if AUTO evaluates to ON).
--set-gtid-purged=commented SET @@SESSION.SQL_LOG_BIN=0; is added to the output.


--数据恢复
3、新建恢复账号

mysql> create user apply_log@'%' identified by '123456';
mysql> grant super,system_variables_admin,drop,create,insert,alter,file on *.* to apply_log@'%';
mysql> flush privileges;
mysql> exit


4、导入数据

--直接导入
mysqldump -uapply_log -p123456 -h192.168.100.83 -P3306 mydb <mydb.sql 

--source导入
mysqldump -uapply_log -p123456 -h192.168.100.83 -P3306
use mydb
source mydb.sql;