mysql备份与恢复实战_MySQL备份和恢复实战

MySQL备份和恢复实战

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.查看数据内容

[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

范例二

参数：-u  –p -B库名

1.备份

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

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 

范例三

参数：-u  –p -B库名gzip1.备份

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

[root@localhost opt]# ll

total 16

-rw-r--r--. 1 root root2007Aug 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

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

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