溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文主要給大家帶來備份及恢復MySQL數據庫，希望這些內容能夠帶給大家實際用處，這也是我編輯備份及恢復MySQL數據庫這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。
經常有網友問，運維工作到底是什么工作，到底做什么？其實，運維工作簡單的概括就兩件事，第一個是保護公司的數據，第二個是網站7*24小時提供服務。
而對于數據和網站7*24小時提供服務那個跟重要呢？
這個具體要看業務和公司。
例如：銀行，金融行業，數據是最重要的，一條都不能丟，可能宕機停機影響就沒那么大。
再例如：百度搜索，騰訊qq聊天記錄丟失幾萬條數據，都不算啥。
對于數據來講，數據最核心的就是數據庫核數據，當然也包括其他數據，以下主要講解的是數據庫的數據。
對于絕大數企業來講，時區數據就時區商機，時區產品，時區客戶，公司倒閉…….，因此，數據庫的備份與恢復就顯得十分重要了。
備份的兩種方式：
1. 邏輯備份：以SQL語句的形式，將數據導出，速度慢，跨平臺，跨版本，可以跨軟件。
2. 物理備份，直接對數據文件備份，cp tar，速度快，不容易跨平臺、跨版本。跨軟件，常用方法： linux備份工具（cp tar）。
3. xtrabackup
mysql數據庫自帶了一個很好用的備份命令，就是mysqldump，它的基本使用如下：
命令語法：mysqldump -u 用戶名-p密碼 數據庫名 > 備份的文件名
如下5個范例目錄：
（1）備份名字為oldboy數據庫
（2）解決備份文件亂碼問題
（3）利用上述備份數據進行回復測試
（4）備份時使用-B參數和不適用-B參數的區別及兩種備份方法。
（5）優化使用—compact參數使備份文件不輸出注釋，優化空間。
（6）指定壓縮命令壓縮備份的MySQL數據
范例操作如下：
（1）范例1：備份名字為oldboy的庫
1. 備份前查看oldboy數據
[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. 在命令行執行備份命令
[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. 備份完檢查備份完的數據
[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;
提示：根據以上得出的結果，我們看看到已備份的表結構語句及插入的數據整合的sql語句。
（2）范例2：如果出現亂碼指定字符集參數解決備份亂碼問題
假如備份數據庫備份文件出現亂碼情況，用以下參數解決中文亂碼，過程如下
1. 查看備份的數據庫客戶端及服務端的字符集設置
[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. 指定對應的字符集備份，這里為--default-character_set=utf8 （指定字符集參數）
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock - oldboy>/opt/oldboy_$(date +%F).sql
執行結果：
[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
提示：如果出現亂碼情況，使用指定支付及查看，就不會在亂碼了，其實，即使導出的中文亂碼也沒關系，導入數據庫依然是正常的。
（3）范例2:：利用上述備份好的文件進行數據恢復測試
[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：備份時加-B參數以及使用備份文件恢復數據的兩種方法
1. 查看不加-B參數備份文件和加-B備份文件區別
不加-B參數備份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy>/opt/oldboy_$(date +%F).sql
加-B參數備份命令：
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock oldboy >/opt/oldboy_B_$(date +%F).sql
使用vimdiff命令比較不加-B參數配置文件和加-B參數配置文件區別
[root@db01 /]# cd /opt/ [root@db01 opt]# vimdiff oldboy_2016-08-28.sql oldboy_B_2016-08-28.sql -- Current Database: `oldboy` -- --
# 結論：
直觀看，加上-B參數的作用是增加創建數據庫核連接數據庫的語句了，我們使用-B的備份文件就可以直接恢復，而不適用-B的備份文件還需要先創建庫才可以，區別即如下兩條語句：
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` USE `oldboy`;
第一種恢復數據（不加-B參數的備份數據護膚數據）
[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]#
說明：現在使用沒有帶-B參數備份的備份文件來恢復數據庫，在上面已經跟加-B參數的備份文件做了對比，-B參數備份文件中比不帶-B參數備份的文件多了創建oldboy數據庫核進入oldbou數據庫，就是說-B參數的備份文件為我們節省了創建庫和進入庫的兩個步驟，直接將備份文件導入數據庫即可，還不用指定那個庫，因為里面指定了使oldboy數據庫。
第二種備份文件恢復數據（使用加-B參數備份文件恢復）
[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 | +---------------------+
# 說明：在上面已經將加-B參數備份的配置文件和不加-B參數備份的配置文件進行對比和說明，加上-B參數在恢復數據時會自動建庫并恢復數據，不加-B則還得先手動建庫，所以這是加-B參數的好處，如果說詳見一個數據庫的數據拷貝到另一個庫里可以不加-B參數，到時候直接將備份文件導入到該庫即可。
提示：-B不僅只有自動建庫use庫的一鍵恢復庫數據的應用，還可以指定多個庫
（5）范例5：優化備份文件大小減少輸出注釋（debug調試）
利用mysqldump的—compact參數優化下備份結果：
[root@db01 opt]# mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy -----------------------
帶--compact參數備份
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參數說明：
注意：--compact此參數不在生產環境使用，用于調試即可。
（6）范例6：指定壓縮命令壓縮備份的MySQL數據
在將數據庫數據備份時通過gzip命令壓縮，以節省磁盤空間
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
以上例子小結：
1. 備份數據使用-B參數，會在備份數據中增加建庫及use庫的語句。
2. 備份數據使用-B參數，使得后面可以直接接多個庫名。
3. 備份數據使用gzip對備份的數據壓縮，可以節省磁盤空間。
4. debug時可以用—compact減少輸出，但不用于生產。
5. 指定字符集備份用–default-character-set=字符集（一般不適用）
利用mysqldump命令備份數據的過程，實際上就是把數據從mysql庫里以邏輯的sql語句的形式直接輸出或者生成備份的文件的過程。
可否舉一反三，利用這個備份文件恢復的時候的原理？
備份的數據過濾掉注釋，都是SQL語句，結果如下：
[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. 查看當前有幾個庫
[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參數備份多個庫
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參數說明：該參數用于導出若干個數據庫，在備份結果中會加入CREATE DATABASE `db_name`和USE db `db_name`兩個mysql數據庫命令，-B后的參數都將被作為數據庫名，該參數比較常用，當-B后的數據庫列全時，用-A參數，-A參數意思是轉儲所有數據中的所有表。
分庫備份實際上就是執行一個備份語句備份一個庫，如果數據庫里面有多個表，就執行多條相同的備份單個庫的備份語句就可以備份多個庫了，注意每個庫都可以對應備份的庫作為庫名，結尾加sql，
分庫意義： 舉例，有時一個企業數據庫里會有多個庫，例如（www，bbs，blog），但是出問題時候的很可能是某一個庫，如果在備份時把所有的庫都備份成一個數據文件的話，恢復某一個庫的數據時就比較麻煩了。
方法1：使用一條一條的命令備份：
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy>/opt/……… mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -B oldboy_gbk >/opt/........
方法二：利用命令拼接實現分庫備份
[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
單標備份命令語法：mysqldump-u用戶名 -p密碼 數據庫名 表名> 備份的文件名
（1）備份單個表：以下備份oldboy數據庫的test表為例
1. 備份前查看oldboy數據庫有那些表
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" +-----------------+ | Tables_in_oldboy| +-----------------+ | student | | test | +-----------------+
2. 開始備份單個表（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）一條命令分表備份：
數據庫分表備份其實就是一條一條的備份，但是又時候一條命令一條命令麻煩，可以使用一條命令將多個數據庫表備份，這樣更簡潔，以下將student和test表備份使用一條命令，具體命令如下：
[root@db01 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock -e "show tables from oldboy;" #<== 備份前查看當前oldboy數據庫中有哪些表 +------------------+ | 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
分表備份的缺點
1.被一個完整全被，在做一個分庫分表備份。
2. 腳本批量恢復SQL文件
面試題：多個庫或者多個表備份到一塊，如何恢復單個庫或者表？
解答：
a. 第三方測試庫，導入到庫里，然后把需要的備份出來，最后恢復到正式庫里。
b. 單表：grep 表名 bak.sql> 表名.sql
單庫：循環過濾庫里所有表，grep表名 bak.ssql > 表名.sql（多個表數據）
c. 實現分庫分表備份。
以下備份oldboy數據庫的多個表
1.查看oldboy數據庫中的表，命令如下：
mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showtables from oldboy;"
2. 備份oldboy數據庫中的student、test兩個表，命令如下
mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock >/opt/tables.sql
說明：備份多個表時在oldboy數據庫后面指定多個表即可，不需要加-B參數！
命令語法：mysqldump -u用戶 -p密碼數據庫名 表名 -d > 備份文件名
利用mysqldump –d參數值備份數據庫里面的表結構，
（1）備份oldboy庫的所有表的結構：
備份前輸出且過檢查
[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.. 以上輸出正確后將其輸出到備份文件中進行備份（備份全部表結果），命令如下
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy -d>/opt/bak_oldboy.sql
命令語法：mysqldump-u用戶 -p密碼數據庫名 表名 -t > 備份文件名
1. 備份oldboy庫中test表前輸出查看數據是否想要的
[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. 以上結果正確開始備份
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql
使用--tab參數備份的表結構和數據分離
[root@db01 3306]# mysqldump-uroot -poldboy123 -S /data/3306/mysql.sock oldboy test --compact [root@db01 3306]# ls /tmp/ a.txt b.txt
說明：通過以上的分離后，數據表和sql語句就分開備份了，一個是文本一個是sql語句
-B備份多個庫（并添加create和use庫的語句）。
-d值備份庫表結構。
-t只備份數據（sql語句形式）。
-T分離庫表和數據成不同的文件，數據是文本，非SQL語句。
binlog是什么？是記錄數據庫更新的SQL語句。二進制文件
mysqldump用于定時對某一時刻的數據的全備，例如：00點進行bak.sql.gz
增量備份，當有數據寫入到數據庫時，還會同時更新的·SQL語句寫入到對應的文件里。這個文件就叫做binlog文件。
例如10點丟失數據需要恢復數據：
1. 00點時刻備份的bak.sql.gz數據還原到數據庫，這個時候數據恢復到了00點。
2. 00點到10::00數據，就要從binlog里恢復
binglog文件生效需要一個參數：log-bin
個人理解：我們平時對數據庫進行備份時定時備份，而不是實時備份數據，假設我們數據庫北田在晚上00點備份，而我早上8點數據庫出現故障導致數據丟失時，我們怎么去找回00點到8點這之間的數據呢？那就要通過binlog文件啦，而binlog文件會切割的，但是在瓦上備份00點前我們是不需要的，所以在備份時要進行刷險binlog文件，重新生成，就是說重新生成的binlog是00-8:00之間的我們需要，需要靠這之間的binlog去恢復數據，
備份刷新binlog是binlog重新生成需要使用-F參數，生成新的binlog文件
下面測試：
[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日志切割，確定全備和增量備份的臨界點。
-F刷新binlog日志，生成新文件，將來增量恢復從這個文件開始，
--master-data參數說明：
--master-data在備份文件里面會添加CHANGEMASTER語句及binlog文件及位置點信息。
1. 使用-master-data參數備份范例，值為1：為可執行的CHANGEMASTER語句
mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock oldboy test >/opt/bak_test.sql #<== 使用--master-data=1參數進行備份 [root@db01 3306]# cat /opt/bak_test.sql #<== 看下使用該參數真假的說明內容 CHANGE MASTER TO MASTER_LOG_FILE='',MASTER_LOG_POS=
# 以上是使用--master-data=1參數增加的內容，實惠執行的，此內容會顯示出binlog文件從那個開始切割，而且還有從文件里面的第幾行開始
2. 使用-master-data參數備份范例，值為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;
# 以上結果表明值為1和2是不一樣的，值為1的是可執行的，值為2的則是注釋的
說明：一般-master-data=1時用于做從庫，想inotify和backup差不多，如果是單純的增量恢復只需要-master-data=2即可。
--master-data除啦增量恢復確定臨界點外，做主從復制是作用更大
參數 | 解釋說明 |
–B | 指定多個庫，增加建庫語句和use語句， ***** |
--compact | 去掉注釋，適合調試輸出，生產不適用 |
-A | 備份所有庫 |
-F | 刷新binlog日志，生成新文件，將來增量恢復從這個文件開始 |
--master-data | 增加binlog日志文件名及對應的位置點（及CHANGE MASTER語句）。 --master-data=1不注釋；--master-data=2注釋； |
--evebts | 備份所有，如果想備份數據庫中mysql庫就要求使用此參數 |
-R | 備份存儲過程 |
-d | 只備份庫表結構，無數據。 |
-t | 只備份數據，無庫表結構，SQL語句形式。 |
-T --tab=name | 庫表和數據分離不同文件，數據是文本形式。 |
-x | 鎖表，在某一時刻不能往里面更新數據，所以備份要小心。 |
-l –lock-tables | 只讀鎖表 |
--single-transaction | 適合innodb食物數據庫備份 innodb表在備份時，通常啟用選項—single-transaction來保證備份的一致性，實際上他的工作原理是設定本次會話的隔離級別為：REPEATABLE READ，以確保本次會話（dump）時，不會看到其他會話已經提交了的數據。 |
-q --quick | 不做緩存查詢查詢，直接導到標準輸出，加快備份速度 |
更多參數：mysqdump –help
myisam引擎企業生產備份命令（適合所有引擎或混合殷勤）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events|gzip >/opt/alL__$(date +%F).sql.sql.gz
# -F也可以不用，與—master-data有些重復
innodb引擎企業生產備份命令（推薦使用的）
mysqldump -uroot -poldboy123 -A -B -F -R--master-data=2 -x --events - |gzip>/opt/all.sql.gz
# -F也可以不用，與—master-data有些重復
額外補充：
（1）mysqldump是邏輯備份說明
缺點：效率不是很高。
優點：簡單，方便。可靠，遷移。
適合于數量不是很特別大的場景，打包前50G以內數據。
（2）如果超過50G可選方案如下：
1. xtrabackup物理備份工具，全備和增量。
2. 物理備份方案，從庫停止SQL線程，打包，cp。
（3）什么時候會使用備份的數據？
1. 恢復數據到測試庫的時候。
2. 人為通過SQL語句將數據刪除的時候。
3. 做主從復制的時候。
提示：
1. 數據恢復和字符集關聯很大，如果字符集不正確戶導致恢復的數據亂碼，
2. mysql命令以及source命令恢復數據庫的原理就是把文件的SQL語句，在數據庫里重新執行的過程。
5.2 利用source命令恢復數據庫
進入mysql數據庫控制臺，mysql –uroot –p登陸后
mysql> use 數據庫
然后使用source命令，后面參數為腳本文件（如這里用到的sql）。
mysql> source oldboy_db.sql #這個文件是系統路徑，默認是登錄mysql前的系統路徑。
source實踐操作如下：
[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) ------以下內容輸出省略--------- mysql> show databases; +----------------------+ | Database | +----------------------+ | information_schema | | mysql | | oldboy | | performance_schema | +----------------------+ 4 rows in set (0.00 sec)
5.3 利用mysql命令恢復（標準）
命令語法：
mysql -u用戶 -p密碼 -S /data/3306/mysql.sock 數據庫<備份文件
提示：此命令分為兩種方式恢復。取決于備份文件備份時是否指定-B參數進行備份
第一種恢復數據（不加-B參數的備份數據護膚數據）
[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]# #<== 先創建oldboy數據庫 [root@db01 opt]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock oldboy</opt/oldboy_2016-08-28.sql
第二種備份文件恢復數據（使用加-B參數備份文件恢復）
[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 | +---------------------+
# 說明：在上面已經將加-B參數備份的配置文件和不加-B參數備份的配置文件進行對比和說明，加上-B參數在恢復數據時會自動建庫并恢復數據，不加-B則還得先手動建庫，所以這是加-B參數的好處，如果說詳見一個數據庫的數據拷貝到另一個庫里可以不加-B參數，到時候直接將備份文件導入到該庫即可。
如果在導出時指定-B參數，恢復時無需指定庫恢復，為什么？
因為使用-B參數導出數據時自帶了useoldboy和create database oldboy；而恢復時指定類似于use oldboy；
提示：-B不僅只有自動建庫use庫的一鍵恢復庫數據的應用，還可以指定多個庫
方法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參數在命令行查看有哪些數據庫
[root@db01 /]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "showdatabases;" +----------------------+ | Database | +----------------------+ | information_schema | | 3ooo | | mysql | | oldboy | | oldboy_gbk | | performance_schema | +----------------------+
使用-e參數在命令行執行多條mysql數據命令
[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參數查看SQL線程執行狀態
[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 | +-----+-----+----------+------+---------+--------+--------+------------------+
查看完整的線程狀態，此參數才查看滿語句是非常有用
[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 | +-----+-----+----------+------+----------+--------+--------+------------------------+
企業案例：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 #<== 超時時間；此參數設置后wait_timeout自動生效。
wait_timeout = 120 #<== 超時時間
2. 其他解決方法：
PHP程序中，不使用持久鏈接，及使用mysql_connect而不是pconnect（JAVA調整連接池）
3. PHP程序執行完畢，應該顯示調用mysql_close。
4. 逐步分析MySQL的SQL查詢及慢查詢日志，找到查詢國漫的SQL優化之。
使用命令show variables可以查看相關參數是否在數據庫中生效
# 查看mysql數據庫中所有的變量
[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 | -------------------------以下內容省略-----------------------
現在想查看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 | +---------------------------------+---------+
不重啟數據庫修改數據庫參數，但是要求重啟后還能生效。
[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)
不重啟數據庫更改數據庫參數小結：
1. set global key_buffer_size = 1024*1024*32; #<== 臨時生效，重啟失效
2. 配置文件也要改，編輯/etc/my.cnf，修改key_buffer_size = 32k
6.4 生產場景常用重要命令小結
show processlist; #<== 查看數據庫里正在執行的SQL語句，可能無法查看完整的SQL語句
show full processlist; #<== 查看正在執行的完整SQL語句，完整顯示（組好使用-e外面看）
set global key_buffer_size = 1024*1024*32; #<== 不重啟數據庫調整數據庫參數，直接生效，重啟后失效
show variables; #<== 查看數據庫的配置參數信息，例如：my.cnf里參數的生效情況
例如：show variables like '%log_bin';
kill ID; #<== 殺掉SQL線程的命令，ID為線程號
show session status; #<== 查看當前會話的數據庫狀態信息
show global status; #<== 查看整個數據庫運行狀態信息，很重要，要分析并做好監控
show engine innodb status; #<== 顯示innodb引擎的性能狀態（早期版本show innodb status）
計算一天之內，MySQL數據庫有多個insert，delete，有沒有好方法？
1.定時每天0點，show global status 按天取出對比。
2.按天分析binlog日志，獲取數據庫不同語句的頻率。
mysql常用命令參數：
-u：用戶
-p：密碼
-S：指定socket
-h：主機
-P：端口
-e：命令行執行數據庫命令
mysqladmin的相關命令：
mysqladmin password 123456 #<== 數據庫無密碼設置密碼
mysqladmin -uroot -p123456 password 123123 #<== 修改密碼
mysqladmin -uroot -p123456 status #<== 查看狀態
mysqladmin -uroot -p123456 extended-status #<== 類似show global status，查看狀態信息
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock -i 1 status #<== 每隔1秒查看一次
mysqladmin -uroot -p123456 flush-logs #<== 刷新binlog日志
mysqladmin -uroot -p123456 processlist #<== 查看MySQL數據庫進程
mysqladmin -uroot -p123456 processlist -i 1 #<== 實時跟蹤進程列表（抓慢查詢）
which mysqladmin -uroot -p123456 processlist #<== 實時查詢進程列表
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockstutdown #<== 關機
mysqladmin -uroot -p123456 -S /data/3306/mysql.sockvariables #<== 查看變量
對于以上關于如何備份及恢復MySQL數據庫，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
