MySQL數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)備份與恢復(fù)方法

本篇文章給大家主要講的是關(guān)于MySQL數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)備份與恢復(fù)方法的內(nèi)容，感興趣的話就一起來(lái)看看這篇文章吧，相信看完MySQL數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)備份與恢復(fù)方法對(duì)大家多少有點(diǎn)參考價(jià)值吧。

MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù)

mysqdump工作原理

         利用mysqldump命令備份數(shù)據(jù)的過(guò)程，實(shí)際上就是把數(shù)據(jù)從mysql庫(kù)里一邏輯的sql語(yǔ)句的形式直接輸出或生成備份的文件的過(guò)程。

備份單個(gè)數(shù)據(jù)

mysql數(shù)據(jù)庫(kù)自帶了一個(gè)很好用的備份命令，就是mysqldump基本使用如下

語(yǔ)法：mysqldump -u 用戶名 -p 數(shù)據(jù)庫(kù)名  > 備份的文件名

1、備份數(shù)據(jù)并恢復(fù)：

mysqldump -uroot -p radius > /opt/mysql_bak.sql

還原數(shù)據(jù)庫(kù)

mysql -uroot -p radius </opt/mysql_bak.sql

2、指定拉丁字符集備份數(shù)據(jù)庫(kù)

mysqldump -uroot -p --default-character-set=latin1 radius > /opt/mysql_bak.sql

還原數(shù)據(jù)庫(kù)

mysql -uroot -p radius </opt/mysql_bak.sql

3、備份時(shí)加參數(shù)（-B）     #會(huì)在備份時(shí)增加創(chuàng)建數(shù)據(jù)庫(kù)的連接數(shù)據(jù)的語(yǔ)句

mysqldump -uroot -p -B radius > /opt/mysql_bak_B.sql

還原數(shù)據(jù)庫(kù)

mysql -uroot -p  </opt/mysql_bak_B.sq

4、壓縮備份(用gzip對(duì)備份的數(shù)據(jù)壓縮)

mysqldump -uroot -p -B radius|gzip >/opt/mysq_bak.sql.gz

5、查看備份內(nèi)容

egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

root@solin:~# mysql -uroot -p -e "select id,username,is_superuser,last_login from radius.auth_user;"

Enter password:

+----+----------+--------------+---------------------+

| id | username | is_superuser | last_login          |

+----+----------+--------------+---------------------+

|  1 | root     |            1 | 2016-11-25 03:26:43 |

|  2 | WeiFei   |            0 | NULL                |

+----+----------+--------------+---------------------+

備份多個(gè)庫(kù)

1、同時(shí)備份mysql和radius庫(kù)

mysqldump -uroot -p -B radius mysql|gzip >/opt/mul.bak.sql.gz

提示：-B

      表示連接多個(gè)庫(kù)，并增加use db和create database db的命令信息

2、備份所有的庫(kù)（-A備份所有的庫(kù)）

mysqldump -uroot -p -A -B |gzip >/opt/a.sql.gz

3、利用source命令恢復(fù)數(shù)據(jù)庫(kù)

進(jìn)入數(shù)據(jù)庫(kù)控制臺(tái)，mysql -uroot -p登陸后

msyql>use 數(shù)據(jù)庫(kù)

然后使用source命令，后面參數(shù)為數(shù)據(jù)文件

例:root@solin:/opt# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 251

Server version: 5.6.28-0solin0.15.04.1 (Solin)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

+--------------------+

1 row in set (0.00 sec)

mysql> system ls /opt

a.sql  mysql.sql.gz  radius_t.sql.gz  shop.sql

bak    radius.sql    shop_shop.sql

mysql> source a.sql;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

…

…

…

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show database;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| radius             |

+--------------------+

3 rows in set (0.00 sec)

mysql> \q

Bye

分庫(kù)備份

企業(yè)的數(shù)據(jù)庫(kù)會(huì)有很多庫(kù)，出問(wèn)題可能是某一個(gè)庫(kù)，如果在備份時(shí)把所有的庫(kù)備份成一個(gè)數(shù)據(jù)文件的，恢復(fù)某一個(gè)庫(kù)時(shí)會(huì)較麻煩，這是就用到了分庫(kù)備份。

1、命令行分庫(kù)備份

root@solin:~# mysql -uroot -p -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p -B \1|gzip >/opt/bak/\1.sql.gz#g'|bash

Enter password:

root@solin:~# ls /opt/bak/

mysql.sql.gz  radius.sql.gz

2、for循環(huán)實(shí)現(xiàn)分庫(kù)備份

[root@solin scripts]# cat fenku.sh

#!/bin/sh

for dbname in `mysql -uroot -p'centos' -e "show databases;"|grep -Evi "database|info|perfor"`

do

    mysqldump -uroot -p'centos' --events -B $dbname|gzip >/opt/bak/${dbname}_bak.sql.gz

done

[root@solin scripts]# sh fenku.sh

[root@solin scripts]# ls /opt/bak/

mysql_bak.sql.gz  solin_gbk_bak.sql.gz  solin_utf8_bak.sql.gz  test_bak.sql.gz

分庫(kù)備份還原

[root@solin ~]# gzip -d /opt/bak/*

[root@solin ~]# cd /opt/bak/

[root@solin bak]# ls *.sql|sed 's#_bak.sql##g'

mysql

radius

