一文教你如何备份及恢复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 oldboy
Database changed

mysql> 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> quit
Bye

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.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;
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.sql
mysqldump -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库的所有表的结构：

1. 备份前输出且过检查

[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 --compact
INSERT 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的关键参数说明

更多参数：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 sec

mysql> 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.sock
mysql> 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数据库，大家是不是觉得非常有帮助。如果需要了解更多内容，请继续关注我们的行业资讯，相信你会喜欢上这些内容的。
						
            
            
                        

            网站名称：一文教你如何备份及恢复MySQL数据库            

            本文来源：http://6mz.cn/article/pjhjoo.html