Mysql 备份与恢复

数据库备份的作用不言而喻,虽然不一定用的上,却还是必须要做的。

数据库备份可以从两个方向考虑备份,一个是直接备份存储后的数据文件 tar cp,称之为物理备份,这样做在数据回复时特别快直接挂载到数据目录即可,同时他拷贝数据文件时会很慢,而且不容易跨平台跨版本跨软件还不能分库分表,笨重适用于超过50G的大数据。常用的备份工具有xtrabackup(Innodb), cp tar等
另一个则是备份数据库执行的SQL语句,恢复数据时,需要将sql语句在执行一边,这样做备份时会很快,恢复时就很慢,mysqldump,可以跨平台跨软件分库分表。

一、 mysqldump


 - 1.1 全部备份
[root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock  --all-databases  
 #mysqldump实际上就是导出所有的sql语句
                    --add-databases  所有数据库
。。。。。。
LOCK TABLES `test2` WRITE;
/*!40000 ALTER TABLE `test2` DISABLE KEYS */;
/*!40000 ALTER TABLE `test2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-05-07 10:32:45
。。。

 - 1.2 分库分表备份
#使用-B 指定数据库名称
 [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -B test_db
#导出多个数据库
  [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -B test_db test_db2  
#导出表
  [root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test

#使用输出重定向保存sql语句
[root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test >./1.test_db-test.sql
[root@Mysql_5 ~]# ll 1.test_db-test.sql
-rw-r--r-- 1 root root 2145 May  7 12:56 1.test_db-test.sql
[root@Mysql_5 ~]# cat 1.test_db-test.sql
-- MySQL dump 10.13  Distrib 5.5.49, for Linux (x86_64)
--
-- Host: localhost    Database: test_db
-- ------------------------------------------------------
-- Server version   5.5.49-log

 - 1.3 压缩保存
[root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock test_db test |gzip >./1.test_db-test.sql.gz
[root@Mysql_5 ~]# ll 1.test_db-test.sql.gz
-rw-r--r-- 1 root root 784 May  7 12:57 1.test_db-test.sql.gz
[root@Mysql_5 ~]#


  • 1.5 mysqldump 其他参数
    -d 只备份库表结构
    -T 分离库表和数据成不同的文件,数据是文本,非SQL语句
    -A 备份所有库
    -B 制定单个库
  • DATABASE TABLE 备份表

  • 2.0 binlog
    mysql在写入数据到数据库时,同时会将sql语句记录到binlog中。
    这里写图片描述

    这个时间段丢失的数据就可以通过binlog恢复, 在mysqldump备份数据库时-F 刷新binlog ,这样就可以找到备份后的临界点,回复丢失的数据。

  • 启动binlog记录日志

[root@Mysql_5 ~]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
[root@Mysql_5 ~]#
[root@Mysql_5 ~]# ll /data/3306/mysql-bin.*
-rw-rw---- 1 mysql mysql  126 May  1 15:50 /data/3306/mysql-bin.000017
-rw-rw---- 1 mysql mysql  126 May  1 15:50 /data/3306/mysql-bin.000018
-rw-rw---- 1 mysql mysql  126 May  1 15:59 /data/3306/mysql-bin.000019
-rw-rw---- 1 mysql mysql  840 May  7 12:50 /data/3306/mysql-bin.index
  • 备份数据中添加位置点信息
 mysqldump  -F 生成新的binlog文件,将来恢复时从这个文件开始
 --master-data  在数据文件里添加CHANGE MASTER语句 binlog文件以及位置点信息。
               =1 为可执行的CHANGE MASTER语句
               =2 为注释的CHANGE MASTER语句
[root@Mysql_5 ~]# mysqldump -uroot -predhat -S /data/3306/mysql.sock -F  -B test_db --master-data=1 >test.sql
[root@Mysql_5 ~]# sed -n '22p' test.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000046', MASTER_LOG_POS=107;
[root@Mysql_5 ~]#
  • 锁表
    -x,–lock-all-table 加全局锁。myisam引擎,非事物引擎,混合引擎
    -single-transaction 事务引擎innodb 备份,不用锁表 ,利用了事务的ACID 隔离性

  • 1.4 生产场景不同引擎mysqldump备份命令

myisam引擎企业生产备份命令(适合所有引擎或混合引擎):
mysqldump -uroot -predhat -S /data/3306/mysql.sock -A -B -R –master-data=2 -x |gzip >/opt/alL__$(date +%F).sql.gz
提示:-F也可以不用,与–master-data有些重复。

innodb引擎企业生产备份命令:推荐使用的
mysqldump -uroot -predhat -S /data/3306/mysql.sock -A -B -R –master-data=2 –single-transaction |gzip >/opt/alL__$(date +%F).sql.gz

  • Mysql 恢复数据
    将导出的数据库文件恢复到数据库中。

 - mysql命令
[root@Mysql_5 ~]# mysqldump -S /data/3306/mysql.sock  -uroot -predhat -B test_db >test_db.sql
[root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock <test_db.sql 
[root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock -e "show databases;"
                                  # -e 非交互式执行sql语句。
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
[root@Mysql_5 ~]#


#压缩恢复
[root@Mysql_5 ~]# mysqldump -S /data/3306/mysql.sock  -uroot -predhat -B test_db |gzip >test_db.sql.gz
[root@Mysql_5 ~]# zcat test_db.sql.gz |mysql -uroot -predhat -S /data/3307/mysql.sock
[root@Mysql_5 ~]# mysql -uroot -predhat -S /data/3307/mysql.sock -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
[root@Mysql_5 ~]#

PS:如果全备了所有库但是只会恢复单个库时 可以将数据全部导入第三方数据库,dump出需要的库恢复。
如果dump时没有使用-B 指定库,而导入的mysql -server中没有要导入的db 需要先执行创建db 。
 - source恢复
mysql> source test_db.sql;   
             #这里是数据文件路径 这是相对路径 进入数据库前的位置。
使用source 恢复,和字符集关联很大 必须要一致,不然会乱码。 数据库---数据文件
  • 查看线程状态
#查看完整的线程状态
mysql> show full processlist;
+----+------+-----------+---------+---------+------+-------+-----------------------+
| Id | User | Host      | db      | Command | Time | State | Info                  |
+----+------+-----------+---------+---------+------+-------+-----------------------+
| 15 | root | localhost | test_db | Query   |    0 | NULL  | show full processlist |
+----+------+-----------+---------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

#查看当前的线程状态
mysql> show  processlist;
+----+------+-----------+---------+---------+------+-------+-------------------+
| Id | User | Host      | db      | Command | Time | State | Info              |
+----+------+-----------+---------+---------+------+-------+-------------------+
| 15 | root | localhost | test_db | Query   |    0 | NULL  | show  processlist |
+----+------+-----------+---------+---------+------+-------+-------------------+
1 row in set (0.00 sec)

mysql>

#结束线程
mysql> kill 15;

mysql线程过多

mysql命令行
set global wait_timeout = 60;
set global interactive_timeout = 60;

修改配置文件
[mysqld]
interactive_timeout = 120 此参数设置后wait_timeout自动生效。 mysql的连接数的多少
wait_timeout = 120 每一个连接的超时时间

  • 查看mysql变量及性能状态

mysql> show variables like '%slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| log_slow_queries    | OFF                              |
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /data/3307/data/Mysql_5-slow.log |
+---------------------+----------------------------------+
4 rows in set (0.00 sec)

mysql>
mysql> show variables;
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name                                     | Value                                                                 |

 - 查看其他变量
 mysql> show variables like '%server_id%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)
mysql>
 - 不重启设置变量,重启生效

 mysql> show variables like '%key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)
   #当前生效
mysql> set global key_buffer_size = 1024*32;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%key_buffer%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| key_buffer_size | 32768 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql>
    #下次重启生效
mysql> system grep key_buffer /data/3307/my.cnf
key_buffer_size = 32K
mysql>
写入配置文件 ; sql中 system 表示执行shell命令
  • 数据库掌握命令
    show processlist; 查看数据库正在执行的sql语句,可能无法看到完整的sql语句
    show full processlist; 查看完整的正在执行的语句.
    kill ID; 杀死线程
    show variables; 查看变量信息.
    show variables like ’ %…%’; 精确查看变量信息, % 通配符任意字符
    set global key_buffer_size = 1024*32; 设置变量 当前生效
    show session status; 查看当前会话的数据库状态信息.
    show global status; 查看全局的状态信息.
    show engine innodb status; 查看innodb 引擎的状态信息.
    show slave status; 查看主从复制的状态信息.
    /G;
  • mysql 恢复练习
[root@localhost]_(none)>create database test;
Query OK, 1 row affected (0.00 sec)

[root@localhost]_(none)>use test;
Database changed
[root@localhost]_test>
[root@localhost]_test>create table test2 (`id` int(4) NOT NULL ,`name` char(20) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
[root@localhost]_test>insert  into test.test2(id,name) values(1,'aa');
Query OK, 1 row affected (0.00 sec)

[root@localhost]_test>insert  into test.test2(id,name) values(2,'aaa');
Query OK, 1 row affected (0.00 sec)
[root@localhost]_test>select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | aaa  |
+----+------+
2 rows in set (0.00 sec)

[root@localhost]_test>
[root@Mysql_5 ~]#  mysqldump -S /data/3306/mysql.sock  -uroot -predhat -B -R -F  --single-transaction   test |gzip >/tmp/$(date +%F).sql.gz

[root@Mysql_5 ~]# ll /tmp/2017-05-08.sql.gz
-rw-r--r-- 1 root root 743 May  8 09:14 /tmp/2017-05-08.sql.gz
[root@Mysql_5 ~]#



[root@localhost]_(none)>use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[root@localhost]_test>insert  into test.test2(id,name) values(3,'bb');
Query OK, 1 row affected (0.00 sec)

[root@localhost]_test>insert  into test.test2(id,name) values(4,'bbb');
Query OK, 1 row affected (0.00 sec)

[root@localhost]_test>select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | aaa  |
|  3 | bb   |
|  4 | bbb  |
+----+------+
4 rows in set (0.00 sec)

[root@localhost]_test>


[root@localhost]_test>drop database test;
Query OK, 1 row affected (0.01 sec)

[root@localhost]_(none)>
[root@localhost]_(none)>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


[root@Mysql_5 ~]# zcat /tmp/2017-05-08.sql.gz |my06
[root@Mysql_5 ~]# my06 -e "use test; select * from test2;"
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | aaa  |
+----+------+
[root@Mysql_5 ~]#

#结合binlog 时间 和 备份时间找到刷新后的日志。

[root@Mysql_5 ~]# mysqlbinlog /data/3306/mysql-bin.000052
。。。。。。。。。。。
# at 107

[root@Mysql_5 ~]# mysqlbinlog /data/3306/mysql-bin.000052|grep drop
drop database test
[root@Mysql_5 ~]#
[root@Mysql_5 ~]# sed -i '/drop/d' /tmp/1.sql
[root@Mysql_5 ~]# grep 'drop' /tmp/1.sql
[root@Mysql_5 ~]#
[root@Mysql_5 ~]# my06 </tmp/1.sql
[root@Mysql_5 ~]# my06 -e "select * from test.test2;"
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | aaa  |
|  3 | bb   |
|  4 | bbb  |
+----+------+
[root@Mysql_5 ~]#



#这里是恢复的整个test库的数据,如果要恢复单个表 先导出test库的表结构到测试库,然后把1.sql恢复到新库,导出指定表的数据,恢复即可。

恢复bin log 也可根据出问题的时间进行恢复。
具体参考mysqlbinlog的其他参数。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值