[root@solin bak]# for dbname in `ls *.sql|sed 's#_bak.sql##g'`;do mysql -uroot -pbdyun < ${dbname}_bak.sql;done

Warning: Using a password on the command line interface can be insecure.

Warning: Using a password on the command line interface can be insecure.

[root@solin bak]#

備份表

1、備份單個(gè)表

語(yǔ)法：mysqldump -u用戶名 -p 數(shù)據(jù)庫(kù)名 表名>備份的文件名

例: mysqldump -u root -p radius shop_shop >/opt/shop_shop.sql

查看結(jié)果

root@solin:~# ls /opt/

bak  mysql.sql.gz  radius.sql.gz  shop_shop.sql

root@solin:~# egrep -v "#|\*|--|^$" /opt/shop_shop.sql

DROP TABLE IF EXISTS `shop_shop`;

CREATE TABLE `shop_shop` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `shopname` varchar(200) COLLATE utf8_unicode_ci NOT NULL,

  `contract_person_qq_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

  `agent` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

  `url` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

……

2、備份多個(gè)表

語(yǔ)法：mysqldump -u用戶名 -p 數(shù)據(jù)庫(kù)名 表名1 表名2 >備份的文件名

例: root@solin:~# mysqldump -uroot -p radius shop_shop shop_shop_admins >/opt/shop.sql

Enter password:

root@solin:~# ls /opt/

bak  mysql.sql.gz  radius.sql.gz  shop_shop.sql  shop.sql

root@solin:~# egrep -v "#|\*|--|^$" /opt/shop.sql DROP TABLE IF EXISTS `shop_shop`;

CREATE TABLE `shop_shop` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `shopname` varchar(200) COLLATE utf8_unicode_ci NOT NULL,

  `contract_person_qq_number` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

  `agent` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

  `url` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

  `address` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,

……

3、分庫(kù)分表備份

mysqdump -uroot -p radius test >radius_test.sql

mysqdump -uroot -p radius test1 >radius_test2.sql

……

注：將上述命令放入一個(gè)腳本里就是腳本分表備份了，當(dāng)然你也可以搜索其他更好的備份腳本，恢復(fù)時(shí)批量恢復(fù)多個(gè)SQl文件

備份數(shù)據(jù)庫(kù)表結(jié)構(gòu)和數(shù)據(jù)

1、mysqldump -d參數(shù)只備份表的結(jié)構(gòu)

root@solin:~# mysqldump -uroot -p -B -d radius >/opt/radius_d.sql.gz

Enter password:

root@solin:~# egrep -v "#|\*|--|^$" /opt/radius_d.sql.gz

USE `radius`;

DROP TABLE IF EXISTS `accounting_authconnectionhistory`;

CREATE TABLE `accounting_authconnectionhistory` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `wlanuserip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `wlanacname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `wlanacip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

  `ssid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

……

2、mysqldump -t參數(shù)只備份表的數(shù)據(jù)

例:root@solin:~# mysqldump -uroot -p -B -t radius >/opt/radius_t.sql.gz

Enter password:

root@solin:~# egrep -v "#|\*|--|^$" /opt/radius_t.sql.gz

USE `radius`;

LOCK TABLES `accounting_authconnectionhistory` WRITE;

UNLOCK TABLES;

LOCK TABLES `accounting_authconnectionuseronlinetimelist` WRITE;

UNLOCK TABLES;

LOCK TABLES `accounting_tempertoryalloweduserlist` WRITE;

UNLOCK TABLES;

LOCK TABLES `accounting_userrecord` WRITE;

UNLOCK TABLES;

LOCK TABLES `adminbd_adpullhistory` WRITE;

UNLOCK TABLES;

……

刷新、切割binlog

1、mysqldump -F參數(shù)刷新、切割binlog

2、mysqldump --master-data=1參數(shù) 找到binlog的文件和位置

mysqldump的關(guān)鍵參數(shù)說(shuō)明

1、-A備份所有庫(kù)

2、-B指定多個(gè)庫(kù)，增加建表語(yǔ)句和use語(yǔ)句

3、-F刷新binlog日志

4、-d只備份表結(jié)構(gòu)

5、-t只備份表數(shù)據(jù)

6、-x提交請(qǐng)求鎖定所有數(shù)據(jù)庫(kù)中的所有表，以保證數(shù)據(jù)的一致性。這是一個(gè)全局讀鎖，并且自動(dòng)關(guān)閉--single-transaction 和--lock-tables 選項(xiàng)。

7、-l --lock-tables 鎖所有的表為只讀

8、--master-data增加binlog日志文件名及對(duì)應(yīng)的位置點(diǎn)

9、--compact去掉注釋，適合調(diào)試輸出（生產(chǎn)環(huán)境不用）

10、--singe-transaction 適合innodb事物數(shù)據(jù)庫(kù)備份

生產(chǎn)場(chǎng)景備份

1、常規(guī)備份

myisam備份:

mysqldump -uroot -p -A -B --master-data=2 -x |gzip >/opt/all.sql.gz

innodb備份:

mysqldump -uroot -p -A -B --master-data=2 --singe-transaction|gzip >/opt/all.sql.gz

2、專業(yè)DBA mysqldump備份演練

以上關(guān)于MySQL數(shù)據(jù)庫(kù)如何實(shí)現(xiàn)備份與恢復(fù)方法詳細(xì)內(nèi)容，對(duì)大家有幫助嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。