MySQL备份与恢复

　　

运维的工作就两件事，
第一保护公司的数据，第二是网站7*24小时提供服务
[root@mangodb ~]# egrep -v "#|\*|--|^$" s.sql
[root@mangodb ~]# mysqldump -uroot -p scott --default-character-set=utf8 > s.sql
[root@mangodb ~]# mysqldump -uroot -p -B scott --default-character-set=utf8 > s.sql
[root@mangodb ~]# mysqldump -uroot -p --compact -B scott --default-character-set=utf8 > s.sql
[root@mangodb ~]# mysqldump -uroot -p --compact -B scott --default-character-set=utf8 > s.sql
[root@mangodb ~]# mysqldump -uroot -p --compact -B scott|gzip --default-character-set=utf8 > s.sql.gz
-B 在备份库的时候 他会在备份的时候 他会把你创建库的语句和切换库的语句一起备份
compact 不常用
在库的名字后面再加上gzip起到压缩然后再放到文件里面



mysqldump 的工作原理就是把数据库从mysql库里以逻辑的sql语句形式直接导出或者
生成备份文件的过程

物理备份 就是直接对文件进行打包

如何做分库备份
  分库备份实际上就是执行一个备份语句备份一个库，如果数据库里有多个库，就执行多条
相同的备份单个库的备份语句就可以备份多个库了，注意每个库都可以用对应备份的库作为库名，
结尾加上sql 备份多个库的命令如下

[root@mangodb ~]# mysql -uroot -pddbddb -e "show databases;"|grep -Eiv "database|perfor|info"|sed 's#^#mysqldump -uroot -pddbddb -B #g'
mysqldump -uroot -pddbddb -B mysql
mysqldump -uroot -pddbddb -B Scot
mysqldump -uroot -pddbddb -B scott
mysqldump -uroot -pddbddb -B test2

[root@mangodb ~]# mysql -uroot -pddbddb -e "show databases;"|grep -Eiv "database|perfor|info"|sed -r 's#^([a-z].*$)#mysqldump -uroot -pddbddb -B \1|gzip > /root/sco.sql#g'|bash

[root@mangodb ~]# mysql -uroot -pddbddb -e "show databases;"|grep -Eiv "database|perfor|info"|sed -r 's#^([a-z].*$)#mysqldump -uroot -pddbddb --events -B \1|gzip > /root/back/sco.sql.gz#g'|bash
[root@mangodb ~]# ls back/
sco.sql.gz

'''
[root@mangodb back]# cat back.sh
for dbname in `mysql -uroot -pddbddb -e "show databases;"|grep -Eiv "database|perfor|info"`
do
  mysqldump -uroot -pddbddb --events -B $dbname |gzip > /root/back/${dbname}_bak.sql.gz

done

[root@mangodb back]# ls
back.sh  mysql_bak.sql.gz  Scot_bak.sql.gz  scott_bak.sql.gz  test2_bak.sql.gz

'''

备份库表
不要在指定-B 不然会出问题的
备份多个表的时候 直接在库后面写就可以了 第一个是库后面的都是表
mysql -uroot -pddbddb scott student > scott.sql


备份表结构和数据
只备份表结构
mysql -uroot -pddbddb -d scott > scott.sql

直接备份数据
mysql -uroot -pddbddb -t scott > scott.sql

备份所有数据库
mysql -uroot -pddbddb -A -B  --events|gzip scott > scott.sql

binlog备份  -F 切割nonlog / 刷新binlog参数
mysql -uroot -pddbddb -A -B -F --events|gzip scott > scott.sql
备份的数据 以前的时间点是没有用的 因为binlog 跟数据库是冲突的
所以在备份的时候要制定一个位置点

自动找到binlog的参数位置
 mysql -uroot -pddbddb --master-data = 1 scott > scott.sql

binlog 记录是通过位置 时间点记录的


总结
导出数据用-B参数
--compact 去掉注释 适合调试输出，生产不用
用gzip进行压缩
-A 备份所有库
-F 刷新binlog日志
--master-data=1  增加binlog日志文件名及对应的位置点
 -x --lock-all-tables
-l   只读锁表
-d 只备份表结构
-t  只备份数据

--single-transaction 适合innodb事物数据库备份


myisam
常规备份
mysqldump -uroot -pddbddb -A -B --master-data=2 -x --events |gzip > /root/scott.sql


innodb
mysqldump -uroot -pddbddb -A -B --master-data=2 --events --single-transaction |gzip > /root/scott.sql



数据库恢复实践
'''
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| Scot             |
| performance_schema |
| test2              |
+--------------------+
5 rows in set (0.01 sec)

mysql> system ls /root
all.txt        back        install.log.syslog     sco.sql    scott.sql
anaconda-ks.cfg  install.log  mysqlbin_scott.000001  sco.sql.gz  s.sql
mysql> source /root/scott.sql
'''

2. 命令恢复
[root@mangodb back]# gzip -d scott_bak.sql.gz
[root@mangodb back]# mysql -uroot -pddbddb < scott_bak.sql

查看进程
[root@mangodb back]# mysql -uroot -pddbddb -e "show full processlist;"
查看参数
[root@mangodb back]# mysql -uroot -pddbddb -e "show variables;" |grep log_bin
log_bin    ON
log_bin_trust_function_creators    OFF
sql_log_bin    ON

查看状态
[root@mangodb back]# mysql -uroot -pddbddb -e "show global status;" |grep sel
Com_insert_select  0
Com_replace_select 0
Com_select 348

[root@mangodb back]# mysql -uroot -pddbddb -e "show global status;" |grep insert
Com_insert 7
Com_insert_select  0
Delayed_insert_threads 0
Innodb_rows_inserted   32
Qcache_inserts 0


mysql> show global status like '%insert%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_insert             | 7     |
| Com_insert_select      | 0     |
| Delayed_insert_threads | 0     |
| Innodb_rows_inserted   | 32    |
| Qcache_inserts         | 0     |
+------------------------+-------+
5 rows in set (0.00 sec)
--------------------------------------------------------------

不重启修改配置参数

mysql> show variables like 'key_buffer%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)

常用命令小结
show status  查看当前会话的数据库状态信息
show global status 查看整个数据库运行状态信息，很重要要分析并要做好监控

show processlist 查看正在执行的sql语句 看不全
show full processlist 查看正在执行的s完整sql语句
set global key_buffer_size=1024*1024*32; 不重启数据库调整数据库参数，直接生效，重启后失效

show variables 查看数据库的参数信息，例如 my.cnf里的参数