mysql 1556_mysql数据库的备份和恢复

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

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

mysql

-uroot -p123456 -e"show databases;"  | grep -Evi

"infor|perfor|database|mysql" |sed "s#^#mysqldump -uroot -p123456 -B

>/kong/#g"|bash

-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 -v "Database|information_schema" |sed 's#^#mysqldump -uroot -p123456 -B  --events |gzip >/back/#' |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

#如果在备份的时候没有添加-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}'截取数据库名

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

问题：

mysqldump -uroot -p123456  -A  -B --master-data=1 mysql >/opt/mysql.back

#备份时不要在指定-A(备份整个数据库) 是再次指定库名；

报错：mysqldump: Got error: 1556: You can't use locks with log tables.

命令找不到

查看： which mysqldump

修改PATH变量的位置-范例：export PATH=/application/mysql/bin:$PATH

. /etc/profile

echo $PATH 即可！