MySQL日志及主從復(fù)制實(shí)現(xiàn)
一：MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，最早由瑞典MySQL AB 公司發(fā)，后在2008年1月16號被Sun公司用10億美金收購，但好景不長，2010年4月20日oracle用74億美金收購了Sun，因此到目前MySQL屬于 Oracle旗下產(chǎn)品。MySQL 最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，在 WEB 應(yīng)用方面MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫管理系統(tǒng)) 應(yīng)用軟件之一，MySQL是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)，關(guān)聯(lián)數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉庫內(nèi)，這樣就增加了速度并提高了靈活性。
MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標(biāo)準(zhǔn)化語言。MySQL 軟件采用了雙授權(quán)政策，它分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫，由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境。
MySQL主從同步一共需要三個(gè)線程的操作，主MySQL有一個(gè)IO線程，從MySQL有一個(gè)IO線程和一個(gè)SQL線程， MySQL主從是實(shí)現(xiàn)MySQL高可用、數(shù)據(jù)備份、讀寫分離架構(gòu)的一種最常見的解決方案，在絕大部分公司都有使用，要實(shí)現(xiàn)MySQL主從復(fù)制，必須要在Master打開binary log(bin-log)功能，因?yàn)檎麄€(gè)MySQL的復(fù)制過程實(shí)際就是Slave從Master端獲取響應(yīng)的二進(jìn)制日志，然后在Slave端順序的執(zhí)行日志中所記錄的各種操作，二進(jìn)制日志中幾乎記錄了出select以外的所有針對數(shù)據(jù)庫的sql操作語句，具體的復(fù)制過程如下：
1.1.1：Slave端的IO線程連接上Master，并向Master請求指定日志文件的指定位置（新部署的Master和Slave從最開始的日志）之后的日志。
1.1.2：Master接收到來自Slave的IO線程請求，負(fù)責(zé)IO復(fù)制的IO線程根據(jù)Slave的請求信息讀取相應(yīng)的日志內(nèi)容，然后將本地讀取的bin-log的文件名、位置及指定位置之后的內(nèi)容一起返回給Slave的IO線程處理。
1.1.3：Slave的IO線程將接收到的信息依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到Master-info文件中，以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從哪個(gè)bin-log的哪個(gè)位置開始往后的日志內(nèi)容請發(fā)給我”。
1.1.4：Slave的sql線程檢查到relay-log中新增了內(nèi)容后，會(huì)馬上將relay-log中的內(nèi)容解析為在Master端真實(shí)執(zhí)行時(shí)候的可執(zhí)行命令，并順序執(zhí)行，從而保證對Slave的MySQL進(jìn)行響應(yīng)的增加或刪除等操作，最終實(shí)現(xiàn)和Master數(shù)據(jù)保持一致。
1.2：減少主從同步延時(shí)的方案：
1.2.1：從庫使用SSD硬盤，這樣從庫可以用最快的速度從主控同步數(shù)據(jù)
1.2.2：盡量在主庫使用避免大量的寫入，可以使用其他非關(guān)系型數(shù)據(jù)庫的盡量使用非關(guān)系型數(shù)據(jù)庫
1.2.3：主庫和從庫直接使用專用網(wǎng)絡(luò)高速互聯(lián)
1.2.4：對于數(shù)據(jù)一致性要求比較嚴(yán)格的場景，就不查詢從庫，因?yàn)閺膸炜赡苡袛?shù)據(jù)未同步到數(shù)據(jù)
1.2.5：讀場景比較大的場景，可以使用多個(gè)從庫，將讀請求在各個(gè)slave服務(wù)器分解處理
1.3：數(shù)據(jù)庫延遲復(fù)制，即設(shè)置slave故意從master延遲同步一小時(shí)或其他指定時(shí)間差，用于解決一下三個(gè)問題：
1.3.1：用于在數(shù)據(jù)庫誤操作之后快速的恢復(fù)數(shù)據(jù)，比如誤刪除表，那個(gè)這個(gè)延遲操作在延遲時(shí)間內(nèi)從庫的數(shù)據(jù)還沒有發(fā)生變化，可以使用從庫的數(shù)據(jù)進(jìn)行恢復(fù)，然后再把一小時(shí)內(nèi)的bin-log補(bǔ)寫到Master即可完成數(shù)據(jù)的全部恢復(fù)。
1.3.2：用來做延遲測試，比如做好的數(shù)據(jù)庫讀寫分離，把從庫作為讀庫，即測試主從數(shù)據(jù)延遲五分鐘或指定的時(shí)候，業(yè)務(wù)會(huì)發(fā)生什么樣的問題，即可以模擬數(shù)據(jù)庫延遲指定時(shí)間的業(yè)務(wù)錯(cuò)誤。
1.3.3：用于老數(shù)據(jù)的查詢，比如經(jīng)常需要查看某天前的一個(gè)表或者字段的數(shù)值，你可能需要把備份恢復(fù)之后才能進(jìn)行查看，但是如果有延遲從庫，比如延遲一周，那么久可以解決這一的類似的業(yè)務(wù)需求場景。
1.3.4：設(shè)置延遲復(fù)制，通過設(shè)置SLAVE上的MASTER TO MASTER_DELAY參數(shù)來實(shí)現(xiàn)：
CHANGE MASTER TO MASTER_DELAY = N;
N為多少秒，該語句設(shè)置從庫延時(shí)N秒后再與主數(shù)據(jù)庫進(jìn)行同步復(fù)制
1.3.5：具體MySQL延遲復(fù)制操作，登錄到SLAVE之上進(jìn)行操作：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 600;
mysql> start slave;
mysql> SHOW SLAVE STATUS \G;
1.4：生產(chǎn)環(huán)境MySQL經(jīng)驗(yàn)：
1.4.1：生產(chǎn)應(yīng)用MySQL用戶，不允許執(zhí)行delete，可以只授權(quán)給update、select、insert，可以把delete更改為update，將要?jiǎng)h除的數(shù)據(jù)加一個(gè)字段標(biāo)記為已刪除，因此線上的業(yè)務(wù)可以不需要delete
1.4.2：在有條件的情況下做一個(gè)延遲一小時(shí)等制定時(shí)間的從庫
1.4.3：所有DML操作之前必須備份
1.4.4：規(guī)范開發(fā)在update數(shù)據(jù)庫必須有兩個(gè)腳本，如下：
1.4.4.1：數(shù)據(jù)庫修改腳本，修改數(shù)據(jù)庫的腳本
1.4.4.2：數(shù)據(jù)庫回滾腳本，當(dāng)數(shù)據(jù)更新失敗的時(shí)候用于回滾
1.5：DML操作備份腳本
dml_backup.sh stable_name #輸入表明即可備份表
1.5.1：開啟相關(guān)日志:
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
relay-log = /data/mysql
server-id=20
log-error= /data/mysql_error.txt #記錄錯(cuò)誤日志
log-bin=/data/mysql/master-log #MySQL的bin-log即二進(jìn)制日志，記錄二進(jìn)制形式SQL語句、主從復(fù)制和增量備份使用
general_log=ON #正常查詢?nèi)罩?span id="w0obha2h00" class="pln">
general_log_file=/data/general_mysql.log
long_query_time=2 #慢查詢?nèi)罩九渲?span id="w0obha2h00" class="pln">
slow_query_log=1
slow_query_log_file= /data/slow_mysql.txt
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock
1.5.2：mysqlsla 工具使用：
使用語法如下：
# mysqlslap [options]
常用參數(shù) [options] 詳細(xì)說明：
–auto-generate-sql, -a 自動(dòng)生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力。
–auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))。
–auto-generate-sql-add-auto-increment 代表對生成的表自動(dòng)添加auto_increment列，從5.1.18版本開始支持。
–number-char-cols=N, -x N 自動(dòng)生成的測試表中包含多少個(gè)字符類型的列，默認(rèn)1
–number-int-cols=N, -y N 自動(dòng)生成的測試表中包含多少個(gè)數(shù)字類型的列，默認(rèn)1
–number-of-queries=N 總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
–query=name,-q 使用自定義腳本執(zhí)行測試，例如可以調(diào)用自定義的一個(gè)存儲過程或者sql語句來執(zhí)行測試。
–create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database。
–commint=N 多少條DML后提交一次。
–compress, -C 如果服務(wù)器和客戶端支持都壓縮，則壓縮信息傳遞。
–concurrency=N, -c N 表示并發(fā)量，也就是模擬多少個(gè)客戶端同時(shí)執(zhí)行select。可指定多個(gè)值，以逗號或者–delimiter參數(shù)指定的值做為分隔符。例如：–concurrency=100,200,500。
–engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個(gè)，用分隔符隔開。例如：–engines=myisam,innodb。
–iterations=N, -i N 測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測試多少次。
–only-print 只打印測試語句而不實(shí)際執(zhí)行。
–detach=N 執(zhí)行N條語句后斷開重連。
–debug-info, -T 打印內(nèi)存和CPU的相關(guān)信息。
說明：
測試的過程需要生成測試表，插入測試數(shù)據(jù)，這個(gè)mysqlslap可以自動(dòng)生成，默認(rèn)生成一個(gè)mysqlslap的schema，如果已經(jīng)存在則先刪除。可以用–only-print來打印實(shí)際的測試過程，整個(gè)測試完成后不會(huì)在數(shù)據(jù)庫中留下痕跡。
各種測試參數(shù)實(shí)例（-p后面跟的是mysql的root密碼）：
單線程測試。測試做了什么。
# mysqlslap -a -uroot -p123456
多線程測試。使用–concurrency來模擬并發(fā)連接。
# mysqlslap -a -c 100 -uroot -p123456
迭代測試。用于需要多次執(zhí)行測試得到平均值。
# mysqlslap -a -i 10 -uroot -p123456
# mysqlslap —auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a –auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a –auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a –auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap –create-schema world -q “select count(*) from City” -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a –number-of-queries=10 -uroot -p123456
測試同時(shí)不同的存儲引擎的性能進(jìn)行對比：
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info -uroot -p123456
執(zhí)行一次測試，分別50和100個(gè)并發(fā)，執(zhí)行1000次總查詢：
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -p123456
50和100個(gè)并發(fā)分別得到一次測試結(jié)果(Benchmark)，并發(fā)數(shù)越多，執(zhí)行完所有查詢的時(shí)間越長。為了準(zhǔn)確起見，可以多迭代測試幾次:
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info -uroot -p123456
二：數(shù)據(jù)庫主從同步實(shí)現(xiàn)：
2.1：兩臺服務(wù)器分別安裝MySQL，提前準(zhǔn)備好安裝包和my.conf文件，使用以下腳本自動(dòng)安裝：
[root@sql-master mysql]# cat mysql-install.sh
#!/bin/bash
DIR=`pwd`
NAME="mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
DATA_DIR="/data/mysql"
yum install vim gcc gcc-c++ wget autoconf net-tools lrzsz iotop lsof iotop bash-completion -y
yum install curl policycoreutils openssh-server openssh-clients postfix -y
if [ -f ${FULL_NAME} ];then
echo "安裝文件存在"
else
echo "安裝文件不存在"
exit 3
fi
if [ -h /usr/local/mysql ];then
echo "Mysql 已經(jīng)安裝"
exit 3
else
tar xvf ${FULL_NAME} -C /usr/local/src
ln -sv /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local/mysql
if id mysql;then
echo "mysql 用戶已經(jīng)存在，跳過創(chuàng)建用戶過程"
fi
useradd mysql -s /sbin/nologin
if id mysql;then
chown -R mysql.mysql /usr/local/mysql/* -R
if [ ! -d /data/mysql ];then
mkdir -pv /data/mysql && chown -R mysql.mysql /data -R
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/
cp /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64/support-files