一文教你如何备份及恢复MySQL数据库
下文主要给大家带来备份及恢复MySQL数据库，希望这些内容能够带给大家实际用处，这也是我编辑备份及恢复MySQL数据库这篇文章的主要目的。好了，废话不多说，大家直接看下文吧。
1. 备份数据的意义
经常有网友问，运维工作到底是什么工作，到底做什么？其实，运维工作简单的概括就两件事，第一个是保护公司的数据，第二个是网站7*24小时提供服务。
而对于数据和网站7*24小时提供服务那个跟重要呢？
这个具体要看业务和公司。
例如：银行，金融行业，数据是最重要的，一条都不能丢，可能宕机停机影响就没那么大。
再例如：百度搜索，腾讯qq聊天记录丢失几万条数据，都不算啥。
对于数据来讲，数据最核心的就是数据库核数据，当然也包括其他数据，以下主要讲解的是数据库的数据。
对于绝大数企业来讲，时区数据就时区商机，时区产品，时区客户，公司倒闭…….，因此，数据库的备份与恢复就显得十分重要了。
备份的两种方式：
1. 逻辑备份：以SQL语句的形式，将数据导出，速度慢，跨平台，跨版本，可以跨软件。
2. 物理备份，直接对数据文件备份，cp tar，速度快，不容易跨平台、跨版本。跨软件，常用方法： linux备份工具（cp tar）。
3. xtrabackup
2. 数据库备份
2.1 备份单个数据库练习多种参数使用
mysql数据库自带了一个很好用的备份命令，就是mysqldump，它的基本使用如下：
命令语法：mysqldump -u 用户名-p密码 数据库名 > 备份的文件名
如下5个范例目录：
（1）备份名字为oldboy数据库
（2）解决备份文件乱码问题
（3）利用上述备份数据进行回复测试
（4）备份时使用-B参数和不适用-B参数的区别及两种备份方法。
（5）优化使用-compact参数使备份文件不输出注释，优化空间。
（6）指定压缩命令压缩备份的MySQL数据
范例操作如下：
（1）范例1：备份名字为oldboy的库
1. 备份前查看oldboy数据
[root@db01 /]# mysql -U -uroot -poldboy123 -S /data/3306/mysql.sock-------- ---------mysql> show databases; +---------------------+| Database |+---------------------+| information_schema || mysql || oldboy |+---------------------+6 rows in set (0.00 sec)mysql> use oldboyDatabase changedmysql> show tables; +------------------+| Tables_in_oldboy |+------------------+| student || test |+------------------+2 rows in set (0.00sec)mysql> select * from test; +------+---------+| id | name |+------+---------+| 3 | hehe || 5 | kaka || 1 |oldboy || 2 | oldgirl || 4 | zuma |+------+---------+5 rows in set (0.00sec)mysql> quitBye
2. 在命令行执行备份命令
[root@db01 /]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql[root@db01 /]# ll /opt/oldboy_2016-08-28.sql-rw-r--r-- 1 rootroot 2700 Aug 28 23:25 /opt/oldboy_2016-08-28.sql
3. 备份完检查备份完的数据
[root@db01 /]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS`student`;CREATE TABLE `student`( `id` int(4) NOT NULL AUTO_INCREMENT,---------------------------------INSERT INTO `test`VALUES (3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma');UNLOCK TABLES;
提示：根据以上得出的结果，我们看看到已备份的表结构语句及插入的数据整合的sql语句。
（2）范例2：如果出现乱码指定字符集参数解决备份乱码问题
假如备份数据库备份文件出现乱码情况，用以下参数解决中文乱码，过程如下
1. 查看备份的数据库客户端及服务端的字符集设置
[root@db01 opt]#mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variableslike '%character%'"+--------------------------+--------------------------------------------+| Variable_name |Value |+--------------------------+--------------------------------------------+| character_set_client | utf8 ||character_set_connection | utf8 ||character_set_database | utf8 || character_set_filesystem | binary ||character_set_results | utf8 ||character_set_server | utf8 ||character_set_system | utf8 ||character_sets_dir | /application/mysql-5.5.49/share/charsets/ |+--------------------------+--------------------------------------------+
2. 指定对应的字符集备份，这里为--default-character_set=utf8 （指定字符集参数）
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock - oldboy>/opt/oldboy_$(date +%F).sql
执行结果：
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy >/opt/oldboy_$(date +%F).sql [root@db01 opt]# egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
提示：如果出现乱码情况，使用指定支付及查看，就不会在乱码了，其实，即使导出的中文乱码也没关系，导入数据库依然是正常的。
（3）范例2:：利用上述备份好的文件进行数据恢复测试
[root@db01 opt]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock oldboy < /opt/oldboy_2016-08-28.sql[root@db01 opt]#mysql-uroot -poldboy123 -S /data/3306/mysql.sock -e "select * fromoldboy.test;"+--------+-----------+| id | name |+--------+-----------+| 3 | hehe || 5 | kaka || 1 | oldboy || 2 | oldgirl || 4 | zuma | +--------+-----------+
（4）范例4：备份时加-B参数以及使用备份文件恢复数据的两种方法
1. 查看不加-B参数备份文件和加-B备份文件区别
不加-B参数备份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql
加-B参数备份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy_B_$(date +%F).sql
使用vimdiff命令比较不加-B参数配置文件和加-B参数配置文件区别
[root@db01 /]# cd /opt/[root@db01 opt]# vimdiff oldboy_2016-08-28.sql oldboy_B_2016-08-28.sql-- Current Database: `oldboy` -- --
# 结论：
直观看，加上-B参数的作用是增加创建数据库核连接数据库的语句了，我们使用-B的备份文件就可以直接恢复，而不适用-B的备份文件还需要先创建库才可以，区别即如下两条语句：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy`USE `oldboy`;
第一种恢复数据（不加-B参数的备份数据护肤数据）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +---------------------+| Database |+---------------------+| information_schema || mysql || oldboy || performance_schema |+---------------------+6 rows in set (0.00sec)mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +----------------------+| Database |+----------------------+| information_schema || mysql || performance_schema |+----------------------+5 rows in set (0.00sec)mysql> quit Bye[root@db01 opt]# [root@db01 opt]#
说明：现在使用没有带-B参数备份的备份文件来恢复数据库，在上面已经跟加-B参数的备份文件做了对比，-B参数备份文件中比不带-B参数备份的文件多了创建oldboy数据库核进入oldbou数据库，就是说-B参数的备份文件为我们节省了创建库和进入库的两个步骤，直接将备份文件导入数据库即可，还不用指定那个库，因为里面指定了使oldboy数据库。
第二种备份文件恢复数据（使用加-B参数备份文件恢复）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database |+---------------------+| information_schema || mysql | | performance_schema |+---------------------+[root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database |+---------------------+| information_schema || mysql || | | performance_schema |+---------------------+
# 说明：在上面已经将加-B参数备份的配置文件和不加-B参数备份的配置文件进行对比和说明，加上-B参数在恢复数据时会自动建库并恢复数据，不加-B则还得先手动建库，所以这是加-B参数的好处，如果说详见一个数据库的数据拷贝到另一个库里可以不加-B参数，到时候直接将备份文件导入到该库即可。
提示：-B不仅只有自动建库use库的一键恢复库数据的应用，还可以指定多个库
（5）范例5：优化备份文件大小减少输出注释（debug调试）
利用mysqldump的-compact参数优化下备份结果：
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy -----------------------
带--compact参数备份
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy --compact >/opt/oldboy_$(date +%F).sql egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
--compact参数说明：
注意：--compact此参数不在生产环境使用，用于调试即可。
（6）范例6：指定压缩命令压缩备份的MySQL数据
在将数据库数据备份时通过gzip命令压缩，以节省磁盘空间
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy|>/opt/oldboy_$(date +%F).sql.[root@db01 opt]# ll /opt/total 24-rw-r--r-- 1root root 2700 Aug28 23:25 oldboy_2016-08-28.sql-rw-r--r-- 1root root 897 Aug 29 01:55 oldboy_2016-08-29.sql.gz-rw-r--r-- 1root root 2843 Aug28 23:54 oldboy_B_2016-08-28.sql
以上例子小结：
1. 备份数据使用-B参数，会在备份数据中增加建库及use库的语句。
2. 备份数据使用-B参数，使得后面可以直接接多个库名。
3. 备份数据使用gzip对备份的数据压缩，可以节省磁盘空间。
4. debug时可以用-compact减少输出，但不用于生产。
5. 指定字符集备份用-default-character-set=字符集（一般不适用）
2.1 mysqldump的工作原理
利用mysqldump命令备份数据的过程，实际上就是把数据从mysql库里以逻辑的sql语句的形式直接输出或者生成备份的文件的过程。
可否举一反三，利用这个备份文件恢复的时候的原理？
备份的数据过滤掉注释，都是SQL语句，结果如下：
[root@db01 opt]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sqlDROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age` tinyint(2)NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `student` WRITE;UNLOCK TABLES;DROP TABLE IF EXISTS `test`;CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma');UNLOCK TABLES;
.3. 数据库备份实践
3.1 备份多个库
1. 查看当前有几个库
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+| Database |+----------------------+| information_schema || 3ooo || mysql || oldboy | #<== 备份| oldboy_gbk | #<== 备份| performance_schema | +----------------------+6 rows in set (0.00 sec)
2 开始使用-B参数备份多个库
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy_gbkoldboy >/opt/bak_$(date +%F).sql.gz egrep -v '#|\*|--|^$'/opt/bak_2016-08-29.sql.gz
-B参数说明：该参数用于导出若干个数据库，在备份结果中会加入CREATE DATABASE `db_name`和USE db `db_name`两个mysql数据库命令，-B后的参数都将被作为数据库名，该参数比较常用，当-B后的数据库列全时，用-A参数，-A参数意思是转储所有数据中的所有表。
3.2 分库备份
分库备份实际上就是执行一个备份语句备份一个库，如果数据库里面有多个表，就执行多条相同的备份单个库的备份语句就可以备份多个库了，注意每个库都可以对应备份的库作为库名，结尾加sql，
分库意义： 举例，有时一个企业数据库里会有多个库，例如（www，bbs，blog），但是出问题时候的很可能是某一个库，如果在备份时把所有的库都备份成一个数据文件的话，恢复某一个库的数据时就比较麻烦了。
方法1：使用一条一条的命令备份：
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy>/opt/………mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy_gbk >/opt/........
方法二：利用命令拼接实现分库备份
[root@db01 ~]# mkdir /server/backup [root@db01 ~]# [root@db01 ~]# ls /server/backup/ 3ooo_2016-08-29.sql.gz oldboy_2016-08-29.sql.gz oldboy_gbk_2016-08-29.sql.gz
3.3 备份单表及分表备份
单标备份命令语法：mysqldump-u用户名 -p密码 数据库名 表名> 备份的文件名
（1）备份单个表：以下备份oldboy数据库的test表为例
1. 备份前查看oldboy数据库有那些表
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" +-----------------+| Tables_in_oldboy|+-----------------+| student || test |+-----------------+
2. 开始备份单个表（test）表
[root@db01 ~]# [root@db01 ~]# egrep -v '#|\*|--|^$'/opt/tables.sql DROP TABLE IF EXISTS `student`;CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age`tinyint(2) NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;LOCK TABLES `student` WRITE;UNLOCK TABLES;
（2）一条命令分表备份：
数据库分表备份其实就是一条一条的备份，但是又时候一条命令一条命令麻烦，可以使用一条命令将多个数据库表备份，这样更简洁，以下将student和test表备份使用一条命令，具体命令如下：
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" #<== 备份前查看当前oldboy数据库中有哪些表+------------------+| Tables_in_oldboy |+------------------+| student || test |+------------------+-[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;"|egrep -v"Tables_in_oldboy"|sed -r 's#(.*)#mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy \1 >/opt/\1_tables.sql #g' mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboystudent >/opt/student_tables.sqlmysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/test_tables.sql [root@db01 ~]# [root@db01 ~]# ll -rt /opt/|tail -2 -rw-r--r-- 1root root 2010 Aug 29 12:18 student_tables.sql-rw-r--r-- 1root root 1941 Aug 29 12:18 test_tables.sql
分表备份的缺点
1.被一个完整全被，在做一个分库分表备份。
2. 脚本批量恢复SQL文件
面试题：多个库或者多个表备份到一块，如何恢复单个库或者表？
解答：
a. 第三方测试库，导入到库里，然后把需要的备份出来，最后恢复到正式库里。
b. 单表：grep 表名 bak.sql> 表名.sql
单库：循环过滤库里所有表，grep表名 bak.ssql > 表名.sql（多个表数据）
c. 实现分库分表备份。
3.4 备份多个表
以下备份oldboy数据库的多个表
1.查看oldboy数据库中的表，命令如下：
mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showtables from oldboy;"
2. 备份oldboy数据库中的student、test两个表，命令如下
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock >/opt/tables.sql
说明：备份多个表时在oldboy数据库后面指定多个表即可，不需要加-B参数！
3.5 备份数据库表结构（不包含数据）
命令语法：mysqldump -u用户 -p密码数据库名 表名 -d > 备份文件名
利用mysqldump -d参数值备份数据库里面的表结构，
（1）备份oldboy库的所有表的结构：
备份前输出且过检查
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboytest -d -compact ，/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8;/*!40101 SET character_set_client =@saved_cs_client */;
2.. 以上输出正确后将其输出到备份文件中进行备份（备份全部表结果），命令如下
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy -d>/opt/bak_oldboy.sql
3.6 备份表中数据
命令语法：mysqldump-u用户 -p密码数据库名 表名 -t > 备份文件名
1. 备份oldboy库中test表前输出查看数据是否想要的
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test -t --compactINSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma');
2. 以上结果正确开始备份
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql
使用--tab参数备份的表结构和数据分离
[root@db01 3306]# mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy test --compact [root@db01 3306]# ls /tmp/a.txt b.txt
说明：通过以上的分离后，数据表和sql语句就分开备份了，一个是文本一个是sql语句
3.7 小结
-B备份多个库（并添加create和use库的语句）。
-d值备份库表结构。
-t只备份数据（sql语句形式）。
-T分离库表和数据成不同的文件，数据是文本，非SQL语句。
3.8 刷新binlog的参数
binlog是什么？是记录数据库更新的SQL语句。二进制文件
mysqldump用于定时对某一时刻的数据的全备，例如：00点进行bak.sql.gz
增量备份，当有数据写入到数据库时，还会同时更新的·SQL语句写入到对应的文件里。这个文件就叫做binlog文件。
例如10点丢失数据需要恢复数据：
1. 00点时刻备份的bak.sql.gz数据还原到数据库，这个时候数据恢复到了00点。
2. 00点到10::00数据，就要从binlog里恢复
binglog文件生效需要一个参数：log-bin
个人理解：我们平时对数据库进行备份时定时备份，而不是实时备份数据，假设我们数据库北田在晚上00点备份，而我早上8点数据库出现故障导致数据丢失时，我们怎么去找回00点到8点这之间的数据呢？那就要通过binlog文件啦，而binlog文件会切割的，但是在瓦上备份00点前我们是不需要的，所以在备份时要进行刷险binlog文件，重新生成，就是说重新生成的binlog是00-8:00之间的我们需要，需要靠这之间的binlog去恢复数据，
备份刷新binlog是binlog重新生成需要使用-F参数，生成新的binlog文件
下面测试：
[root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001-rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 [root@db01 3306]#mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy test >/opt/bak_test.sql [root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001-rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002-rw-rw---- 1 mysqlmysql 150 Aug 29 15:47 mysql-bin.000003
binlog日志切割，确定全备和增量备份的临界点。
-F刷新binlog日志，生成新文件，将来增量恢复从这个文件开始，
--master-data参数说明：
--master-data在备份文件里面会添加CHANGEMASTER语句及binlog文件及位置点信息。
1. 使用-master-data参数备份范例，值为1：为可执行的CHANGEMASTER语句
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql #<== 使用--master-data=1参数进行备份[root@db01 3306]# cat /opt/bak_test.sql #<== 看下使用该参数真假的说明内容CHANGE MASTER TO MASTER_LOG_FILE='',MASTER_LOG_POS=
# 以上是使用--master-data=1参数增加的内容，实惠执行的，此内容会显示出binlog文件从那个开始切割，而且还有从文件里面的第几行开始
2. 使用-master-data参数备份范例，值为2：注释的--CHANGEMASTER语句。
[root@db01 3306]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sockoldboy test -F --master-data=2 >/opt/bak_test.sql [root@db01 3306]# cat /opt/bak_test.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=107;
# 以上结果表明值为1和2是不一样的，值为1的是可执行的，值为2的则是注释的
说明：一般-master-data=1时用于做从库，想inotify和backup差不多，如果是单纯的增量恢复只需要-master-data=2即可。
--master-data除啦增量恢复确定临界点外，做主从复制是作用更大
3.9 mysqldump的关键参数说明
参数 | 解释说明 |
-B | 指定多个库，增加建库语句和use语句， ***** |
--compact | 去掉注释，适合调试输出，生产不适用 |
-A | 备份所有库 |
-F | 刷新binlog日志，生成新文件，将来增量恢复从这个文件开始 |
--master-data | 增加binlog日志文件名及对应的位置点（及CHANGE MASTER语句）。 --master-data=1不注释；--master-data=2注释； |
--evebts | 备份所有，如果想备份数据库中mysql库就要求使用此参数 |
-R | 备份存储过程 |
-d | 只备份库表结构，无数据。 |
-t | 只备份数据，无库表结构，SQL语句形式。 |
-T --tab=name | 库表和数据分离不同文件，数据是文本形式。 |
-x | 锁表，在某一时刻不能往里面更新数据，所以备份要小心。 |
-l -lock-tables | 只读锁表 |
--single-transaction | 适合innodb食物数据库备份 innodb表在备份时，通常启用选项-single-transaction来保证备份的一致性，实际上他的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话（dump）时，不会看到其他会话已经提交了的数据。 |
-q --quick | 不做缓存查询查询，直接导到标准输出，加快备份速度 |
更多参数：mysqdump -help
4. 生产场景不同引擎mysqldump备份命令
myisam引擎企业生产备份命令（适合所有引擎或混合殷勤）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events|gzip >/opt/alL__$(date +%F).sql.sql.gz
# -F也可以不用，与-master-data有些重复
innodb引擎企业生产备份命令（推荐使用的）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events - |gzip>/opt/all.sql.gz
# -F也可以不用，与-master-data有些重复
额外补充：
（1）mysqldump是逻辑备份说明
缺点：效率不是很高。
优点：简单，方便。可靠，迁移。
适合于数量不是很特别大的场景，打包前50G以内数据。
（2）如果超过50G可选方案如下：
1. xtrabackup物理备份工具，全备和增量。
2. 物理备份方案，从库停止SQL线程，打包，cp。
（3）什么时候会使用备份的数据？
1. 恢复数据到测试库的时候。
2. 人为通过SQL语句将数据删除的时候。
3. 做主从复制的时候。
5. 恢复数据库实践
5.1 数据库恢复事项
提示：
1. 数据恢复和字符集关联很大，如果字符集不正确户导致恢复的数据乱码，
2. mysql命令以及source命令恢复数据库的原理就是把文件的SQL语句，在数据库里重新执行的过程。
5.2 利用source命令恢复数据库
进入mysql数据库控制台，mysql -uroot -p登陆后
mysql> use 数据库
然后使用source命令，后面参数为脚本文件（如这里用到的sql）。
mysql> source oldboy_db.sql #这个文件是系统路径，默认是登录mysql前的系统路径。
source实践操作如下：
[root@db01 3306]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+| Database |+----------------------+| information_schema || mysql || oldboy || performance_schema |+----------------------+4 rows in set (0.00 sec) mysql> drop database oldboy; Query OK, 2 rows affected (0.00 secmysql> show databases; +----------------------+| Database |+----------------------+| information_schema || mysql || performance_schema |+----------------------+3 rows in set (0.00 sec) mysql> Query OK, 0 rows affected (0.00 sec)------以下内容输出省略---------mysql> show databases; +----------------------+| Database |+----------------------+| information_schema || mysql || oldboy || performance_schema |+----------------------+4 rows in set (0.00 sec)
5.3 利用mysql命令恢复（标准）
命令语法：
mysql -u用户 -p密码 -S /data/3306/mysql.sock 数据库<备份文件
提示：此命令分为两种方式恢复。取决于备份文件备份时是否指定-B参数进行备份
第一种恢复数据（不加-B参数的备份数据护肤数据）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +----------------------+| Database |+----------------------+| information_schema || mysql || oldboy || performance_schema |+----------------------+6 rows in set (0.00sec)mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +---------------------+| Database |+---------------------+| information_schema || mysql || performance_schema |+---------------------+5 rows in set (0.00sec)[root@db01 opt]# #<== 先创建oldboy数据库[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy
第二种备份文件恢复数据（使用加-B参数备份文件恢复）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database |+---------------------+| information_schema || mysql | | performance_schema |+---------------------+[root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database |+---------------------+| information_schema || mysql | || performance_schema |+---------------------+# 说明：在上面已经将加-B参数备份的配置文件和不加-B参数备份的配置文件进行对比和说明，加上-B参数在恢复数据时会自动建库并恢复数据，不加-B则还得先手动建库，所以这是加-B参数的好处，如果说详见一个数据库的数据拷贝到另一个库里可以不加-B参数，到时候直接将备份文件导入到该库即可。
如果在导出时指定-B参数，恢复时无需指定库恢复，为什么？
因为使用-B参数导出数据时自带了useoldboy和create database oldboy；而恢复时指定类似于use oldboy；
提示：-B不仅只有自动建库use库的一键恢复库数据的应用，还可以指定多个库
5.4 针对压缩的备份文件恢复数据
方法1：
cd /opt/gzip bak_2016-08-29.sql.gz mysql -uroot -poldboy123 -S /data/3306/mysql.sock
方法2：
6. 实现和mysql非交互式对话
6.1 利用mysql -e 参数查看mysql数据库
使用-e参数在命令行查看有哪些数据库
[root@db01 /]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;"+----------------------+| Database |+----------------------+| information_schema || 3ooo || mysql || oldboy || oldboy_gbk || performance_schema |+----------------------+
使用-e参数在命令行执行多条mysql数据命令
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "use oldboy; select * from oldboy.test;" +----+----------+| id name |+----+----------+| 3 | hehe || 5 | kaka || 1 | oldboy || 2 | oldgirl || 4 | zuma |+----+----------+
利用mysql -e参数查看SQL线程执行状态
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show processlist;" +-----+-----+----------+------+---------+--------+--------+------------------+| Id | User|Host | db | Command |Time | State | Info |+-----+-----+----------+------+---------+--------+--------+------------------+| 30 | root|localhost | NULL | Query | 0 | NULL | showprocesslist |+-----+-----+----------+------+---------+--------+--------+------------------+
查看完整的线程状态，此参数才查看满语句是非常有用
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show full processlist;"+-----+-----+----------+------+----------+--------+--------+------------------------+| Id | User|Host | db | Command |Time | State | Info |+-----+-----+----------+------+----------+--------+--------+------------------------+| 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist |+-----+-----+----------+------+----------+--------+--------+------------------------+
企业案例：mysql sleep 线程过多的问题案例
mysql> show full processlist;+-----+-----+----------+------+----------+--------+--------+------------------------+| Id | User|Host | db | Command |Time | State | Info |+-----+-----+----------+------+----------+--------+--------+------------------------+| 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist |+-----+-----+----------+------+----------+--------+--------+------------------------+1 row in set (0.00 sec)
1. 解决方法：配置文件里修改
[mysqld]
interactive_timeout = 120 #<== 超时时间；此参数设置后wait_timeout自动生效。
wait_timeout = 120 #<== 超时时间
2. 其他解决方法：
PHP程序中，不使用持久链接，及使用mysql_connect而不是pconnect（JAVA调整连接池）
3. PHP程序执行完毕，应该显示调用mysql_close。
4. 逐步分析MySQL的SQL查询及慢查询日志，找到查询国漫的SQL优化之。
6.2 利用mysql -e参数查看mysql变量及性能状态
使用命令show variables可以查看相关参数是否在数据库中生效
# 查看mysql数据库中所有的变量
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables;"| Variable_name（变量名称） | Value （值的意思） |+------------------------------------+--------------------------------------+|auto_increment_increment | 1 || auto_increment_offset | 1 || autocommit | ON || automatic_sp_privileges | ON |-------------------------以下内容省略-----------------------
现在想查看mysql中的server_id，如下：
[root@db01/]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ;" +---------------+-------+| Variable_name | Value |+---------------+-------+| server_id | 1 |+---------------+-------+查看下binlog是否开启[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ';" +---------------------------------+---------+| Variable_name |Value |+---------------------------------+---------+| log_bin | ON || log_bin_trust_function_creators | OFF || sql_log_bin | ON |+---------------------------------+---------+6.3 不重启数据库修改数据库参数
不重启数据库修改数据库参数，但是要求重启后还能生效。
[root@db01 /]#mysql -uroot -poldboy123 -S /data/3306/mysql.sockmysql> showvariables like '%key_buffer%'; +-------------------+----------+| Variable_name | Value |+-------------------+----------+| key_buffer_size |16777216 | +-------------------+----------+1 row in set (0.00 sec)mysql> Query OK, 0 rows affected (0.07 sec) mysql> show variables like '%key_buffer%'; +------------------+----------+| Variable_name | Value |+------------------+----------+| key_buffer_size |33554432 | +------------------+----------+1 row in set (0.00 sec)
不重启数据库更改数据库参数小结：
1. set global key_buffer_size = 1024*1024*32; #<== 临时生效，重启失效
2. 配置文件也要改，编辑/etc/my.cnf，修改key_buffer_size = 32k
6.4 生产场景常用重要命令小结
show processlist; #<== 查看数据库里正在执行的SQL语句，可能无法查看完整的SQL语句
show full processlist; #<== 查看正在执行的完整SQL语句，完整显示（组好使用-e外面看）
set global key_buffer_size = 1024*1024*32; #<== 不重启数据库调整数据库参数，直接生效，重启后失效
show variables; #<== 查看数据库的配置参数信息，例如：my.cnf里参数的生效情况
例如：show variables like '%log_bin';
kill ID; #<== 杀掉SQL线程的命令，ID为线程号
show session status; #<== 查看当前会话的数据库状态信息
show global status; #<== 查看整个数据库运行状态信息，很重要，要分析并做好监控
show engine innodb status; #<== 显示innodb引擎的性能状态（早期版本show innodb status）
计算一天之内，MySQL数据库有多个insert，delete，有没有好方法？
1.定时每天0点，show global status 按天取出对比。
2.按天分析binlog日志，获取数据库不同语句的频率。
mysql常用命令参数：
-u：用户
-p：密码
-S：指定socket
-h：主机
-P：端口
-e：命令行执行数据库命令
7. mysqladmin命令
mysqladmin的相关命令：
mysqladmin password 123456 #<== 数据库无密码设置密码
mysqladmin -uroot -p123456 password 123123 #<== 修改密码
mysqladmin -uroot -p123456 status #<== 查看状态
mysqladmin -uroot -p123456 extended-status #<== 类似show global status，查看状态信息
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock -i 1 status #<== 每隔1秒查看一次
mysqladmin -uroot -p123456 flush-logs #<== 刷新binlog日志
mysqladmin -uroot -p123456 processlist #<== 查看MySQL数据库进程
mysqladmin -uroot -p123456 processlist -i 1 #<== 实时跟踪进程列表（抓慢查询）
which mysqladmin -uroot -p123456 processlist #<== 实时查询进程列表
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockstutdown #<== 关机
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockvariables #<== 查看变量
对于以上关于如何备份及恢复MySQL数据库，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。
