一、备份单个数据库练习多种参数使用

mysql数据库自带了一个很好用的备份命令，就是 mysqldump ，它的基本使用如下：

语法： mysqldump -u 用户名 -p密码 数据库名 > 备份的文件名mysqldump备份原理：mysql 数据库备份实质就是将 mysql 里的数据以SQL语句的形式导出。范例1：备份名字为 oldboy 的库

[root@mysql-server ~]# mysqldump -uroot -p123456 oldboy > /opt/oldboy_bak.sql [root@mysql-server ~]# egrep -v "#|\*|--|^$" /opt/oldboy_bak.sql DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;LOCK TABLES `student` WRITE;INSERT INTO `student` VALUES (1,'oldboy',0,NULL);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`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'老男孩'),(4,'老女孩');UNLOCK TABLES;

恢复数据：

[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql # 必须指定库名 oldboy ，如果不想指定就在备份时加上 -B范例二：备份时加 -B 参数

[root@mysql-server ~]# mysqldump -uroot -p123456 -B oldboy >/opt/oldboy_B_bak.sql 和前面的备份文件对比，看看-B参数的作用[root@mysql-server ~]# cd /opt/[root@mysql-server opt]# diff oldboy_bak.sql oldboy_B_bak.sql 18a19,26> -- Current Database: `oldboy`> --> > CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;> > USE `oldboy`;> > --76c84< -- Dump completed on 2018-04-22  4:46:07---> -- Dump completed on 2018-04-22  5:14:09提示：直观看，加 -B 参数的作用是增加创建数据库和连接数据库的命令了，即如下两条语句：CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `oldboy`;

利用上述加 -B 的备份进行恢复测试：

[root@mysql-server opt]# mysql -uroot -p123456 < /opt/oldboy_B_bak.sql范例三：指定压缩命令压缩备份的 mysql 数据

[root@mysql-server opt]# mysqldump -uroot -p123456 -B oldboy|gzip > /opt/oldboy_bak.sql.gz[root@mysql-server opt]# ls -l /opt/oldboy_*-rw-r--r-- 1 root root 2579 Apr 22 04:46 /opt/oldboy_bak.sql-rw-r--r-- 1 root root  894 Apr 22 05:22 /opt/oldboy_bak.sql.gz-rw-r--r-- 1 root root 2722 Apr 22 05:14 /opt/oldboy_B_bak.sql

通过以上例子可以得出什么结论？

1、导出数据用 -B 参数2、用 gzip 对备份的数据压缩，提高效率。

二、备份多个库及多个参数练习

1、操作结果

[root@mysql-server opt]# mysqldump -uroot -p123456 -B oldboy oldboy_gbk|gzip > /opt/all_bak.sql.gz 备份所有库：

[root@mysql-server ~]# /application/mysql/bin/mysqldump -uroot -p123456 --all-databases --events --ignore-table=mysql.events > 2.sql# 备份多个库，库与库用空格隔开     [root@mysql-server opt]# ll /opt/all_bak.sql.gz -rw-r--r-- 1 root root 916 Apr 22 05:35 /opt/all_bak.sql.gz

2、-B 参数说明

-B, --databases     Dump several databases. Note the difference in usage; in                      this case no tables are given. All name arguments are                      regarded as database names. 'USE db_name;' will be                      included in the output. -B 该参数用于导出若干个数据库，在备份结果中会加入 create database ‘db_name’；和 use db_name提示：-B 参数是关键，表示接多个库，并且增加 use db，和 create database db 的信息。

三、分库备份

分库备份实际上就是执行一个备份语句备份一个库，如果数据库里有多个库，就执行多条相同的备份单个库的备份语句就可以备份多个库了，注意每个库都可以用对应备份的库作为库名，结尾加.sql。命令如下：

法1：命令拼接

[root@mysql-server opt]# mysql -uroot -p123456 -e "show databases;"|egrep -vi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1|gzip > /opt/bak/\1.sql.gz#g'|bash[root@mysql-server opt]# ll bak total 164-rw-r--r-- 1 root root 144463 Apr 22 06:14 mysql.sql.gz-rw-r--r-- 1 root root    535 Apr 22 06:14 oldboy_gbk.sql.gz-rw-r--r-- 1 root root    911 Apr 22 06:14 oldboy.sql.gz-rw-r--r-- 1 root root    801 Apr 22 06:14 oldboy_utf8.sql.gz-rw-r--r-- 1 root root    532 Apr 22 06:14 test.sql.gz-rw-r--r-- 1 root root    538 Apr 22 06:14 wordpress.sql.gz

法二：shell 脚本（for 循环）

[root@mysql scripts]# cat mysqldump1.sh#!/bin/shUSER=rootPASSWORD=oldboy123SOCKET=/data/3306/mysql.sockLOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"DUMP="mysqldump  -u$USER -p$PASSWORD -S $SOCKET"DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')for database in $DATABASEdo $DUMP -B $database |gzip >/server/backup/${database}_B_$(date +%F).sql.gzdone

分库备份的意义何在？

有时一个企业的数据库里会有多个库，例如（www，bbs，blog），但是出问题时可能是某一个库，如果在备份时把所有的库都备份成了一个数据文件的话，恢复某一个库的数据时就比较麻烦了。

四、备份表

1、备份单个表

语法：mysqldump -uroot -p123456 数据库名 表名 > 备份的文件名执行结果：

[root@mysql-server opt]# mysqldump -uroot -p123456 oldboy test > /opt/test_table.sql提示：不能加 -B 参数了，因为库 oldboy 后面就是 test 表了。[root@mysql-server opt]# egrep -v "#|\*|--|^$" /opt/test_table.sql  DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;LOCK TABLES `test` WRITE;INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'老男孩'),(4,'老女孩');UNLOCK TABLES;

2、备份多个表

语法：mysqldump -uroot -p123456 数据库名 表名1 表名2 ... > 备份的文件名以下命令是备份 test 表和 student 表两个表：[root@mysql-server opt]# mysqldump -uroot -p123456 oldboy test student > /opt/all_table.sql3、分表备份企业需求：一个库里有大表有小表，有时可能需要只恢复某一个小表，上述的多表备份很难拆开，就会像没有分库那样导致恢复某一个小表很麻烦。那么分表备份和分库的思想一样，每执行一条语句备份一个表，生成不同的数据文件即可。利用shell 脚本实现（两个 for 循环）：

[root@mysql backup]# cat /server/scripts/mysqldump1.sh #!/bin/shUSER=rootPASSWORD=oldboy123SOCKET=/data/3306/mysql.sockLOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"DUMP="mysqldump  -u$USER -p$PASSWORD -S $SOCKET"DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')for database in $DATABASEdo    TABLE=$($LOGIN -e "use $database;show tables;"|sed '1d')    for table in $TABLE    do      #[ ! -d /server/backup/$database/ ] && mkdir /server/backup/$database/ -p      [ -d /server/backup/$database/ ] || mkdir /server/backup/$database/ -p      $DUMP $database |gzip >/server/backup/$database/${database}_${table}_$(date +%F).sql.gz    done done

分表备份缺点：文件多，很碎。

a、备一个完整全备，再做一个分库分表备份。b、脚本批量恢复多个SQL 文件。4、备份数据表结构（不包含数据）利用 mysqldump -d 参数只备份表的结构，例：备份 oldboy 库的所有表的结构。

[root@mysql-server opt]# mysqldump -uroot -p123456 -B -d oldboy > /opt/biao_jiegou.sql[root@mysql-server opt]# egrep -v "#|\*|--|^$" /opt/biao_jiegou.sql USE `oldboy`;DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` int(4) NOT NULL,  `name` char(20) NOT NULL,  `age` tinyint(2) NOT NULL DEFAULT '0',  `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;    如果只备份数据，用 -t[root@mysql-server opt]# mysqldump -uroot -p123456 -B -t oldboy > /opt/biao_shuju.sql

5、mysqldump 的关键参数说明

关键参数：mysqldump --help

1）、-B 指定多个库，增加建库语句和 use 语句    2）、--compact 去掉注释，适合调试输出，生产不用    3）、-A 备份所有库    4）、-F 刷新 binlog 日志    5）、--master-data 增加 binlog 日志文件名及对应的位置点    6）、-x，--lock-all-tables 锁表    7）、-l，--lock-tables 只读锁表    8）、-d 只备份表结构    9）、-t 只备份数据    10）、--single-transaction 适合 innodb 事务数据库备份

innodb 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：repeatable read，以确保本次会话（dump）时，不会看到其他会话已经提交了的数据。

myisam 引擎备份命令：mysqldump -uroot -p123456 -A -B --master-data=2 -x --events|gzip > /opt/all.sql.gzinnodb 引擎备份命令：推荐使用的

mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction --events|gzip > /opt/all.sql.gz  # --master-data=2 作用就是找到开始增量的 binlog 文件名和位置点，-- 在SQL语句中是注释的意思。[root@mysql-server ~]# mysqldump -uroot -p123456 --master-data=2 --events oldboy student-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000052', MASTER_LOG_POS=107;

分库分表备份脚本：

[root@mysql oldboy]# cat /server/scripts/mysqldump1.sh #!/bin/shUSER=rootPASSWORD=oldboy123SOCKET=/data/3306/mysql.sockLOGIN="mysql -u$USER -p$PASSWORD -S $SOCKET"DUMP="mysqldump  -u$USER -p$PASSWORD -S $SOCKET"DATABASE=$($LOGIN -e "show databases;"|egrep -v "*chema|mysql"|sed '1d')   for database in $DATABASE   do     TABLE=$($LOGIN -e "use $database;show tables;"|sed '1d')       for table in $TABLE       do         #[ ! -d /server/backup/$database/ ] && mkdir /server/backup/$database/ -p         [ -d /server/backup/$database/ ] || mkdir /server/backup/$database/ -p         $DUMP $database |gzip >/server/backup/$database/${database}_${table}_$(date +%F).sql.gz       done      $DUMP -B $database |gzip >/server/backup/${database}_B_$(date +%F).sql.gz   done

五、恢复数据库实践

1、利用 source 命令恢复数据库

mysql> system ls /opt/all_bak.sql.gz  biao_jiegou.sql    oldboy_B_bak.sqlall_table.sql   oldboy_bak.sql     rhbak             oldboy_bak.sql.gz  test_table.sqlmysql> source /opt/oldboy_B_bak.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.00 sec)Database changedQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.05 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 1 row affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.06 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)

