MySQL新增從庫

項目背景描述：

    在項目的開始只有一個MySQL實例在運行，后期因為安全性，壓力，備份等原因需要在此實例的基礎(chǔ)上面新增一個從庫。

分析：

    MySQL主從是基于binlog日志來實現(xiàn)的，那么需要主服務(wù)器開啟binlog，此選項默認是關(guān)閉。我這邊服務(wù)器在部署的時候就開啟了，因為使用binlog可以用來恢復(fù)MySQL數(shù)據(jù)，并且還為以后做主從做好準備，所以我們推薦大家在部署主服務(wù)器的時候就開啟此選項。另外在做主從的時候還需要主庫和從庫的server-id唯一，server-id在做主從的時候也是相當關(guān)鍵的一薦參數(shù)。

    另外因為我們MySQL主庫已經(jīng)運行一段時間了，里面已經(jīng)有相當多數(shù)據(jù)，我們需要將這些數(shù)據(jù)備份出來，然后從庫再從備份的節(jié)點同步數(shù)據(jù)，這樣來保持主從的數(shù)據(jù)一致性，并且在操作過程中最好不要影響我們的業(yè)務(wù)正常運行。最終決定使用xtrabackup來備份數(shù)據(jù)，因為用xtrabackup備份數(shù)據(jù)的時候不需要瑣表，但只限于InnoDB引擎的數(shù)據(jù)庫和XtraDB引擎的數(shù)據(jù)庫，對于MyISAM引擎的數(shù)據(jù)庫還是會瑣表，剛好我們的數(shù)據(jù)庫引擎使用的是InnoDB

操作步驟：

第一步：部署從數(shù)據(jù)庫服務(wù)器，最好數(shù)據(jù)庫版本一致，部署過程省略

第二步：修改配置文件

# 以下為主服務(wù)器需要修改的配置，server_id不一定為82,可以為任何一個數(shù)字，比如我們可以用本機的IP最后一位
[mysqld]
datadir=/data/mysql       #此選項一定要指定，就算有默認也需要指定
log_bin=mysql-bin
server_id=10
expire_logs_days=5        #指定binlog文件保留的天數(shù)，如果硬盤空間夠大可以不指定，或者指定天數(shù)多一點

# 以下為可選項
binlog-ignore-db=mysql,information_schema,performance_schema,sys    #指定忽略不同步的數(shù)據(jù)庫
binlog-do-db=discuz,phpcms                                          #指定要同步的數(shù)據(jù)庫

在主服務(wù)器上面授權(quán)從服務(wù)器一個主從同步賬號

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123qweASD';

說明：以上授權(quán)命令根據(jù)自己需求更改，比如權(quán)限，可訪問數(shù)據(jù)庫，用戶名，授權(quán)客戶端，密碼等信息

# 以下為從服務(wù)器需要修改的配置
datadir=/data/mysql                      #此選項一定要指定，就算有默認也需要指定
server_id = 2

# 以下為可選項
replicate-do-db                          #指定需要同步的數(shù)據(jù)庫
replicate-ignore-db                      #指定不同步的數(shù)據(jù)庫
replicate-do-table                       #指定需要同步的表
replicate-ignore-table                   #指定不同步的表
replicate-wild-do-table                  #指定需要同步的表，可以使用通配符,如test.tables1%
replicate-wild-ignore-table              #指定不需要同步的表，可以使用通配符
# 優(yōu)化同步延遲，根據(jù)項目設(shè)置
slave_parallel_workers = 16     #多少個線程用于主從復(fù)制
slave_parallel_type= logical_clock

第三步：安裝xtrabackup工具（主從服務(wù)器都需要安裝）

至以下連接下載適合自己操作系統(tǒng)版本和MySQL版本的xtrabackup

xtrabackup下載

下載完成后執(zhí)行以下命令安裝 

# 這樣可以自動安裝所需要依賴包
yum -y install percona-xtrabackup-24-2.4.10-1.el7.x86_64.rpm

