mysql数据库的备份和恢复

ewrfdsfwqe

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 </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}'截取数据库名
-------------------------------------------------------------------------------------------