2、利用 mysql 命令恢复（标准）

[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql     对于备份的压缩文件，先解压再恢复数据[root@mysql-server opt]# gzip -d oldboy_bak.sql.gz             # -d 是删除源文件[root@mysql-server ~]# mysql -uroot -p123456 oldboy < /opt/oldboy_bak.sql

3、恢复分库分表的备份数据

[root@mysql-server bak]# lsmysql.sql       oldboy.sql       test.sqloldboy_gbk.sql  oldboy_utf8.sql  wordpress.sql[root@mysql-server bak]# for dbname in `ls *\.sql|sed 's#\.sql##g'`;do mysql -uroot -p123456 < ${dbname}.sql;done[root@mysql-server bak]# mysql -uroot -p123456 -e "show databases;"+--------------------+| Database           |+--------------------+| information_schema || mysql              || oldboy             || oldboy_gbk         || oldboy_utf8        || performance_schema || test               || wordpress          |+--------------------+

六、mysql数据库生产常用命令

1、show status； 《== 查看当前会话的数据库状态信息

2、show global status； 《== 查看整个数据库运行状态信息，很重要，要分析并要做好监控

mysql> show global status like 'select%';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| Select_full_join       | 0     || Select_full_range_join | 0     || Select_range           | 0     || Select_range_check     | 0     || Select_scan            | 3     |+------------------------+-------+5 rows in set (0.00 sec)

3、show processlist； 《== 查看正在执行的 SQL 语句，看不全

4、show full processlist； 《== 查看正在执行的完整SQL语句，完整显示5、set global key_buffer_size = 32777218； 《== 不重启数据库调整数据库参数，直接生效，重启后失效。（如果想永久生效，修改配置文件 my.cnf ）

mysql> set global key_buffer_size = 1024*32;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    1Current database: *** NONE ***Query OK, 0 rows affected (0.00 sec)

6、show variables； 《== 查看数据库的参数信息，例如：my.cnf 里参数的生效情况

mysql> show variables like 'key_buffer_size'; +-----------------+-------+| Variable_name   | Value |+-----------------+-------+| key_buffer_size | 32768 |+-----------------+-------+1 row in set (0.00 sec)

七、mysqlbinlog 命令介绍及实战讲解

1、 如何开启 mysqlbinlog 功能

在配置文件 my.cnf 里取消注释：[root@mysql-server bak]# sed -i 's%#log-bin=mysql-bin%log-bin=mysql-bin%g' /etc/my.cnf 2、mysqlbinlog 是解析 mysql 的 binlog 日志的，那么 mysql 的 binlog 日志是什么？数据目录下的如下文件就是 mysql 的 binlog 日志：

[root@mysql-server bak]# cat /application/mysql/data/mysql-bin.index./mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004

3、mysql 的 binlog 日志作用是什么？

用来记录 mysql 内部增删改查等对 mysql 数据库有更新的内容的记录。（主要就是对更新、修改的内容记录）查看 mysql 的 binlog 日志：

[root@mysql-server data]# mysqlbinlog mysql-bin.000025/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

4、mysqlbinlog 重要参数命令

1）、-d 截取指定库的 binlog[root@mysql-server data]# mysqlbinlog -d oldboy_gbk mysql-bin.000024 >oldboy.gbk.sql # 截取指定 oldboy_gbk 库的 binlog2）、按照位置截取：[root@mysql-server data]# mysqlbinlog mysql-bin.000024 --start-position=592 --stop-position=695 -r pos.sql # 从592开始到695结束，截取之间的内容重定向到文件里，-r 相当于 >，重定向的意思3）、按照时间截取：[root@mysql-server data]# mysqlbinlog mysql-bin.000024 --start-datetime='2018-04-23 17:57:07' --stop-datetime='2018-04-23 17:57:08' -r time.sql # 从这个时间段里截取内容重定向到文件里