mysql的備份與恢複

標籤：

mysql的備份和恢複：

[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 sales > /data0/mysqldump/sales_bak.sql
[[email protected] mysqldump]# egrep -v "#|\*|--|^$" /data0/mysqldump/sales_bak.sql

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "use sales; drop table vendors"
[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "use sales; show tables;"
+-----------------+
| Tables_in_sales |
+-----------------+
| PRODUTCUSTOMERS |
| customers |
| orderitems |
| orders |
| producnotes |
+-----------------+

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe sales < /data0/mysqldump/sales_bak.sql
[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "use sales; show tables;"
+-----------------+
| Tables_in_sales |
+-----------------+
| PRODUTCUSTOMERS |
| customers |
| orderitems |
| orders |
| producnotes |
| vendors |
+-----------------+

[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales > /data0/mysqldump/sales_B_bak.sql
[[email protected] mysqldump]# diff sales_bak.sql sales_B_bak.sql
18a19,26
> -- Current Database: `sales`
> --
>
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sales` /*!40100 DEFAULT CHARACTER SET utf8 */;
>
> USE `sales`;
>
> --
172a181,186
> -- Current Database: `sales`
> --
>
> USE `sales`;
>
> --
200c214
< -- Dump completed on 2016-03-24 18:03:03
---
> -- Dump completed on 2016-03-24 18:20:27

-B選項就是增加建立和串連資料庫的選項

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe sales < /data0/mysqldump/sales_bak.sql
ERROR 1049 (42000): Unknown database ‘sales‘

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe < /data0/mysqldump/sales_B_bak.sql
[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "use sales; show tables;"
+-----------------+
| Tables_in_sales |
+-----------------+
| PRODUTCUSTOMERS |
| customers |
| orderitems |
| orders |
| producnotes |
| vendors |
+-----------------+

最佳化輸入內容的大小，讓容量更小，適合調試
[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 --compact -B sales > /data0/mysqldump/sales_compact_B_bak.sql

備份壓縮（壓縮效率將近3倍）
[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales|gzip > /data0/mysqldump/sales_B_bak_gzip.sql.gz
[[email protected] mysqldump]# ll
total 28
-rw-r--r-- 1 root root 7266 Mar 24 18:03 sales_bak.sql
-rw-r--r-- 1 root root 1780 Mar 24 18:47 sales_B_bak_gzip.sql.gz
-rw-r--r-- 1 root root 7456 Mar 24 18:20 sales_B_bak.sql
-rw-r--r-- 1 root root 4511 Mar 24 18:35 sales_compact_B_bak.sql

mysqldump的工作原理：

利用mysqldump命令備份資料的過程，實際上就是把資料從mysql庫裡以邏輯的sql語句形式直接輸出或者生產備份檔案的過程，就這麼簡單

備份多個庫
[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales cider 08day5 |gzip > /data0/mysqldump/sales_cider_08day5.sql.gz

 

 

mysqldump的工作原理

利用mysqldump命令備份資料的過程，實際上就是把資料從mysql庫裡以邏輯的sql語句形式直接輸出或者生產備份檔案的過程，就這麼簡單

備份多個庫
[[email protected] mysqldump]# mysqldump -uroot -p123qwe -h10.39.3.110 -B sales cider 08day5 |gzip > /data0/mysqldump/sales_cider_08day5.sql.gz

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5" |sed "s#^#mysql -uroot -h10.39.3.110 -p123qwe -B#g"
mysql -uroot -h10.39.3.110 -p123qwe -Bcider
mysql -uroot -h10.39.3.110 -p123qwe -Bkeystone
mysql -uroot -h10.39.3.110 -p123qwe -Bsales

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5" |sed -r "s#^([a-z].*$)#mysqldump -uroot -h10.39.3.110 -p123qwe -B \1|gzip > /data0/\1.sql.gz#g"
mysqldump -uroot -h10.39.3.110 -p123qwe -B cider|gzip > /data0/cider.sql.gz
mysqldump -uroot -h10.39.3.110 -p123qwe -B keystone|gzip > /data0/keystone.sql.gz
mysqldump -uroot -h10.39.3.110 -p123qwe -B sales|gzip > /data0/sales.sql.gz

[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5" |sed -r "s#^([a-z].*$)#mysqldump -uroot -h10.39.3.110 -p123qwe -B \1|gzip > /data0/mysqldump/bak/\1.sql.gz#g"|bash

[[email protected] mysqldump]# ls /data0/mysqldump/bak/
cider.sql.gz keystone.sql.gz sales.sql.gz

方法1：
[[email protected] mysqldump]# mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5" |sed -r "s#^([a-z].*$)#mysqldump -uroot -h10.39.3.110 -p123qwe --events -B \1|gzip > /data0/mysqldump/bak/\1.sql.gz#g"|bash

方法2：
for迴圈備份
for data_name in `mysql -uroot -h10.39.3.110 -p123qwe -e "show databases"|grep -Evi "database|information_schema|performance_schema|mysql|test|08day5
"`;do
mysqldump -uroot -h10.39.3.110 -p123qwe -B $data_name|gzip > /data0/mysqldump/bak/${data_name}.sql.gz
done

 

備份表：
第一個是庫，後面可以多個表
[[email protected] mysqldump]# mysqldump -uroot -h10.39.3.110 --compact -p123qwe sales customers > sales_customers.sql

mysql的備份與恢複