MySQL备份

备份单个数据库

　　MySQL数据库自带一个很好的备份命令，就是mysqldump。

　　基本语法：mysqldump -u 用户名 -p 数据库名 > 备份的文件名 

示例

1  备份一个库

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)
# 备份
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock db1 >/opt/mysql_db1_bak.sql
# 查看
[root@localhost ~]# egrep -v "#|*|--|^$" /opt/mysql_db1_bak.sql 
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e');
UNLOCK TABLES;
[root@localhost ~]# 

 删除db1中的表进行测试

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "drop table db1.t1;"

用备份文件进行恢复

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1 </opt/mysql_db1_bak.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;"
Warning: Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | e    |
+----+------+
[root@localhost ~]# 

 加 -B参数，增加创建数据库(create database)和连接数据库(use db)的命令。

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 >/opt/mysql_db1_b_bak.sql
[root@localhost opt]# diff mysql_db1_bak.sql mysql_db1_b_bak.sql 
18a19,26
> -- Current Database: `db1`
> --
> 
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */;
> 
> USE `db1`;
> 
> --
51c59
< -- Dump completed on 2018-11-29  8:26:33
---
> -- Dump completed on 2018-11-29  8:45:31
[root@localhost opt]# 

利用-B的备份文件恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database db1;
Query OK, 1 row affected (0.37 sec)
# 备份恢复
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1 </opt/mysql_db1_bak.sql 
Warning: Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'db1'
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock </opt/mysql_db1_b_bak.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;"
Warning: Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | e    |
+----+------+
[root@localhost ~]# 

压缩备份|gzip

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1|gzip >/opt/mysql_db1_bak_gz.sql.gz
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# cd /opt/
[root@localhost opt]# ll
total 16
-rw-r--r--. 1 root root  779 Nov 29 09:01 mysql_db1_bak_gz.sql.gz
-rw-r--r--. 1 root root 1884 Nov 29 08:26 mysql_db1_bak.sql
-rw-r--r--. 1 root root 2020 Nov 29 08:45 mysql_db1_b_bak.sql

压缩备份恢复 命令gunzip　　

gunzip < /opt/mysql_db1_bak_gz.sql.gz | mysql -uroot -p123456 -S /tmp/mysql_3306.sock

　　1.到处数据用-B参数

　　2.用gzip对备份数据进行压缩

mysqldump工作原理

逻辑备份：利用mysqldump命令备份数据过程，实际上就是把数据从mysql库以逻辑的sql语句形式直接输出或者生产备份文件的过程。

 备份多个库

查看mysqldump参数：mysqldump --help

-B 参数，表示连接多个库，并且增加use db和create database db的信息。

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 db2|gzip >/opt/mysql_mul.sql.gz

删除db1和db2库进行测试

