MySQL备份-白红宇

MySQL备份

阅读量：5237 次

发布时间：2019-06-14

本文共 13025 字，大约阅读时间需要 43 分钟。

备份单个数据库

　　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

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

压缩备份|gzip

[root@localhost ~]# mysqldump -uroot -p123456 -S /tmp/mysql_3306.sock -B db1|gzip >/opt/mysql_db1_bak_gz.sql.gzWarning: Using a password on the command line interface can be insecure.[root@localhost ~]# cd /opt/[root@localhost opt]# lltotal 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

备份多个表：

　　语法：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 db1Warning: 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 t1Warning: 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]# lltotal 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 db1Warning: 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_binWarning: Using a password on the command line interface can be insecure.log_bin    ONlog_bin_basename    /data/mysql/mysql_3306/log/mysql3306_binlog_bin_index    /data/mysql/mysql_3306/log/mysql3306_bin.indexlog_bin_trust_function_creators    OFFlog_bin_use_v1_row_events    OFFsql_log_bin    ON[root@localhost ~]#

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

[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql_3306.sock -e "show status;"|grep selWarning: Using a password on the command line interface can be insecure.Com_insert_select    0Com_replace_select    0Com_select    1Connection_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.sockmysql> 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 /opta_b.sql.gz         mysql_db1_bak.sql    mysql_mul.sql.gz    table1.sqlmysql_db1_bak_gz.sql.gz  mysql_db1_b_bak.sql  rhmysql> source /opt/mysql_db1_b_bak.sqlmysql> 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]# lltotal 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

转载于:https://www.cnblogs.com/wangzihong/p/10036840.html

你可能感兴趣的文章

【转】Linux下tcp连接断开后不释放的解决办法

HTTP的应用httpclient 和线程

查看>>

pytest七：assert断言

查看>>

linux　shell 判断空字符串的几种方法！

Sa身份登陆SQL SERVER失败的解决方案