mysql的备份与恢复

mysql的备份和恢复：

[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 sales > /data0/mysqldump/sales_bak.sql
[root@yz3110 mysqldump]# egrep -v "#|*|--|^$" /data0/mysqldump/sales_bak.sql

[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales > /data0/mysqldump/sales_B_bak.sql
[root@yz3110 mysqldump]# diff sales_bak.sql sales_B_bak.sql
18a19,26
> -- Current Database: `sales`
> --
>
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sales` /*!40100 DEFAULT CHARACTER SET utf8 */;
>
> USE `sales`;
>
> --
172a181,186
> -- Current Database: `sales`
> --
>
> USE `sales`;
>
> --
200c214
< -- Dump completed on 2016-03-24 18:03:03
---
> -- Dump completed on 2016-03-24 18:20:27

-B选项就是增加创建和连接数据库的选项

[root@yz3110 mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe sales < /data0/mysqldump/sales_bak.sql
ERROR 1049 (42000): Unknown database 'sales'

优化输入内容的大小，让容量更小，适合调试
[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 --compact -B sales > /data0/mysqldump/sales_compact_B_bak.sql

备份压缩（压缩效率将近3倍）
[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales|gzip > /data0/mysqldump/sales_B_bak_gzip.sql.gz
[root@yz3110 mysqldump]# ll
total 28
-rw-r--r-- 1 root root 7266 Mar 24 18:03 sales_bak.sql
-rw-r--r-- 1 root root 1780 Mar 24 18:47 sales_B_bak_gzip.sql.gz
-rw-r--r-- 1 root root 7456 Mar 24 18:20 sales_B_bak.sql
-rw-r--r-- 1 root root 4511 Mar 24 18:35 sales_compact_B_bak.sql

mysqldump的工作原理：

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

备份多个库
[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales cider 08day5 |gzip > /data0/mysqldump/sales_cider_08day5.sql.gz

mysqldump的工作原理

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

备份多个库
[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales cider 08day5 |gzip > /data0/mysqldump/sales_cider_08day5.sql.gz

[root@yz3110 mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5" |sed "s#^#mysql -uroot -h10.39.3.110 -p123qwe -B#g"
mysql -uroot -h10.39.3.110 -p123qwe -Bcider
mysql -uroot -h10.39.3.110 -p123qwe -Bkeystone
mysql -uroot -h10.39.3.110 -p123qwe -Bsales

[root@yz3110 mysqldump]# ls /data0/mysqldump/bak/
cider.sql.gz keystone.sql.gz sales.sql.gz

方法2：
for循环备份
for data_name in `mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5
"`;do
mysqldump -uroot -h10.39.3.110 -p123qwe -B $data_name|gzip > /data0/mysqldump/bak/${data_name}.sql.gz
done

备份表：
第一个是库，后面可以多个表
[root@yz3110 mysqldump]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe sales customers > sales_customers.sql

-d 备份表结构：
[root@yz3110 ~]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe --compact -d sales customers
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customers` (
`cust_id` int(11) NOT NULL AUTO_INCREMENT,
`cust_name` char(50) NOT NULL,
`cust_address` char(50) DEFAULT NULL,
`cust_city` char(50) DEFAULT NULL,
`cust_state` char(5) DEFAULT NULL,
`cust_zip` char(10) DEFAULT NULL,
`cust_country` char(50) DEFAULT NULL,
`cust_contact` char(50) DEFAULT NULL,
`cust_email` char(255) DEFAULT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

-t 是直接备份数据：
[root@yz3110 ~]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe --compact -t sales customers
INSERT INTO `customers` VALUES (1,'Pep E. LaPew','100 Main street','los angeles','CA','90046','USA',NULL,NULL),(2,'simon','58 street','beijing','CA','10008','CHINA','185xxxxxx','yongsan'),(3,'alex','32 street','shanghai','CA','10086','CHINA','183xxxxxx','lys'),(4,'a1','33 street','hefei','HR','10386','JAPAN','123xxxxxx','sina@staff'),(5,'YS','33 street','SHanghai','CD','10010','HK','111xxxxxx','ly1s');

刷新binlog参数：
1，备份所以数据库，所有表
[root@yz3110 ~]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe -A -B --events |gzip > /data0/mysqldump/all_databases.sql.gz
-F 切割binlog
[root@yz3110 ~]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe -A -B -F --events |gzip > /data0/mysqldump/all_databases_F.sql.gz

--master-data查看恢复二进制数据的位置
[root@yz3110 mysql3306]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe --master-data=1 --compact sales
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=107;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;

[root@yz3110 mysql3306]# mysqlbinlog mysql-bin.000028
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160325 10:36:15 server id 3306 end_log_pos 107 Start: binlog v 4, server v 5.5.24-log created 160325 10:36:15
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
H6T0Vg/qDAAAZwAAAGsAAAABAAQANS41LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@yz3110 mysql3306]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe --master-data=2 --compact sales
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=107;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;

mysqldump --help
1，-B 指定多个库，增加建库语句和use语句
2，--compact 去掉注释，适合调试输出，生产不用
3，-A备份所有库
4，-F 刷新binlog日志
5，--master-data增加binlog日志文件名及对应的位置点
6-x,--lock-all-tables 锁表
7,-l 只读锁表
8，-d 只备份表结构
9，-t 只备份数据
10，--single-transaction 适合innodb事务数据库备份

myisam引擎(亦适合混合引擎)：
常规备份：
mysqldump -u -p -A -B --master-data={1,2} -x |gzip > /opt/all.sql.gz

innodb引擎：
mysqldump -u -p -A -B --master-data={1,2} ---single-transaction |gzip > /opt/all.sql.gz

[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -S /tmp/mysql3306.sock --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --events --hex-blob > /data0/mysqldump/full_dump_`date +%Y%m%d%H%M%S`.sql

[root@yz3110 mysqldump]# mysqldump -uroot -p123qwe -S /tmp/mysql3306.sock --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --events --hex-blob > /data0/mysqldump/full_dump_single_trans_`date +%Y%m%d%H%M%S`.sql

恢复数据库实战
1）source命令恢复
mysql> system ls /data0/mysqldump
all_databases_F.sql.gz backup.sh full_dump_20160325111205.sql sales_bak.sql sales_B_bak.sql sales_compact_B_bak.sql
all_databases.sql.gz bak full_dump_single_trans_20160325111415.sql sales_B_bak_gzip.sql.gz sales_cider_08day5.sql.gz sales_customers.sql
mysql> source /data0/mysqldump/sales_B_bak.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.27 sec)

set name gbk;

[root@yz3110 bak]# gzip -d cider.sql.gz
[root@yz3110 bak]# ls *.gz |sed 's#sql.gz##g'
[root@yz3110 bak]# mysql -uroot -p123qwe -S /tmp/mysql3306.sock < cider.sql

查看MySQL变量:
[yongsan@yz3110 ~]$ mysql -uroot -p123qwe -h10.39.3.110 -e 'show variables'

[yongsan@yz3110 ~]$ mysql -uroot -p123qwe -h10.39.3.110 -e 'show variables' |grep log_bin
log_bin ON
log_bin_trust_function_creators ON
sql_log_bin ON

查看MySQL状态(global查看全局状态):
[yongsan@yz3110 ~]$ mysql -uroot -p123qwe -h10.39.3.110 -e 'show global status'

[yongsan@yz3110 ~]$ mysql -uroot -p123qwe -h10.39.3.110 -e 'show global status' |grep sel
Com_insert_select 0
Com_replace_select 0
Com_select 1057

重启后失效：
mysql> set global key_buffer_size=1024*1024*128;

mysql> set log_bin=False;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable

mysqlbinlog 解析mysqlbinlog日志

mysqlbinlog日志：
[yongsan@yz3110 mysql3306]$ sudo cat mysql-bin.index
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
./mysql-bin.000010
./mysql-bin.000011
./mysql-bin.000012
./mysql-bin.000013
./mysql-bin.000014
./mysql-bin.000015
./mysql-bin.000016
./mysql-bin.000017
./mysql-bin.000018
./mysql-bin.000019
./mysql-bin.000020
./mysql-bin.000021
./mysql-bin.000022
./mysql-bin.000023
./mysql-bin.000024
./mysql-bin.000025
./mysql-bin.000026
./mysql-bin.000027
./mysql-bin.000028
./mysql-bin.000029
./mysql-bin.000030

作用：
用来记录mysql内部增删改查等对mysql有更新的内容的记录

-d 分库：指定数据库恢复
[root@yz3110 mysql3306]# mysqlbinlog -d sales mysql-bin.000030 > sales.sql

vim diff file1 file2

指定恢复位置：
[root@yz3110 mysqldump]# mysqlbinlog /data0/mysqldata/mysql3306/mysql-bin.000030 --start-position=6469 --stop-position=20943 -r pos.sql

指定恢复时间：
[root@yz3110 mysqldump]# mysqlbinlog /data0/mysqldata/mysql3306/mysql-bin.000030 --start-datetime='2016-03-25 14:12:00' --stop-datetime='2016-03-25 14:30:39' -r time.sql