不停止 MySQL 服務增加從庫的兩種方式

現在生產環境mysql資料庫是一主一從，由於業務量訪問不斷增大，故再增加一台從庫。前提是不能影響線上業務使用，也就是說不能重啟MySQL服務，為了避免出現其他情況，選擇在網站訪問量低峰期時間段操作。 一般線上增加從庫有兩種方式，一種是通過mysqldump備份主庫，恢複到從庫，mysqldump是邏輯備份，資料量大時，備份速度會很慢，鎖表的時間也會很長。另一種是通過xtrabackup工具備份主庫，恢複到從庫，xtrabackup是物理備份，備份速度快，不鎖表。為什麼不鎖表？因為自身會監控主庫日誌，如果有更新的資料，就會先寫到一個檔案中，然後再迴歸到備份檔案中，從而保持資料一致性。伺服器資訊：主庫：192.168.18.212（原有）從庫1：192.168.18.213（原有）從庫2：192.168.18.214（新增）資料庫版本：MySQL5.5儲存引擎：Innodb測試庫名：weibo一、mysqldump方式MySQL主從是基於binlog日誌，所以在安裝好資料庫後就要開啟binlog。這樣好處是，一方面可以用binlog恢複資料庫，另一方面可以為主從做準備。原有主庫配置參數如下：# vi my.cnfserver-id = 1 #id要唯一log-bin = mysql-bin #開啟binlog日誌auto-increment-increment= 1 #在Ubuntu系統中MySQL5.5以後已經預設是1auto-increment-offset = 1slave-skip-errors =all #跳過主從複製出現的錯誤1. 主庫建立同步帳號mysql> grant all on*.* to 'sync'@'192.168.18.%' identified by 'sync';2. 從庫配置MySQL# vi my.cnfserver-id = 3 #這個設定3log-bin = mysql-bin #開啟binlog日誌auto-increment-increment= 1 #這兩個參數在Ubuntu系統中MySQL5.5以後都已經預設是1auto-increment-offset = 1slave-skip-errors =all #跳過主從複製出現的錯誤3. 備份主庫# mysqldump -uroot -p123--routines --single_transaction --master-data=2 --databases weibo >weibo.sql參數說明：--routines：匯出預存程序和函數--single_transaction：匯出開始時設定事務隔離狀態，並使用一致性快照開始事務，然後unlock tables;而lock-tables是鎖住一張表不能寫操作，直到dump完畢。--master-data：預設等於1，將dump起始（change master to）binlog點和pos值寫到結果中，等於2是將changemaster to寫到結果中並注釋。 4. 把備份庫拷貝到從庫# scp weibo.sqlroot@192.168.18.214:/home/root5. 在主庫建立test_tb表，類比資料庫新增資料，weibo.sql是沒有的mysql> create tabletest_tb(id int,name varchar(30));6. 從庫匯入備份庫# mysql -uroot -p123 -e'create database weibo;'# mysql -uroot -p123weibo < weibo.sql7. 在備份檔案weibo.sql查看binlog和pos值# head -25 weibo.sql-- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; #大概22行8. 從庫設定從這個日誌點同步，並啟動mysql> change masterto master_host='192.168.18.212', -> master_user='sync', -> master_password='sync', -> master_log_file='mysql-bin.000001', -> master_log_pos=107;mysql> start slave;mysql> show slavestatus\G;ERROR 2006 (HY000): MySQLserver has gone awayNo connection. Trying toreconnect...Connection id: 90Current database: ***NONE ******************************1. row *************************** Slave_IO_State: Waiting formaster to send event Master_Host: 192.168.18.212 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 358 Relay_Log_File:mysqld-relay-bin.000003 Relay_Log_Pos: 504 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes......9. 從庫查看weibo庫裡面的表可以看到IO和SQL線程均為YES，說明主從配置成功。mysql> show tables;+---------------------------+| Tables_in_weibo |+---------------------------+| test_tb |發現剛才類比建立的test_tb表已經同步過來！二、xtrabackup方式（推薦）在上面配置基礎上做實驗，先刪除掉從庫配置：mysql> stopslave; #停止同步mysql> resetslave; #清除從串連資訊mysql> show slavestatus\G; #再查看從狀態，可以看到IO和SQL線程都為NOmysql> drop databaseweibo; #刪除weibo庫此時，從庫現在和新裝的一樣，繼續前進！1. 主庫使用xtrabackup備份# innobackupex--user=root --password=123 ./產生一個以時間為命名的備份目錄：2015-07-01_16-49-43# ll 2015-07-01_16-49-43/total 18480drwxr-xr-x 5 rootroot 4096 Jul 1 16:49 ./drwx------ 4 rootroot 4096 Jul 1 16:49 ../-rw-r--r-- 1 rootroot 188 Jul 1 16:49 backup-my.cnf-rw-r----- 1 root root18874368 Jul 1 16:49 ibdata1drwxr-xr-x 2 rootroot 4096 Jul 1 16:49 mysql/drwxr-xr-x 2 rootroot 4096 Jul 1 16:49 performance_schema/drwxr-xr-x 2 rootroot 12288 Jul 1 16:49 weibo/-rw-r--r-- 1 rootroot 21 Jul 1 16:49 xtrabackup_binlog_info-rw-r----- 1 rootroot 89 Jul 1 16:49 xtrabackup_checkpoints-rw-r--r-- 1 rootroot 563 Jul 1 16:49 xtrabackup_info-rw-r----- 1 rootroot 2560 Jul 1 16:49 xtrabackup_logfile2. 把備份目錄拷貝到從庫上# scp -r2015-07-01_16-49-43 root@192.168.18.214:/home/root3. 從庫上把MySQL服務停掉，刪除datadir目錄，將備份目錄重新命名為datadir目錄# sudo rm -rf/var/lib/mysql/# sudo mv2015-07-01_16-49-43/ /var/lib/mysql# sudo chown mysql.mysql-R /var/lib/mysql# sudo /etc/init.d/mysqlstart# ps -ef |grep mysql #查看已經正常啟動mysql 8832 1 0 16:55 ? 00:00:00 /usr/sbin/mysqld4. 在主庫建立test_tb2表，類比資料庫新增資料mysql> create tabletest_tb2(id int,name varchar(30));5. 從備份目錄中xtrabackup_info檔案擷取到binlog和pos位置# cat/var/lib/mysql/xtrabackup_info uuid =201af9db-1fce-11e5-96b0-525400e4239dname = tool_name = innobackupextool_command =--user=root --password=... ./tool_version =1.5.1-xtrabackupibbackup_version =xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revisionid: )server_version =5.5.43-0ubuntu0.12.04.1-logstart_time = 2015-07-0116:49:43end_time = 2015-07-0116:49:46lock_time = 1binlog_pos = filename'mysql-bin.000001', position 429 #這個位置innodb_from_lsn = 0innodb_to_lsn = 1598188partial = Nincremental = Nformat = filecompact = Ncompressed = N6. 從庫設定從這個日誌點同步，並啟動mysql> change masterto master_host='192.168.18.212', -> master_user='sync', -> master_password='sync', -> master_log_file='mysql-bin.000001', -> master_log_pos=429;mysql> start slave;mysql> show slavestatus\G;***************************1. row *************************** Slave_IO_State: Waiting formaster to send event Master_Host: 192.168.18.212 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 539 Relay_Log_File:mysqld-relay-bin.000002 Relay_Log_Pos: 363 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes......7. 從庫查看weibo庫裡面的表可以看到IO和SQL線程均為YES，說明主從配置成功。mysql> show tables;+---------------------------+| Tables_in_weibo |+---------------------------+| test_tb || test_tb2 |發現剛才類比建立的test_tb2表已經同步過來。免費領取兄弟連IT教育原創linux營運工程師視頻/細說linux教程，詳情諮詢官網客服：http://www.lampbrother.net/linux/學PHP、Linux、HTML5、UI、Android等視頻教程（課件+筆記+視頻）！聯絡Q2430675018參加活動領取兄弟連原創視頻教程光碟片合集：http://www.lampbrother.net/newcd.html

