MySQL备份和恢复实战

23htg

MyISAM数据表备份之mysqlhotcopy

数据表为myisam引擎的备份。可以使用mysqlhotcopy和mysqldump工具进行备份。

1）介绍

           这个工具是一个Perl语言写的脚本。使用mysqlhotcopy必须安装perl-DBD-MySQL、perl-DBD。

2）特点

           a：文件系统级别的copy，mysqldump则是数据库端的SQL语句集合

           b：只能运行在数据库目录所在的机器上，mysqldump则任何机器都可以。

           c：mysqldump和mysqlhotcopy都执行lock tables和unlock tables

3）备份

           a：mysqlhotcopy-u admin -p 'jidian123'  --addtodest  virtual1 virtual 2 > /tmp

                     --addtodest：指当备份存在时，不中断当前备份，只添加新文件进去

4）还原

           a：停止数据库

           b：copy  -rp /tmp/virtual    /mydata55/data

           c：启动数据库        



2、mysqldump范例

范例一

参数：-u  –p  库名

    1.          备份

mysqldump -uadmin -p'jidian123' virtual>/opt/virtual.sql



2.查看数据内容

[iyunv@localhost mydata55]# egrep  -v"#|--|^$|\*" /opt/virtual.sql

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` int(11)NOT NULL,

  `name`varchar(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `user` WRITE;

INSERT INTO `user` VALUES (1,'summer'),(1,'于江磊');

UNLOCK TABLES;



3.导入数据

Mysql –uadmin –p –S /mydata55/mysql.sock  virtual </opt/virtual.sql



范例二

参数：-u  –p -B  库名

1.备份

mysqldump -uadmin -p'jidian123' -B virtual>/opt/virtual_B.sql



    2.          查看virtual.sql 和virtual_B.sql两个文件的区别，得出-B作用

[iyunv@localhost opt]# diff virtual.sql  virtual_B.sql

18a19,26

> -- Current Database: `virtual`

> --

>

> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `virtual` /*!40100 DEFAULTCHARACTER SET utf8 */;

>

> USE `virtual`;

>

> --

50c58

< -- Dump completed on 2015-08-18 23:07:17

---

> -- Dump completed on 2015-08-18 23:07:27

3.导入数据，注意不要加数据库

Mysql –uadmin –p –S /mydata55/mysql.sock  </opt/virtual_B.sql



范例三

参数：-u  –p -B  库名  gzip

    1.          备份

mysqldump -uadmin -p'jidian123' -B  virtual |gzip >/opt/virtual_B.sql.gz



[iyunv@localhost opt]# ll

total 16

-rw-r--r--. 1 root root 2007Aug 18 23:07 virtual_B.sql

-rw-r--r--. 1 root root  761 Aug 18 23:38virtual_B.sql.gz

-rw-r--r--. 1 root root 3308 Aug 18 23:27virtual_null.sql

-rw-r--r--. 1 root root 1861 Aug 18 23:07 virtual.sql





小结论：

1.导出数据用-B参数

-B：导出多个库、增加建库语句、增加use 语句



2.压缩数据gzip









2、mysqldump多库备份

法一：

mysql -u admin-p'jidian123' -S /mydata55/mysql.sock  -e"show databases" | grep -Eiv "informat|perform|database" |sed -r 's#^([a-z].*$)#mysqldump -uadmin -p'jidian123' --events -B  \1|gzip >/opt/bak/\1.sql.gz#g'|bash





法二：

For循环

金庸新著 $dbname_bak.sql

#!/bin/bash

for dbname in `mysql -u admin -p'jidian123' -S/mydata55/mysql.sock  -e "showdatabases;" | grep -Eiv "informat|perform|datab"`

do

mysqldump -uadmin -p'jidian123' -S/mydata55/mysql.sock  --events -B  $dbname | gzip > ${dbname}_sql.gz



引深：

多表备份：

#!/bin/bash

for tbname in `mysql -u admin -p'jidian123' -S/mydata55/mysql.sock  -e "usemysql;show tables"`

do

mysqldump -uadmin -p'jidian123' -S/mydata55/mysql.sock  mysql  $tbname |gzip >/opt/tb/${tbname}_sql.gz

done



线上多order表备份脚本：(1)

#!/bin/bash

#订单表备份，共24张表。

#

DE=`date +%F`

DT=`date+%F_%T`

Directory="/home/bak/order"

Host='192.168.0.140'

User="virtual"

DB="virtual"

Pass="V#!2707GIcsSingulax201566"

if [  -d $Directory ];then

   echo "备份目录存在，不用重复建立！"

else

   mkdir $Directory -p

fi

cd$Directory  &&  mkdir $DE

for  tbname in `mysql  -h $Host -u$User  -p$Pass -e "use $DB;show tables;" | egrep  -Ei  "^tb_orders$|*.[0-9]$"`

do

        mysqldump -h $Host -u$User  -p$Pass $DB  $tbname |gzip >$Directory/$DE/${DT}_${tbname}.sql.gz

done



线上多other表备份脚本：(2)

#!/bin/bash

#备份所有非订单表，共49张。

DE=`date +%F`

DT=`date+%F_%T`

Directory="/home/bak/other"

Host='192.168.0.140'

User="virtual"

DB="virtual"

Pass="V#!2707GIcsSingulax201566"

if [ ! -d$Directory ];then

   mkdir $Directory  -p

fi

cd$Directory  &&  mkdir $DE

for  tbname in `mysql  -h $Host -u$User  -p$Pass -e "use  $DB ;show  tables;" | egrep  -iv '^tb_orders_h_[0-9]|^tb_orders$|^Tables_in_virtual$|^orders_view$'`

do

        mysqldump -h $Host -u$User  -p$Pass $DB  $tbname |gzip >$Directory/$DE/${DT}_${tbname}.sql.gz

done












3、mysqldump多个参数 *****

参数：

-u

–p

-A    --all-databases

-B    导出多个数据库，sql文件包含CREATE DATABASE and USE statements

-F    --flush-logs

gzip

-d     只导结构

-t      只导数据

-x     --lock-all-tables

--single-transaction  保证mysqldump进程看到的数据，是落地的。数据完整性

--master-data=1|2    1代表pos点不注释，2代表

   

-E    --events

---triggers

-R    --routines

--default-character-set  导出指定字符集

--flush-privileges       刷新权限



生产环境：

提示：

1.加-A不用加-B

2.

for MyISAM:

mysqludmp-uadmin -p'jidian123' -A --flush-privileges -x  --master-data=1 \

-F --events  --triggers --routines  >bak.sql



for InnoDB:

mysqldump -uroot-p'jidian123' -A --flush-privileges --single-transaction \

--master-data=1  --flush-logs --events --triggers --routines  >bak.sql