mysql数据库的备份和恢复

浏览数：23 / 时间：2015年06月12日

mysql5.5

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

语法：

备份

mysqldump -u 用户名 -p 密码数据库名、表名 > 备份的位置

范例： mysqldump -uroot -p‘123456‘ test>/opt/mysql_bak.sql

查看：egrep -v "#|\*|^$|--" /opt/mysql_bak.sql

备份单个表：mysqldump -u用户 -p密码数据库名表名>位置

备份多个表：mysqldump -u用户 -p密码数据库名表1 表2 >位置

范例： mysqldump -uroot -p123456 test test_1>/back/sql

意义：如果备份时把所有数据库都备份成一个数据文件的话，恢复某一个库的数据时比较麻烦

####备份就是以数据库实际语句导出；如果是乱码，是因为导出的格式没加字符集，一般恢复到数据库里就会正常，只是系统外查看不正常而已。另外insert是批量插入的的方式。这样恢复时的效率就很高。###### 多实例的话就是指定sock的然后备份。

工作中的使用

innodb 推荐使用

mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction --events |gzip >/opt/mysql.all.gz

myisam

mysqldump -uroot -p123456 -A -B --master-data=2 -x --events |gzip >/opt/mysql.all.gz

如果是混合引擎以innodb为主；

自带备份命令mysqldump中常见的参数：

-A 备份整个数据库；注意添加：--events；

-B 增加创建数据库和连接数据库的命令；指定多个库备份；

#实际就是增加use db和create database db的信息，在备份表的时候不要添加-B选项；

|gzip 备份数据库时压缩;提高压缩的效率；

范例：

备份单库test并压缩范例：

mysqldump -uroot -p123456 -B test|gzip >/kong/mysql.back.gz

备份多个分库并压缩范例：

-d 备份数据库表结构；

-F刷新binlog日志

范例：mysqldump -uroot -p123456 -A -B -F --events >/opt/mysql.back

#如备份没有指定-F ，当恢复的时候，会无法写入数据，因为不知道从哪里写入数据，原有一部分数据已存在时会冲突；

#使用mysqlbinlog 查看在mysql/data下的binlog，（需要提前在my.cnf文件中打开binlog）

#binlog的记录是通过位置点（文件大小）或者时间来记录的

-t 只备份数据；

-l只读锁表；

-x ，--lock-all-tables 锁表，所有的人都不可以操作，保持数据的一致性；

--compact，去掉注释，适合debug调试时减少输出；

--master-data，增加binlog日志文件名及对应的位置点

--master-data=1

--master-data=2 备份时加入注释

--defult-character-set=指定字符集导出：

范例：

mysqldump -ukong -p‘123456‘ --defult-character-set=latin1 test>/opt/mysql_bak1.sql

--single-transaction适合innodb事物数据库备份，保证数据的一致性；

#就是一个隔离级别，备份时其它用户提交的数据是它是不进行处理的

备份多个库不使用shell的步骤：

1、利用-e 在命令行显示出都有哪些数据库：

mysql -uroot -p123456 -e "show databases;"

2、使用egrep过滤掉不需要备份的库；

3、利用sed的替换功能，在库名前添加mysqldump的备份命令；

范例：sed "s#^#mysqldump -uroot -p123456 -B -A# #^#g"

sed -r "s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1 >/opt/\1.sql#g"

4、然后传递给bash执行即可；

mysql -uroot -p123456 -e "show databases;" | egrep -iv "Database|information_schema" |sed -r "s#^([a-z].*$)#mysqldump -uroot -p123456 --events -B \1 >/opt/\1.sql#g" | bash

傻瓜式shell备份：

for dbname in ` mysql -uroot -p123456 -e "show databases" | egrep -v "Database|information_schema" `
do
mysqldump -uroot -p123456 -B --events $adbname |gzip >/back/${dbname}_back.sql.gz
done

分库备份：

#!/bin/bash
USER=root
PASSWD=123456
MYSQLCMD="mysql -u$USER -p$PASSWD "
MYDUMP="mysqldump -u$USER -p$PASSWD -B |gzip"
for database in test mysql name

#for database in `$MYSQLCMD -e "show databases;"|sed ‘2,4p‘`

do
$MYDUMP $database >/back/${database}_$(date +%F).sql.gz
done

注意：变量后边有多余内容的时候，要使用{}号括起来，例如：${dbnamel}_back.sql.gz

分表备份：

#!/bin/bash
USER=root
PASSWD=123456
MYSQLCMD="mysql -u$USER -p$PASSWD "
MYDUMP="mysqldump -u$USER -p$PASSWD "
for database in `$MYSQLCMD -e "show databases;"|egrep -v "Database|information_schema|mysql"`
do
for tables in `$MYSQLCMD -e "show tables from $database;" |sed "1d"`
do
        mkdir /back/${database} -p
        $MYDUMP $database $tables |gzip >/back/${database}s_${tables}_$(date +%F).sql.gz
        done
done

恢复

1、 source 命令（需登录到数据库里）：

查看数据备份的位置: system ls /kong/

恢复：source 备份文件的位置

恢复: mysql -uroot -p123456 </kong/mysql

#如果在备份的时候没有添加-B 选项，在恢复的时候需指定恢复数据库的名。

#如果是压缩文件，需gzip -d解压备份的数据库（会把原文件删除），接着直接恢复即可

批量恢复：

傻瓜恢复范例：

for dbname in `ls *.sql | awk -F . ‘{print $1}‘`; do mysql -uroot -p123456 < ${dbname}.sql ;done

#ls *.sql | awk -F . ‘{print $1}‘截取数据库名

-------------------------------------------------------------------------------------------