MySQL多執行個體，主從同步

標籤：style   blog   http   color   io   os   ar   使用   for   

由於背景原因，所做的主從同步還是要基於MySQL 5.1的版本，主從同步主要是一個資料庫讀寫訪問原來的資料庫熱度過大，需要做到使用從庫對讀分壓。

MySQL主從同步介紹

    MySQL 支援單雙向、鏈式級聯、非同步複製。在複製過程中，一個伺服器充當主伺服器（Master）,而一個或多個其它的伺服器充當從伺服器（Slave）。    如果設定了鏈式級聯複製，那麼，從（slave）伺服器本身除了充當從伺服器外，也會同時充當其下面從伺服器的主伺服器。鏈式級聯複製類似A->B ->C ->D 的複製形式。    當配置好主從複製後，所有對資料庫內容的更新就必須在主伺服器上進行，以避免使用者對主伺服器上資料內容的更新與對從伺服器上資料庫內容的更新之間發生衝突。生產環境中一般會，忽略授權表同步，然後對從伺服器上的而使用者授權select讀許可權，或在my.cnf設定檔中加read-only 參數來確保從庫唯讀，當然二者同時操作效果更佳。 MySQL主從複製的原理MySQL 主從複製是一個非同步複製過程（但看起來也是即時的），資料庫資料從一個MySQL資料庫（我們稱為Master)複製到另一個MySQL資料庫（我們稱之為Slave）。在Master和Slave之間實現整個主從複製的過程有三個線程參與完成。其中兩個線程（SQL線程和IO線程）在Slave端，另一個線程（IO線程）在Master端。   要實現MySQL的主從複製，首先必須開啟Master端的Binlog(MySQL-bin.XXXXX)功能，否則無法實現主從複製。因為整個複製過程實際上就是Slave從Master端擷取Binlog日誌，然後再在Slave自身上以相同順序執行binlog日誌中所記錄的各種操作。開啟MySQL的binlog可以通過MySQL的設定檔my.cnf中的mysqld模組（[mysqld]標識後的參數部分）添加“log-bin”參數項。   MySQL主從複製流程說明  下面簡單描述下MySQL Replication的複製過程：1.Slave伺服器上執行start slave，開啟主從複製開關。2.此時，Slave伺服器的IO線程會通過在Master上授權的複製使用者請求串連Master伺服器，並請求從指定Binlog記錄檔的指定位置（記錄檔和位置是在配置主從服務時change master 時指定的）之後的Binlog日誌內容。3.Master伺服器接收到來自Slave伺服器的IO線程的請求後，Master伺服器上負責複製IO線程根據Slave伺服器的IO線程請求的資訊讀取指定Binlog記錄檔指定位置之後的Binlog日誌資訊，然後返回給Slave端的IO線程。返回的資訊中除了日誌內容外，還有本次返回日誌內容在Master伺服器端的新Binlog檔案名稱以及在Binlog中的指定位置。4. 當Slave伺服器的IO線程擷取到來自Master伺服器上IO線程發送日誌內容及記錄檔及位置點後，將Binlog日誌內容依次寫入到Slave端自身的Relay Log（即中繼日誌）檔案（MySQL-relay-bin.xxxxx)的最末端，並將新的Binlog檔案名稱和位置記錄到master-info檔案中，以便下一次讀取Master端新binlog日誌時能夠告訴Master伺服器需要從新binlog日誌的那個檔案那個位置開始請求新的Binlog日誌內容。5. Slave伺服器的SQL線程會即時的檢測本地Relay Log中新增加了日誌內容，然後及時的把Log檔案中的內容解析成在Master端曾經執行的SQL語句的內容，並在自身Slave伺服器上按語句的順序執行應用這些ＳＱＬ語句。6. 經過了上面的過程，就可以確保在Master端和Slave端執行了同樣的SQL語句。當複製狀態正常的情況下，Master端和Slave端的資料是完全一樣的。主從複製的原理圖  具體實施主從複製下面介紹的測試環境的安裝，關於線上的環境不好多講，具體步驟如下： 單一實例安裝步驟

1.建立MySQL 賬戶

 

#groupadd mysql #useradd -s /sbin/nologin -g mysql -M mysql

#tail -l /etc/passwd

建立 MySQL 軟體目錄

#mkdir -p /home/tools

#cd /home/tools/

