溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
下文主要給大家?guī)韨浞菁盎謴?fù)MySQL數(shù)據(jù)庫，希望這些內(nèi)容能夠帶給大家實(shí)際用處，這也是我編輯備份及恢復(fù)MySQL數(shù)據(jù)庫這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。
經(jīng)常有網(wǎng)友問，運(yùn)維工作到底是什么工作，到底做什么？其實(shí)，運(yùn)維工作簡(jiǎn)單的概括就兩件事，第一個(gè)是保護(hù)公司的數(shù)據(jù)，第二個(gè)是網(wǎng)站7*24小時(shí)提供服務(wù)。
而對(duì)于數(shù)據(jù)和網(wǎng)站7*24小時(shí)提供服務(wù)那個(gè)跟重要呢？
這個(gè)具體要看業(yè)務(wù)和公司。
例如：銀行，金融行業(yè)，數(shù)據(jù)是最重要的，一條都不能丟，可能宕機(jī)停機(jī)影響就沒那么大。
再例如：百度搜索，騰訊qq聊天記錄丟失幾萬條數(shù)據(jù)，都不算啥。
對(duì)于數(shù)據(jù)來講，數(shù)據(jù)最核心的就是數(shù)據(jù)庫核數(shù)據(jù)，當(dāng)然也包括其他數(shù)據(jù)，以下主要講解的是數(shù)據(jù)庫的數(shù)據(jù)。
對(duì)于絕大數(shù)企業(yè)來講，時(shí)區(qū)數(shù)據(jù)就時(shí)區(qū)商機(jī)，時(shí)區(qū)產(chǎn)品，時(shí)區(qū)客戶，公司倒閉…….，因此，數(shù)據(jù)庫的備份與恢復(fù)就顯得十分重要了。
備份的兩種方式：
1. 邏輯備份：以SQL語句的形式，將數(shù)據(jù)導(dǎo)出，速度慢，跨平臺(tái)，跨版本，可以跨軟件。
2. 物理備份，直接對(duì)數(shù)據(jù)文件備份，cp tar，速度快，不容易跨平臺(tái)、跨版本?？畿浖?，常用方法： linux備份工具（cp tar）。
3. xtrabackup
mysql數(shù)據(jù)庫自帶了一個(gè)很好用的備份命令，就是mysqldump，它的基本使用如下：
命令語法：mysqldump -u 用戶名-p密碼 數(shù)據(jù)庫名 > 備份的文件名
如下5個(gè)范例目錄：
（1）備份名字為oldboy數(shù)據(jù)庫
（2）解決備份文件亂碼問題
（3）利用上述備份數(shù)據(jù)進(jìn)行回復(fù)測(cè)試
（4）備份時(shí)使用-B參數(shù)和不適用-B參數(shù)的區(qū)別及兩種備份方法。
（5）優(yōu)化使用—compact參數(shù)使備份文件不輸出注釋，優(yōu)化空間。
（6）指定壓縮命令壓縮備份的MySQL數(shù)據(jù)
范例操作如下：
（1）范例1：備份名字為oldboy的庫
1. 備份前查看oldboy數(shù)據(jù)
[root@db01 /]# mysql -U -uroot -poldboy123 -S /data/3306/mysql.sock -------- --------- mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | oldboy | +---------------------+ 6 rows in set (0.00 sec) mysql> use oldboy Database changed mysql> show tables; +------------------+ | Tables_in_oldboy | +------------------+ | student | | test | +------------------+ 2 rows in set (0.00sec) mysql> select * from test; +------+---------+ | id | name | +------+---------+ | 3 | hehe | | 5 | kaka | | 1 |oldboy | | 2 | oldgirl | | 4 | zuma | +------+---------+ 5 rows in set (0.00sec) mysql> quit Bye
2. 在命令行執(zhí)行備份命令
[root@db01 /]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql [root@db01 /]# ll /opt/oldboy_2016-08-28.sql -rw-r--r-- 1 rootroot 2700 Aug 28 23:25 /opt/oldboy_2016-08-28.sql
3. 備份完檢查備份完的數(shù)據(jù)
[root@db01 /]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS`student`; CREATE TABLE `student`( `id` int(4) NOT NULL AUTO_INCREMENT, --------------------------------- INSERT INTO `test`VALUES (3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma'); UNLOCK TABLES;
提示：根據(jù)以上得出的結(jié)果，我們看看到已備份的表結(jié)構(gòu)語句及插入的數(shù)據(jù)整合的sql語句。
（2）范例2：如果出現(xiàn)亂碼指定字符集參數(shù)解決備份亂碼問題
假如備份數(shù)據(jù)庫備份文件出現(xiàn)亂碼情況，用以下參數(shù)解決中文亂碼，過程如下
1. 查看備份的數(shù)據(jù)庫客戶端及服務(wù)端的字符集設(shè)置
[root@db01 opt]#mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show variableslike '%character%'" +--------------------------+--------------------------------------------+ | Variable_name |Value | +--------------------------+--------------------------------------------+ | character_set_client | utf8 | |character_set_connection | utf8 | |character_set_database | utf8 | | character_set_filesystem | binary | |character_set_results | utf8 | |character_set_server | utf8 | |character_set_system | utf8 | |character_sets_dir | /application/mysql-5.5.49/share/charsets/ | +--------------------------+--------------------------------------------+
2. 指定對(duì)應(yīng)的字符集備份，這里為--default-character_set=utf8 （指定字符集參數(shù)）
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock - oldboy>/opt/oldboy_$(date +%F).sql
執(zhí)行結(jié)果：
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy >/opt/oldboy_$(date +%F).sql [root@db01 opt]# egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
提示：如果出現(xiàn)亂碼情況，使用指定支付及查看，就不會(huì)在亂碼了，其實(shí)，即使導(dǎo)出的中文亂碼也沒關(guān)系，導(dǎo)入數(shù)據(jù)庫依然是正常的。
（3）范例2:：利用上述備份好的文件進(jìn)行數(shù)據(jù)恢復(fù)測(cè)試
[root@db01 opt]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock oldboy < /opt/oldboy_2016-08-28.sql [root@db01 opt]#mysql-uroot -poldboy123 -S /data/3306/mysql.sock -e "select * fromoldboy.test;" +--------+-----------+ | id | name | +--------+-----------+ | 3 | hehe | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +--------+-----------+
（4）范例4：備份時(shí)加-B參數(shù)以及使用備份文件恢復(fù)數(shù)據(jù)的兩種方法
1. 查看不加-B參數(shù)備份文件和加-B備份文件區(qū)別
不加-B參數(shù)備份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql
加-B參數(shù)備份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy_B_$(date +%F).sql
使用vimdiff命令比較不加-B參數(shù)配置文件和加-B參數(shù)配置文件區(qū)別
[root@db01 /]# cd /opt/ [root@db01 opt]# vimdiff oldboy_2016-08-28.sql oldboy_B_2016-08-28.sql -- Current Database: `oldboy` -- --
# 結(jié)論：
直觀看，加上-B參數(shù)的作用是增加創(chuàng)建數(shù)據(jù)庫核連接數(shù)據(jù)庫的語句了，我們使用-B的備份文件就可以直接恢復(fù)，而不適用-B的備份文件還需要先創(chuàng)建庫才可以，區(qū)別即如下兩條語句：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` USE `oldboy`;
第一種恢復(fù)數(shù)據(jù)（不加-B參數(shù)的備份數(shù)據(jù)護(hù)膚數(shù)據(jù)）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +---------------------+ 6 rows in set (0.00sec) mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | performance_schema | +----------------------+ 5 rows in set (0.00sec) mysql> quit Bye [root@db01 opt]# [root@db01 opt]#
說明：現(xiàn)在使用沒有帶-B參數(shù)備份的備份文件來恢復(fù)數(shù)據(jù)庫，在上面已經(jīng)跟加-B參數(shù)的備份文件做了對(duì)比，-B參數(shù)備份文件中比不帶-B參數(shù)備份的文件多了創(chuàng)建oldboy數(shù)據(jù)庫核進(jìn)入oldbou數(shù)據(jù)庫，就是說-B參數(shù)的備份文件為我們節(jié)省了創(chuàng)建庫和進(jìn)入庫的兩個(gè)步驟，直接將備份文件導(dǎo)入數(shù)據(jù)庫即可，還不用指定那個(gè)庫，因?yàn)槔锩嬷付耸筼ldboy數(shù)據(jù)庫。
第二種備份文件恢復(fù)數(shù)據(jù)（使用加-B參數(shù)備份文件恢復(fù)）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ [root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | | | performance_schema | +---------------------+
# 說明：在上面已經(jīng)將加-B參數(shù)備份的配置文件和不加-B參數(shù)備份的配置文件進(jìn)行對(duì)比和說明，加上-B參數(shù)在恢復(fù)數(shù)據(jù)時(shí)會(huì)自動(dòng)建庫并恢復(fù)數(shù)據(jù)，不加-B則還得先手動(dòng)建庫，所以這是加-B參數(shù)的好處，如果說詳見一個(gè)數(shù)據(jù)庫的數(shù)據(jù)拷貝到另一個(gè)庫里可以不加-B參數(shù)，到時(shí)候直接將備份文件導(dǎo)入到該庫即可。
提示：-B不僅只有自動(dòng)建庫use庫的一鍵恢復(fù)庫數(shù)據(jù)的應(yīng)用，還可以指定多個(gè)庫
（5）范例5：優(yōu)化備份文件大小減少輸出注釋（debug調(diào)試）
利用mysqldump的—compact參數(shù)優(yōu)化下備份結(jié)果：
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy -----------------------
帶--compact參數(shù)備份
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy --compact >/opt/oldboy_$(date +%F).sql egrep -v '#|\*|--|^$' /opt/oldboy_2016-08-28.sql
--compact參數(shù)說明：
注意：--compact此參數(shù)不在生產(chǎn)環(huán)境使用，用于調(diào)試即可。
（6）范例6：指定壓縮命令壓縮備份的MySQL數(shù)據(jù)
在將數(shù)據(jù)庫數(shù)據(jù)備份時(shí)通過gzip命令壓縮，以節(jié)省磁盤空間
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -B oldboy|>/opt/oldboy_$(date +%F).sql. [root@db01 opt]# ll /opt/ total 24 -rw-r--r-- 1root root 2700 Aug28 23:25 oldboy_2016-08-28.sql -rw-r--r-- 1root root 897 Aug 29 01:55 oldboy_2016-08-29.sql.gz -rw-r--r-- 1root root 2843 Aug28 23:54 oldboy_B_2016-08-28.sql
以上例子小結(jié)：
1. 備份數(shù)據(jù)使用-B參數(shù)，會(huì)在備份數(shù)據(jù)中增加建庫及use庫的語句。
2. 備份數(shù)據(jù)使用-B參數(shù)，使得后面可以直接接多個(gè)庫名。
3. 備份數(shù)據(jù)使用gzip對(duì)備份的數(shù)據(jù)壓縮，可以節(jié)省磁盤空間。
4. debug時(shí)可以用—compact減少輸出，但不用于生產(chǎn)。
5. 指定字符集備份用–default-character-set=字符集（一般不適用）
利用mysqldump命令備份數(shù)據(jù)的過程，實(shí)際上就是把數(shù)據(jù)從mysql庫里以邏輯的sql語句的形式直接輸出或者生成備份的文件的過程。
可否舉一反三，利用這個(gè)備份文件恢復(fù)的時(shí)候的原理？
備份的數(shù)據(jù)過濾掉注釋，都是SQL語句，結(jié)果如下：
[root@db01 opt]# egrep -v '#|\*|--|^$'/opt/oldboy_2016-08-28.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age` tinyint(2)NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; UNLOCK TABLES; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8; LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma'); UNLOCK TABLES;
1. 查看當(dāng)前有幾個(gè)庫
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | 3ooo | | mysql | | oldboy | #<== 備份 | oldboy_gbk | #<== 備份 | performance_schema | +----------------------+ 6 rows in set (0.00 sec)
2 開始使用-B參數(shù)備份多個(gè)庫
mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy_gbkoldboy >/opt/bak_$(date +%F).sql.gz egrep -v '#|\*|--|^$'/opt/bak_2016-08-29.sql.gz
-B參數(shù)說明：該參數(shù)用于導(dǎo)出若干個(gè)數(shù)據(jù)庫，在備份結(jié)果中會(huì)加入CREATE DATABASE `db_name`和USE db `db_name`兩個(gè)mysql數(shù)據(jù)庫命令，-B后的參數(shù)都將被作為數(shù)據(jù)庫名，該參數(shù)比較常用，當(dāng)-B后的數(shù)據(jù)庫列全時(shí)，用-A參數(shù)，-A參數(shù)意思是轉(zhuǎn)儲(chǔ)所有數(shù)據(jù)中的所有表。
分庫備份實(shí)際上就是執(zhí)行一個(gè)備份語句備份一個(gè)庫，如果數(shù)據(jù)庫里面有多個(gè)表，就執(zhí)行多條相同的備份單個(gè)庫的備份語句就可以備份多個(gè)庫了，注意每個(gè)庫都可以對(duì)應(yīng)備份的庫作為庫名，結(jié)尾加sql，
分庫意義： 舉例，有時(shí)一個(gè)企業(yè)數(shù)據(jù)庫里會(huì)有多個(gè)庫，例如（www，bbs，blog），但是出問題時(shí)候的很可能是某一個(gè)庫，如果在備份時(shí)把所有的庫都備份成一個(gè)數(shù)據(jù)文件的話，恢復(fù)某一個(gè)庫的數(shù)據(jù)時(shí)就比較麻煩了。
方法1：使用一條一條的命令備份：
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy>/opt/……… mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy_gbk >/opt/........
方法二：利用命令拼接實(shí)現(xiàn)分庫備份
[root@db01 ~]# mkdir /server/backup [root@db01 ~]# [root@db01 ~]# ls /server/backup/ 3ooo_2016-08-29.sql.gz oldboy_2016-08-29.sql.gz oldboy_gbk_2016-08-29.sql.gz
單標(biāo)備份命令語法：mysqldump-u用戶名 -p密碼 數(shù)據(jù)庫名 表名> 備份的文件名
（1）備份單個(gè)表：以下備份oldboy數(shù)據(jù)庫的test表為例
1. 備份前查看oldboy數(shù)據(jù)庫有那些表
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" +-----------------+ | Tables_in_oldboy| +-----------------+ | student | | test | +-----------------+
2. 開始備份單個(gè)表（test）表
[root@db01 ~]# [root@db01 ~]# egrep -v '#|\*|--|^$'/opt/tables.sql DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, `age`tinyint(2) NOT NULL DEFAULT '0', `dept`varchar(16) DEFAULT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`), KEY`index_name_dept` (`name`,`dept`), KEY`index_123` (`name`,`dept`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `student` WRITE; UNLOCK TABLES;
（2）一條命令分表備份：
數(shù)據(jù)庫分表備份其實(shí)就是一條一條的備份，但是又時(shí)候一條命令一條命令麻煩，可以使用一條命令將多個(gè)數(shù)據(jù)庫表備份，這樣更簡(jiǎn)潔，以下將student和test表備份使用一條命令，具體命令如下：
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" #<== 備份前查看當(dāng)前oldboy數(shù)據(jù)庫中有哪些表 +------------------+ | Tables_in_oldboy | +------------------+ | student | | test | +------------------+ - [root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;"|egrep -v"Tables_in_oldboy"|sed -r 's#(.*)#mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy \1 >/opt/\1_tables.sql #g' mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboystudent >/opt/student_tables.sql mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/test_tables.sql [root@db01 ~]# [root@db01 ~]# ll -rt /opt/|tail -2 -rw-r--r-- 1root root 2010 Aug 29 12:18 student_tables.sql -rw-r--r-- 1root root 1941 Aug 29 12:18 test_tables.sql
分表備份的缺點(diǎn)
1.被一個(gè)完整全被，在做一個(gè)分庫分表備份。
2. 腳本批量恢復(fù)SQL文件
面試題：多個(gè)庫或者多個(gè)表備份到一塊，如何恢復(fù)單個(gè)庫或者表？
解答：
a. 第三方測(cè)試庫，導(dǎo)入到庫里，然后把需要的備份出來，最后恢復(fù)到正式庫里。
b. 單表：grep 表名 bak.sql> 表名.sql
單庫：循環(huán)過濾庫里所有表，grep表名 bak.ssql > 表名.sql（多個(gè)表數(shù)據(jù)）
c. 實(shí)現(xiàn)分庫分表備份。
以下備份oldboy數(shù)據(jù)庫的多個(gè)表
1.查看oldboy數(shù)據(jù)庫中的表，命令如下：
mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showtables from oldboy;"
2. 備份oldboy數(shù)據(jù)庫中的student、test兩個(gè)表，命令如下
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock >/opt/tables.sql
說明：備份多個(gè)表時(shí)在oldboy數(shù)據(jù)庫后面指定多個(gè)表即可，不需要加-B參數(shù)！
命令語法：mysqldump -u用戶 -p密碼數(shù)據(jù)庫名 表名 -d > 備份文件名
利用mysqldump –d參數(shù)值備份數(shù)據(jù)庫里面的表結(jié)構(gòu)，
（1）備份oldboy庫的所有表的結(jié)構(gòu)：
備份前輸出且過檢查
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboytest -d –compact ， /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `test` ( `id` int(4)NOT NULL AUTO_INCREMENT, `name`char(20) NOT NULL, PRIMARY KEY(`id`), KEY`index_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULTCHARSET=utf8; /*!40101 SET character_set_client =@saved_cs_client */;
2.. 以上輸出正確后將其輸出到備份文件中進(jìn)行備份（備份全部表結(jié)果），命令如下
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy -d>/opt/bak_oldboy.sql
命令語法：mysqldump-u用戶 -p密碼數(shù)據(jù)庫名 表名 -t > 備份文件名
1. 備份oldboy庫中test表前輸出查看數(shù)據(jù)是否想要的
[root@db01 3306]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test -t --compact INSERT INTO `test` VALUES(3,'hehe'),(5,'kaka'),(1,'oldboy'),(2,'oldgirl'),(4,'zuma');
2. 以上結(jié)果正確開始備份
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql
使用--tab參數(shù)備份的表結(jié)構(gòu)和數(shù)據(jù)分離
[root@db01 3306]# mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy test --compact [root@db01 3306]# ls /tmp/ a.txt b.txt
說明：通過以上的分離后，數(shù)據(jù)表和sql語句就分開備份了，一個(gè)是文本一個(gè)是sql語句
-B備份多個(gè)庫（并添加create和use庫的語句）。
-d值備份庫表結(jié)構(gòu)。
-t只備份數(shù)據(jù)（sql語句形式）。
-T分離庫表和數(shù)據(jù)成不同的文件，數(shù)據(jù)是文本，非SQL語句。
binlog是什么？是記錄數(shù)據(jù)庫更新的SQL語句。二進(jìn)制文件
mysqldump用于定時(shí)對(duì)某一時(shí)刻的數(shù)據(jù)的全備，例如：00點(diǎn)進(jìn)行bak.sql.gz
增量備份，當(dāng)有數(shù)據(jù)寫入到數(shù)據(jù)庫時(shí)，還會(huì)同時(shí)更新的·SQL語句寫入到對(duì)應(yīng)的文件里。這個(gè)文件就叫做binlog文件。
例如10點(diǎn)丟失數(shù)據(jù)需要恢復(fù)數(shù)據(jù)：
1. 00點(diǎn)時(shí)刻備份的bak.sql.gz數(shù)據(jù)還原到數(shù)據(jù)庫，這個(gè)時(shí)候數(shù)據(jù)恢復(fù)到了00點(diǎn)。
2. 00點(diǎn)到10::00數(shù)據(jù)，就要從binlog里恢復(fù)
binglog文件生效需要一個(gè)參數(shù)：log-bin
個(gè)人理解：我們平時(shí)對(duì)數(shù)據(jù)庫進(jìn)行備份時(shí)定時(shí)備份，而不是實(shí)時(shí)備份數(shù)據(jù)，假設(shè)我們數(shù)據(jù)庫北田在晚上00點(diǎn)備份，而我早上8點(diǎn)數(shù)據(jù)庫出現(xiàn)故障導(dǎo)致數(shù)據(jù)丟失時(shí)，我們?cè)趺慈フ一?0點(diǎn)到8點(diǎn)這之間的數(shù)據(jù)呢？那就要通過binlog文件啦，而binlog文件會(huì)切割的，但是在瓦上備份00點(diǎn)前我們是不需要的，所以在備份時(shí)要進(jìn)行刷險(xiǎn)binlog文件，重新生成，就是說重新生成的binlog是00-8:00之間的我們需要，需要靠這之間的binlog去恢復(fù)數(shù)據(jù)，
備份刷新binlog是binlog重新生成需要使用-F參數(shù)，生成新的binlog文件
下面測(cè)試：
[root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001 -rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 [root@db01 3306]#mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy test >/opt/bak_test.sql [root@db01 3306]#ll /data/3306/ -rw-rw---- 1 mysqlmysql 2547 Aug 28 15:11 mysql-bin.000001 -rw-rw---- 1 mysqlmysql 6282 Aug 29 01:15 mysql-bin.000002 -rw-rw---- 1 mysqlmysql 150 Aug 29 15:47 mysql-bin.000003
binlog日志切割，確定全備和增量備份的臨界點(diǎn)。
-F刷新binlog日志，生成新文件，將來增量恢復(fù)從這個(gè)文件開始，
--master-data參數(shù)說明：
--master-data在備份文件里面會(huì)添加CHANGEMASTER語句及binlog文件及位置點(diǎn)信息。
1. 使用-master-data參數(shù)備份范例，值為1：為可執(zhí)行的CHANGEMASTER語句
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql #<== 使用--master-data=1參數(shù)進(jìn)行備份 [root@db01 3306]# cat /opt/bak_test.sql #<== 看下使用該參數(shù)真假的說明內(nèi)容 CHANGE MASTER TO MASTER_LOG_FILE='',MASTER_LOG_POS=
# 以上是使用--master-data=1參數(shù)增加的內(nèi)容，實(shí)惠執(zhí)行的，此內(nèi)容會(huì)顯示出binlog文件從那個(gè)開始切割，而且還有從文件里面的第幾行開始
2. 使用-master-data參數(shù)備份范例，值為2：注釋的--CHANGEMASTER語句。
[root@db01 3306]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sockoldboy test -F --master-data=2 >/opt/bak_test.sql [root@db01 3306]# cat /opt/bak_test.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=107;
# 以上結(jié)果表明值為1和2是不一樣的，值為1的是可執(zhí)行的，值為2的則是注釋的
說明：一般-master-data=1時(shí)用于做從庫，想inotify和backup差不多，如果是單純的增量恢復(fù)只需要-master-data=2即可。
--master-data除啦增量恢復(fù)確定臨界點(diǎn)外，做主從復(fù)制是作用更大
參數(shù) | 解釋說明 |
–B | 指定多個(gè)庫，增加建庫語句和use語句， ***** |
--compact | 去掉注釋，適合調(diào)試輸出，生產(chǎn)不適用 |
-A | 備份所有庫 |
-F | 刷新binlog日志，生成新文件，將來增量恢復(fù)從這個(gè)文件開始 |
--master-data | 增加binlog日志文件名及對(duì)應(yīng)的位置點(diǎn)（及CHANGE MASTER語句）。 --master-data=1不注釋；--master-data=2注釋； |
--evebts | 備份所有，如果想備份數(shù)據(jù)庫中mysql庫就要求使用此參數(shù) |
-R | 備份存儲(chǔ)過程 |
-d | 只備份庫表結(jié)構(gòu)，無數(shù)據(jù)。 |
-t | 只備份數(shù)據(jù)，無庫表結(jié)構(gòu)，SQL語句形式。 |
-T --tab=name | 庫表和數(shù)據(jù)分離不同文件，數(shù)據(jù)是文本形式。 |
-x | 鎖表，在某一時(shí)刻不能往里面更新數(shù)據(jù)，所以備份要小心。 |
-l –lock-tables | 只讀鎖表 |
--single-transaction | 適合innodb食物數(shù)據(jù)庫備份 innodb表在備份時(shí)，通常啟用選項(xiàng)—single-transaction來保證備份的一致性，實(shí)際上他的工作原理是設(shè)定本次會(huì)話的隔離級(jí)別為：REPEATABLE READ，以確保本次會(huì)話（dump）時(shí)，不會(huì)看到其他會(huì)話已經(jīng)提交了的數(shù)據(jù)。 |
-q --quick | 不做緩存查詢查詢，直接導(dǎo)到標(biāo)準(zhǔn)輸出，加快備份速度 |
更多參數(shù)：mysqdump –help
myisam引擎企業(yè)生產(chǎn)備份命令（適合所有引擎或混合殷勤）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events|gzip >/opt/alL__$(date +%F).sql.sql.gz
# -F也可以不用，與—master-data有些重復(fù)
innodb引擎企業(yè)生產(chǎn)備份命令（推薦使用的）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events - |gzip>/opt/all.sql.gz
# -F也可以不用，與—master-data有些重復(fù)
額外補(bǔ)充：
（1）mysqldump是邏輯備份說明
缺點(diǎn)：效率不是很高。
優(yōu)點(diǎn)：簡(jiǎn)單，方便。可靠，遷移。
適合于數(shù)量不是很特別大的場(chǎng)景，打包前50G以內(nèi)數(shù)據(jù)。
（2）如果超過50G可選方案如下：
1. xtrabackup物理備份工具，全備和增量。
2. 物理備份方案，從庫停止SQL線程，打包，cp。
（3）什么時(shí)候會(huì)使用備份的數(shù)據(jù)？
1. 恢復(fù)數(shù)據(jù)到測(cè)試庫的時(shí)候。
2. 人為通過SQL語句將數(shù)據(jù)刪除的時(shí)候。
3. 做主從復(fù)制的時(shí)候。
提示：
1. 數(shù)據(jù)恢復(fù)和字符集關(guān)聯(lián)很大，如果字符集不正確戶導(dǎo)致恢復(fù)的數(shù)據(jù)亂碼，
2. mysql命令以及source命令恢復(fù)數(shù)據(jù)庫的原理就是把文件的SQL語句，在數(shù)據(jù)庫里重新執(zhí)行的過程。
5.2 利用source命令恢復(fù)數(shù)據(jù)庫
進(jìn)入mysql數(shù)據(jù)庫控制臺(tái)，mysql –uroot –p登陸后
mysql> use 數(shù)據(jù)庫
然后使用source命令，后面參數(shù)為腳本文件（如這里用到的sql）。
mysql> source oldboy_db.sql #這個(gè)文件是系統(tǒng)路徑，默認(rèn)是登錄mysql前的系統(tǒng)路徑。
source實(shí)踐操作如下：
[root@db01 3306]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 4 rows in set (0.00 sec) mysql> drop database oldboy; Query OK, 2 rows affected (0.00 sec mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | performance_schema | +----------------------+ 3 rows in set (0.00 sec) mysql> Query OK, 0 rows affected (0.00 sec) ------以下內(nèi)容輸出省略--------- mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 4 rows in set (0.00 sec)
5.3 利用mysql命令恢復(fù)（標(biāo)準(zhǔn)）
命令語法：
mysql -u用戶 -p密碼 -S /data/3306/mysql.sock 數(shù)據(jù)庫<備份文件
提示：此命令分為兩種方式恢復(fù)。取決于備份文件備份時(shí)是否指定-B參數(shù)進(jìn)行備份
第一種恢復(fù)數(shù)據(jù)（不加-B參數(shù)的備份數(shù)據(jù)護(hù)膚數(shù)據(jù)）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 6 rows in set (0.00sec) mysql> drop database oldboy; Query OK, 2 rowsaffected (0.11 sec) mysql> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ 5 rows in set (0.00sec) [root@db01 opt]# #<== 先創(chuàng)建oldboy數(shù)據(jù)庫 [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy</opt/oldboy_2016-08-28.sql
第二種備份文件恢復(fù)數(shù)據(jù)（使用加-B參數(shù)備份文件恢復(fù)）
[root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "dropdatabase oldboy;" [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | performance_schema | +---------------------+ [root@db01 opt]# [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +---------------------+ | Database | +---------------------+ | information_schema | | mysql | | | performance_schema | +---------------------+
# 說明：在上面已經(jīng)將加-B參數(shù)備份的配置文件和不加-B參數(shù)備份的配置文件進(jìn)行對(duì)比和說明，加上-B參數(shù)在恢復(fù)數(shù)據(jù)時(shí)會(huì)自動(dòng)建庫并恢復(fù)數(shù)據(jù)，不加-B則還得先手動(dòng)建庫，所以這是加-B參數(shù)的好處，如果說詳見一個(gè)數(shù)據(jù)庫的數(shù)據(jù)拷貝到另一個(gè)庫里可以不加-B參數(shù)，到時(shí)候直接將備份文件導(dǎo)入到該庫即可。
如果在導(dǎo)出時(shí)指定-B參數(shù)，恢復(fù)時(shí)無需指定庫恢復(fù)，為什么？
因?yàn)槭褂?B參數(shù)導(dǎo)出數(shù)據(jù)時(shí)自帶了useoldboy和create database oldboy；而恢復(fù)時(shí)指定類似于use oldboy；
提示：-B不僅只有自動(dòng)建庫use庫的一鍵恢復(fù)庫數(shù)據(jù)的應(yīng)用，還可以指定多個(gè)庫
方法1：
cd /opt/ gzip bak_2016-08-29.sql.gz mysql -uroot -poldboy123 -S /data/3306/mysql.sock</opt/bak_2016-08-29.sql
方法2：
使用-e參數(shù)在命令行查看有哪些數(shù)據(jù)庫
[root@db01 /]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +----------------------+ | Database | +----------------------+ | information_schema | | 3ooo | | mysql | | oldboy | | oldboy_gbk | | performance_schema | +----------------------+
使用-e參數(shù)在命令行執(zhí)行多條mysql數(shù)據(jù)命令
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "use oldboy; select * from oldboy.test;" +----+----------+ | id name | +----+----------+ | 3 | hehe | | 5 | kaka | | 1 | oldboy | | 2 | oldgirl | | 4 | zuma | +----+----------+
利用mysql –e參數(shù)查看SQL線程執(zhí)行狀態(tài)
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show processlist;" +-----+-----+----------+------+---------+--------+--------+------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+---------+--------+--------+------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showprocesslist | +-----+-----+----------+------+---------+--------+--------+------------------+
查看完整的線程狀態(tài)，此參數(shù)才查看滿語句是非常有用
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show full processlist;" +-----+-----+----------+------+----------+--------+--------+------------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+----------+--------+--------+------------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist | +-----+-----+----------+------+----------+--------+--------+------------------------+
企業(yè)案例：mysql sleep 線程過多的問題案例
mysql> show full processlist; +-----+-----+----------+------+----------+--------+--------+------------------------+ | Id | User|Host | db | Command |Time | State | Info | +-----+-----+----------+------+----------+--------+--------+------------------------+ | 30 | root|localhost | NULL | Query | 0 | NULL | showfull processlist | +-----+-----+----------+------+----------+--------+--------+------------------------+ 1 row in set (0.00 sec)
1. 解決方法：配置文件里修改
[mysqld]
interactive_timeout = 120 #<== 超時(shí)時(shí)間；此參數(shù)設(shè)置后wait_timeout自動(dòng)生效。
wait_timeout = 120 #<== 超時(shí)時(shí)間
2. 其他解決方法：
PHP程序中，不使用持久鏈接，及使用mysql_connect而不是pconnect（JAVA調(diào)整連接池）
3. PHP程序執(zhí)行完畢，應(yīng)該顯示調(diào)用mysql_close。
4. 逐步分析MySQL的SQL查詢及慢查詢?nèi)罩?，找到查詢?guó)漫的SQL優(yōu)化之。
使用命令show variables可以查看相關(guān)參數(shù)是否在數(shù)據(jù)庫中生效
# 查看mysql數(shù)據(jù)庫中所有的變量
[root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables;" | Variable_name（變量名稱） | Value （值的意思） | +------------------------------------+--------------------------------------+ |auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | -------------------------以下內(nèi)容省略-----------------------
現(xiàn)在想查看mysql中的server_id，如下：
[root@db01/]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ;” +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 查看下binlog是否開啟 [root@db01 /]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show variables ';" +---------------------------------+---------+ | Variable_name |Value | +---------------------------------+---------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+---------+
不重啟數(shù)據(jù)庫修改數(shù)據(jù)庫參數(shù)，但是要求重啟后還能生效。
[root@db01 /]#mysql -uroot -poldboy123 -S /data/3306/mysql.sock mysql> showvariables like '%key_buffer%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | key_buffer_size |16777216 | +-------------------+----------+ 1 row in set (0.00 sec) mysql> Query OK, 0 rows affected (0.07 sec) mysql> show variables like '%key_buffer%'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | key_buffer_size |33554432 | +------------------+----------+ 1 row in set (0.00 sec)
不重啟數(shù)據(jù)庫更改數(shù)據(jù)庫參數(shù)小結(jié)：
1. set global key_buffer_size = 1024*1024*32; #<== 臨時(shí)生效，重啟失效
2. 配置文件也要改，編輯/etc/my.cnf，修改key_buffer_size = 32k
6.4 生產(chǎn)場(chǎng)景常用重要命令小結(jié)
show processlist; #<== 查看數(shù)據(jù)庫里正在執(zhí)行的SQL語句，可能無法查看完整的SQL語句
show full processlist; #<== 查看正在執(zhí)行的完整SQL語句，完整顯示（組好使用-e外面看）
set global key_buffer_size = 1024*1024*32; #<== 不重啟數(shù)據(jù)庫調(diào)整數(shù)據(jù)庫參數(shù)，直接生效，重啟后失效
show variables; #<== 查看數(shù)據(jù)庫的配置參數(shù)信息，例如：my.cnf里參數(shù)的生效情況
例如：show variables like '%log_bin';
kill ID; #<== 殺掉SQL線程的命令，ID為線程號(hào)
show session status; #<== 查看當(dāng)前會(huì)話的數(shù)據(jù)庫狀態(tài)信息
show global status; #<== 查看整個(gè)數(shù)據(jù)庫運(yùn)行狀態(tài)信息，很重要，要分析并做好監(jiān)控
show engine innodb status; #<== 顯示innodb引擎的性能狀態(tài)（早期版本show innodb status）
計(jì)算一天之內(nèi)，MySQL數(shù)據(jù)庫有多個(gè)insert，delete，有沒有好方法？
1.定時(shí)每天0點(diǎn)，show global status 按天取出對(duì)比。
2.按天分析binlog日志，獲取數(shù)據(jù)庫不同語句的頻率。
mysql常用命令參數(shù)：
-u：用戶
-p：密碼
-S：指定socket
-h：主機(jī)
-P：端口
-e：命令行執(zhí)行數(shù)據(jù)庫命令
mysqladmin的相關(guān)命令：
mysqladmin password 123456 #<== 數(shù)據(jù)庫無密碼設(shè)置密碼
mysqladmin -uroot -p123456 password 123123 #<== 修改密碼
mysqladmin -uroot -p123456 status #<== 查看狀態(tài)
mysqladmin -uroot -p123456 extended-status #<== 類似show global status，查看狀態(tài)信息
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock -i 1 status #<== 每隔1秒查看一次
mysqladmin -uroot -p123456 flush-logs #<== 刷新binlog日志
mysqladmin -uroot -p123456 processlist #<== 查看MySQL數(shù)據(jù)庫進(jìn)程
mysqladmin -uroot -p123456 processlist -i 1 #<== 實(shí)時(shí)跟蹤進(jìn)程列表（抓慢查詢）
which mysqladmin -uroot -p123456 processlist #<== 實(shí)時(shí)查詢進(jìn)程列表
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockstutdown #<== 關(guān)機(jī)
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockvariables #<== 查看變量
對(duì)于以上關(guān)于如何備份及恢復(fù)MySQL數(shù)據(jù)庫，大家是不是覺得非常有幫助。如果需要了解更多內(nèi)容，請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會(huì)喜歡上這些內(nèi)容的。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
