MySQL备份和恢复实战

MyISAM数据表备份之mysqlhotcopy

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

1)介绍

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

2)特点

           a:文件系统级别的copymysqldump则是数据库端的SQL语句集合

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

           cmysqldumpmysqlhotcopy都执行lock tablesunlock tables

3)备份

           amysqlhotcopy-u admin -p 'jidian123'  --addtodest  virtual1 virtual 2 > /tmp

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

4)还原

           a:停止数据库

           bcopy  -rp /tmp/virtual    /mydata55/data

           c:启动数据库        

 

2mysqldump范例

范例一

参数:-u  –p  库名

  1. 1.          备份

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

 

2.查看数据内容

[root@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

 

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

[root@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. 1.          备份

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

 

[root@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

 

 

 

 

2mysqldump多库备份

法一:

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

http://edu.51cto.com/course/course_id-808.html

 

 

 

 

 

3mysqldump多个参数 *****

参数:

-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