2.編譯安裝MySQL 軟體(http://down1.chinaunix.net/distfiles/mysql-5.1.62.tar.gz)

 

#tar zxf mysql-5.1.62.tar.gz #cd mysql-5.1.62

配置

./configure \

--prefix=/usr/local/mysql \

--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \

--localstatedir=/usr/local/mysql/data \

--enable-assembler \

--enable-thread-safe-client \

--with-mysqld-user=mysql \

--with-big-tables \

--without-debug \

--with-pthread \

--enable-assembler \

--with-extra-charsets=complex \

--with-ssl \

--with-embedded-server \

--enable-local-infile \

--with-plugins=partition,innobase \

--with-plugin-PLUGIN \

--with-mysqld-ldflags=-all-static \

--with-client-ldflags=-all-static

3.靜態編譯產生mysqld的執行檔案

 #make

4.安裝MySQL

 #make install

5.擷取MySQL 設定檔

 #ls -l support-files/*.cnf #cp support-files/my-small.cnf /etc/my.cnf

6.建立資料庫檔案

 #mkdir -p /usr/local/mysql/data #chown -R mysql.mysql /usr/local/mysql

#/usr/local/mysql/bin/mysql_install_db --user=mysql

#

 7.啟動MySQL 資料庫

 #cp support-files/mysql.server /usr/local/mysql/bin #netstat -lnt|grep 3306

#/user/local/bin/mysql_safe --user=mysql &

8.配置MySQL 命令的全域使用路徑

#echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ >>/etc/profile #source /etc/profile

9.配置/etc/init.d/mysqld start 方式啟動資料庫

#cp support-files/mysql.server /etc/init.d/mysqld #chmod 700 /etc/init.d/mysqld

#/etc/init.d/mysqld restart

 多執行個體安裝

 1.採用不同的連接埠來作為二級目錄

mkdir -p /data/{3306,3307}/data

 2 建立MySQL多執行個體設定檔

ls -l support-files/*.cnf /bin/cp support-files/my-small.cnf /etc/my.cnf

  3 通過vi命令添加如下：

vi /data/3306/my.cnf vi /data/3307/my.cnf

 my.cnf配置

[client] port            = 3306 socket          = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user    = mysql port    = 3306 socket  = /data/3306/mysql.sock basedir = /usr/local/mysql datadir = /data/3306/data open_files_limit    = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_cache_size = 2M query_cache_limit = 1M query_cache_min_res_unit = 2k default_table_type = InnoDB thread_stack = 192K transaction_isolation = READ-COMMITTED tmp_table_size = 2M max_heap_table_size = 2M long_query_time = 1 log_long_format log-error = /data/3306/error.log log-slow-queries = /data/3306/slow.log pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_buffer_size = 16M read_buffer_size = 1M read_rnd_buffer_size = 1M bulk_insert_buffer_size = 1M myisam_sort_buffer_size = 1M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover lower_case_table_names = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_barry3306.err pid-file=/data/3306/mysqld.pid

 4 建立MySQL多執行個體的開機檔案  開機檔案mysql 3306

#!/bin/sh#/data/3306/mysql 指令碼 #init port=3306 mysql_user="root" mysql_pwd="" CmdPath="/usr/local/mysql/bin" #startup function function_start_mysql() {     printf "Starting MySQL...\n"     /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & } #stop function function_stop_mysql() {     printf "Stoping MySQL...\n"     ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown } #restart function function_restart_mysql() {     printf "Restarting MySQL...\n"     function_stop_mysql     sleep 2     function_start_mysql } case $1 in start)     function_start_mysql ;; stop)     function_stop_mysql ;; restart)     function_restart_mysql ;; *)     printf "Usage: /data/${port}/mysql {start|stop|restart}\n" esac

 5 目錄結構

tree /data/data   --3306      |--my.cnf      |--mysql     |--data  --3307     |--my.cnf      |--mysql     |--data #授權chown -R mysql.mysql /datafind /data -name mysql -exec chmod 700 {} \

 6 配置MySQL命令全域使用路徑 配置全域路徑意義  如果不配置MySQL命令的全域路徑，就無法直接敲MySQL等命令，也就是只能/usr/local/msyql/bin/mysql這樣敲命令。 

echo ‘export PATH=$PATH:/usr/local/mysql/bin‘ >>/etc/profile source /etc/profile

 7 建立MySQL多執行個體資料檔案

mysql_install_db --datadir=/data/3306/data  --user=mysql mysql_install_db --datadir=/data/3307/data  --user=mysql

 8啟動MySQL多執行個體資料庫

/data/3306/mysql start /data/3307/mysql start #檢查MySQL資料是否啟動 netstat -lnt|grep 330[6,7]

 9 配置MySQL多執行個體資料庫開機自動啟動

echo "/data/3306/mysql start" >>/etc/rc.localecho "/data/3307/mysql start" >>/etc/rc.local

 10 登入MySQL測試

mysql -S /data/3306/mysql.sockmysql -S /data/3307/mysql.sock

 11 MySQL 安全配置  為root增加密碼

mysqladmin -u root -S /data/3306/mysql.sock password ‘barry123‘  #<- 更改預設密碼。 mysql -S /data/3306/mysql.sock #<- 無法直接登入 mysql -uroot -p -S /data/3306/mysql.sock #<-新的登入方式

 查看並清理多餘使用者

select user,host form mysql.user

 

 主從複製配置

  主庫，稱為Master 從庫稱為Slave。  1. 主庫上執行操作 （1） 設定server-id 值並開啟binlog設定  根據前文MySQL的同步原理，我們知道複製的關鍵因素就是binlog日誌。執行 vi /data/3306/my.cnf 編輯my.cnf設定檔，按如下兩個參數內容修改：

[mysqld] server-id =1 log-bin=/data/3306/mysql-bin

檢查配置後的結果

grep -E "server-id|log-bin" /data/3306/my.cnf log-bin=/data/3306/mysql-bin   server-id=1

 （2） 建立用於同步的帳號rep 

mysql -uroot -p‘‘ -S /data/3306/mysql.sock grant replication slave on *.* to ‘rep‘@‘10.0.0.%‘ identified by ‘password‘;

 （3） 鎖表唯讀（當前視窗不要關閉）生產環境時，操作主從複製，需要申請停機事件，鎖表會影響業務。

flush tables with read lock; interactive_timeout=60 wait_timeout=60

 （4） 查看主庫狀態 查看主庫狀態，即當前記錄檔名和二進位位移量show master status 命令顯示的資訊要記錄在案，後面的從庫複製時是從這個位置開始同步。 （5）匯出資料庫資料 單開新視窗，匯出資料庫資料，如果資料量比較大，可以停庫直接打包資料檔案遷移。

mkdir /server/backup/ -p mysqldump -uroot -p‘password‘ -S /data/3306/mysql.sock -A -B |gzip >/server/backup/mysql_bak.${date +%F}.sql.gz ls -l /server/backup/mysql_bak.${date +%F}.sql.gz

 導為了確保導庫期間，資料庫沒有資料插入，可以再檢查下主庫狀態資訊

mysql -u root -p‘password‘ -S /data/3306/mysql.sock -e "show master status"

 導庫後，解鎖主庫，恢複可寫：

unlock tables;

 （6） 把主庫備份的MySQL資料移轉到從庫 這不常用命令有scp,rsync等，相關命令前面的課程已詳細講解過了，這裡就不多描述了。本文講解的是單資料庫多執行個體主從配置，因此，資料在一台機器上，查看下資料  2 從庫執行操作 （1） 設定server-id 值並關閉binlog設定 資料庫的server-id一般在LAN內是唯一的，這裡的server-id要和主庫及其他從庫不同，並解析掉從庫的binlog參數配置執行vi /data/3307/my.cnf設定檔，按如下兩個參數內容修改：

[mysqld] server-id=2 #log-bin=/data/3307/mysql-bin

 （2） 還原主庫匯出的資料備份

gzip -d mysql_bak.2014-04-17.sql.gz mysql -uroot -p‘password‘ -S /data/3307/mysql.sock < mysql_bak.2014-04-17.sql

 （3） 登入從庫配置同步參數

mysql -uroot -p‘password‘ -S /data/3307/mysql.sock CHANGE MASTER TO MASTER_HOST=‘10.0.0.x‘, <==這裡是主庫的IP MASTER_PORT=3306,  <==這裡是主庫的連接埠，從庫連接埠可以和主庫不同。 MASTER_USER=‘rep‘,<==這裡是主庫上建立的用於複製的使用者rep MASTER_PASSWORD=‘password‘, MASTER_LOG_FILE=‘mysql-bin.0000008‘,<==這裡是show master status時看到的查到二進位檔案名稱 MASTER_POS=342;<==這裡是show master status時看到的查看二進位日誌位移量，注意不能多空格。

 不登陸資料庫，在命令列快速執行CHANGE MASTER的語句（適合在指令碼中批量建Slave庫用）本文即用此法來操作

cat |mysql -uroot -p‘password‘ -S /data/3307/mysql.sock<< EOFCHANGE MASTER TOMASTER_HOST=‘10.0.0.x‘, MASTER_PORT=3306,  MASTER_USER=‘rep‘, MASTER_PASSWORD=‘password‘, MASTER_LOG_FILE=‘mysql-bin.0000008‘, MASTER_LOG_POS=342;EOF

 也可登入資料庫裡面執行如下語句： 

CHANGE MASTER TOMASTER_HOST=‘192.168.1.234‘, MASTER_PORT=3306,  MASTER_USER=‘rep‘, MASTER_PASSWORD=‘password‘, MASTER_LOG_FILE=‘mysql-bin.000010‘, MASTER_LOG_POS=261;

 (4) 啟動從庫同步開關啟動從庫同步開關，並查看同步狀態 

mysql -uroot -p‘password‘ -S /data/3307/mysql.sock -e "start slave;" mysql -uroot -p‘password‘ -S /data/3307/mysql.sock -e "show slave status\G;"

 判斷複製是否搭建成功就看如下IO和SQL兩個線程是否顯示為Yes狀態Slave_IO_Running:Yes  負責從庫去主庫讀取Binlog日誌，並寫入從庫的中繼日誌中Slave_SQL_Runnint:Yes  負責讀取並中繼日誌中Binlog，轉換SQL語句後應用到資料庫匯總 Ok 到此整個過程基本上完成了。

 

MySQL多執行個體，主從同步