MySQL日常维护工具-备份
目錄
1、mysql字符集
1.1、字符集介紹
1.2、MySQL數據庫常見字符集介紹
1.3、MySQL怎樣選擇合適的字符集
1.4、查看MySQL支持的字符集
1.5、查看MySQ當前的字符集設置情況
1.6、實例，遷移數據
1.6.1、準備實驗環境
?1.6.2、導出數據表結構
1.6.3、編輯booktable.sql將latin1修改為utf8
1.6.4、確保數據庫不再更新，導出所有數據
1.6.5、打開bookdata.sql將set name latin1修改成set name utf8
1.6.6、新建book2庫
1.6.7、建立表，導入我們之前導出的表結構
1.6.8、導入數據
1.6.9、查看結果
1.6.10、查看表內容
2、mysql備份恢復
2.1、MySQL備份的類型
2.1.1、按照備份時對數據庫的影響范圍分類：
2.1.2、按照備份后文件內容分類：
2.1.3、按照備份數據庫的內容來分，又可以分為：
2.1.4、建議的備份策略
2.2、邏輯備份工具mysqldump
2.2.1、導出數據：
2.2.2、導入數據：
1、mysql字符集
1.1、字符集介紹
字符（Character）是各種文字和符號的總稱，包括各國家文字、標點符號、圖形符號、數字等。 字符集（Character set）是多個字符的集合，字符集種類較多，每個字符集包含的字符個數不同，常見字符集名稱：ASCII字符集、GB2312字符集、GIG5字符集、GB18030字符集、GB2312字符集、GIG5字符集、GB18030字符集、Unicode字符集等。計算機要準確的處理各種字符集文字，就需要進行字符編碼，以便計算機能夠存儲和識別各種文字。
字符集補充說明：
ASCII字符集：American Standard Code for Information Interchange,美國信息互換標準編碼。7位（bits）表示一個字符，共128字符，字符值從0到127.包括空值字符：回車鍵、退格、換行鍵等。可顯示字符：英文大小寫字符、阿拉伯數字和西文符號。
ASCII擴展字符集：它是從ASCII字符集擴充出來的，擴充后的符號增加了表格符號、計算符號、希臘字母和特殊的拉丁符號。ASCII擴展字符使用8位表示一個字符，共256字符。
GB2312由原中國國家標準局發布，收錄簡化漢字及一般符號、序號、數字、拉丁字母、希臘字母、俄文字母、漢語拼音符號、漢語注音字母，共7445個圖形字符。其中包括6763個漢字，用雙字節表示。
Big5又稱大五碼或五大碼，1984年由臺灣財團法人信息工業策進會和五家軟件公司宏基（Acer）、神通（MiTAC）、佳佳、零壹、大眾創立，故稱五大碼。字符集共收錄13053個中文字，該字符集在中國臺灣使用。Big5碼使用了雙字節存儲方法。
GB18030字符集標準解決漢字、日文、朝鮮語和中國少數民族漢字組成的大字符集計算機編碼問題。是我國政府于2000年3月17日發布的新的漢字編碼國家標準，收錄27484個漢字，覆蓋中文、日文、朝鮮語和中國少數民族漢字。滿足中國大陸、香港、臺灣、日本和韓國等東亞地區信息交換多文種、大字量、多用途、統一編碼格式的要求。GB18030采用單字節、雙字節和四字節三種方式對字符編碼。單字節部分對應ASCII碼的相應部分，雙字節部分收錄內容主要包括GB13000.1全部CJK漢字20902個、有關標點符號、表意文字描述符13個、增充的漢字和部首80個、雙字節編碼的歐元符號等。四字節部分收錄了上述雙字節之外的，包括CJK統一漢字在內的GB13000.1中的全部字符。
Unicode字符集編碼是Universal Multiple-Octet Coded Character Set通用多八位編碼字符集的簡稱，是一個由Unicode學術學會（Unicode Consortium）的機構制訂的字符編碼系統，支持現今世界各種語言的書面文本的交換、處理及顯示。
UTF-8是Unicode的其中一個使用方式。UTF是Unicode Tranformation Format,即把Unicode轉做某種格式的意思。UTF-8使用可變長度字節來存儲Unicode字符，又稱萬國碼。例如ASCII字母繼續使用1字節存儲，重音文字、希臘字母或西里爾字母等使用2字節來存儲，而常用的漢字就要使用3字節。輔助平面字符則使用4字節。
Mysql數據庫字符集包括字符集（CHARACTER）和校對規則（COLIATION）兩個概念，其中字符集用來定義MySQL數據字符串的存儲方式，而校對規則定義字符串比較的方式。
1.2、MySQL數據庫常見字符集介紹
選擇字符集建議使用國際標準的utf8
1.3、MySQL怎樣選擇合適的字符集
1、如果處理各種各樣的文字、發布到不同語言國家地區，應選Unicode字符集，對MySQL來說就是utf8（每個漢字3個字節）。
2、如果只是需要支持中文，并且數據量很大，性能要求也高，可選GBK（定長，每個漢字占雙字節，英文也占雙字節），如果是大量運算，比較排序等，定長字符集更快，性能也高
3、處理移動互聯網業務，可能需要utf8mb4字符集。?
1.4、查看MySQL支持的字符集
MySQL可以支持多種字符集，同一臺服務器，庫或表的不同字段都可以指定不同的字符集
查看所有的字符集
mysql -uroot -p123456 -e "show create set \G"? ? #查看所有的字符集
查看常用的字符集：
mysql -uroot -p123456 -e "show character set \G"; | egrep "gbk|utf8|latin1" | awk '{print $0}'
查看字符集的校對規則：
mysql -uroot -p123456 -e "show collation;"
1.5、查看MySQ當前的字符集設置情況
mysql -uroot -p123456
show variables like 'character_set%';
名次解釋：
character_set_client:客戶端請求數據的字符集
character_set_connection：客戶機/服務器連接的字符集
character_set_database：默認數據庫的字符集
character_set_filesystem：把os上文件名轉化成字符集，即把character_set_client轉換成character_set_filesystem，默認binary是不做任何轉換的
character_set_results:結果集，即返回給客戶端的字符集
character_set_server：數據庫服務器的默認字符集
character_set_system：系統字符集，這個值總是utf8，不需要設置。這個字符集用于數據庫對象（如表和列）的名字，也用于存儲在目錄表中的函數的名字。
查看當前數據庫的校對規則：
mysql -uroot -p123456 -e "show varibles like 'collation%';"
默認情況下字符集選擇規則：
(1)編譯MySQL時，指定了一個默認的字符集（-DDEFAULT_CHARSET=utf8），如果未指定默認是latin1；
(2)安裝MySQL后，可以在配置文件（my.cnf）中指定服務器的默認字符集（character_set_server=utf8），如果沒指定，這個值繼承自編譯時指定的；
配置文件中指定服務器的默認字符集會影響參數：character_set_server和character_set_database
(3)啟動mysqld時，可以在命令行參數中指定一個默認的字符集，如果沒指定，這個值繼承自配置文件中的配置，此時character_set_server被設定為這個默認的字符集
例如：./mysqld --character_set_server=utf8 &
影響參數：character_set_server和character_set_database
(4)當創建一個新的數據庫時，除非明確指定，這個數據庫的字符集被缺省設定為character_set_server
例如：create database 數據庫名 default character set=utf8;
(5)當選定了一個數據庫時，character_set_database被設定為這個數據庫默認的字符集；
例如：set character_set_database=utf8;
(6)在這個數據庫里創建一個表時，表默認的字符集被設定為character_set_database，也就是這個數據庫默認的字符集；
(7)當在表內設置列時，除非明確指定，否則此欄缺省的字符集就是表默認的字符集；
1.6、實例，遷移數據
背景：公司業務數據book，由于之前建表沒注意字符集的問題，導致之前寫入的數據出現亂碼。現在要將之前的數據和現在數據的字符集保持一致，不出現亂碼情況，將字符集latin1已有記錄的數據轉成utf8，并且已經存在的記錄不亂碼。
步驟：
1：建庫及建表的語句導出，修改為utf8
2：導出之前所有的數據
3：修改mysql服務端和客戶端編碼為utf8
4：刪除原有的庫表及數據
5：導入新的建庫及建表語句
6：導入之前的數據
1.6.1、準備實驗環境
1、確保數據庫服務器的默認字符集是utf8
修改/etc/my.cnf配置文件中character-set-server=utf8，重啟mysql
vim /etc/my.cnf
systemctl restart mysqld
由于我們books表的字符集是utf8，刪除book數據庫，重新創建book數據庫，導入字符集為latin1的sql文件book.sql
上傳book<book_latin1.sql文件
mysql -uroot -p123456 -e "drop databse book;"
mysql -uroot -p123456 -e "create database book;"
mysql -uroot -p123456 book<book_latin1.sql
查看books表的字符集
mysql -uroot -p123456 -e "show create table book.books;"
查看表內容：
除了英文和時間，中文都是亂碼
?1.6.2、導出數據表結構
mysqldump -uroot -p123456 --default-character-set=latin1 -d book > booktable.sql
1.6.3、編輯booktable.sql將latin1修改為utf8
vim booktable.sql
1.6.4、確保數據庫不再更新，導出所有數據
mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 book>bookdata.sql
?參數說明：
--quick:用于轉儲大的表，強制mysqldump從服務器一次一行的檢索數據而不是檢索所有行，并輸出當前cache到內存中
--no-create-info:不要創建create table 語句
--extended-inseret：使用包括幾個values列表的多行insert語法，這樣文件更小，io也小，倒數數據時會非常快
--default-character-set=latin1:按照原有字符集導出數據，這樣導出的文件中，所有中文都是可見的，不會保存成亂碼。
1.6.5、打開bookdata.sql將set name latin1修改成set name utf8
vim bookdata.sql
1.6.6、新建book2庫
mysql -e "create database book2 default charset utf8;" -uroot -p123456
1.6.7、建立表，導入我們之前導出的表結構
mysql -uroot -p123456 book2 < booktable.sql
1.6.8、導入數據
mysql -uroot -p123456 < bookdata.sql
1.6.9、查看結果
mysql -uroot -p123456 -e "show create table book2.books;"
1.6.10、查看表內容
mysql -uroot -p123456 -e "select * from boo2.books"?
2、mysql備份恢復
一、備份的目的：
做災難恢復：對損壞的數據進行恢復和還原
需求改變：因需求改變需要把數據還原到改變以前
測試：測試新功能是否可用
二、備份需要考慮的問題：
可以容忍丟失多長時間的數據；
恢復數據要在多長時間內完成；
恢復的時候是否需要持續提供服務；
恢復的對象，是整個庫，多個表，還是單個庫，單個表。
2.1、MySQL備份的類型
2.1.1、按照備份時對數據庫的影響范圍分類：
Hot backup（熱備） Cold Backup（冷備） Warm Backup（溫備）
1、Hot backup：指在數據庫運行中直接備份，對正在運行的數據庫沒有任何影響。（Online Backup），官方手冊稱為在線備份。（備份的同時，業務不受影響）
2、Cold Backup：指在數據庫停止的情況下進行備份（Offline Backup），官方手冊稱為離線備份。（需要關mysql服務，讀寫請求均不允許狀態下進行）
3、Ware Backup：備份同樣在數據庫運行時進行，但僅支持讀請求，不允許寫請求；例如，加一個讀鎖以保證備份數據的一致性。（服務在線，但僅支持讀請求，不允許寫請求）
2.1.2、按照備份后文件內容分類：
1、邏輯備份
指備份后的文件內容是可讀的，通常為文本文件，內容一般是sql語句，或者是表內的實際數據，如mysqldump和select * into outfile的方法，一般適用于數據庫的升級和遷移，恢復時間較長
2、物理文件備份
對數據庫物理文件的備份（如數據文件、日志文件等）的備份，數據庫即可以處于運行狀態（mysqlhotcopy、ibbackup、xtrabackup這類工具），也可以處于停止狀態（cp、tar等），恢復時間較短。
2.1.3、按照備份數據庫的內容來分，又可以分為：
1、完全備份：每次對數據進行完整的備份。可以備份整個數據庫，包含用戶表、系統表、索引、視圖和存儲過程等所有數據庫對象。但它需要花費更多的時間和存儲空間，所以，做一次完全備份的周期要長些。完全是其他類型的基礎。
2、差異備份：在上一次完全備份基礎上，對更新的數據進行備份。因為只備份數據庫部分的內容。它比完全備份小，因為只包含自上次完全備份以來所改變的數據。它的優點是存儲和恢復速度快。
3、增量備份：在上次備份的基礎上，對更新的數據進行備份。
4、日志備份：二進制日志備份
2.1.4、建議的備份策略
完全+差異+二進制日志?
完全+增量+二進制日志
2.2、邏輯備份工具mysqldump
mysqldump是MySQL數據庫自帶的一款命令行工具，mysqldump屬于單線程，功能是非常強大的，不僅常被用于執行數據備份任務，甚至還可以用于數據遷移。
mysqldump是mysql自帶的邏輯備份工具，它的備份原理是，通過協議連接到mysql數據庫，將數據轉換成標準sql語句（一堆create，drop，insert等語句）；
但我們需要還原時，只要執行這些語句即可將對應的數據還原。
優點：備份粒度相當靈活，既可以針對某個MySQL服務，也可以只備份某個或者某幾個DB，或者還可以指定只備份某個或者某幾個表對象，甚至可以實現只備份表中某些符合條件的記錄（-w，--where：只導出符合條件的記錄）。
缺點：
1、當數據是浮點數時，會出現精度丟失。
2、mysqldump的備份過程屬于邏輯備份，備份數據、恢復速度與物理備份工具相比較慢，而且mysqldump備份的過程是串行化的，不會并行的進行備份，當數據量較大時，一般不會使用mysqldump進行備份，因為效率較低。
···············································分割線······················································
mysqldump對innodb存儲引擎支持熱備，innodb支持事務，我們可以基于事務通過mysqldump對數據庫進行熱備（--single-transaction選項）
mysqldump對mysiam引擎只支持溫備，通過mysqldump對使用mysiam存儲引擎的表進行備份時，最多只能實現溫備，因為備份時會對備份的表請求一個讀鎖，當備份完成時，鎖會被釋放。
2.2.1、導出數據：
語法：mysqldump [options] [db_name [tb_name]...] >導出的文件名.sql
我們說多mysqldump是一個客戶端命令，所以，就像使用mysql命令連接數據庫一樣，我們需要指定連接的用戶名，需要連接的數據庫服務ip，以及使用-p選項提示我們輸入密碼，這些用法都與我們的mysql命令一致。
常用參數：
-？，--help:顯示幫助信息，英文的；
-u，--user：指定連接的用戶名；
-p，--password：指定用戶的密碼，可以交互輸入密碼；
-S，--socket：指定socket文件連接，本地登陸才會使用。
-h，--host：指定連接的服務器名稱或者ip。
-p，--port：連接數據庫監聽的端口。
--default-character-set：設置默認字符集，默認是utf8。
-A，--all-databases：導出所有數據庫，不過默認情況下是不會導出information_schema庫。
-B，--databases：導出指定的某個或者某幾個數據庫，參數后面所有名字都被看作數據庫名，用空格隔開，包含create database創建庫的語句。
--tables：導出指定表對象，參數格式為“庫名 表名”，默認該參數將覆蓋-B參數。
-w，--where：只導出符合條件的記錄。
-l，--lock-tables：默認參數，鎖定讀取的表對象，想導出一致性備份的話最好使用該參數，但會導致無法對表執行寫入操作。
--single-transaction：該選項導出數據之前提交一個BEGIN SQL語句，BEGIN不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用于innodb存儲引擎。
在innodb導出時會建立一致性快照，在保證導出數據的一致性前提下，又不會堵塞其他會話的讀寫操作。指定這個參數后，其他連接不能執行alter table、drop table、rename table、truncate table這類語句，事務的隔離級別無法空值dd語句。本選項和--lock-tables選項是互斥的，使用參數--single-transaction會自動關閉該選項。
-d，--no-data：只導出表結構，不導出表數據。
-t，--no-create-info：只導出數據，而不添加create table語句。
-f，--force：即使遇到sql錯誤，也繼續執行。
-F，--flush-logs：在執行導出前先刷新二進制日志文件，一般來說，如果是全庫導出，建議先刷新日志文件，否則就不用了。
-x，--lock-all-tables：在導出任務執行期間鎖定所有數據庫的所有表，以保證數據的一致性。這是一個全局鎖定，并且自動關閉--single-transaction和--lock-tables選項。這個參數副作用比較大，這是全局鎖定，備份執行過程中，該庫無法進行讀寫操作，不是所有業務場景都能接受的。請慎用。
-n，--no-create-db：不生成建庫的語句create database...if exists，即使指定--all-database或--database這類參數。
--triggers：導出表的觸發器腳本，默認是啟用狀態。使用--skip-trigger禁用它。
-R，--outlines：導出存儲過程以及自定義函數。
?
1、導出所有數據庫
mysqldump -uroot -p123456 -A > all.sql
或
mysqldump?-uroot -p123456 --all-databases > all2.sql
參數-A代表所有，等同于--all-databases
2、導出某個數據庫
mysqldump -u 用戶名 -p 密碼 數據庫名 > 導出的文件名.sql
mysqldump -uroot -p123456 book > book.sql
3、導出單張表
mysqldump -uroot -p123456 book books > books.sql? ? #導出book庫books表
4、導出庫的表結構
mysqldump -uroot -p123456 -d?book>booktable.sql? ? #只導出book庫中的數據
5、只導出數據
mysqldump -uroot -p123456 -t book>bookdata.sql? ? #只導出book庫中的數據
6、導出數據庫，并自動生成庫的創建語句
mysqldump -uroot -p123456 -B?book2 > book2.sql
mysql -uroot -p123456 < book2.sql? ? 導入不用指定數據庫名
2.2.2、導入數據：
1、導入所有數據庫
mysqldump -uroot -p123456 <all.sql
2、導入數據庫
mysql -uroot -p123456 book <book.sql? ? #如果導入時，沒有對應的數據庫，需要手動創建：create database book
使用source導入
create database book;
use book;
source /root/book.sql
3、導入表
drop table books;
source /root/book.sql;? ? #導入表時，不需要重新創建表，要先進到相應的數據庫中。
4、導入表結構和數據
create database book;
mysql -uroot -p123456 book<booktable.sql
mysql -uroot -p123456 book<bookdata.sql
?
?
總結
以上是生活随笔為你收集整理的MySQL日常维护工具-备份的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Java API 操作 OpenLDAP
- 下一篇： 1-Redis 核心篇：唯快不破的秘密(