Mysql数据库备份工具,逻辑备份
分类:数据库

       几天前收到某个业务项目,MySQL数据库逻辑备份mysqldump备份失败的邮件,本是在休假,但本着工作认真负责,7*24小时不间断运维的高尚职业情操,开始了DBA的排错之路(一开始数据库的备份都是成功的,巧的是我休假就出问题,怀疑是数据量又有增长)

【逻辑备份】深入浅出mysqldump:常用操作、案例分享、意外终止的原因以及解决方法

用下面方法解决(管理mysql用的是navicat).,设置以下几个参数的值后就正常了,以下语句也可以在mysql的控制台上执行 .

导出数据库的时候报如下错误 mysqldump: Error 2013: Lost connection to MySQL server during query when dumping tablemailat row: 2637433

MySQLStudy之--Mysql数据库备份工具(mysqldump)

MySQL Study之--Mysql数据库备份工具(mysqldump)
对于Mysql Database的备份方式有很多种,此次文档主要介绍mysqldump工具:
mysqldump:
mysqldump工具很多方面类似相反作用的工具mysqlimport。它们有一些同样的选项。但mysqldump能够做更多的事情。它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式(Schema,后面有解释)并且将其转换成DDL语法(CREATE语句,即数据库定义语句),取得所有的数据,并且从这些数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼地地方。

mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。
现在来讲一下 mysqldump 的一些主要参数:

  1. --compatible=name

它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼容。

  1. --complete-insert,-c

导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,但是可能会受到max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我不推荐。

  1. --default-character-set=charset

指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。

  1. --disable-keys

告诉 mysqldump 在 INSERT 语句的开头和结尾增加/*!40000 ALTER TABLE table DISABLE KEYS */; 和/*!40000 ALTER TABLE table ENABLE KEYS */; 语句,这能大大提高插入语句的速度,因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

  1. --extended-insert = true|false

默认情况下,mysqldump 开启 --complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的值为false 即可。

  1. --hex-blob

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

  1. --lock-all-tables,-x

在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项。

  1. --lock-tables

它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表,如果是 Innodb 表可以用 --single-transaction 选项。

  1. --no-create-info,-t

只导出数据,而不添加 CREATE TABLE 语句。

  1. --no-data,-d

不导出任何数据,只导出数据库表结构。

  1. --opt

这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option--disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用--skip-opt 禁用。注意,如果运行mysqldump 没有指定 --quick 或 --opt 选项,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

  1. --quick,-q

该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

  1. --routines,-R

导出存储过程以及自定义函数。

  1. --single-transaction

该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如InnoDB 和BDB。
本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
要想导出大表的话,应结合使用 --quick 选项。

  1. --triggers

同时导出触发器。该选项默认启用,用 --skip-triggers 禁用它。

案例1:通过mysqldump建立数据库的全备
[[email protected] ~]$cd /dat1/mybak

1、备份全部的database:

[[email protected] mybak]$mysqldump -uroot -poracle --all-databases>bak0703.sql

查看备份文件内容:
[[澳门新萄京,email protected] mybak]$more bak0703.sql

-- MySQL dump 10.13  Distrib 5.6.4-m7, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.6.4-m7-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `mysql`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `mysql`;
--
-- Table structure for table `columns_priv`

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '
',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `columns_priv`
--
LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `db`
......
[[email protected] mybak]$mysqldump -u root -poracle prod >prod0703.sql
[[email protected] mybak]$cat prod0703.sql 
-- MySQL dump 10.13  Distrib 5.6.4-m7, for Linux (x86_64)
--
-- Host: localhost    Database: prod
-- ------------------------------------------------------
-- Server version       5.6.4-m7-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'tom'),(2,'scott'),(3,'jerry');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET [email protected]_TIME_ZONE */;
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2015-07-03 15:20:24

......

[[email protected] mybak]$ls -lt
total 4
-rw-r--r-- 1 mysql mysql 1832 Jul 3 15:20 prod0703.sql

对于innodb引擎,常用的备份参数:

[[email protected] mybak]$mysqldump -u root -poracle --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction prod > prod.sql
查看备份文件内容:
[[email protected] mybak]$cat prod.sql

