MySQL备份_mysql备份和还原数据库

备份单个数据库

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

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

示例

①.? 备份一个库

[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 
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 
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 
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

①到处数据用-B参数

mysqldump工作原理

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

备份多个库

-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

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 -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

备份多个表：

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

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

mysqldump -u root -p db1 t1 > db1_t1.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

[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关键参数

①.? -B 指定多个库,增加建库语句和use语句

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

分引擎备份

myisam引擎：

InnoDB引擎：

参数使用

-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 ~]# 

[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;

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

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

恢复实践

登陆到数据库里面使用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恢复

以上就是洋佩百科网小编为大家整理的MySQL备份相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用，就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！