首页 > 代码库 > mysql数据库备份
mysql数据库备份
前一段时间因为误操作删除了一张表的几条数据，弄得很尴尬，正好这周有空就折腾了下数据备份的知识，现把mysql的数据备份相关实践和心得总结如下：
一.使用mysqldump命令备份数据库：
备份整个数据库（包括表结构和数据）,用法如下
mysqldump -h10.38.14.143 -uroot -prootroot weixin > weixin.sql
其中 weixin 是数据库名，可以一次备份多个数据库 (用空格隔开)，默认保存在当前目录下
这个wexin.sql文件里面主要由四部分组成：数据库及操作系统的信息、表结构、具体的数据（实际上是insert语句）、锁表以及释放表的命令语句
补充：
1.读锁定：本人和其他人只能读取数据库
写锁定：只有本人能进行增删改查，其他人不能进行任何操作
2.有时用mysqldump命令会提示没权限去操作数据库相关的提示语 如 mysqldump: Got error: 1044: Access denied for user ‘php‘@‘10.38.%‘ to database ‘cms_fgw‘ when using LOCK TABLES
这是因为mysql数据库限制了远程服务器对他的相关操作，请和DBA沟通开放权限(主要是执行下面类似的命令去授权)
grant all privileges on *.* to ‘root‘@‘%‘ identified by ‘123456‘ with grant option;
flush privileges;
3.如若忘记mysql命令的一些用法可以多多使用？操作,相当于man帮助，这个很有用，如下
4.用mysqldump也可以只备份表结构，用法如下
mysqldump -h10.38.14.143 -uroot -prootroot --no-data --databases weixin cjdaily > /tmp/table_structure.sql
table_structure.sql中的部内容如下
5.可以根据mysqldump命令进行一些扩展写一个备份数据库的shell脚本，如下是根据我们自己的需求写的一个脚本（shell脚本的具体语法就不讲了）
#!/bin/bash
#backup database
if [ $# -ge 1 ] ; then
databaselist=$@
else
databaselist="cjdaily cms_rmrb guangzhou pladaily"
fi
for database in $databaselist
do
if [ ! -e /home/pengyudi/backup_databases/$database ]
then
mkdir -p /home/pengyudi/backup_databases/$database
fi
mysqldump --opt -h10.38.10.5 -uchen -pab#@c-123 $database |gzip > /home/pengyudi/backup_databases/$database/$database$(date +"%Y-%m-%d %H:%M:%S").sql.gz
if [ $? -eq 0 ]
then
echo "$(date +"%Y-%m-%d %H:%M:%S") Database ---$database--- Backup Successfully \r\n"
find /home/pengyudi/backup_databases/$database/ -name $database\* -ctime +1 -exec rm -rf {} \;
else
echo "$(date +"%Y-%m-%d %H:%M:%S") Database ---$database--- Backup unsuccessfully,please check out the reason\r\n"
fi
done
6.备份某台主机上的所有数据库 mysqldump --opt -h10.38.10.5 -uchenliangliagasd -pabcdeqasdadfa -1238 --all-databases> test.dump
二.数据的恢复
1.切换到mysql的工作环境
use test;
source /tmp/table_structure.sql ;
2.不切换到mysql环境
/usr/bin/mysql -h192.168.4.47 -um_bbs_test_admin -P3309 -p89603d5a test</tmp/table_structure.sql ; (注意得指定具体的数据库)
三.使用load导入数据 和select **** from tabelName into outfile ‘file_name‘的方式导出数据
补充
1.注意保存数据的文件要打引号以及保存数据的文件路径，要特别留意一些细节，如上，确实很容易出错
select * from weixin_data_rmrb into outfile ‘weixin_outfile.sql‘;
2.使用技巧：
当用load命令导入数据时可以通过适当数据提高导入速度，对于MyISAM储存引擎的表
(1)可以通过以下方式快速导入大量数据(disable keys 和enable keys 可以用来打开或关闭非唯一索引的更新，提高导入速度，但是对InnoDB表无效)
alter table weixin_data_rmrb disable keys
load data infile ‘weixin_outfile.sql‘ into table weixin_data_rmrb
alter table weixin_data_rmrb enable keys
(2)关闭唯一性校验可以提高导入速度
在导入数据前后分别执行 set unique_checks=0（关闭唯一性校验） 和 set unique_checks=1来提高导入速度
对于Innodb表可以使用一下方式提高导入速度：
(1)将导入的数据按照主键的顺序排列，可以提高导入速度，因为Innodb表是按照主键顺序保存的
(2)在导入之前执行set autocommit=0关闭自动提交事物，导入结束后执行set autocommit=1回复自动提交事物
四.使用二进制日志恢复数据
1.开启binlog日志
修改/etc/my.cnf配置文件
vim /etc/my.cnf
在[mysqld]下面添加如下两行用于开启及设置binlog日志的保存路径
log-bin=/home/logs/mysql/mysql-bin #记得要修改权限，不然mysql无法启动; mysql-bin为日志文件的名称的组成部分(mysql-bin.000001)
binlog_format=mixed
log=/home/logs/mysql/mysql.log
保存退出
chown mysql.mysql /home/logs/mysql -R #修改目录权限
其中mysql-bin.index记录了生成了哪些二进制日志文件
另外跟日志相关的参数：
expire_logs_days = 7 #保留七天的日志
slow-query-log = on #开启慢查询
slow_query_log_file = /home/logs/mysql/slow.log #慢查询日志保存路径
long_query_time = 1 #慢查询的时间，超过一秒的记录下来
log-queries-not-using-indexes = on #记录没用使用到索引的SQL语句
修改配置后重启mysql：service mysqld restart
进入到mysql命令界面查看二进制日志相关信息：
show variables like "%bin%"
2.与binlog日志有关的命令
flush logs: 执行完之后在存放binlog日志的目录下多一个最新的binlog日志文件
show master status 查看最后一个bin日志
reset master 清空所有的binlog日志
通过binlog日志恢复到某一个日志文件的操作命令：
/usr/bin/mysqlbinlog --no-defaults -f /home/logs/mysql/mysql-bin.000008| mysql -h10.38.14.143 -uroot -prootroot
查看某个binlog日志命令： /usr/bin/mysqlbinlog --no-defaults -f /home/logs/mysql/mysql-bin.000008
说明：可以根据需要恢复到某一个点的数据，mysqlbinlog后面的主要参数有：
--stop-position="120"
--start-positon="20"
--stop-date="2016-11-19 18:50:42"
--start-date="2016-11-19 18:30:21"
友情链接：
binlog详细的操作例子请参考如下博客：
http://blog.chinaunix.net/uid-20494084-id-3753682.html
mysql5.7官方文档地址http://dev.mysql.com/doc/refman/5.7/en/preface.html
mysql数据库备份