数据库备份的作用不言而喻,虽然不一定用的上,却还是必须要做的。
数据库备份可以从两个方向考虑备份,一个是直接备份存储后的数据文件 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的其他参数。