mysql> drop database db1;
Query OK, 1 row affected (0.01 sec)
mysql> drop database db2;
Query OK, 2 rows affected (0.07 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
# 恢复
[root@localhost ~]# gunzip < /opt/mysql_mul.sql.gz | mysql -uroot -p123456 -S /tmp/mysql_3306.sock
# 登陆查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

分库备份

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

语法：

　　mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1 >/opt/mysql_db1_bak.sql

　　mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db2 >/opt/mysql_db2_bak.sql
　　...

分库备份意义：

　　企业数据库里有多个库，出问题时可能是某一个库，备份时把所有库备份成一个数据文件，恢复某个库的数据比较麻烦。

备份表

语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名 

单表备份：

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock db1 t1 >/opt/table1.sql
# 删除表进行测试
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
# 恢复
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock db1</opt/table1.sql 

备份多个表：

　　语法：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名

需求：一个库里有大表小表，有时只需要恢复小表，上述的多表备份很难拆分。

解决：和分库的思想一样，每执行一条语句备份一个表，生成不同的数据文件。

　　mysqldump -u root -p db1 t1 > db1_t1.sql

　　mysqldump -u root -p db1 t2 > db1_t2.sql 　

　　...

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

1.备一个完整全备，再做一个分库分表备份。

2.脚本批量恢复多个SQL文件。

备份表结构或数据

# 备份表结构加 -d 
[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -d db1
Warning: Using a password on the command line interface can be insecure.
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
[root@localhost ~]#  

备份数据 加 -t 参数

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -t db1 t1
Warning: Using a password on the command line interface can be insecure.
INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e');
[root@localhost ~]# 

备份数据库所有数据 参数 -A -B

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -A -B --events|gzip >/opt/a_b.sql.gz

打开log-bin是mysql的增量恢复

# bin-log文件位置
[root@localhost log]# cd /data/mysql/mysql_3306/log/
[root@localhost log]# ll
total 84
-rw-rw----. 1 mysql mysql 41065 Nov 29 08:13 error.log
-rw-rw----. 1 mysql mysql   143 Nov 27 00:46 mysql3306_bin.000001
-rw-rw----. 1 mysql mysql   143 Nov 27 00:47 mysql3306_bin.000002
-rw-rw----. 1 mysql mysql   806 Nov 27 11:33 mysql3306_bin.000003
-rw-rw----. 1 mysql mysql  1203 Nov 27 16:01 mysql3306_bin.000004
-rw-rw----. 1 mysql mysql  2809 Nov 28 15:25 mysql3306_bin.000005
-rw-rw----. 1 mysql mysql  6137 Nov 29 12:02 mysql3306_bin.000006
-rw-rw----. 1 mysql mysql   288 Nov 29 08:13 mysql3306_bin.index
-rw-rw----. 1 mysql mysql  1008 Nov 29 08:13 slow.log
[root@localhost log]#

使用-F切割bin-log，刷新bin-log参数

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --compact -A -B -F --events|gzip >/opt/a_b.sql.gz

--master-data=1 自动找到bin-log的位置 

--master-data 找到全备的点：mysql3306_bin.000006

[root@localhost log]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock --master-data=1 --compact db1
Warning: Using a password on the command line interface can be insecure.
CHANGE MASTER TO MASTER_LOG_FILE='mysql3306_bin.000006', MASTER_LOG_POS=6137;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'e');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
[root@localhost log]# 

mysqldump关键参数

mysqldump --help|less 

1  -B 指定多个库，增加建库语句和use语句

2  --compact 去掉注释，适合调试输出

3  -A 所有库

4  -F 刷新binlog日志

5  -x，--lock-all-tables  锁表

6  --master-data 增加binlog日志文件及对应的位置点

7  -l ,--lock-tables 只读锁表

8  -d 只备份表结构

9  -t 只备份数据

10  --single-transaction 适合InnoDB事务数据库备份

　　工作原理： 设定本次会话的隔离级别：REPEATABLE READ，以确保本次会话（dump）时，不会看到其它会话提交的数据。

分引擎备份

myisam引擎：

　　mysqldump -uroot -p123456 -A -B --master-data=2 -x|gzip > /opt/all.sql.gz

　　mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --events --hex-blob > $backup_dir/full_dump_$backup_timestamp.sql

InnoDB引擎：

　　mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction|gzip > /opt/all.sql.gz

　　mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --events --hex-blob >$backup_dir/full_dump_$backup_timestamp.sql

参数使用

-e 参数可以不进入mysql查看信息

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "select * from db1.t1;"
Warning: Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | e    |
+----+------+
[root@localhost ~]# 

show processlist/ show full processlist

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show processlist;"
Warning: Using a password on the command line interface can be insecure.
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 48 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

show variables

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show variables;"|grep log_bin
Warning: Using a password on the command line interface can be insecure.
log_bin    ON
log_bin_basename    /data/mysql/mysql_3306/log/mysql3306_bin
log_bin_index    /data/mysql/mysql_3306/log/mysql3306_bin.index
log_bin_trust_function_creators    OFF
log_bin_use_v1_row_events    OFF
sql_log_bin    ON
[root@localhost ~]# 

show global status --计数器 mysql调优查看

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show status;"|grep sel
Warning: Using a password on the command line interface can be insecure.
Com_insert_select    0
Com_replace_select    0
Com_select    1
Connection_errors_select    0
[root@localhost ~]# 

更改数据库参数不重启生效

# 不重启都生效
mysql> show variables like 'key_buffer%';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> set global key_buffer_size=2M;

# 在配置文件也修改，是否重启都生效

1.show status 查看当前会话的数据库状态信息。

2.show global status 查看整个数据库运行状态信息，分析并做好监控。

3.show processlist 查看正在执行的sql语句，不全。

4.show full processlist 查看正在执行的完整sql语句，完整显示。

5.set global key_buffer_size=1M 不重启数据库直接生效，重启后失效。

6.show variables 查看数据库的参数信息，如my.cnf里参数的生效情况。

恢复实践

登陆到数据库里面使用source进行恢复

[root@localhost opt]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> 
 
mysql> drop database db1;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;

mysql> system ls /opt
a_b.sql.gz         mysql_db1_bak.sql    mysql_mul.sql.gz    table1.sql
mysql_db1_bak_gz.sql.gz  mysql_db1_b_bak.sql  rh

mysql> source /opt/mysql_db1_b_bak.sql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| db_3306            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> select * from db1.t1;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | e    |
+----+------+
4 rows in set (0.00 sec)

mysqlbinlog

mysqlbinlog：解析mysql的binlog日志。

mysql的binlog日志，数据目录下的文件

[root@localhost log]# ll
total 88
-rw-rw----. 1 mysql mysql 41065 Nov 29 08:13 error.log
-rw-rw----. 1 mysql mysql   143 Nov 27 00:46 mysql3306_bin.000001
-rw-rw----. 1 mysql mysql   143 Nov 27 00:47 mysql3306_bin.000002
-rw-rw----. 1 mysql mysql   806 Nov 27 11:33 mysql3306_bin.000003
-rw-rw----. 1 mysql mysql  1203 Nov 27 16:01 mysql3306_bin.000004
-rw-rw----. 1 mysql mysql  2809 Nov 28 15:25 mysql3306_bin.000005
-rw-rw----. 1 mysql mysql  8512 Nov 29 13:33 mysql3306_bin.000006
-rw-rw----. 1 mysql mysql   288 Nov 29 08:13 mysql3306_bin.index
-rw-rw----. 1 mysql mysql  1598 Nov 29 13:33 slow.log
[root@localhost log]# 

作用：用来记录mysql内部增删该查等对mysql数据库有更新的内容的记录。

使用mysqlbinlog命令查看

[root@localhost log]# mysqlbinlog mysql3306_bin.000006

备份所有 日志里面是所有库的

[root@localhost ~]# mysqlbinlog mysql3306_bin.000006 > all.sql

拆库

[root@localhost ~]# mysqlbinlog -d db1 mysql3306_bin.000006 > db1.sql

查看帮助：mysqlbinlog --help 

指定位置点截取

[root@localhost ~]# mysqlbinlog mysql306_bin.000006 --start-position=455 --stop-position=466 -r pos.sql

指定时间点截取

mysqlbinlog mysql306_bin.000006 --start-datetime='2018-11-29 16:51:10' --stop-datetime='2018-11-29 17:30:00' -r time.sql