第四步：備份數(shù)據(jù)

注意：以需要使用root用戶執(zhí)行，或者使用sudo權(quán)限執(zhí)行

備份整個庫

xtrabackup --user=root --password=123456 --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--user             指定數(shù)據(jù)庫訪問用戶名

--password    指定數(shù)據(jù)庫訪問密碼，如果密碼有特殊字符需要使用單引號引起來

--target-dir    指定備份路徑，最好寫絕對路徑

--backup        與--target-dir選項一起使用

-S                   指定mysql.sock文件位置

備份單個庫

xtrabackup --user=root --password=123456 --databases=test1 --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    指定需要備份的庫名

備份多個庫

xtrabackup --user=root --password=123456 --databases="test1 test2 test3" --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    多庫用雙引號引起來，使用空格進行分隔

備份某個庫的指定表

xtrabackup --user=root --password=123456 --databases="test1.tables1 test2.tables2 test3.tables3" --target-dir=/root/mysql_bak -S /tmp/mysql.sock --backup

--databases    備份表使用庫名.表名的方式，如果是多個表就使用雙引號引起來，不同表使用空格分隔

第五步：將備份數(shù)據(jù)復(fù)制到從服務(wù)器

scp -pr /root/mysql_bak/* root@172.16.10.11:/data/mysql_slave

第六步：將數(shù)據(jù)還原到從服務(wù)器

注意：還原之前需要停止從庫數(shù)據(jù)庫服務(wù)并清空從服務(wù)器數(shù)據(jù)目錄，如果有需要的數(shù)據(jù)就先備份到其實地方

# 停止服務(wù)
service mysqld restart || systemctl restart mysqld

# 備份原數(shù)據(jù)
mkdir /data/mysql_bak
mv /data/mysql/* /data/mysql_bak

還原操作

xtrabackup --prepare --target-dir=/data/mysql_slave
xtrabackup --copy-back --target-dir=/data/mysql_slave

第七步：啟動數(shù)據(jù)庫并啟用主從

# 將原備份數(shù)據(jù)復(fù)制回原目錄，注意：ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1四個文件不要覆蓋
# 如果從庫沒有數(shù)據(jù)需要往回復(fù)制的話就不需要cp這步操作
cp -r /data/mysql_bak/* /data/mysql
chown -R mysql:mysql /data/mysql
service mysqld restart || systemctl restart mysqld

執(zhí)行以下命令

mysql> change master to master_host='172.16.10.10',master_port=3306,master_user='slave',master_password='123qweASD',master_log_file='mysql-bin.000011',master_log_pos=154;

master_host：             master服務(wù)器的IP地址

master_port：             master服務(wù)器的端口

master_user：             master服務(wù)器授權(quán)從服務(wù)器主從同步的用戶名

master_password：    master服務(wù)器授權(quán)從服務(wù)器主從同步的密碼

master_log_file：        從主服務(wù)器備份出來的文件：xtrabackup_info中獲取

grep "binlog_pos" xtrabackup_info | awk -F "'" '{print $2}'

master_log_pos：       從主服務(wù)器備份出來的文件：xtrabackup_info中獲取

grep "binlog_pos" xtrabackup_info | awk -F "'" '{print $4}'

執(zhí)行以下命令啟用主從同步

mysql> start slave;

小知識：停止主從同步命令為

# 以下為擴展知識命令，如果執(zhí)行后需要再執(zhí)行上條命令start slave;
mysql> stop slave;

第八步：測試并檢查主從狀態(tài)

mysql> show slave status \G;

如果看以以下兩個值為Yes說明主從同步正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

第九步：測試

可以修改下需要同步的庫，或者表的數(shù)據(jù)看上是否能正常同步去，或者在備份完的時候就去修改下數(shù)據(jù)，這時候備份里同是沒有此修改記錄，當啟用主從同步的時候看下數(shù)據(jù)是否會同步過來