☘️博主介绍☘️:
✨又是一天没白过,我是奈斯,DBA一名✨
✌✌️擅长Oracle、MySQL、SQLserver、阿里云AnalyticDB for MySQL(分布式数据仓库)、Linux,也在扩展大数据方向的知识面✌✌️
❣️❣️❣️大佬们都喜欢静静的看文章,并且也会默默的点赞收藏加关注❣️❣️❣️
清明小长假已经画上句号。是时候为我们的学习生活注入新的活力与规划了。今天给大家详细介绍一下mysqldump逻辑迁移工具,它能够将数据库的内容以 SQL语句的形式导出为文件 ,为数据库的迁移、备份和恢复工作提供了极大的便利。
因此,无论是数据库管理员还是数据库开发者,掌握mysqldump逻辑迁移工具的使用方法都是非常重要的。在接下来的文章中,我将详细介绍mysqldump工具的基本概念、使用方法以及应用场景,希望能够为大家在数据库管理工作中提供有益的参考和帮助。
除了mysqldump逻辑迁移工具,在mysql 5.7.8版本之后还引入了mysqlpump,相对于mysqldump而言,mysqlpump支持 并行导出 、 压缩导出 等优势,在效率上要比mysqldump要高很多。mysql逻辑迁移工具的介绍和案例,我会分成四篇内容内容进行讲解,四篇的内容分别如下:
- 第一篇:一文搞清mysqldump逻辑迁移工具的用法和定时全备实例(当前篇)
- 第二篇:一文搞清mysqlpump逻辑迁移工具的用法和定时全备实例
- 第三篇:mysqlpump和mysqldump参数区别总汇
- 第四篇:使用mysqldump全量+mysqlbinlog增量完成实例的全库恢复
目录
案例4:导出指定表(使用--tables参数跟库名表名,表名之间空格隔开)
5、linux系统上制定mysqldump的定时全库备份和binlog日志的定时备份
mysqldump工具在数据库逻辑迁移和备份中发挥着重要的作用,但是没有十全十美的工具,每个工具都有它的优点和缺点。
优点:
1.简单易用:mysqldump是一个命令行工具,使用起来相对简单直观,不需要额外的图形界面或复杂的配置。
2.数据备份:mysqldump可以方便地将整个数据库或特定表的数据导出到一个文件中,用于备份和迁移数据。
3.灵活性:mysqldump提供了各种选项和参数,可以 按需备份数据库 的结构和数据,包括表结构、数据、触发器、存储过程等。
4.跨平台支持:mysqldump适用于多个操作系统,包括Windows、Linux和Mac等,可以在不同的环境中使用。
缺点:
1.文件大小限制:由于mysqldump生成的备份文件是文本格式的,因此对于大型数据库,备份文件的大小可能会很大,可能会遇到文件大小限制或存储问题。2.数据恢复缓慢:mysqldump的恢复采用mysql命令进行恢复,对于大型数据库、高频率备份和快速恢复等需求不太合适。因为mysqldump会生成包含SQL语句的文本文件,而这些 SQL 语句在恢复时需要被MySQL逐条执行,这个过程可能会很慢,特别是对于非常大的数据库。
官方文档对mysqldump的介绍(8.0版本):
MySQL :: MySQL 8.0 Reference Manual :: Search Results
1、mysqldump导出语法:
mysqldump --help选项:
参数选项 | 描述 |
-u, --user=name | 用于指定执行导出操作的用户名 |
-p, --password[=name] | 用于指定执行导出操作的用户名密码 |
-h, --host=name | 指定连接的主机IP |
-P, --port=# | 大写P,用于指定端口,默认3306 |
> | 用于指定导出的文件名 |
--compatible=name | 导入兼容,用于不同数据库的数据迁移。如:--compatible=(oracle/mssql/postgresql) |
-A, --all-databases | 导出所有数据库。对于默认数据库只导出数据库mysql,其他三个库不导出 |
-B, --databases | 导出指定的数据库 |
--log-error=name | 输出导入时的错误日志 |
-f, --force | 忽略报错强制性导出 |
--set-charset | 是否开启字符集,--set-charset=1|0(默认开启) |
--default-character-set=name | 指定字符集。如:utf8、gbk、utf8mb4 |
-R, --routines | 导出函数、存储过程(默认不导出) |
--triggers | 导出触发器(默认导出) |
-E, --events | 导出调度事件(默认不导出) |
--flush-privileges | 刷新权限FLUSH PRIVILEGES(赋权之后需要刷新权限不然重启失效) |
-F, --flush-logs | 导出之前切换二进制日志,达到一致性导出(默认不进行切换) |
-e, --extended-insert(--skip-extended-insert) | 是否开启insert插入包含多个值,默认为true。True:一个很长的insert语句;false:每行一个insert语句。虽然mysqldump默认是一个很长的insert语句,但也是有限度的,官方文档并没找到一个insert包含多少个值,测试也没有得出结论(mysqlpump不支持true和false参数,但关闭insert插入可以使用--extended-insert=1实现,默认一个insert包含250个值) |
--add-drop-database | 在CREATE DATABASE前DROP DATABASE。如果mysql中有对应导入的数据库那么导入会失败,加上参数后,删除后添加(默认关闭) |
--add-drop-table | 导出时在CREATE TABLE前DROP TABLE IF EXISTS。如果导入mysql中有对应导入的表那么导入会失败,加上参数后,删除后添加(默认打开) |
--skip-add-drop-table | 跳过创建之前删除存在的表,如果目标表中有这个表并且有数据,那么会先删除表,是万万不允许的,所以一定要--skip-add-drop-table。类似oracle的ignore的追加数据,加上这个参数在导入时不会先删除存在的表,配合--force直接追加数据 |
--add-drop-trigger | 在CREATE TRIGGER 前DROP TRIGGER(默认关闭) |
-N, --no-set-names | 与--skip-set-charset参数相同 |
-d, --no-data | 不导出表中的数据,只导出结构 |
--ignore-table=name | 不导出某个表。如--ignore-table=test.tb01,不导出test数据库的tb01表 |
-n, --no-create-db | 不导出CREATE DATABASE创建数据库结构 |
-t, --no-create-info | 不导出CREATE TABLE创建表结构 |
-w, --where=name | 指定导出表的前多少行(从where子句后开始) |
--master-data[=#] | master-data选项的作用就是将备份时二进制写入的文件和position点信息输出到sql文件中,使用时需要打开bin log二进制文件,不然导出报Binlogging on server not active。有三个值: 0:不写入bin log日志记录(默认值) 1:change master to .... 记录 binlog 文件及终点 2:#change master to .... 记录 binlog 文件及终点(将1注释) |
--add-locks | 用LOCK TABLES和UNLOCK TABLES语句包围每个表转储(默认打开) |
--skip-add-locks | 跳过对对象的加锁。mysqldump导出时先LOCK TABLES `table_name` WRITE,然后UNLOCK TABLES |
事务一致性和锁参数。三个参数互斥。只能用一个 | |
-l, --lock-tables (--skip-lock-tables) | 导出锁表,导一个锁一个,导完解锁(默认打开) (跳过锁表。Mysqldump在导出时导一个锁一个表,此参数在备份时不锁表进行备份) |
-x, --lock-all-tables | 导出时锁定所有数据库中的所有表,导完解锁(默认关闭) |
--single-transaction | 开启InnoDB表的一致性快照备份,可以不锁表,保证数据备份一致性的参数,只针对innodb导出过程中不允许运行表的DDL操作。因为事务持有表的metadata lock的共享锁,而DDL会申请metadata lock的互斥锁,所以会阻塞。--single-transaction关掉默认--lock-tables选项(即不加锁),因为mysqldump默认会打开一个lock-tables在导出过程中锁住所有的表。 |
和主从复制相关参数 | |
--dump-slave[=#] | 在从库上面使用,和--master-data参数相同,为了slave建立下一级的slave |
--apply-slave-statements | 和--master-data=1类似。在“CHANGE MASTER”之前添加“STOP SLAVE”,并在转储底部添加“START SLAVE”。 |
--include-master-host-port | 结合--dump-slave=1/2,在导出中加入port |
--delete-master-logs | 在备份之后,删除master的bin log日志,默认打开--master-data=2。一般不用,因为日志一般不能随便删除 |
--set-gtid-purged[=name] | 导出添加 'SET @@GLOBAL.GTID_PURGED' |
mysqldump导出案例:
案例1:全库导出
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events --all-databases --log-error=full_err.log --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > mysqldump_full.sql
###所有数据库导出时只包括mysql数据库,其他三个默认数据库是不导出的。导出时用户和权限也导出了,因为权限和用户都在mysql数据库中。
案例2:远端备份(异机上直接运行,备份到异机本地)
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events --all-databases --log-error=full_err.log --skip-add-locks --skip-add-drop-table -hip地址 -P端口 > mysqldump_full.sql
###所有数据库导出时只包括mysql数据库,其他三个默认数据库是不导出的。导出时用户和权限也导出了,因为权限和用户都在mysql数据库中。
案例3:指定数据库导出
备份指定数据库分为两步:第一步备份指定数据库,第二步导出默认数据库mysql中的权限和用户或者也同时导出mysql数据库就不需要单独写脚本导入权限和用户(因为权限和用户都在mysql数据库中):
第一步:备份指定数据库
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --routines --events --databases db1 db2 --log-error=full_err.log --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > mysqldump_db1.sql ###导出db1、db2数据库
第二步:导出默认数据库mysql中的权限和用户
[root@mysql ~]# mysql_exp_grants() { mysql -B -uroot -p密码 -N -P端口 -hip地址 $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \ mysql -uroot -p密码 -N -P端口 -hip地址 -f $@ | \ sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' mysql -B -uroot -p密码 -N -P端口 -hip地址 $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \ mysql -uroot -p密码 -N -P端口 -hip地址 -f $@ | \ sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}' } mysql_exp_grants > mysql_exp_grants_out.sql ---输出所有数据库中用户和权限语句
案例4:导出指定表(使用--tables参数跟库名表名,表名之间空格隔开)
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --routines --events --log-error=full_err.log --tables test tb1 tb2 --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > mysqldump_tb1.sql
###导出test库tb1和tb2
案例5:导出指定表的某个列前10 ID
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --routines --events --log-error=full_err.log --tables test tb1 --where="id<10" --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > mysqldump_tb1.sql
###导出test库tb1表的id字段前10行
2、mysqldump导入语法:
小提示:
mysqldump导入很鸡肋真的让人有些哭笑不得,就是使用mysql命令进行导入的,所以 没有办法看到每张表的导入情况 ,也没有办法指定某个表、某个库的导入,导入一个mysqldump导出的sql文件,然后它默默地在那边工作,你除了等待和祈祷,只能 看看后台这个导入进程还是否存在 ,几乎什么都做不了。虽然
mysqldump
导入有些“鸡肋”,但我们也不要太过苛求。毕竟它为我们提供了基本的数据库备份和恢复功能,这已经足够我们在很多场景下使用了。如果真的需要更高级的功能,那也不妨考虑一下那些收费的商业软件,它们或许能给你带来更好的体验。
mysql --help选项:
参数选项 | 描述 |
< | 用于指定导入的文件名 |
-u, --user=name | 用于指定执行导入操作的用户名 |
-p, --password[=name] | 用于指定执行导入操作的用户名密码 |
-h, --host=name | 指定连接的主机IP |
-P, --port=# | 大写P,用于指定端口,默认3306 |
-o, --one-database | 指定连接的数据库(直接就连接进去了) |
-f, --force | 强制继续,即使我们得到一个SQL错误。如果表存在(ERROR 1050 (42S01) at line 24: Table 'qwe' already exists)强制继续,提示的话忽略,因为强制了 |
--default-character-set=name | 指定导入数据时的默认字符集编码(utf8、utf8mb4)。出现导入数据时的默认编码(utf8mb4)与导出文件的默认编码(utf8)不一致,就会报错ERROR at line 1474: Unknown command '\Z'.ERROR at line 1474: Unknown command '\?,那么就需要指定导入数据时的默认字符集编码,通过show variables like '%character_set%'; 确定 |
mysql导入案例:
案例1:全备数据库导入
小提示:
前提是进行了mysqldump全备
[root@mysql ~]# mysql -uroot -p123456 --force < mysqldump_full.sql
###全备导出时用户和权限也导出了(因为权限和用户都在mysql数据库中,备份的文件包括mysql数据库,所以导入mysql库会引起冲突,使用--force:强制继续)
案例2:指定数据库导入
小提示:
前提是进行了某个数据库的mysqldump备份。如果是全备,那么就是全库导入了,而不是指定数据库导入,因为导入时不能指定数据库导入
导入指定数据库分为两步:第一步导入备份文件,第二步导入默认数据库mysql中的权限和用户(因为权限和用户都在mysql数据库中):
第一步:导入备份文件
[root@mysql ~]# mysql -uroot -p123456 < mysqldump_db.sql ###导入备份的指定数据库的导出数据文件
第二步:导出默认数据库mysql中的权限和用户
[root@mgr1 ~]# mysql -uroot -p123456 mysql> source mysql_exp_grants_out.sql
案例3:导入指定表
小提示:
前提是进行了某个表的mysqldump备份,如果是全备,那么就是全库导入了,而不是指定表的导入,因为导入时不能指定表导入
[root@mysql ~]# mysql -uroot -p123456 -o database_name < mysqldump_tb.sql
注意:mysqldump导出某张表时,不会有create database和use database,创建表时不会写成schema.table_name;insert不会写成schema.table_name。
如果导入同实例不同的库,需要先create database新的库名,然后运行mysql -uroot -p123456 -o database_name < mysqldump_tb.sql(-o参数就是直接连接到这个库中,然后在这个库下运行sql文件)
3、mysqldump深入解析与实现原理
前提条件:必须打开数据库的general log,在my.cnf设置下列参数,重启mysql生效:
[root@mysql ~]# vi /mysql/data/3306/my.cnf
general_log = 1
general_log_file=/mysql/log/3306/general.err
mysql> show variables like '%general%';
[root@mysql ~]# mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events --all-databases --log-error=full_err.log --skip-add-locks --skip-add-drop-table --socket=/mysql/data/3306/mysql.sock > mysqldump_full.sql
###全库导出。所有数据库导出只包括mysql数据库,其他三个默认数据库是不导出的。所以只导出mysql和test库。
[root@mysql1 ~]# tail -200f /mysql/log/3306/general.err ---查看常规日志
2020-07-02T08:21:31.671373Z 5 Connect root@localhost on using Socket ---使用root用户连接mysql数据库
2020-07-02T08:21:31.671588Z 5 Query /*!40100 SET @@SQL_MODE='' */ ---设置sql模式
2020-07-02T08:21:31.672033Z 5 Query /*!40103 SET TIME_ZONE='+00:00' */ ---设置时区
2020-07-02T08:21:31.672466Z 5 Query FLUSH /*!40101 LOCAL */ TABLES ---关闭打开的表,并且刷新查询缓存 (closes all open tables,forces all table in use to be closed,and flushes the query cache). 如果是myisam存储引擎:将脏数据刷到文件,同时关闭文件描述符,关闭文件。
如果是innodb存储引擎:并不会真正的关闭文件描述符,同时也不会写脏数据,所以这个功能在innodb中用处不大
2020-07-02T08:21:31.692708Z 5 Query FLUSH TABLES WITH READ LOCK ---简称FTWRL:执行FLUSH tables操作,会加一个全局读锁,主要还是获取一致性备份。 主要是避免比较长的事务没有关闭,会导致 FLUSH tables with read lock 操作一直得不到锁,就会阻塞其它客户端的操作。
FTWRL:一般需要持有两把全局的 MDL 锁(metadata lock),而且还需要关闭所有的表对象。
FTWRL 主要包括 3 个步骤: A.上全局读锁 B.清理表缓存 C.上全局commit锁
2020-07-02T08:21:31.707327Z 5 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ---设置当前的事务隔离级别为可重复读,避免不可重复读和幻读
2020-07-02T08:21:31.707507Z 5 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ ---获取当前数据库的一个快照,由--single-transaction决定。只适合支持事务的表,就是innodb引擎。主要是在开启事务的时候,对所有的表做一次select操作,得到一个快照,备份时就可以一致。
案例说明:
start transaction:别人插入数据,本会话也能看见,出现备份不一致。
start transaction with consistent snapshot :当前会话,对之前的数据可见,对后面的新数据不可见(比如10点开始备份,12点结束,备份期间插入的数据不会进行备份)
2020-07-02T08:21:31.710738Z 5 Query SHOW MASTER STATUS ---这个由--master-data 参数决定,记录了备份时,binlog的状态信息,包括 binlog file和log position.
2020-07-02T08:21:31.711325Z 5 Query UNLOCK TABLES ---释放锁。、
2020-07-02T08:21:31.714738Z 5 Query SHOW DATABASES ---查看要备份哪些数据库。所的有备份,但不包括information_schema、sys、performance_schema数据库
.........
2020-07-02T08:21:31.719087Z 5 Query SHOW CREATE DATABASE IF NOT EXISTS `itpuxdb` ---显示创建数据库语句,备份结构
2020-07-02T08:21:31.721349Z 5 Query show create table `bm` ---显示创建表语句,备份结构
2020-07-02T08:21:31.723479Z 5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `bm` ---查询表的数据,然后备份数据
2020-07-02T08:21:31.724814Z 5 Query SHOW TRIGGERS LIKE 'bm' ---显示创建触发器语句,然后备份触发器
2020-07-02T08:19:10.977883Z 3 Query SHOW FUNCTION STATUS WHERE Db = 'itpuxdb' ---显示创建函数语句,然后备份函数
2020-07-02T08:19:11.033393Z 3 Query SHOW PROCEDURE STATUS WHERE Db = 'itpuxdb' ---显示创建过程语句,然后备份过程
.........
2020-07-02T08:21:31.719330Z 5 Query SAVEPOINT sp ---设置保存点
2020-07-02T08:19:11.530988Z 3 Query ROLLBACK TO SAVEPOINT sp ---回滚到保存点
2020-07-02T08:19:11.531019Z 3 Query RELEASE SAVEPOINT sp ---释放保存点。设置savepointh点,然后备份完了后再回滚到savepoint;
这样做的好处,不会阻塞在备份期间对已经备份完的表的 DDL 操作。主要是提高 DDL 的并发性。
4、mysqldump导出和导入常见问题
一、导入时GTID_PURGED can only be set when GTID_EXECUTED is empty
解决方式一:在导入库执行reset master清空二进制日志,因为这个报错是因为导出的库是gtid模式并且导出文件生成了gtid点,导入的库也有日志并且有gtid,导入和导出gtid是不一样,所以报错
解决方式二:在导入时加上参数--force,强制继续,提示的话忽略,因为强制了
解决方式三:在导出时设置--set-gtid-purged=OFF,不会在dmp文件有@@GLOBAL.GTID_PURGED参数
二、导入时ERROR at line 1474: Unknown command '\Z'.ERROR at line 1474: Unknown command '\?
解决办法:导入数据时的默认编码(utf8mb4)与导出文件的默认编码(utf8)不一致,就会报这个报错,那么就需要指定导入数据时的默认字符集编码,通过show variables like '%character_set%';确定。
导入语句:mysql -uroot -p123456 --default-character-set=utf8|utf8mb4 --force <导入的文件名
三、导出时有SQL_LOG_BIN(如果导入的实例下面还有从库,那么就要取消SQL_LOG_BIN):
SQL_LOG_BIN:对当前的会话是否记录到二进制日志中,如果关闭那么在这个会话的操作都不记录到二进制日志中。
mysqldump的官方文档
在mysqldump进行导出时默认带SET @MYSQLDUMP_TEMP_LOG_BIN、SET @@SESSION.SQL_LOG_BIN、SET @@GLOBAL.GTID_PURGED,如果导出不想有MYSQLDUMP_TEMP_LOG_BIN、SQL_LOG_BIN那么就要再导出时,指定--set-gtid-purged=OFF参数,不仅会去掉GTID_PURGED还会有MYSQLDUMP_TEMP_LOG_BIN、SQL_LOG_BIN(通过对比工具对比)
mysqlpump的官方文档
在mysqlpump进行导出时默认带SET @@SESSION.SQL_LOG_BIN、SET @@GLOBAL.GTID_PURGED,如果导出不想有SQL_LOG_BIN那么就要再导出时,指定--set-gtid-purged=OFF参数,官方文档表示不仅会去掉GTID_PURGED还会有SQL_LOG_BIN,但是实测这只是取消了GTID_PURGED,但是并没有取消SQL_LOG_BIN(bug)(通过对比工具对比)
5、linux系统上制定mysqldump的定时全库备份和binlog日志的定时备份
通过crontab制定备份策略,实现对mysqldump
的定时全库备份以及binlog日志的定时备份。binlog日志作为MySQL数据库的重要组成部分,记录了数据库的所有变更操作,对于数据恢复和故障排查具有重要意义。通过定时备份binlog日志,我们可以为数据库的恢复操作提供更为丰富的数据支持。
(1)数据备份的路径规划:
[root@mysql1 ~]# mkdir -p /mysql/backup/full
[root@mysql1 ~]# mkdir -p /mysql/backup/binlog
[root@mysql1 ~]# mkdir -p /mysql/backup/script
###full(mysqldump全备的数据)、script(备份脚本)、binlog(需要打开binlog日志功能)
[root@mysql1 ~]# chown -R mysql:mysql /mysql/backup/
(2)mysqldump全备脚本
[root@mysql1 ~]# cd script/
[root@mysql1 ~]# vi mysqldump_full.sh
Date=`date +%Y%m%d`
Begin=`date +"%Y-%m-%d %H:%M:%S"`
/mysql/app/mysql/bin/mysqldump -uroot -p123456 --single-transaction --master-data=2 --routines --events --flush-logs --flush-privileges --all-databases --log-error=/mysql/backup/full/full-err.log --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > /mysql/backup/full/mysqldump_full_$Date.sql ###所有数据库导出只包括mysql数据库,其他三个默认数据库是不导出的
/mysql/app/mysql/bin/mysqldump -uroot -p123456 --single-transaction --master-data=2 --flush-logs --routines --events --databases sys performance_schema information_schema --log-error=/mysql/backup/full/sys-err.log --skip-add-locks --skip-add-drop-table --socket=sock文件(多实例时需要指定。或者-hip地址 -P端口) > /mysql/backup/full/mysqldump_sys_$Date.sql ###在全备时不会对默认数据库 performance_schema、information_schema、sys这些只保存性能的数据库进行备份。如果需要单独备份
find /mysql/backup/full/ -name 'mysqldump*.sql' -mtime +2 -exec rm -rf {} \; ###---mtime +2 -exec rm -rf {} \; 表示删除目录下2天之前被修改过的文件。因为计划任务是每天执行,所以find查出2天之前的进行rm删除,也就是保留2天的2份。
###导出权限和用户脚本。mysqldump使用--all-databases时就导出mysql数据库了,因为权限都在mysql数据库中,所以就不需要单独写脚本导入权限和用户(可选)
mysql_exp_grants()
{
/mysql/app/mysql/bin/mysql -B -uroot -p123456 -N -P3306 -h192.168.56.190 $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
/mysql/app/mysql/bin/mysql -uroot -p123456 -N -P3306 -h192.168.56.190 -f $@ | \
sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'
/mysql/app/mysql/bin/mysql -B -uroot -p123456 -N -P3306 -h192.168.56.190 $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
/mysql/app/mysql/bin/mysql -uroot -p123456 -N -P3306 -h192.168.56.190 -f $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
mysql_exp_grants > /mysql/backup/full/mysql_exp_grants_out_$Date.sql
find /mysql/backup/full/ -name 'mysql_exp*.sql' -mtime +2 -exec rm -rf {} \; ---保留2份权限脚本。
(3)binlog日志增量脚本(增量:只copy上次备份时没有的二进制日志)
[root@mysql1 ~]# cd script/
[root@mysql1 ~]# vi backup-mysql-binlog.sh
BinLogDir=/mysql/log/3306/binlog ###二进制目录日志(实际路径,定义在my.cnf文件中)
BinIndexFile=/mysql/log/3306/binlog/itpuxdb-binlog.index ###二进制索引输出路径(实际文件,定义在my.cnf文件中)
BinLogBakDir=/mysql/backup/binlog ###备份二进制日志的路径
LogOutFile=/mysql/backup/binlog/bak-bin.log ###日志信息
mysqladmin -uroot -p123456 flush-logs
NextLogFile=`tail -n 1 $BinIndexFile`
LogCounter=`wc -l $BinIndexFile |awk '{print $1}'` ###统计索引二进制文件数量
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
echo "--------------------------------------------------------------------" >> $LogOutFile
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Start... >> $LogOutFile
for binfile in `cat $BinIndexFile`
do
base=`basename $binfile`
#basename用于截取mysql-bin.00000*文件名,去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $LogCounter ]
then
echo $base skip! >> $LogOutFile
else
dest=$BinLogBakDir/$base
if(test -e $dest)
#test -e用于检测目标文件是否存在,存在就写exist!到$LogFile去
then
echo $base exist! >> $LogOutFile
else
cp $BinLogDir/$base $BinLogBakDir
echo $base copying >> $LogOutFile
fi
fi
done
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Complete! Next LogFile is: $NextLogFile >> $LogOutFile
find $BinLogBakDir -mtime +30 -name "*binlog.**" -exec rm -rf {} \; ###清理30天前的备份的binlog日志
(4)在root下添加全备脚本和binlog增量脚本的自动计划任务
[root@lf script]# chmod 775 /mysql/backup/script/*.sh
[root@lf script]# crontab -e
00 02 * * * /mysql/backup/script/mysqldump_full.sh ###全备:每天晚上 2点执行全备脚本。
00 13 * * * /mysql/backup/script/backup-mysql-binlog.sh ###增量:每天 13 点备份 binlog 日志
(5)Root下测试脚本可用性,并查看日志
全备:
[root@lf script]# /mysql/backup/script/mysqldump_full.sh [root@lf logs]$ tail -2000f /mysql/backup/full/full-err.log
增量:
[root@mysql1 script]# /mysql/backup/script/backup-mysql-binlog.sh [root@mysql1 binlog]# tail -200f /mysql/backup/binlog/bak-bin.log
在发布每篇文章之前,我都会逐一验证其中的命令认真打磨,以尽可能保证内容的准确无误。这样的处理会使撰写过程耗时较长,但我认为这是为有需要的小伙伴提供可靠帮助的必经之路。因此小伙伴们的 点赞关注收藏 ,便是我持续前行的最大动力,感谢支持与鼓励!