-- MySQL dump 10.13  Distrib 5.6.4-m7, for Linux (x86_64)
--
-- Host: localhost    Database: prod
-- ------------------------------------------------------
-- Server version       5.6.4-m7-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'tom');
INSERT INTO `t1` VALUES (2,'scott');
INSERT INTO `t1` VALUES (3,'jerry');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Dumping routines for database 'prod'
--
/*!40103 SET [email protected]_TIME_ZONE */;
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;
-- Dump completed on 2015-07-03 15:23:18

案例2:通过备份恢复

1、误操作,删除数据库
[[email protected] ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 4
Server version: 5.6.4-m7-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| prod |
| test |
--------------------
5 rows in set (0.09 sec)

mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
----------------
| Tables_in_prod |
----------------
| t1 |
----------------
1 row in set (0.00 sec)

mysql> select * from t1;
------ -------
| id | name |
------ -------
| 1 | tom |
| 2 | scott |
| 3 | jerry |
------ -------
3 rows in set (0.01 sec)

误删除数据库prod:
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop database prod;
Query OK, 1 row affected (0.04 sec)

prod数据库无法访问:
mysql> use prod
ERROR 1049 (42000): Unknown database 'prod'
mysql>

利用备份恢复数据库:
对于mysqldump备份的数据恢复很简单,只需要通过mysql客户端就可以恢复!

[[email protected] mybak]$mysql -u root -poracle prod ERROR 1049 (42000): Unknown database 'prod'
[[email protected] mybak]$mysql -u root -poracle ERROR 1046 (3D000) at line 22: No database selected

首先建立prod库:
mysql> create database prod;
Query OK, 1 row affected (0.00 sec)

在通过mysql客户端恢复:
[[email protected] mybak]$mysql -u root -poracle prod

验证恢复:
mysql> use prod;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
----------------
| Tables_in_prod |
----------------
| t1 |
----------------
1 row in set (0.00 sec)

mysql> select * from t1;
------ -------
| id | name |
------ -------
| 1 | tom |
| 2 | scott |
| 3 | jerry |
------ -------
3 rows in set (0.00 sec)

---数据恢复成功!

mysqldump增量备份
mysqlbinlog二进制日志增量备份

从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据,所以还需要结合mysqlbinlog二进制日志增量备份。确保my.ini或者my.cnf中包含下面的配置以启用二进制日志,或者mysqld ---log-bin:

[mysqld]
log-bin=mysql-bin
mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件:

mysqldump --single-transaction --flush-logs --master-data=2 > backup.sql
这样生成的增量二进制日志文件比如为mysql-bin.000003,那么恢复数据时如下:

shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql
shell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd
此外mysqlbinlog还可以指定--start-date、--stop-date、--start-position和--stop-position参数,用于精确恢复数据到某个时刻之前或者跳过中间某个出问题时间段恢复数据,直接摘录MySQL文档说明中相关内容如下:

5.9.3.1. 指定恢复时间
对于MySQL 4.1.4,可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明,假设在今天上午10:00(今天是2005年4月20日),执行SQL语句来删除一个大表。要想恢复表和数据,你可以恢复前晚上的备份,并输入:
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 
     | mysql -u root -pmypwd
该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句,可能你想要恢复后面发生的活动。根据这些,你可以用起使日期和时间再次运行mysqlbinlog:

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 
     | mysql -u root -pmypwd 
在该行中,从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

5.9.3.2. 指定恢复位置
也可以不指定日期和时间,而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同,不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法,特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号,可以运行mysqlbinlog寻找执行了不期望的事务的时间范围,但应将结果重新指向文本文件以便进行检查。操作方法为:
mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" 
      /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp目录创建小的文本文件,将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件,寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作,应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后,你应从命令行输入下面内容:

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 
    | mysql -u root -pmypwd 

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 
    | mysql -u root -pmypwd  
上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句,恢复的数据和相关MySQL日志将反应事务执行的原时间。

MySQL Study之--Mysql数据库备份工具(mysqldump) 对于Mysql Database的备份方式有很多种,此次文档主要...

       首先我们了解下mysqldump备份,数据流向的一个过程:MySQL Server端从数据文件中检索出数据,然后分批将数据返回给mysqldump客户端,然后mysqldump再把数据写入到NFS上。一般情况下存储不是SSD或者是普通磁盘,那么向NFS上写入数据比Server端检索完数据发送给mysqldump客户端要慢得多,这就有可能mysqldump无法及时接收MySQL Server端发送过来的数据,导致Server端检索出来的数据在内存中积压等待发送。当超过等待的时间net_write_timeout(默认60s)时就连接断开,同时抛出错误。

 

复制代码 代码如下:

查询资料

 1、定位问题

 ㈠ 常用操作

show variables like '%timeout%';
show variables like '%packet%';
set global max_allowed_packet=99328000;
set global wait_timeout=2880000;
set global interactive_timeout=2880000;
-- wait_timeout=2880000
-- interactive_timeout=2880000
-- max_allowed_packet=100M

大概说是因为mysqldump来不及接受mysql server端发送过来的数据,Server端的数据就会积压在内存中等待发送,这个等待不是无限期的,当Server的等待时间超过net_write_timeout(默认是60秒)时它就失去了耐心,mysqldump的连接会被断开,同时抛出错误Got error: 2013: Lost connection。

       登录到机器上,先查看了备份文件的逻辑,再查看备份的日志和备份文件大小,确认备份失败并定位到是备份命令mysqldump行执行一半失败(根据备份文件较之前的几天减少了一半且脚本运行日志来断定)。凌晨的备份无效,因而手动触发脚本执行备份,发现了报错如下:

  

PS:如果是在服务器上,要记得恢复后这几个参数要调回去,不然性能会下降的,具体这几个参数功能,大家自己找找吧.

解决方案一

[root@mysql_query hk_sa]# bash /opt/shells/mysqldump.sh    
Warning: Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `rrd_api_log` at row: 2821866

  ① 备份全库

您可能感兴趣的文章:

  • mysqldump备份还原和mysqldump导入导出语句大全详解
  • PHP备份/还原MySQL数据库的代码
  • mysql 数据库备份和还原方法集锦 推荐
  • MySQL数据库备份和还原的常用命令小结
  • MySQL中使用innobackupex、xtrabackup进行大数据的备份和还原教程
  • 详解MYSQL的备份还原(PHP实现)
  • 命令行模式下备份、还原 MySQL 数据库的语句小结
  • 深入解析Linux下MySQL数据库的备份与还原
  • MySQL 备份还原数据库批处理
  • PHP程序员玩转Linux系列 备份还原MySQL

增加net_write_timeout可以解决上述的问题的。在实践中发现,在增大 net_write_timeout后,Server端会消耗更多的内存,有时甚至会导致swap的使用(并不确定是不是修改 net_write_timeout所至)。建议在mysqldump之前修改net_write_timeout为一个较大的值(如1800),在 mysqldump结束后,在将这个值修改到默认的60。

2、排查问题

     

在sql命令行里面设置临时全局生效用类似如下命令:
SET GLOBAL net_write_timeout=1800;

      查看备份失败的表的行数为4982704,查看手动备份失败处的行信息是2017-02-05 04:03:18写入,之前都没有出现过这个备份失败的问题。于是开始怀疑是不是最近数据增长太大或者表的字段太宽的问题(其他数据库的表更大,有的甚至达到400G也没有出现过这个问题,表数据量太大的可能性不大,但单行备份失败,怀疑大字段的问题)

     语法:

修改了这个参数后再备份,不再报错
注意,这个参数不是mysqldump选项,而是mysql的一个配置参数

      查看表结构如下:

     mysqldump -h主机名  -P端口 -u用户名 -p密码 (–database) 数据库名 > 文件名.sql

解决方案二

[root@localhost] | 08:42:21 | [heika0516] > desc rrd_api_log;
 --------------- ------------- ------ ----- --------- ---------------- 
| Field         | Type        | Null | Key | Default | Extra          |
 --------------- ------------- ------ ----- --------- ---------------- 
| id            | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| api_command   | varchar(30) | NO   |     | NULL    |                |
| request_info  | text        | NO   |     | NULL    |                |
| response_info | text        | NO   |     | NULL    |                |
| create_time   | datetime    | NO   |     | NULL    |                |
 --------------- ------------- ------ ----- --------- ---------------- 
5 rows in set (0.01 sec) 

     例子:

在执行 mysqldump 的时候可以通过添加 --quick 的参数来避免出现这样的问题

     方向定了剩下的就是验证自己的猜想了,于是我开始查找资料,果然被我百度到了一些有价值的东西,包括MySQL官方的一些说法:

     mysqldump -hlocalhost -P3306 -urocky -p123456 db_test > bakfile1.sql

 --quick,-q

该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

     然而新的问题又出来了,很多前辈都讲增大net_write_timeout的值,Server端会消耗更多的内存甚至导致swap的使用影响性能,但又不确定是不是参数调整所致,存在潜在的风险。不过这种说法我并不同意,因为我执行的过程中发现,MEM的free反而变多了,你没有听错真的变多了。

     


#mysqldump备份执行前
[root@mysql_query hk_sa]# free -m
             total       used       free     shared    buffers     cached
Mem:         16080      13305       2775          0        121       3729
-/  buffers/cache:       9454       6626
Swap:         8191        349       7842

#增大了net_write_timeout的值
[root@localhost] | 08:51:53 | [(none)] > set @@global.net_write_timeout=500;
Query OK, 0 rows affected (0.01 sec)

#bash完脚本发现备份OK的
[root@mysql_query hk_sa]# ls -lh /opt/app/mysql/data/heika0516/rrd_api_log.ibd 
-rw-r--r-- 1 mysql mysql 4.1G Aug  7 22:03 /opt/app/mysql/data/heika0516/rrd_api_log.ibd

#mysqldump备份执行后
[root@mysql_query hk_sa]# free -m
             total       used       free     shared    buffers     cached
Mem:         16080      12434       3646          0         93       2890
-/  buffers/cache:       9450       6630
Swap:         8191        349       7842

  ② 带删除表的全库备份

参考文档

  到此为止,mysqldump备份失败确实是解决,但是前辈们反映的问题是消耗更多的内存,到我这反而释放了更多的内存,这样修改参数终究是会存在安全隐患,且这个参数会影响所有的会话连接。那就先不管了,过个好假期再搞。不过我始终有个疑问,我这维护的400G的大表,且部分表比这更大,也有大字段就没出现过这个问题,怎么会突然出现呢,因此我怀疑不是表数据过多的问题,还是大字段的问题。

     

http://www.linuxyw.com/linux/yunweiguzhang/20130609/566.html

       休假回来后,立马开始test排错的工作,先分析确认好切入点,我就把net_write_timeout的值改为默认60,但这样备份肯定会失败的,于是想到了max_allowed_packet参数,但全局调整这个参数,对网络发包和各个会话也有影响,继续对mysqldump这个备份进行调研,居然让我发现了一个牛逼的可接参数max_allowed_packet,在mysqldump后面加了这个选项,值大小大家可以根据表的大小进行设置,我这里给的500M,至此问题彻底解决了,也没有改变参数的全局值影响会话。

     备份MySQL数据库为带删除表的格式、能够让该备份覆盖已有数据库而不需要手动删除原有数据库

http://www.cnblogs.com/haven/archive/2012/10/27/2742141.html

     语法:

     mysqldump -–add-drop-table -u{username} -p{password} {databasename} > {backfile.sql}

     例子:

     mysqldump -–add-drop-table –urocky -p123456 db_test > bakfile2.sql

     

  ③ 压缩备份

     

     语法:

     mysqldump -h{hostname} -u{username} -p{password} {databasename} | gzip > {backfile.sql.gz}

     例子:

     mysqldump –hlocalhost –urocky –p123456 db_test | gzip > bakfile3.sql.gz

     

  ④ 备份某些表

     

     语法:

Mysql数据库备份工具,逻辑备份。     mysqldump -h主机名  -P端口 -u用户名 -p密码 (–tables | –quick) 数据库名 表名1 (表名2 …) > 文件名.sql 

     例子:

     mysqldump -hlocalhost -urocky -p123456 db_test tbl_test > bakfile4-1.sql

     mysqldump -hlocalhost -P3306 -urocky -p123456 db_test tbl_test > bakfile4-2.sql

     mysqldump -hlocalhost -P3306 -urocky -p123456 --quick db_test tbl_test > bakfile4-3.sql

     mysqldump -hlocalhost -P3306 -urocky -p123456 --tables db_test tbl_test1 tbl_test2 > bakfile4-4.sql

     

  ⑤ 同时备份多个库

     

     语法:

     mysqldump -h{hostname} (-P{port}) -u{username} -p{password} –databases {dbname1} {dbname2} {dbname3} > multibackfile.sql

     例子:

     mysqldump -hlocalhost -urocky -p123456 –databases db_test1 db_test2 db_test3 > multibackfile.sql

     

  ⑥ 备份服务器上的所有数据库

     

     语法:

     mysqldump –all-databases > allbackupfile.sql

     

  ⑦ 仅仅备份数据库结构

     

     语法:

     mysqldump –no-data –databases {databasename1} {databasename2} > {structurebackfile.sql}

     例子:

     mysqldump –no-data –databases db_test1 db_test2 > structurebackfile.sql

     

  

  ⑧ 导出某个表的部分数据

     

     语法:

     mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径

     例子:

     mysqldump -uroot -p123456 test test_data --where=" id > 100" > /tmp/test.sql

     

     

  

  ㈡ 案例分享

  

  ① 案例描述:

  

  mysqldump命令常规方式创建备份拉到某机器上恢复、恢复执行很成功、一条错误信息都没看着

  但等恢复完登录到数据库中一瞅、你猜怎么地、数据不全

  第一反应当然是查看备份文件、经过检查、果然、恢复操作确实没有问题、

  因为备份集中的内容就不全,那么,为什么备份集内容不全

  

  ② 原因分析:

  

  分析发现、原来是在导出某个视图对象时报错、mysqldump自动中止、因此所有该对象之后的就都没备份了

  

  

  ③ 场景模拟回放:

 

[plain] 

Session_A:  

  

mysql> use test;  

Database changed  

mysql> create table rocky (id int,name varchar(100));  

Query OK, 0 rows affected (0.04 sec)  

  

mysql> create view rocky_view as select * from rocky;  

Query OK, 0 rows affected (0.01 sec)  

  

mysql> rename table rocky to robbin;  

Query OK, 0 rows affected (0.03 sec)  

  

mysql> commit;  

Query OK, 0 rows affected (0.00 sec)  

  

  

Session_B:  

  

[[email protected] bin]$ ./mysqldump --tables test robbin rocky_view > bak.sql  

mysqldump: Got error: 1356: View 'test.rocky_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when doing LOCK TABLES  

 

 

  创建备份时、view对象引用的表对象不存在、执行LOCK TABLES失败、于是mysqldump就中止了

  这其实真不怪 mysqldump、因为mysqldump执行过程中遇到任何问题、默认情况下都是直接退出

  

  ④ 解决方案:

  

  执行mysqldump时附加--force参数、该参数功能是当遇到错误时忽略、继续执行后面的操作

  这个参数提供类似 Oracle 数据库中exp命令的ignore=y参数的功能、

  事实上在 Oracle 数据库中执行exp时通常都会指定ignore、

  对应到MySQL数据库、我想在执行mysqldump命令行过程中、--force参数也应做为必备参数调用

  

  

  

  ㈢ mysqldump意外终止的原因以及解决方法

  

  ① 错误现象:

       Lost connection to MySQL server at ‘reading initial communication packet’:

       原因分析:

       因为DNS不稳定导致的

       解决方案:

       开启skip-name-resolve选项将会最大程度避免这个问题

     

  ② 错误现象:

       Lost connection to MySQL server at ‘reading authorization packet’:

       原因分析:

       从MySQL获取一个可用的连接是多次握手的结果。在多次握手的过程中,网络波动会导致握手失败

       解决方案:

       最好的解决办法是让mysqldump重新发起连接请求

     

  ③ 错误现象:

       Lost connection to MySQL server during query

       原因分析:

       mysqldump处理数据过慢(NFS、gzip引起)会导致MySQL主动断开连接

       解决方案:

       加大net_write_timeout的设置

㈠ 常用操作 ① 备份全库 语法: mysqldump -h主机名 -P端...

本文由澳门新萄京发布于数据库,转载请注明出处:Mysql数据库备份工具,逻辑备份

上一篇:澳门新萄京数据库入眼知识点总括,存储进程和 下一篇:没有了
猜你喜欢
热门排行
精彩图文