MySQL备份,mysql备份表和恢复表

摘要：

备份单个数据库MySQL数据库需要一个好的备份命令mysqldump。

备份单个数据库

　　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)

source恢复

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

MySQL备份

备份单个数据库

示例

压缩备份|gzip

mysqldump工作原理

备份多个库

分库备份

备份表

备份表结构或数据

mysqldump关键参数

分引擎备份

参数使用

恢复实践

mysqlbinlog

相关文章

Asp.net Mvc 使用EF6 code first 方式连接MySQL总结

MySQL InnoDB表空间加密

Zabbix 系统概述与部署

mysql修改字段防止锁表

Mysql，重复字段只取其中一行

mysql设计表结构数据类型的选择

最新文章

随机推荐

思享工具箱导航

JSON工具

格式化转换

加解密编码

文本数字

网络

站长

计算

其他

对照列表