MySQL 系列（四）备份恢复方案生产环境实战

1>    语法：mysqldump –u 用户名 –p 数据库名> 备份的数据库名


2>    备份nick_defailt数据库，查看内容。
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt >/opt/mysql_nick_defailt.bak
Enter password:
[root@localhost ~]# egrep -v "#|\*|--|^$" opt/mysql_nick_defailt.bak
DROP TABLE IF EXISTS `oldsuo`;
CREATE TABLE `oldsuo` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES `oldsuo` WRITE;
INSERT INTO `oldsuo` VALUES (2,'索宁',0,NULL),(3,'索尼',0,NULL),(4,'底底',0,NULL);
UNLOCK TABLES;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `qq` varchar(15) DEFAULT NULL,
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `suo` int(4) DEFAULT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  `sex` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL);
UNLOCK TABLES;


3>    启用压缩备份数据库
[root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz
Enter password:
[root@localhost ~]# ll opt/
总用量 28
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sq2
-rw-r--r--. 1 root root 2109 10月 24 16:36 data_bak.sql
-rw-r--r--. 1 root root 1002 10月 27 11:55 mysql_nick_defailt.bak
-rw-r--r--. 1 root root 1002 10月 27 11:56 mysql_nick_defailt.bak.gz
-rw-r--r--. 1 root root 3201 10月 27 11:46 mysql_nick_defailt_B.bak
drwxr-xr-x. 2 root root 4096 11月 22 2013 rh
-rw-r--r--. 1 root root 1396 10月 24 16:11 student_bak.sql


4>    恢复nick_defailt数据库
[root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak
Enter password:
#加-B恢复方法
[root@localhost ~]#  mysql -uroot -p  </opt/mysql_nick_defailt_B.bak
Enter password:


5>    总结
1、    备份用-B参数。增加use db,和create database的信息。
2、    用gzip对备份的数据压缩。
复制

#多个数据库名中间加空格
[root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz
Enter password:
复制

1>    语法：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak
Enter password:


2>    语法：mysqldump -u 用户名 -p 数据库名 表名1  表名2  > 备份的文件名
[root@localhost ~]# mysqldump -uroot -p  nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak
Enter password:
复制

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

1.    开启MySQL数据库log-bin参数记录binlog日志。
[root@localhost 3306]# grep log-bin data/3306/my.cnf
log-bin = /data/3306/mysql-bin
2.    存在数据库全备。
复制

# 进行数据库全备，（生产环境还通过定时任务每日凌晨执行）
mysqldump -uroot -pnick -S data/3306/mysql.sock --default-character-set=gbk --single-transaction -F -B nick |gzip >/server/backup/mysql_$(date +%F).sql.gz
# innodb引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A -B |gzip >$DATA_FILE
# myisam引擎备份
mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FIL
复制

# 通过防火墙禁止web等应用向主库写数据或者锁表。让主库暂时停止更新，然后再进行恢复。
# 误操作删除nick库！


1.    检查全备及binlog日志
[root@localhost 3306]# cd /server/backup/
[root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz
[root@localhost backup]# vim mysql_2015-10-31.sql
[root@localhost backup]# grep -i "change" mysql_2015-10-31.sql


2.    立即刷新并备份出binlog
[root@localhost 3306]# mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs
[root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/
#误操作log-bin,倒数第二
[root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql    #导出为.sql格式。
[root@localhost backup]# vim bin.sql
找到语句drop database nick删除！！！（误操作语句）


3.    恢复
[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock <mysql_2015-10-31.sql        #恢复之前的数据库全备
[root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql
#恢复删除误操作语言的bin-log。


# 搞定！！！
复制