浙江省一建建设集团网站首页,免费申请网站空间和域名,wordpress 仿百度模板,兰州网站外包这片博文主要用来介绍MySQL的备份与恢复#xff1a; MySQL的备份形式可以分为如下几种#xff1a; 热备----即不停机备份冷备----需要关闭MySQL#xff0c;然后备份其数据文件。#xff08;停机备份一般是直接拷贝其datadir目录#xff09;温备----在线备份#xff0c;对… 这片博文主要用来介绍MySQL的备份与恢复 MySQL的备份形式可以分为如下几种 热备----即不停机备份冷备----需要关闭MySQL然后备份其数据文件。停机备份一般是直接拷贝其datadir目录温备----在线备份对应用影响大通常加一个读锁【会阻塞写的应用】意义不大基本不用。从导出的备份文件结构可分为如下几种 逻辑备份---备份的数据是导出的SQL语句如mysqldump, mysqlpump【MySQL5.7加入的】,mydumper物理备份--备份的是物理文件如xtracebackup接下来会详细介绍这四种命令的通常用法如果不特别说明数据库存储引擎为INNODB mysqldump备份与恢复 mysqldump的用法如下 [roottest3 ~]# mysqldump
Usage: mysqldump [OPTIONS] database [tables] #备份单个库
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] #备份多个库
OR mysqldump [OPTIONS] --all-databases [OPTIONS] #备份所有的数据库#可以使用mysqldump --help查看mysqldump的更多参数会在下面使用的时候介绍到经常用的参数。 备份单张表 [roottest3 ~]# mysqldump -uroot -p123456 --single-transaction employees departments dep.sql#备份INNODB存储引擎时建议加上参数--single-transaction【实际上是必须加】这样会保证数据的一致性。查看一下备份出来的数据[roottest3 ~]# cat dep.sql #可以看到基本就是SQL语句-- MySQL dump 10.13 Distrib 5.7.22, for linux-glibc2.12 (x86_64)---- Host: localhost Database: employees-- -------------------------------------------------------- Server version 5.7.22-log/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT */;/*!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS */;/*!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET OLD_TIME_ZONETIME_ZONE */;/*!40103 SET TIME_ZONE00:00 */;/*!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 */;/*!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 */;/*!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO */;/*!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 */;---- Table structure for table departments--DROP TABLE IF EXISTS departments; #看到这里发现了建表语句也就是没有建库语句因此若想把sql语句导入指定库需要先创建库/*!40101 SET saved_cs_client character_set_client */;/*!40101 SET character_set_client utf8 */;CREATE TABLE departments ( dept_no char(4) NOT NULL, dept_name varchar(40) NOT NULL, PRIMARY KEY (dept_no), UNIQUE KEY dept_name (dept_name)) ENGINEInnoDB DEFAULT CHARSETlatin1;/*!40101 SET character_set_client saved_cs_client */;---- Dumping data for table departments--LOCK TABLES departments WRITE;/*!40000 ALTER TABLE departments DISABLE KEYS */; #插入语句INSERT INTO departments VALUES (d009,Customer Service),(d005,Development),(d002,Finance),(d003,Human Resources),(d001,Marketing),(d004,Production),(d006,Quality Management),(d008,Research),(d007,Sales);/*!40000 ALTER TABLE departments ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONEOLD_TIME_ZONE */;/*!40101 SET SQL_MODEOLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTESOLD_SQL_NOTES */;-- Dump completed on 2018-11-28 9:51:01在备份文件中插入二进制日志的信息该选项将binlog的位置和文件名追加到输出文件中。如果为1将会输出CHANGE MASTER 命令如果为2输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项除非--single-transaction也被指定在这种情况下全局读锁在开始导出时获得很短的时间。该选项自动关闭--lock-tables选项。[roottest3 mysql]# mysqldump -uroot -p123456 --single-transaction --master-data employees departments dep.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[roottest3 mysql]# cat dep.sql -- MySQL dump 10.13 Distrib 5.7.22, for linux-glibc2.12 (x86_64)---- Host: localhost Database: employees-- -------------------------------------------------------- Server version 5.7.22-log......---- Position to start replication or point-in-time recovery from #这里有个提示基于这个点开始复制或者PIT恢复--CHANGE MASTER TO MASTER_LOG_FILEtest3-bin.000001, MASTER_LOG_POS20182;.....#mysqldump备份时常用的两个选项 --single-transaction Creates a consistent snapshot by dumping all tables in asingle transaction. Works ONLY for tables stored instorage engines which support multiversioning (currentlyonly InnoDB does); the dump is NOT guaranteed to beconsistent for other storage engines. While a--single-transaction dump is in process, to ensure avalid dump file (correct table contents and binary logposition), no other connection should use the followingstatements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolatedfrom them. Option automatically turns off --lock-tables.
在备份INNODB数据库时事实上必须加上--single-transaction参数这个参数会保证备份出的数据是一致的也就是备份的数据是当前执行此命令时刻点的数据。
--single-transaction利用INNODB的MVCC特性在备份数据会根据undo和redo得到一份快照数据。INNODB的MVCC特性在RR和RC的隔离级别下得到的快照数据是不同在RR
得到的是当前事务开始时的快照数据在RC时得到的最新的事务数据。因为数据库的正式环境一般是RC模式因此这条命令在备份开始时会设置当前会话的隔离级别为RR模式 --single-transaction 参数说明 备份单个库和备份所有的库 [roottest3 mysql]# mysqldump -uroot -p123456 --single-transaction --master-data --databases cmdb hostinfo dep.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[roottest3 mysql]# mysqldump -uroot -p123456 --single-transaction --master-data --all-databases all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#查看数据
[roottest3 mysql]# cat dep.sql #查看其中的一个数据
-- MySQL dump 10.13 Distrib 5.7.22, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: cmdb
-- ------------------------------------------------------
-- Server version 5.7.22-log
......--
-- Position to start replication or point-in-time recovery from
--CHANGE MASTER TO MASTER_LOG_FILEtest3-bin.000001, MASTER_LOG_POS20182;--
-- Current Database: cmdb #这里有了创建数据库的语句
--CREATE DATABASE /*!32312 IF NOT EXISTS*/ cmdb /*!40100 DEFAULT CHARACTER SET utf8 */;USE cmdb;#因此在使用指定库备份文件恢复数据时不需要再创建库直接恢复即可 恢复数据的时候直接使用mysql导入即可 mysql -uroot -p123456 all.sql 切记在备份INNODB数据库时一定要加上--single-transaction 参数为了基于PIT恢复也要加上--master-data参数。 mysqldump的备份过程 上面我们已经看到了mysqldump备份出的数据文件时对应sql语句我们查看一下在执行mysqldump时MySQL数据库做了哪些操作 首先开启general_log日志如下 mysql set global general_logon;
Query OK, 0 rows affected (0.00 sec)
mysql set global log_outputfile;
Query OK, 0 rows affected (0.00 sec)
mysql show variables like %general%;
-----------------------------------------
| Variable_name | Value |
-----------------------------------------
| general_log | ON |
| general_log_file | /data/mysql/test3.log |
-----------------------------------------
2 rows in set (0.00 sec) 然后再去使用mysqldump备份一次数据文件 [roottest3 ~]# mysqldump -uroot -p123456 --single-transaction --master-data --databases tpcc_test dep.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[roottest3 ~]# 最后查看general_log日志如下 [roottest3 mysql]# cat test3.log 2018-11-28T05:20:31.014426Z 29 Connect rootlocalhost on using Socket
2018-11-28T05:20:31.014662Z 29 Query /*!40100 SET SQL_MODE */
2018-11-28T05:20:31.014792Z 29 Query /*!40103 SET TIME_ZONE00:00 */
2018-11-28T05:20:31.014915Z 29 Query FLUSH /*!40101 LOCAL */ TABLES
2018-11-28T05:20:31.015141Z 29 Query FLUSH TABLES WITH READ LOCK #这里有个锁表
2018-11-28T05:20:31.015213Z 29 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ #设置当前会话的隔离级别
2018-11-28T05:20:31.015271Z 29 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ #开始事务
2018-11-28T05:20:31.015463Z 29 Query SHOW VARIABLES LIKE gtid\_mode #备份gtid信息
2018-11-28T05:20:31.018393Z 29 Query SHOW MASTER STATUS #在备份时指定master-data这里备份日志点信息
2018-11-28T05:20:31.018467Z 29 Query UNLOCK TABLES #释放表
2018-11-28T05:20:31.018618Z 29 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE UNDO LOG AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE DATAFILE AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (tpcc_test))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-11-28T05:20:31.021966Z 29 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE DATAFILE AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (tpcc_test)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-11-28T05:20:31.022750Z 29 Query SHOW VARIABLES LIKE ndbinfo\_version
2018-11-28T05:20:31.023915Z 29 Init DB tpcc_test
2018-11-28T05:20:31.023977Z 29 Query SHOW CREATE DATABASE IF NOT EXISTS tpcc_test
2018-11-28T05:20:31.024045Z 29 Query SAVEPOINT sp #备份每张表之前会设置保存点
2018-11-28T05:20:31.024100Z 29 Query show tables
2018-11-28T05:20:31.024253Z 29 Query show table status like customer
2018-11-28T05:20:31.024475Z 29 Query SET SQL_QUOTE_SHOW_CREATE1
2018-11-28T05:20:31.024541Z 29 Query SET SESSION character_set_results binary
2018-11-28T05:20:31.024607Z 29 Query show create table customer
2018-11-28T05:20:31.024737Z 29 Query SET SESSION character_set_results utf8
2018-11-28T05:20:31.024806Z 29 Query show fields from customer
2018-11-28T05:20:31.025199Z 29 Query show fields from customer
2018-11-28T05:20:31.025582Z 29 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM customer
2018-11-28T05:20:35.251932Z 29 Query SET SESSION character_set_results binary
2018-11-28T05:20:35.252069Z 29 Query use tpcc_test
2018-11-28T05:20:35.252158Z 29 Query select collation_database
2018-11-28T05:20:35.252290Z 29 Query SHOW TRIGGERS LIKE customer
2018-11-28T05:20:35.252706Z 29 Query SET SESSION character_set_results utf8
2018-11-28T05:20:35.252771Z 29 Query ROLLBACK TO SAVEPOINT sp #这张表备份结束之后回滚保存点............以上的过程可以看到mysqldump是在一个事务中备份的因此在备份表时会产生undo日志若是表数据太大则undo日志也会很大因此mysqldump在备份每张表时都设置savepoint这样当这个表备份完成之后就会回滚保存点然后purge线程就会回收undo日志 mysqld的其余常见参数应用 -d只备份指定数据库表的结构也就是建表语句 [roottest3 ~]# mysqldump -uroot -p123456 -d --databases lianxi dep.sql #这样只备份出lianxi这个库中表的建表语句和创建库的语句[roottest3 ~]# mysqldump -uroot -p123456 -d lianxi tb2 dep.sql #如果这里只备份库下面的表则还是没有建库语句只有创建表的语句 -R, --routines 备份时导出存储过程和自定义函数 [roottest3 ~]# mysqldump -uroot -p123456 -R lianxi tb2 dep.sql -f, --force Continue even if we get an SQL error.。发生错误的时候也继续备份。 mysqldump还有很多参数可以在用到的时候查看 mysqldump是逻辑备份单线程备份单线程恢复因此会比较慢。特别是恢复的时候之前恢复过一个800多万行记录的数据结果使用mysql逐条导入sql语句搞库几个小时。 备份与恢复纠错 1在使用mysql导入mysqldump的备份数据时报了如下错误 [roottest3 ~]# mysql -uroot -p123456 financesys financesys.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1118 (42000) at line 25: Row size too large ( 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. 错误原因就是行太长了但是线上环境修改字段属性有点不太合适可以采用如下方法 mysql set global innodb_strict_modeoff;
Query OK, 0 rows affected (0.00 sec)
[roottest3 ~]# mysql -uroot -p123456 financesys financesys.sql #再导入就可以了
mysql: [Warning] Using a password on the command line interface can be insecure. 从MySQL5.5.X版本开始你可以开启InnoDB严格检查模式尤其采用了页数据压缩功能后最好是开启该功能。开启此功能后当创建表CREATE TABLE、更改表ALTER TABLE和创建索引CREATE INDEX语句时如果写法有错误不会有警告信息而是直接抛出错误这样就可直接将问题扼杀在摇篮里。开启InnoDB严格检查模式涉及的参数是innodb_strict_mode默认为OFF支持动态开启开启方式如下
set global innodb_strict_mode1;连接地址 导入成功后如果是线上环境还需要把参数修改回去 2在使用mysqldump备份的时候出现如下问题 [roottest2 data]# mysqldump -uroot -p7abec53701c3eefb --databases financesys financesys1.sql
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table fi_factincome at row: 1303198 我查了一下这个表大概有1500万行的记录报错原因如下 备份失败的原因在向磁盘上备份的时候数据的流向是这样的MySQL Server 端从数据文件中检索出数据然后分批将数据返回给mysqldump 客户端然后 mysqldump 将数据写入到磁盘上。一般地向 磁盘 上写入数据的速度较之Server端检索发送数据的速度要慢得多这就会导致 mysqldump 无法及时的接受 Server 端发送过来的数据Server 端的数据就会积压在内存中等待发送这个等待不是无限期的当 Server 的等待时间超过 net_write_timeout默认是60秒时它就失去了耐心mysqldump 的连接会被断开同时抛出错误 Got error: 2013: Lost connection。其实该错误不是说数据库文件太多而导致出错而是单张表数据量太大导致备份失败 问题的解决方案增加 net_write_timeout 可以解决上述的问题的。在实践中发现在增大 net_write_timeout 后Server 端会消耗更多的内存有时甚至会导致 swap 的使用并不确定是不是修改 net_write_timeout 所至。建议在mysqldump 之前修改 net_write_timeout 为一个较大的值如1800在 mysqldump 结束后在将这个值修改到默认的60。备注net_write_timeout不是mysqldump的配置参数而是mysql的参数 转载于:https://www.cnblogs.com/wxzhe/p/10032153.html
