MySQL 5.5 主從複製非同步、半同步以及注意事項詳解
大綱
一、前言
二、Mysql 基礎知識
三、Mysql 複製(Replication)
四、Mysql 複製(Replication)型別
五、Mysql 主從複製基本步驟
六、Mysql 主從複製（非同步）
七、Mysql 主從複製（半同步）
八、Mysql 複製工具
九、Mysql 複製注意事項
十、Mysql 複製過濾
一、前言
從這一篇部落格開始我們就來學習mysql的高階課程，在前面的幾篇部落格我們講解了mysql基礎知識、mysql日誌型別、mysql配置檔案、mysql備份策略，這一篇部落格中我們來講解mysql的複製。
二、mysql基礎知識
1.mysql日誌型別
二進位制日誌
事務日誌
錯誤日誌
一般查詢日誌
中繼日誌
慢查詢日誌
注，有博友對mysql日誌不怎麼了解，可以參考這篇部落格http://freeloda.blog.51cto.com/2033581/1253991
2.mysql二進位制日誌詳解
說明：預設開啟，精確的記錄了使用者對資料庫中的資料進行操作的命令和操作的資料物件。
二進位制日誌檔案的作用：
提供了增量備份的功能
提供了資料基於時間點的恢復，這個恢復的時間點可以由使用者控制
為mysql的複製架構提供基礎，將這主伺服器的二進位制日誌複製到從伺服器上並執行同樣的操作，就可將資料進行同步
實現資料的高可用
分擔負載
二進位制日誌預設存放位置：
資料目錄下，以mysql-bin.XXXXXX命名的日誌
二進位制日誌格式：
基於語句 statement
基於行 row
混合方式 mixed
二進位制日誌滾動方式：
重啟服務
日誌達到最大上限
執行flush logs
二進位制日誌事件：
position 基於位置，也就是offset（偏移量）
datetime 基於時間
timestamp
二進位制日誌的檢視與刪除方式：
mysql>show master status; 檢視當前正在使用的二進位制日誌
mysql>show binlog events in'mysql-bin.000001'; 檢視二進位制日誌記錄的事件[from position]
mysql>flush logs; 二進位制日誌滾動
mysql>show binary logs; 檢視所有二進位制日誌
mysql>purge binary logs to 'mysql-bin.000003'; 刪除二進位制日誌
檔案系統中檢視二進位制日誌的命令：
mysqlbinlog
相關選項，
--start-position #開始位置
--stop-position #結束位置
--start-datetime 'yyyy-mm-dd hh:mm:ss'; #開始時間
--stop-datetime ''; #結束時間
配置mysql的主配置檔案：
sql_log_bin = {ON|OFF} #用於控制二進位制日誌資訊是否記錄進日誌檔案。預設為ON，表示啟用記錄功能。使用者可以在會話級別修改此變數的值，但其必須具有SUPER許可權
binlog_cache_size = 32768 #預設值32768 Binlog Cache 用於在開啟了二進位制日誌（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時快取binlog資料的記憶體區域。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來快取了
binlog_stmt_cache_size = 32768 #當非事務語句使用二進位制日誌快取，但是超出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句
log_bin = mysql-bin #指定binlog的位置，預設在資料目錄下
binlog-format= {ROW|STATEMENT|MIXED} #指定二進位制日誌的型別，預設為MIXED。如果設定了二進位制日誌的格式，卻沒有啟用二進位制日誌，則MySQL啟動時會產生警告日誌資訊並記錄於錯誤日誌中。
sync_binlog = 10 #設定多久同步一次二進位制日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位制每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位制日誌同步，否則，每個事務的提交會引起二進位制日誌同步
max_binlog_cache_size = {4096 .. 18446744073709547520} #二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取，其上限由max_binlog_stmt_cache_size決定。
max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取
expire_log_days = {0..99} #設定二進位制日誌的過期天數，超出此天數的二進位制日誌檔案將被自動刪除。預設為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時
注：一般建議將binlog日誌與資料檔案分開存放，不但可以提高mysql效能，還可以增加安全性！
三、Mysql 複製(Replication)
1.Mysql 複製作用
負載平衡(load balancing)
備份
高可用性(high availability)和容錯
2.Mysql 複製如何工作
主要有三步（如下圖）：
master將改變記錄到二進位制日誌(binary log)中（這些記錄叫做二進位制日誌事件，binary log events）；
slave將master的binary log events拷貝到它的中繼日誌(relay log)；
slave重做中繼日誌中的事件，將改變反映它自己的資料。
mysql03-1
具體說明：
該過程的第一部分就是master記錄二進位制日誌。在每個事務更新資料完成之前，master在二日誌記錄這些改變。MySQL將事務序列的寫入二進位制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位制日誌完成後，master通知儲存引擎提交事務。
下一步就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作執行緒——I/O執行緒。I/O執行緒在master上開啟一個普通的連線，然後開始binlog dump process。Binlog dump process從master的二進位制日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O執行緒將這些事件寫入中繼日誌。
SQL slave thread處理該過程的最後一步。SQL執行緒從中繼日誌讀取事件，更新slave的資料，使其與master中的資料一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。
此外，在master中也有一個工作執行緒：和其它MySQL的連線一樣，slave在master中開啟一個連線也會使得master開始一個執行緒。複製過程有一個很重要的限制——複製在slave上是序列化的，也就是說master上的並行更新操作不能在slave上並行操作。所以slave上資料一般要慢於master上資料。即master與slave之間的資料在一定時間內會不同步。
四、Mysql 複製(Replication)型別
1.mysql複製型別有以下一些基本原則
每個slave只能有一個master；
每個slave只能有一個唯一的伺服器ID；
每個master可以有很多slave；
如果你設定log_slave_updates，slave可以是其它slave的master，從而擴散master的更新。
注，MySQL不支援多主伺服器複製(Multimaster Replication)——即一個slave可以有多個master。但是，通過一些簡單的組合，我們卻可以建立靈活而強大的複製體系結構。
2.mysql複製型別
(1).一主多從模式
注，由一個master和一個slave組成複製系統是最簡單的情況。Slave之間並不相互通訊，只能與master進行通訊。
具體說明：
如果寫操作較少，而讀操作很時，可以採取這種結構。你可以將讀操作分佈到其它的slave，從而減小master的壓力。但是，當slave增加到一定數量時，slave對master的負載以及網路頻寬都會成為一個嚴重的問題。這種結構雖然簡單，但是，它卻非常靈活，足夠滿足大多數應用需求。一些建議：
不同的slave扮演不同的作用(例如使用不同的索引，或者不同的儲存引擎)
用一個slave作為備用master，只進行復制
用一個遠端的slave，用於災難恢復
傳送複製事件到其它slave，當設定log_slave_updates時，你可以讓slave扮演其它slave的master。此時，slave把SQL執行緒執行的事件寫進行自己的二進位制日誌(binary log)，然後，它的slave可以獲取這些事件並執行它。
(2).雙主模式
注，Master-Master複製的兩臺伺服器，既是master，又是另一臺伺服器的slave。
具體說明：
主動的Master-Master複製有一些特殊的用處。例如，地理上分佈的兩個部分都需要自己的可寫的資料副本。這種結構最大的問題就是更新衝突。假設一個表只有一行(一列)的資料，其值為1，如果兩個伺服器分別同時執行如下語句：
在第一個伺服器上執行：
mysql> UPDATE tbl SET col=col + 1;
在第二個伺服器上執行：
mysql> UPDATE tbl SET col=col * 2;
那麼結果是多少呢？一臺伺服器是4，另一個伺服器是3，但是，這並不會產生錯誤。
實際上，MySQL並不支援其它一些DBMS支援的多主伺服器複製(Multimaster Replication)，這是MySQL的複製功能很大的一個限制(多主伺服器的難點在於解決更新衝突)，但是，如果你實在有這種需求，你可以採用MySQL Cluster，以及將Cluster和Replication結合起來，可以建立強大的高效能的資料庫平臺。但是，可以通過其它一些方式來模擬這種多主伺服器的複製。
(3).主從模式
注，這是master-master結構變化而來的，它避免了M-M的缺點，實際上，這是一種具有容錯和高可用性的系統。它的不同點在於其中一個服務只能進行只讀操作。
(4).帶從伺服器的Master-Master結構(Master-Master with Slaves)
注，這種結構的優點就是提供了冗餘。在地理上分佈的複製結構，它不存在單一節點故障問題，而且還可以將讀密集型的請求放到slave上。
總結：一般常用的兩種複製型別一種是主從模式，另一種是一主多從模式。在這一篇部落格中我們主要講解主從模式複製。
五、Mysql 主從複製基本步驟
1.master 配置
啟用二進位制日誌
配置一個唯一的server-id
建立具有複製許可權的使用者
2.slave 配置
啟用中繼日誌
配置一個唯一的server-id
連線主伺服器，並開始複製資料
啟動資料複製
注，基本步驟我們就說到這裡，下面我們來具體演示一下主從複製。
六、Mysql 主從複製（非同步）
1.mysql非同步複製
非同步複製：MySQL本身支援單向的、非同步的複製。非同步複製意味著在把資料從一臺機器拷貝到另一臺機器時有一個延時，最重要的是這意味著當應用系統的事務在主伺服器上提交併確認時資料並不能在同一時刻拷貝或應用到從伺服器上。通常這個延時是由網路頻寬、資源可用性和系統負載決定的。然而，使用正確的元件並且調優，複製能做到接近瞬時完成。
當主庫有更新的時候，主庫會把更新操作的SQL寫入二進位制日誌(Bin log)，並維護一個二進位制日誌檔案的索引，以便於日誌檔案輪迴（Rotate）。在從庫啟動非同步複製的時候，從庫會開啟兩個I/O執行緒，其中一個執行緒連線主庫，要求主庫把二進位制日誌的變化部分傳給從庫，並把傳回的日誌寫入本地磁碟。另一個執行緒則負責讀取本地寫入的二進位制日誌，並在本地執行，以反映出這種變化。較老的版本在複製的時候只啟用一個I/O執行緒，實現這兩部分的功能。下面我們來具體演示一下mysql的非同步複製。
2.實驗拓撲
注，Active (master,node1) 192.168.1.201 ， Passive (slave,node2)192.168.1.202
3.環境配置
時間同步
[root@node1 ~]# ntpdate 202.120.2.101
[root@node2 ~]# ntpdate 202.120.2.101
4.作業系統
CentOS 6.4 X86_64
5.軟體版本
Mysql 5.5.33 (注，這裡用的是mysql 5.5.33二進位制通用安裝包，解壓就能用)
6.安裝並配置mysql
master:
(1).解壓並連結
[root@node1 src]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@node1 src]# cd /usr/local/
[root@node1 local]# ln -sv /usr/local/mysql-5.5.33-linux2.6-x86_64 mysql
"mysql" -> "/usr/local/mysql-5.5.33-linux2.6-x86_64"
[root@node1 local]# cd mysql
[root@node1 mysql]# ll
總用量 200
drwxr-xr-x 2 root root 4096 8月 24 17:58 bin
-rw-r--r-- 1 7161 wheel 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root root 4096 8月 24 17:58 data
drwxr-xr-x 2 root root 4096 8月 24 17:58 docs
drwxr-xr-x 3 root root 4096 8月 24 17:58 include
-rw-r--r-- 1 7161 wheel 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root root 4096 8月 24 17:58 lib
drwxr-xr-x 4 root root 4096 8月 24 17:58 man
drwxr-xr-x 10 root root 4096 8月 24 17:58 mysql-test
-rw-r--r-- 1 7161 wheel 2496 7月 15 20:01 README
drwxr-xr-x 2 root root 4096 8月 24 17:58 scripts
drwxr-xr-x 27 root root 4096 8月 24 17:58 share
drwxr-xr-x 4 root root 4096 8月 24 17:58 sql-bench
drwxr-xr-x 3 root root 4096 8月 24 17:58 support-files
(2).新建mysql使用者
[root@node1 mysql]# groupadd -g 3306 mysql
[root@node1 mysql]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql
[root@node1 mysql]# id mysql
uid=3306(mysql) gid=3306(mysql) 組=3306(mysql)
(3).修改mysql安裝目錄所有者與所屬組
[root@node1 mysql]# chown -R root.mysql /usr/local/mysql/*
[root@node1 mysql]# ll
總用量 200
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 bin
-rw-r--r-- 1 root mysql 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 data
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 docs
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 include
-rw-r--r-- 1 root mysql 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 lib
drwxr-xr-x 4 root mysql 4096 8月 24 17:58 man
drwxr-xr-x 10 root mysql 4096 8月 24 17:58 mysql-test
-rw-r--r-- 1 root mysql 2496 7月 15 20:01 README
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 scripts
drwxr-xr-x 27 root mysql 4096 8月 24 17:58 share
drwxr-xr-x 4 root mysql 4096 8月 24 17:58 sql-bench
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 support-files
(4).為mysql提供配置檔案並修改
[root@node1 mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@node1 mysql]# vim /etc/my.cnf
[mysqld]
#增加一行
datadir = /mydata/data
(5).為mysql提供啟動指令碼
[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node1 mysql]# chmod +x /etc/init.d/mysqld
(6).初始化mysql資料庫
[root@node1 ~]# mkdir -pv /mydata/data
mkdir: 已建立目錄 "/mydata"
mkdir: 已建立目錄 "/mydata/data"
[root@node1 ~]# chown -R mysql.mysql /mydata/data/
[root@node1 ~]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
Installing MySQL system tables...
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
Installation of system tables failed! Examine the logs in
/mydata/data/ for more information.
You can try to start the mysqld daemon with:
shell> /usr/local/mysql/bin/mysqld --skip-grant &
and use the command line tool /usr/local/mysql/bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/local/mysql/bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /mydata/data/ that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And remember, if
you do mail us, you MUST use the /usr/local/mysql/scripts/mysqlbug script!
[root@node1 ~]# cd /mydata/data/
[root@node1 data]# ll
總用量 8
drwx------ 2 mysql root 4096 8月 24 18:21 mysql
drwx------ 2 mysql root 4096 8月 24 18:21 test
(7).啟動並測試
啟動報錯
[root@node1 data]# service mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/mydata/data/node1.test.com.pid).
檢視一下錯誤日誌
[root@node1 data]# vim node1.test.com.err
130824 18:21:44 mysqld_safe Starting mysqld daemon with databases from /mydata/data
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
130824 18:21:44 mysqld_safe mysqld from pid file /mydata/data/node1.test.com.pid ended
注，從錯誤日誌中我們看到差一個庫檔案libaio，我們用yum安裝一下即可。
[root@node1 data]# yum install -y libaio
重新初始化mysql
[root@node1 data]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
啟動mysql
[root@node1 data]# service mysqld start
Starting MySQL... SUCCESS!
環境變數配置
[root@node1 data]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 data]# source /etc/profile
測試一下
[root@node1 data]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.03 sec)
mysql>
好了，到這裡master的mysql配置完成，下面我們進行slave的mysql配置。
slave:
(1).解壓並連結
[root@node2 ~]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@node2 ~]# cd /usr/local/
[root@node2 local]# ln -sv /usr/local/mysql-5.5.33-linux2.6-x86_64 mysql
"mysql" -> "/usr/local/mysql-5.5.33-linux2.6-x86_64"
[root@node2 local]# cd mysql
[root@node2 mysql]# ll
總用量 200
drwxr-xr-x 2 root root 4096 8月 24 18:41 bin
-rw-r--r-- 1 7161 wheel 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root root 4096 8月 24 18:41 data
drwxr-xr-x 2 root root 4096 8月 24 18:41 docs
drwxr-xr-x 3 root root 4096 8月 24 18:41 include
-rw-r--r-- 1 7161 wheel 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root root 4096 8月 24 18:41 lib
drwxr-xr-x 4 root root 4096 8月 24 18:41 man
drwxr-xr-x 10 root root 4096 8月 24 18:41 mysql-test
-rw-r--r-- 1 7161 wheel 2496 7月 15 20:01 README
drwxr-xr-x 2 root root 4096 8月 24 18:41 scripts
drwxr-xr-x 27 root root 4096 8月 24 18:41 share
drwxr-xr-x 4 root root 4096 8月 24 18:41 sql-bench
drwxr-xr-x 3 root root 4096 8月 24 18:41 support-files
(2).新建mysql使用者
[root@node2 mysql]# groupadd -g 3306 mysql
[root@node2 mysql]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql
[root@node2 mysql]# id mysql
uid=3306(mysql) gid=3306(mysql) 組=3306(mysql)
(3).修改mysql安裝目錄所有者與所屬組
[root@node2 mysql]# chown -R root.mysql /usr/local/mysql/*
[root@node2 mysql]# ll
總用量 200
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 bin
-rw-r--r-- 1 root mysql 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 data
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 docs
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 include
-rw-r--r-- 1 root mysql 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 lib
drwxr-xr-x 4 root mysql 4096 8月 24 18:41 man
drwxr-xr-x 10 root mysql 4096 8月 24 18:41 mysql-test
-rw-r--r-- 1 root mysql 2496 7月 15 20:01 README
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 scripts
drwxr-xr-x 27 root mysql 4096 8月 24 18:41 share
drwxr-xr-x 4 root mysql 4096 8月 24 18:41 sql-bench
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 support-files
(4).為mysql提供配置檔案並修改
[root@node2 mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@node2 mysql]# vim /etc/my.cnf
[mysqld]
datadir = /mydata/data
(5).為mysql提供啟動指令碼
[root@node2 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node2 mysql]# chmod +x /etc/init.d/mysqld
(6).初始化mysql資料庫
先安裝libaio庫檔案
[root@node2 mysql]# yum install -y libaio
[root@node2 mysql]# mkdir -pv /mydata/data
mkdir: 已建立目錄 "/mydata"
mkdir: 已建立目錄 "/mydata/data"
[root@node2 mysql]# cd /mydata/data
[root@node2 data]# chown -R mysql.mysql /mydata/data/
[root@node2 data]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
[root@node2 data]# ll
總用量 1084
drwx------ 2 mysql root 4096 8月 24 18:49 mysql
-rw-rw---- 1 mysql mysql 27698 8月 24 18:49 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1061358 8月 24 18:49 mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 8月 24 18:49 mysql-bin.index
drwx------ 2 mysql mysql 4096 8月 24 18:49 performance_schema
drwx------ 2 mysql root 4096 8月 24 18:49 test
(7).啟動並測試
啟動mysql
[root@node2 data]# service mysqld start
Starting MySQL... SUCCESS!
環境變數配置
[root@node2 data]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 data]# source /etc/profile
測試一下
[root@node2 data]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.06 sec)
mysql>
好了，slave的mysql也配置完成了，下面我們來配置主從複製。
7.配置master
(1).修改配置檔案
[root@node1 ~]# vim /etc/my.cnf
#增加下面幾行
[mysqld]
log-bin=master-bin
log-bin-index=master-bin.index
server-id = 1
innodb_file_per_table = 1
(2).授權複製使用者
mysql> grant replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
Query OK, 0 rows affected (0.61 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.41 sec)
(3).重啟一下mysql服務
[root@node1 ~]# service mysqld restart
Shutting down MySQL....... SUCCESS!
Starting MySQL............. SUCCESS!
8.配置slave
(1).修改配置檔案
[root@node2 ~]# vim /etc/my.cnf
#增加下面幾行
[mysqld]
relay-log = relay-log
relay-log-index = relay-log.index
server-id = 10
(2).重啟mysql服務
[root@node2 ~]# service mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL........... SUCCESS!
(3).連線主伺服器並複製
檢視master上二進位制日誌
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 107 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
連線master伺服器
mysql> change master to master_host='192.168.1.201',master_user='repluser',master_password='replpass',master_log_file='master-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.07 sec)
檢視一下slave狀態
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.201
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
啟動複製並檢視狀態
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 404
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
9.主從複製測試
master:
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)
好了，到這裡非同步的主從複製到這裡配置完成。下面我們來說一下什麼是半同步複製（或說是同步也行）。
七、Mysql 主從複製（半同步）
1.半同步複製
在說明半同步複製之前我們先來了解一下，什麼是同步複製？同步複製：同步複製可以定義為資料在同一時刻被提交到一臺或多臺機器，通常這是通過眾所周知的“兩階段提交”做到的。雖然這確實給你在多系統中保持一致性，但也由於增加了額外的訊息交換而造成效能下降。使用MyISAM或者InnoDB儲存引擎的MySQL本身並不支援同步複製，然而有些技術，例如分散式複製塊裝置（簡稱DRBD），可以在下層的檔案系統提供同步複製，允許第二個MySQL伺服器在主伺服器丟失的情況下接管（使用第二伺服器的複本）。瞭解了同步複製我們正下面來說一下，什麼是半同步複製？
MYSQL 5.5開始，支援半自動複製。之前版本的MySQL Replication都是非同步（asynchronous）的，主庫在執行完一些事務後，是不會管備庫的進度的。如果備庫不幸落後，而更不幸的是主庫此時又出現Crash（例如當機），這時備庫中的資料就是不完整的。簡而言之，在主庫發生故障的時候，我們無法使用備庫來繼續提供資料一致的服務了。Semisynchronous Replication(半同步複製)則一定程度上保證提交的事務已經傳給了至少一個備庫。Semi synchronous中，僅僅保證事務的已經傳遞到備庫上，但是並不確保已經在備庫上執行完成了。
此外，還有一種情況會導致主備資料不一致。在某個session中，主庫上提交一個事務後，會等待事務傳遞給至少一個備庫，如果在這個等待過程中主庫Crash，那麼也可能備庫和主庫不一致，這是很致命的。如果主備網路故障或者備庫掛了，主庫在事務提交後等待10秒（rpl_semi_sync_master_timeout的預設值）後，就會繼續。這時，主庫就會變回原來的非同步狀態。
MySQL在載入並開啟Semi-sync外掛後，每一個事務需等待備庫接收日誌後才返回給客戶端。如果做的是小事務，兩臺主機的延遲又較小，則Semi-sync可以實現在效能很小損失的情況下的零資料丟失。
2.非同步與半同步異同
預設情況下MySQL的複製是非同步的，Master上所有的更新操作寫入Binlog之後並不確保所有的更新都被複制到Slave之上。非同步操作雖然效率高，但是在Master/Slave出現問題的時候，存在很高資料不同步的風險，甚至可能丟失資料。
MySQL5.5引入半同步複製功能的目的是為了保證在master出問題的時候，至少有一臺Slave的資料是完整的。在超時的情況下也可以臨時轉入非同步複製，保障業務的正常使用，直到一臺salve追趕上之後，繼續切換到半同步模式。
3.具體配置
注，mysql5.5半同步外掛是由谷歌提供，具體位置/usr/local/mysql/lib/plugin/下，一個是master用的semisync_master.so，一個是slave用的semisync_slave.so，下面我們就來具體配置一下。
master:
(1).安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.39 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
(2).修改配置檔案
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1 #啟用半同步
rpl_semi_sync_master_timeout=1000 #超時時間為1s
(3).重新啟動服務
[root@node1 ~]# service mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL.. SUCCESS!
slave:
(1).安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.38 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
(2).修改配置檔案
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled=1 #啟用半同步複製
(3).重新啟動服務
[root@node2 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
4.檢視一下狀態
master:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
slave:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
5.測試一下
master:
mysql> create table user (id int(10));
Query OK, 0 rows affected (0.42 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
mysql> insert user value (1);
Query OK, 1 row affected (0.34 sec)
注，大家可以看到建立一個表的插入一個資料的時間都很長，說明半同步配置完成。
6.模擬一下故障
slave:
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
master:
mysql> create table user1 (id int(10));
Query OK, 0 rows affected (1.03 sec)
注，大家可以看到主伺服器會卡1s，我們超時時間設定的為1s。
7.檢視一下狀態
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1560 |
| Rpl_semi_sync_master_net_wait_time | 10920 |
| Rpl_semi_sync_master_net_waits | 7 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 985 |
| Rpl_semi_sync_master_tx_wait_time | 985 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 6 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
好了，到這裡我們就配置完成了半同步複製。希望大家有所收穫。下面我們來簡單說一下mysql複製的工具。
八、Mysql 複製工具
1.percona-toolkit簡介
percona-toolkit是一組高階命令列工具的集合，用來執行各種通過手工執行非常複雜和麻煩的mysql和系統任務，這些任務包括：
檢查master和slave資料的一致性
有效地對記錄進行歸檔
查詢重複的索引
對伺服器資訊進行彙總
分析來自日誌和tcpdump的查詢
當系統出問題的時候收集重要的系統資訊
percona-toolkit源自Maatkit 和Aspersa工具，這兩個工具是管理mysql的最有名的工具，現在Maatkit工具已經不維護了，請大家還是使用percona-toolkit吧！這些工具主要包括開發、效能、配置、監控、複製、系統、實用六大類，作為一個優秀的DBA，裡面有的工具非常有用，如果能掌握並加以靈活應用，將能極大的提高工作效率。
2.安裝percona-toolkit
[root@node1 ~]# wget http://www.percona.com/downloads ... -2.2.4-1.noarch.rpm
[root@node1 ~]# yum install -y percona-toolkit-2.2.4-1.noarch.rpm
[root@node1 ~]# pt #以pt開頭命令
pt-agent pt-fingerprint pt-pmp pt-table-checksum
pt-align pt-fk-error-logger pt-query-digest pt-table-sync
pt-archiver pt-heartbeat pt-show-grants pt-table-usage
pt-config-diff pt-index-usage pt-sift pt-upgrade
pt-deadlock-logger pt-ioprofile pt-slave-delay pt-variable-advisor
pt-diskstats pt-kill pt-slave-find pt-visual-explain
pt-duplicate-key-checker pt-mext pt-slave-restart ptx
pt-fifo-split pt-mysql-summary pt-stalk
pt-find pt-online-schema-change pt-summary
3.簡單使用
常用工具：
(1).伺服器摘要
[root@node1 ~]# pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2013-08-24 15:15:14 UTC (local TZ: CST +0800)
Hostname | node1.test.com
Uptime | 10:37, 3 users, load average: 0.00, 0.08, 0.07
Platform | Linux
Release | CentOS release 6.4 (Final)
Kernel | 2.6.32-358.el6.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.12
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 2, cores = 0, virtual = 2, hyperthreading = no
Speeds | 2x2261.309
Models | 2xIntel(R) Core(TM) i5 CPU M 430 @ 2.27GHz
Caches | 2x3072 KB
# Memory #####################################################
Total | 230.8M
Free | 45.5M
Used | physical = 185.3M, swap allocated = 1000.0M, swap used = 40.5M, virtual = 225.9M
Buffers | 4.3M
Caches | 75.0M
Dirty | 104 kB
UsedRSS | 69.0M
Swappiness | 60
DirtyPolicy | 20, 10
DirtyStatus | 0, 0
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/sda1 194M 14% ext4 rw /boot
/dev/sda2 9.7G 27% ext4 rw /
/dev/sda3 4.9G 3% ext4 rw /data
tmpfs 116M 0% tmpfs rw /dev/shm
# Disk Schedulers And Queue Size #############################
sda | [cfq] 128
sdb | [cfq] 128
sr0 | [cfq] 128
# Disk Partioning ############################################
Device Type Start End Size
============ ==== ========== ========== ==================
/dev/sda Disk 21474836480
/dev/sda1 Part 1 26 205632000
/dev/sda2 Part 26 1301 10487232000
/dev/sda3 Part 1301 1938 5239503360
/dev/sda4 Part 1938 2611 5535613440
/dev/sda5 Part 1939 2066 1044610560
/dev/sdb Disk 21474836480
/dev/sr0 Disk 4353378304
# Kernel Inode State #########################################
dentry-state | 8709 3069 45 0 0 0
file-nr | 736 0 21159
inode-nr | 9018 568
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
Controller | No RAID controller detected
# Network Config #############################################
FIN Timeout | 60
Port Range | 61000
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 0 0 0 0 0 0
eth0 225000000 225000 0 200000000 225000 0
# Network Devices ############################################
Device Speed Duplex
========= ========= =========
eth0 1000Mb/s Full
# Network Connections ########################################
Connections from remote IP addresses
192.168.1.102 2
Connections to local IP addresses
192.168.1.201 2
Connections to top 10 local ports
22 2
States of connections
ESTABLISHED 2
LISTEN 10
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 20 0 19228 648 444 S 0.0 0.3 0:01.71 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.51 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.14 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.13 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.17 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:00.09 ksoftirqd/1
# Notable Processes ##########################################
PID OOM COMMAND
1037 -17 sshd
# Simplified and fuzzy rounded vmstat (wait please) ##########
procs ---swap-- -----io---- ---system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
1 0 0 1 15 15 9 15 0 0 100 0 0
0 0 0 0 0 0 1000 1000 5 18 77 0 0
0 0 0 0 0 0 10 40 0 0 100 0 0
0 0 0 0 0 0 15 40 0 0 100 0 0
0 0 0 0 0 0 15 40 0 0 100 0 0
# The End ####################################################
(2).伺服器磁碟監測
[root@node1 ~]# pt-diskstats
#ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.0 0.3 0% 0 2.0 0.0 0.3
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.0 0.3 0% 0 2.0 0.0 0.3
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
(3).mysql服務狀態摘要
[root@node1 ~]# pt-mysql-summary -- --user=root
# Percona Toolkit MySQL Summary Report #######################
System time | 2013-08-24 15:16:55 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /mydata/data 0 0 /tmp/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | root@localhost
Time | 2013-08-24 23:16:55 (CST)
Hostname | node1.test.com
Version | 5.5.33-log MySQL Community Server (GPL)
Built On | linux2.6 x86_64
Started | 2013-08-24 22:37 (up 0+00:39:17)
Databases | 5
Datadir | /mydata/data/
Processes | 2 connected, 2 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /mydata/data/node1.test.com.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump 1 1 2250 2250
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
repluser 1 1 2250 2250
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
192.168.1.202 1 1 2250 2250
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 2 2 2250 2250
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Master has sent all binlog to 1 1 2250 2250
NULL 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 70
Binlog_cache_use 175
Bytes_received 90000 1 200
Bytes_sent 700000 7 2000
Com_admin_commands 100
Com_change_db 70
Com_create_table 100
Com_insert 175
Com_select 400 1
Com_set_option 250
Com_show_databases 70
Com_show_status 100
Com_show_tables 70
Com_show_variables 250
Connections 350 1
Created_tmp_files 225
Created_tmp_tables 500 4
Flush_commands 35
Handler_commit 350
Handler_prepare 350
Handler_read_first 100
Handler_read_rnd_next 15000 35
Handler_write 12500 35
Innodb_buffer_pool_bytes_data 90000000 1000
Innodb_buffer_pool_pages_flushed 1500
Innodb_buffer_pool_read_requests 22500
Innodb_buffer_pool_reads 5000
Innodb_buffer_pool_write_requests 5000
Innodb_data_fsyncs 1250
Innodb_data_read 175000000 2000
Innodb_data_reads 6000
Innodb_data_writes 2500
Innodb_data_written 50000000 600
Innodb_dblwr_pages_written 1500
Innodb_dblwr_writes 150
Innodb_log_write_requests 1250
Innodb_log_writes 500
Innodb_os_log_fsyncs 700
Innodb_os_log_written 700000 7
Innodb_pages_created 500
Innodb_pages_read 5000
Innodb_pages_written 1500
Innodb_rows_inserted 175
Open_table_definitions 1250
Opened_files 3500 4
Opened_table_definitions 1250
Opened_tables 1250
Qcache_not_cached 400 1
Queries 2250 4
Questions 2250 4
Rpl_semi_sync_master_clients 35
Rpl_semi_sync_master_net_avg_wait_time 60000
Rpl_semi_sync_master_net_wait_time 400000 4
Rpl_semi_sync_master_net_waits 250
Rpl_semi_sync_master_no_times 35
Rpl_semi_sync_master_no_tx 35
Rpl_semi_sync_master_tx_avg_wait_time 35000
Rpl_semi_sync_master_tx_wait_time 35000
Rpl_semi_sync_master_tx_waits 35
Rpl_semi_sync_master_yes_tx 225
Select_scan 500
Table_locks_immediate 1500
Threads_created 70
Uptime 90000 1 1
# Table cache ################################################
Size | 256
Usage | 10%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Not Supported
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | ON
Size | 16.0M
Usage | 0%
HitToInsertRatio | 0%
# Semisynchronous Replication ################################
master semisync status |
master trace level | 32, net wait (more information about network waits)
master timeout in milliseconds | 1000
master waits for slaves | ON
master clients |
master net_avg_wait_time |
master net_wait_time |
master net_waits |
master no_times |
master no_tx |
master timefunc_failures |
master tx_avg_wait_time |
master tx_wait_time |
master tx_waits |
master wait_pos_backtraverse |
master wait_sessions |
master yes_tx |
Slave | Disabled
# Schema #####################################################
(4).慢查詢日誌分析統計
1
[root@node1 ~]# pt-query-digest /mydata/data/mysql-slow.log
(5).主從狀態監測，提供給它一臺mysql伺服器的IP使用者名稱密碼，就可以分析出整個主從架構中每臺伺服器的資訊，包括但不限於mysql版本，IP地址，server ID，mysql服務的啟動時間，角色（主/從），Slave Status(落後於主伺服器多少秒,有沒有錯誤，slave有沒有在執行)。
[root@node1 ~]# pt-slave-find --host=localhost --user=root
Cannot connect to h=192.168.1.202,u=root
localhost
Version 5.5.33-log
Server ID 1
Uptime 42:09 (started 2013-08-24T22:37:38)
Replication Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging MIXED
Slave status
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.5.33
(6).mysql死鎖監測
1
[root@node1 ~]# pt-duplicate-key-checker --database=world h='127.0.0.1' --user=root --password=123456
(7).監測從庫的複製延遲
1
[root@node1 ~]# pt-slave-delay --host 192.168.1.202 --user=root --password=123456
注，簡單說明就到這裡，想學習更詳細的內容，命令的使用可以通過--help獲知
九、Mysql 複製注意事項
注，在主-從架構上建議使用的配置
master:
sync_binlog=1 # 立刻同步binlog
innodb_flush_logs_at_trx_commit=1 #立刻重新整理innodb日誌
slave:
skip_slave_start=1 #設定開機不同步
read_only=1 #設定為只讀
十、Mysql 複製過濾
master:
binlog-do-db=mydb
binlog-ignore-db=mysql
slave:
replicate_do_db
rpplicate_ignore_db
replicate_do_table
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table
測試一下：
在從伺服器上只複製testdb一個資料庫
slave:
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=testdb
replicate_do_db=mysql
[root@node2 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
master:
mysql> create database mydb1;
Query OK, 1 row affected (0.34 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
注，大家可以看到沒有同步mydb1，再測試一下。
master:
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
一、前言
二、Mysql 基礎知識
三、Mysql 複製(Replication)
四、Mysql 複製(Replication)型別
五、Mysql 主從複製基本步驟
六、Mysql 主從複製（非同步）
七、Mysql 主從複製（半同步）
八、Mysql 複製工具
九、Mysql 複製注意事項
十、Mysql 複製過濾
一、前言
從這一篇部落格開始我們就來學習mysql的高階課程，在前面的幾篇部落格我們講解了mysql基礎知識、mysql日誌型別、mysql配置檔案、mysql備份策略，這一篇部落格中我們來講解mysql的複製。
二、mysql基礎知識
1.mysql日誌型別
二進位制日誌
事務日誌
錯誤日誌
一般查詢日誌
中繼日誌
慢查詢日誌
注，有博友對mysql日誌不怎麼了解，可以參考這篇部落格http://freeloda.blog.51cto.com/2033581/1253991
2.mysql二進位制日誌詳解
說明：預設開啟，精確的記錄了使用者對資料庫中的資料進行操作的命令和操作的資料物件。
二進位制日誌檔案的作用：
提供了增量備份的功能
提供了資料基於時間點的恢復，這個恢復的時間點可以由使用者控制
為mysql的複製架構提供基礎，將這主伺服器的二進位制日誌複製到從伺服器上並執行同樣的操作，就可將資料進行同步
實現資料的高可用
分擔負載
二進位制日誌預設存放位置：
資料目錄下，以mysql-bin.XXXXXX命名的日誌
二進位制日誌格式：
基於語句 statement
基於行 row
混合方式 mixed
二進位制日誌滾動方式：
重啟服務
日誌達到最大上限
執行flush logs
二進位制日誌事件：
position 基於位置，也就是offset（偏移量）
datetime 基於時間
timestamp
二進位制日誌的檢視與刪除方式：
mysql>show master status; 檢視當前正在使用的二進位制日誌
mysql>show binlog events in'mysql-bin.000001'; 檢視二進位制日誌記錄的事件[from position]
mysql>flush logs; 二進位制日誌滾動
mysql>show binary logs; 檢視所有二進位制日誌
mysql>purge binary logs to 'mysql-bin.000003'; 刪除二進位制日誌
檔案系統中檢視二進位制日誌的命令：
mysqlbinlog
相關選項，
--start-position #開始位置
--stop-position #結束位置
--start-datetime 'yyyy-mm-dd hh:mm:ss'; #開始時間
--stop-datetime ''; #結束時間
配置mysql的主配置檔案：
sql_log_bin = {ON|OFF} #用於控制二進位制日誌資訊是否記錄進日誌檔案。預設為ON，表示啟用記錄功能。使用者可以在會話級別修改此變數的值，但其必須具有SUPER許可權
binlog_cache_size = 32768 #預設值32768 Binlog Cache 用於在開啟了二進位制日誌（binlog）記錄功能的環境，是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時快取binlog資料的記憶體區域。一般來說，如果我們的資料庫中沒有什麼大事務，寫入也不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果我們的資料庫大事務較多，寫入量比較大，可與適當調高binlog_cache_size。同時，我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案（binlog_cache_disk_use）來快取了
binlog_stmt_cache_size = 32768 #當非事務語句使用二進位制日誌快取，但是超出binlog_stmt_cache_size時，使用一個臨時檔案來存放這些語句
log_bin = mysql-bin #指定binlog的位置，預設在資料目錄下
binlog-format= {ROW|STATEMENT|MIXED} #指定二進位制日誌的型別，預設為MIXED。如果設定了二進位制日誌的格式，卻沒有啟用二進位制日誌，則MySQL啟動時會產生警告日誌資訊並記錄於錯誤日誌中。
sync_binlog = 10 #設定多久同步一次二進位制日誌至磁碟檔案中，0表示不同步，任何正數值都表示對二進位制每多少次寫操作之後同步一次。當autocommit的值為1時，每條語句的執行都會引起二進位制日誌同步，否則，每個事務的提交會引起二進位制日誌同步
max_binlog_cache_size = {4096 .. 18446744073709547520} #二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取，其上限由max_binlog_stmt_cache_size決定。
max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二進定日誌快取空間大小，5.5.9及以後的版本僅應用於事務快取
expire_log_days = {0..99} #設定二進位制日誌的過期天數，超出此天數的二進位制日誌檔案將被自動刪除。預設為0，表示不啟用過期自動刪除功能。如果啟用此功能，自動刪除工作通常發生在MySQL啟動時或FLUSH日誌時
注：一般建議將binlog日誌與資料檔案分開存放，不但可以提高mysql效能，還可以增加安全性！
三、Mysql 複製(Replication)
1.Mysql 複製作用
負載平衡(load balancing)
備份
高可用性(high availability)和容錯
2.Mysql 複製如何工作
主要有三步（如下圖）：
master將改變記錄到二進位制日誌(binary log)中（這些記錄叫做二進位制日誌事件，binary log events）；
slave將master的binary log events拷貝到它的中繼日誌(relay log)；
slave重做中繼日誌中的事件，將改變反映它自己的資料。
mysql03-1
具體說明：
該過程的第一部分就是master記錄二進位制日誌。在每個事務更新資料完成之前，master在二日誌記錄這些改變。MySQL將事務序列的寫入二進位制日誌，即使事務中的語句都是交叉執行的。在事件寫入二進位制日誌完成後，master通知儲存引擎提交事務。
下一步就是slave將master的binary log拷貝到它自己的中繼日誌。首先，slave開始一個工作執行緒——I/O執行緒。I/O執行緒在master上開啟一個普通的連線，然後開始binlog dump process。Binlog dump process從master的二進位制日誌中讀取事件，如果已經跟上master，它會睡眠並等待master產生新的事件。I/O執行緒將這些事件寫入中繼日誌。
SQL slave thread處理該過程的最後一步。SQL執行緒從中繼日誌讀取事件，更新slave的資料，使其與master中的資料一致。只要該執行緒與I/O執行緒保持一致，中繼日誌通常會位於OS的快取中，所以中繼日誌的開銷很小。
此外，在master中也有一個工作執行緒：和其它MySQL的連線一樣，slave在master中開啟一個連線也會使得master開始一個執行緒。複製過程有一個很重要的限制——複製在slave上是序列化的，也就是說master上的並行更新操作不能在slave上並行操作。所以slave上資料一般要慢於master上資料。即master與slave之間的資料在一定時間內會不同步。
四、Mysql 複製(Replication)型別
1.mysql複製型別有以下一些基本原則
每個slave只能有一個master；
每個slave只能有一個唯一的伺服器ID；
每個master可以有很多slave；
如果你設定log_slave_updates，slave可以是其它slave的master，從而擴散master的更新。
注，MySQL不支援多主伺服器複製(Multimaster Replication)——即一個slave可以有多個master。但是，通過一些簡單的組合，我們卻可以建立靈活而強大的複製體系結構。
2.mysql複製型別
(1).一主多從模式
注，由一個master和一個slave組成複製系統是最簡單的情況。Slave之間並不相互通訊，只能與master進行通訊。
具體說明：
如果寫操作較少，而讀操作很時，可以採取這種結構。你可以將讀操作分佈到其它的slave，從而減小master的壓力。但是，當slave增加到一定數量時，slave對master的負載以及網路頻寬都會成為一個嚴重的問題。這種結構雖然簡單，但是，它卻非常靈活，足夠滿足大多數應用需求。一些建議：
不同的slave扮演不同的作用(例如使用不同的索引，或者不同的儲存引擎)
用一個slave作為備用master，只進行復制
用一個遠端的slave，用於災難恢復
傳送複製事件到其它slave，當設定log_slave_updates時，你可以讓slave扮演其它slave的master。此時，slave把SQL執行緒執行的事件寫進行自己的二進位制日誌(binary log)，然後，它的slave可以獲取這些事件並執行它。
(2).雙主模式
注，Master-Master複製的兩臺伺服器，既是master，又是另一臺伺服器的slave。
具體說明：
主動的Master-Master複製有一些特殊的用處。例如，地理上分佈的兩個部分都需要自己的可寫的資料副本。這種結構最大的問題就是更新衝突。假設一個表只有一行(一列)的資料，其值為1，如果兩個伺服器分別同時執行如下語句：
在第一個伺服器上執行：
mysql> UPDATE tbl SET col=col + 1;
在第二個伺服器上執行：
mysql> UPDATE tbl SET col=col * 2;
那麼結果是多少呢？一臺伺服器是4，另一個伺服器是3，但是，這並不會產生錯誤。
實際上，MySQL並不支援其它一些DBMS支援的多主伺服器複製(Multimaster Replication)，這是MySQL的複製功能很大的一個限制(多主伺服器的難點在於解決更新衝突)，但是，如果你實在有這種需求，你可以採用MySQL Cluster，以及將Cluster和Replication結合起來，可以建立強大的高效能的資料庫平臺。但是，可以通過其它一些方式來模擬這種多主伺服器的複製。
(3).主從模式
注，這是master-master結構變化而來的，它避免了M-M的缺點，實際上，這是一種具有容錯和高可用性的系統。它的不同點在於其中一個服務只能進行只讀操作。
(4).帶從伺服器的Master-Master結構(Master-Master with Slaves)
注，這種結構的優點就是提供了冗餘。在地理上分佈的複製結構，它不存在單一節點故障問題，而且還可以將讀密集型的請求放到slave上。
總結：一般常用的兩種複製型別一種是主從模式，另一種是一主多從模式。在這一篇部落格中我們主要講解主從模式複製。
五、Mysql 主從複製基本步驟
1.master 配置
啟用二進位制日誌
配置一個唯一的server-id
建立具有複製許可權的使用者
2.slave 配置
啟用中繼日誌
配置一個唯一的server-id
連線主伺服器，並開始複製資料
啟動資料複製
注，基本步驟我們就說到這裡，下面我們來具體演示一下主從複製。
六、Mysql 主從複製（非同步）
1.mysql非同步複製
非同步複製：MySQL本身支援單向的、非同步的複製。非同步複製意味著在把資料從一臺機器拷貝到另一臺機器時有一個延時，最重要的是這意味著當應用系統的事務在主伺服器上提交併確認時資料並不能在同一時刻拷貝或應用到從伺服器上。通常這個延時是由網路頻寬、資源可用性和系統負載決定的。然而，使用正確的元件並且調優，複製能做到接近瞬時完成。
當主庫有更新的時候，主庫會把更新操作的SQL寫入二進位制日誌(Bin log)，並維護一個二進位制日誌檔案的索引，以便於日誌檔案輪迴（Rotate）。在從庫啟動非同步複製的時候，從庫會開啟兩個I/O執行緒，其中一個執行緒連線主庫，要求主庫把二進位制日誌的變化部分傳給從庫，並把傳回的日誌寫入本地磁碟。另一個執行緒則負責讀取本地寫入的二進位制日誌，並在本地執行，以反映出這種變化。較老的版本在複製的時候只啟用一個I/O執行緒，實現這兩部分的功能。下面我們來具體演示一下mysql的非同步複製。
2.實驗拓撲
注，Active (master,node1) 192.168.1.201 ， Passive (slave,node2)192.168.1.202
3.環境配置
時間同步
[root@node1 ~]# ntpdate 202.120.2.101
[root@node2 ~]# ntpdate 202.120.2.101
4.作業系統
CentOS 6.4 X86_64
5.軟體版本
Mysql 5.5.33 (注，這裡用的是mysql 5.5.33二進位制通用安裝包，解壓就能用)
6.安裝並配置mysql
master:
(1).解壓並連結
[root@node1 src]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@node1 src]# cd /usr/local/
[root@node1 local]# ln -sv /usr/local/mysql-5.5.33-linux2.6-x86_64 mysql
"mysql" -> "/usr/local/mysql-5.5.33-linux2.6-x86_64"
[root@node1 local]# cd mysql
[root@node1 mysql]# ll
總用量 200
drwxr-xr-x 2 root root 4096 8月 24 17:58 bin
-rw-r--r-- 1 7161 wheel 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root root 4096 8月 24 17:58 data
drwxr-xr-x 2 root root 4096 8月 24 17:58 docs
drwxr-xr-x 3 root root 4096 8月 24 17:58 include
-rw-r--r-- 1 7161 wheel 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root root 4096 8月 24 17:58 lib
drwxr-xr-x 4 root root 4096 8月 24 17:58 man
drwxr-xr-x 10 root root 4096 8月 24 17:58 mysql-test
-rw-r--r-- 1 7161 wheel 2496 7月 15 20:01 README
drwxr-xr-x 2 root root 4096 8月 24 17:58 scripts
drwxr-xr-x 27 root root 4096 8月 24 17:58 share
drwxr-xr-x 4 root root 4096 8月 24 17:58 sql-bench
drwxr-xr-x 3 root root 4096 8月 24 17:58 support-files
(2).新建mysql使用者
[root@node1 mysql]# groupadd -g 3306 mysql
[root@node1 mysql]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql
[root@node1 mysql]# id mysql
uid=3306(mysql) gid=3306(mysql) 組=3306(mysql)
(3).修改mysql安裝目錄所有者與所屬組
[root@node1 mysql]# chown -R root.mysql /usr/local/mysql/*
[root@node1 mysql]# ll
總用量 200
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 bin
-rw-r--r-- 1 root mysql 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 data
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 docs
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 include
-rw-r--r-- 1 root mysql 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 lib
drwxr-xr-x 4 root mysql 4096 8月 24 17:58 man
drwxr-xr-x 10 root mysql 4096 8月 24 17:58 mysql-test
-rw-r--r-- 1 root mysql 2496 7月 15 20:01 README
drwxr-xr-x 2 root mysql 4096 8月 24 17:58 scripts
drwxr-xr-x 27 root mysql 4096 8月 24 17:58 share
drwxr-xr-x 4 root mysql 4096 8月 24 17:58 sql-bench
drwxr-xr-x 3 root mysql 4096 8月 24 17:58 support-files
(4).為mysql提供配置檔案並修改
[root@node1 mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@node1 mysql]# vim /etc/my.cnf
[mysqld]
#增加一行
datadir = /mydata/data
(5).為mysql提供啟動指令碼
[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node1 mysql]# chmod +x /etc/init.d/mysqld
(6).初始化mysql資料庫
[root@node1 ~]# mkdir -pv /mydata/data
mkdir: 已建立目錄 "/mydata"
mkdir: 已建立目錄 "/mydata/data"
[root@node1 ~]# chown -R mysql.mysql /mydata/data/
[root@node1 ~]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
Installing MySQL system tables...
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
Installation of system tables failed! Examine the logs in
/mydata/data/ for more information.
You can try to start the mysqld daemon with:
shell> /usr/local/mysql/bin/mysqld --skip-grant &
and use the command line tool /usr/local/mysql/bin/mysql
to connect to the mysql database and look at the grant tables:
shell> /usr/local/mysql/bin/mysql -u root mysql
mysql> show tables
Try 'mysqld --help' if you have problems with paths. Using --log
gives you a log in /mydata/data/ that may be helpful.
Please consult the MySQL manual section
'Problems running mysql_install_db', and the manual section that
describes problems on your OS. Another information source are the
MySQL email archives available at http://lists.mysql.com/.
Please check all of the above before mailing us! And remember, if
you do mail us, you MUST use the /usr/local/mysql/scripts/mysqlbug script!
[root@node1 ~]# cd /mydata/data/
[root@node1 data]# ll
總用量 8
drwx------ 2 mysql root 4096 8月 24 18:21 mysql
drwx------ 2 mysql root 4096 8月 24 18:21 test
(7).啟動並測試
啟動報錯
[root@node1 data]# service mysqld start
Starting MySQL. ERROR! The server quit without updating PID file (/mydata/data/node1.test.com.pid).
檢視一下錯誤日誌
[root@node1 data]# vim node1.test.com.err
130824 18:21:44 mysqld_safe Starting mysqld daemon with databases from /mydata/data
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
130824 18:21:44 mysqld_safe mysqld from pid file /mydata/data/node1.test.com.pid ended
注，從錯誤日誌中我們看到差一個庫檔案libaio，我們用yum安裝一下即可。
[root@node1 data]# yum install -y libaio
重新初始化mysql
[root@node1 data]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
啟動mysql
[root@node1 data]# service mysqld start
Starting MySQL... SUCCESS!
環境變數配置
[root@node1 data]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 data]# source /etc/profile
測試一下
[root@node1 data]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.03 sec)
mysql>
好了，到這裡master的mysql配置完成，下面我們進行slave的mysql配置。
slave:
(1).解壓並連結
[root@node2 ~]# tar xf mysql-5.5.33-linux2.6-x86_64.tar.gz -C /usr/local/
[root@node2 ~]# cd /usr/local/
[root@node2 local]# ln -sv /usr/local/mysql-5.5.33-linux2.6-x86_64 mysql
"mysql" -> "/usr/local/mysql-5.5.33-linux2.6-x86_64"
[root@node2 local]# cd mysql
[root@node2 mysql]# ll
總用量 200
drwxr-xr-x 2 root root 4096 8月 24 18:41 bin
-rw-r--r-- 1 7161 wheel 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root root 4096 8月 24 18:41 data
drwxr-xr-x 2 root root 4096 8月 24 18:41 docs
drwxr-xr-x 3 root root 4096 8月 24 18:41 include
-rw-r--r-- 1 7161 wheel 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root root 4096 8月 24 18:41 lib
drwxr-xr-x 4 root root 4096 8月 24 18:41 man
drwxr-xr-x 10 root root 4096 8月 24 18:41 mysql-test
-rw-r--r-- 1 7161 wheel 2496 7月 15 20:01 README
drwxr-xr-x 2 root root 4096 8月 24 18:41 scripts
drwxr-xr-x 27 root root 4096 8月 24 18:41 share
drwxr-xr-x 4 root root 4096 8月 24 18:41 sql-bench
drwxr-xr-x 3 root root 4096 8月 24 18:41 support-files
(2).新建mysql使用者
[root@node2 mysql]# groupadd -g 3306 mysql
[root@node2 mysql]# useradd -u 3306 -g mysql -s /sbin/nologin -M mysql
[root@node2 mysql]# id mysql
uid=3306(mysql) gid=3306(mysql) 組=3306(mysql)
(3).修改mysql安裝目錄所有者與所屬組
[root@node2 mysql]# chown -R root.mysql /usr/local/mysql/*
[root@node2 mysql]# ll
總用量 200
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 bin
-rw-r--r-- 1 root mysql 17987 7月 15 20:01 COPYING
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 data
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 docs
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 include
-rw-r--r-- 1 root mysql 134493 7月 15 20:01 INSTALL-BINARY
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 lib
drwxr-xr-x 4 root mysql 4096 8月 24 18:41 man
drwxr-xr-x 10 root mysql 4096 8月 24 18:41 mysql-test
-rw-r--r-- 1 root mysql 2496 7月 15 20:01 README
drwxr-xr-x 2 root mysql 4096 8月 24 18:41 scripts
drwxr-xr-x 27 root mysql 4096 8月 24 18:41 share
drwxr-xr-x 4 root mysql 4096 8月 24 18:41 sql-bench
drwxr-xr-x 3 root mysql 4096 8月 24 18:41 support-files
(4).為mysql提供配置檔案並修改
[root@node2 mysql]# cp support-files/my-large.cnf /etc/my.cnf
[root@node2 mysql]# vim /etc/my.cnf
[mysqld]
datadir = /mydata/data
(5).為mysql提供啟動指令碼
[root@node2 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node2 mysql]# chmod +x /etc/init.d/mysqld
(6).初始化mysql資料庫
先安裝libaio庫檔案
[root@node2 mysql]# yum install -y libaio
[root@node2 mysql]# mkdir -pv /mydata/data
mkdir: 已建立目錄 "/mydata"
mkdir: 已建立目錄 "/mydata/data"
[root@node2 mysql]# cd /mydata/data
[root@node2 data]# chown -R mysql.mysql /mydata/data/
[root@node2 data]# /usr/local/mysql/scripts/mysql_install_db --datadir=/mydata/data/ --basedir=/usr/local/mysql --user=mysql
[root@node2 data]# ll
總用量 1084
drwx------ 2 mysql root 4096 8月 24 18:49 mysql
-rw-rw---- 1 mysql mysql 27698 8月 24 18:49 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1061358 8月 24 18:49 mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 8月 24 18:49 mysql-bin.index
drwx------ 2 mysql mysql 4096 8月 24 18:49 performance_schema
drwx------ 2 mysql root 4096 8月 24 18:49 test
(7).啟動並測試
啟動mysql
[root@node2 data]# service mysqld start
Starting MySQL... SUCCESS!
環境變數配置
[root@node2 data]# vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@node1 data]# source /etc/profile
測試一下
[root@node2 data]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.06 sec)
mysql>
好了，slave的mysql也配置完成了，下面我們來配置主從複製。
7.配置master
(1).修改配置檔案
[root@node1 ~]# vim /etc/my.cnf
#增加下面幾行
[mysqld]
log-bin=master-bin
log-bin-index=master-bin.index
server-id = 1
innodb_file_per_table = 1
(2).授權複製使用者
mysql> grant replication slave on *.* to 'repluser'@'192.168.1.%' identified by 'replpass';
Query OK, 0 rows affected (0.61 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.41 sec)
(3).重啟一下mysql服務
[root@node1 ~]# service mysqld restart
Shutting down MySQL....... SUCCESS!
Starting MySQL............. SUCCESS!
8.配置slave
(1).修改配置檔案
[root@node2 ~]# vim /etc/my.cnf
#增加下面幾行
[mysqld]
relay-log = relay-log
relay-log-index = relay-log.index
server-id = 10
(2).重啟mysql服務
[root@node2 ~]# service mysqld restart
Shutting down MySQL..... SUCCESS!
Starting MySQL........... SUCCESS!
(3).連線主伺服器並複製
檢視master上二進位制日誌
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 107 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
連線master伺服器
mysql> change master to master_host='192.168.1.201',master_user='repluser',master_password='replpass',master_log_file='master-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.07 sec)
檢視一下slave狀態
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.201
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
啟動複製並檢視狀態
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 254
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 404
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
9.主從複製測試
master:
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.03 sec)
好了，到這裡非同步的主從複製到這裡配置完成。下面我們來說一下什麼是半同步複製（或說是同步也行）。
七、Mysql 主從複製（半同步）
1.半同步複製
在說明半同步複製之前我們先來了解一下，什麼是同步複製？同步複製：同步複製可以定義為資料在同一時刻被提交到一臺或多臺機器，通常這是通過眾所周知的“兩階段提交”做到的。雖然這確實給你在多系統中保持一致性，但也由於增加了額外的訊息交換而造成效能下降。使用MyISAM或者InnoDB儲存引擎的MySQL本身並不支援同步複製，然而有些技術，例如分散式複製塊裝置（簡稱DRBD），可以在下層的檔案系統提供同步複製，允許第二個MySQL伺服器在主伺服器丟失的情況下接管（使用第二伺服器的複本）。瞭解了同步複製我們正下面來說一下，什麼是半同步複製？
MYSQL 5.5開始，支援半自動複製。之前版本的MySQL Replication都是非同步（asynchronous）的，主庫在執行完一些事務後，是不會管備庫的進度的。如果備庫不幸落後，而更不幸的是主庫此時又出現Crash（例如當機），這時備庫中的資料就是不完整的。簡而言之，在主庫發生故障的時候，我們無法使用備庫來繼續提供資料一致的服務了。Semisynchronous Replication(半同步複製)則一定程度上保證提交的事務已經傳給了至少一個備庫。Semi synchronous中，僅僅保證事務的已經傳遞到備庫上，但是並不確保已經在備庫上執行完成了。
此外，還有一種情況會導致主備資料不一致。在某個session中，主庫上提交一個事務後，會等待事務傳遞給至少一個備庫，如果在這個等待過程中主庫Crash，那麼也可能備庫和主庫不一致，這是很致命的。如果主備網路故障或者備庫掛了，主庫在事務提交後等待10秒（rpl_semi_sync_master_timeout的預設值）後，就會繼續。這時，主庫就會變回原來的非同步狀態。
MySQL在載入並開啟Semi-sync外掛後，每一個事務需等待備庫接收日誌後才返回給客戶端。如果做的是小事務，兩臺主機的延遲又較小，則Semi-sync可以實現在效能很小損失的情況下的零資料丟失。
2.非同步與半同步異同
預設情況下MySQL的複製是非同步的，Master上所有的更新操作寫入Binlog之後並不確保所有的更新都被複制到Slave之上。非同步操作雖然效率高，但是在Master/Slave出現問題的時候，存在很高資料不同步的風險，甚至可能丟失資料。
MySQL5.5引入半同步複製功能的目的是為了保證在master出問題的時候，至少有一臺Slave的資料是完整的。在超時的情況下也可以臨時轉入非同步複製，保障業務的正常使用，直到一臺salve追趕上之後，繼續切換到半同步模式。
3.具體配置
注，mysql5.5半同步外掛是由谷歌提供，具體位置/usr/local/mysql/lib/plugin/下，一個是master用的semisync_master.so，一個是slave用的semisync_slave.so，下面我們就來具體配置一下。
master:
(1).安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.39 sec)
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
(2).修改配置檔案
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_master_enabled=1 #啟用半同步
rpl_semi_sync_master_timeout=1000 #超時時間為1s
(3).重新啟動服務
[root@node1 ~]# service mysqld restart
Shutting down MySQL... SUCCESS!
Starting MySQL.. SUCCESS!
slave:
(1).安裝外掛
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.38 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
(2).修改配置檔案
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enabled=1 #啟用半同步複製
(3).重新啟動服務
[root@node2 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
4.檢視一下狀態
master:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
slave:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
5.測試一下
master:
mysql> create table user (id int(10));
Query OK, 0 rows affected (0.42 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
mysql> insert user value (1);
Query OK, 1 row affected (0.34 sec)
注，大家可以看到建立一個表的插入一個資料的時間都很長，說明半同步配置完成。
6.模擬一下故障
slave:
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
master:
mysql> create table user1 (id int(10));
Query OK, 0 rows affected (1.03 sec)
注，大家可以看到主伺服器會卡1s，我們超時時間設定的為1s。
7.檢視一下狀態
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1560 |
| Rpl_semi_sync_master_net_wait_time | 10920 |
| Rpl_semi_sync_master_net_waits | 7 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 985 |
| Rpl_semi_sync_master_tx_wait_time | 985 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 6 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
好了，到這裡我們就配置完成了半同步複製。希望大家有所收穫。下面我們來簡單說一下mysql複製的工具。
八、Mysql 複製工具
1.percona-toolkit簡介
percona-toolkit是一組高階命令列工具的集合，用來執行各種通過手工執行非常複雜和麻煩的mysql和系統任務，這些任務包括：
檢查master和slave資料的一致性
有效地對記錄進行歸檔
查詢重複的索引
對伺服器資訊進行彙總
分析來自日誌和tcpdump的查詢
當系統出問題的時候收集重要的系統資訊
percona-toolkit源自Maatkit 和Aspersa工具，這兩個工具是管理mysql的最有名的工具，現在Maatkit工具已經不維護了，請大家還是使用percona-toolkit吧！這些工具主要包括開發、效能、配置、監控、複製、系統、實用六大類，作為一個優秀的DBA，裡面有的工具非常有用，如果能掌握並加以靈活應用，將能極大的提高工作效率。
2.安裝percona-toolkit
[root@node1 ~]# wget http://www.percona.com/downloads ... -2.2.4-1.noarch.rpm
[root@node1 ~]# yum install -y percona-toolkit-2.2.4-1.noarch.rpm
[root@node1 ~]# pt #以pt開頭命令
pt-agent pt-fingerprint pt-pmp pt-table-checksum
pt-align pt-fk-error-logger pt-query-digest pt-table-sync
pt-archiver pt-heartbeat pt-show-grants pt-table-usage
pt-config-diff pt-index-usage pt-sift pt-upgrade
pt-deadlock-logger pt-ioprofile pt-slave-delay pt-variable-advisor
pt-diskstats pt-kill pt-slave-find pt-visual-explain
pt-duplicate-key-checker pt-mext pt-slave-restart ptx
pt-fifo-split pt-mysql-summary pt-stalk
pt-find pt-online-schema-change pt-summary
3.簡單使用
常用工具：
(1).伺服器摘要
[root@node1 ~]# pt-summary
# Percona Toolkit System Summary Report ######################
Date | 2013-08-24 15:15:14 UTC (local TZ: CST +0800)
Hostname | node1.test.com
Uptime | 10:37, 3 users, load average: 0.00, 0.08, 0.07
Platform | Linux
Release | CentOS release 6.4 (Final)
Kernel | 2.6.32-358.el6.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.12
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 2, cores = 0, virtual = 2, hyperthreading = no
Speeds | 2x2261.309
Models | 2xIntel(R) Core(TM) i5 CPU M 430 @ 2.27GHz
Caches | 2x3072 KB
# Memory #####################################################
Total | 230.8M
Free | 45.5M
Used | physical = 185.3M, swap allocated = 1000.0M, swap used = 40.5M, virtual = 225.9M
Buffers | 4.3M
Caches | 75.0M
Dirty | 104 kB
UsedRSS | 69.0M
Swappiness | 60
DirtyPolicy | 20, 10
DirtyStatus | 0, 0
# Mounted Filesystems ########################################
Filesystem Size Used Type Opts Mountpoint
/dev/sda1 194M 14% ext4 rw /boot
/dev/sda2 9.7G 27% ext4 rw /
/dev/sda3 4.9G 3% ext4 rw /data
tmpfs 116M 0% tmpfs rw /dev/shm
# Disk Schedulers And Queue Size #############################
sda | [cfq] 128
sdb | [cfq] 128
sr0 | [cfq] 128
# Disk Partioning ############################################
Device Type Start End Size
============ ==== ========== ========== ==================
/dev/sda Disk 21474836480
/dev/sda1 Part 1 26 205632000
/dev/sda2 Part 26 1301 10487232000
/dev/sda3 Part 1301 1938 5239503360
/dev/sda4 Part 1938 2611 5535613440
/dev/sda5 Part 1939 2066 1044610560
/dev/sdb Disk 21474836480
/dev/sr0 Disk 4353378304
# Kernel Inode State #########################################
dentry-state | 8709 3069 45 0 0 0
file-nr | 736 0 21159
inode-nr | 9018 568
# LVM Volumes ################################################
Unable to collect information
# LVM Volume Groups ##########################################
Unable to collect information
# RAID Controller ############################################
Controller | No RAID controller detected
# Network Config #############################################
FIN Timeout | 60
Port Range | 61000
# Interface Statistics #######################################
interface rx_bytes rx_packets rx_errors tx_bytes tx_packets tx_errors
========= ========= ========== ========== ========== ========== ==========
lo 0 0 0 0 0 0
eth0 225000000 225000 0 200000000 225000 0
# Network Devices ############################################
Device Speed Duplex
========= ========= =========
eth0 1000Mb/s Full
# Network Connections ########################################
Connections from remote IP addresses
192.168.1.102 2
Connections to local IP addresses
192.168.1.201 2
Connections to top 10 local ports
22 2
States of connections
ESTABLISHED 2
LISTEN 10
# Top Processes ##############################################
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 root 20 0 19228 648 444 S 0.0 0.3 0:01.71 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.04 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:00.51 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:00.14 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.13 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:00.17 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:00.09 ksoftirqd/1
# Notable Processes ##########################################
PID OOM COMMAND
1037 -17 sshd
# Simplified and fuzzy rounded vmstat (wait please) ##########
procs ---swap-- -----io---- ---system---- --------cpu--------
r b si so bi bo ir cs us sy il wa st
1 0 0 1 15 15 9 15 0 0 100 0 0
0 0 0 0 0 0 1000 1000 5 18 77 0 0
0 0 0 0 0 0 10 40 0 0 100 0 0
0 0 0 0 0 0 15 40 0 0 100 0 0
0 0 0 0 0 0 15 40 0 0 100 0 0
# The End ####################################################
(2).伺服器磁碟監測
[root@node1 ~]# pt-diskstats
#ts device rd_s rd_avkb rd_mb_s rd_mrg rd_cnc rd_rt wr_s wr_avkb wr_mb_s wr_mrg wr_cnc wr_rt busy in_prg io_s qtime stime
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.0 0.3 0% 0 2.0 0.0 0.3
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 2.0 6.0 0.0 33% 0.0 0.3 0% 0 2.0 0.0 0.3
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda2 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
1.0 sda 0.0 0.0 0.0 0% 0.0 0.0 0.0 0.0 0.0 0% 0.0 0.0 0% 0 0.0 0.0 0.0
(3).mysql服務狀態摘要
[root@node1 ~]# pt-mysql-summary -- --user=root
# Percona Toolkit MySQL Summary Report #######################
System time | 2013-08-24 15:16:55 UTC (local TZ: CST +0800)
# Instances ##################################################
Port Data Directory Nice OOM Socket
===== ========================== ==== === ======
3306 /mydata/data 0 0 /tmp/mysql.sock
# MySQL Executable ###########################################
Path to executable | /usr/local/mysql/bin/mysqld
Has symbols | Yes
# Report On Port 3306 ########################################
User | root@localhost
Time | 2013-08-24 23:16:55 (CST)
Hostname | node1.test.com
Version | 5.5.33-log MySQL Community Server (GPL)
Built On | linux2.6 x86_64
Started | 2013-08-24 22:37 (up 0+00:39:17)
Databases | 5
Datadir | /mydata/data/
Processes | 2 connected, 2 running
Replication | Is not a slave, has 1 slaves connected
Pidfile | /mydata/data/node1.test.com.pid (exists)
# Processlist ################################################
Command COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Binlog Dump 1 1 2250 2250
Query 1 1 0 0
User COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
repluser 1 1 2250 2250
root 1 1 0 0
Host COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
192.168.1.202 1 1 2250 2250
localhost 1 1 0 0
db COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
NULL 2 2 2250 2250
State COUNT(*) Working SUM(Time) MAX(Time)
------------------------------ -------- ------- --------- ---------
Master has sent all binlog to 1 1 2250 2250
NULL 1 1 0 0
# Status Counters (Wait 10 Seconds) ##########################
Variable Per day Per second 10 secs
Aborted_clients 70
Binlog_cache_use 175
Bytes_received 90000 1 200
Bytes_sent 700000 7 2000
Com_admin_commands 100
Com_change_db 70
Com_create_table 100
Com_insert 175
Com_select 400 1
Com_set_option 250
Com_show_databases 70
Com_show_status 100
Com_show_tables 70
Com_show_variables 250
Connections 350 1
Created_tmp_files 225
Created_tmp_tables 500 4
Flush_commands 35
Handler_commit 350
Handler_prepare 350
Handler_read_first 100
Handler_read_rnd_next 15000 35
Handler_write 12500 35
Innodb_buffer_pool_bytes_data 90000000 1000
Innodb_buffer_pool_pages_flushed 1500
Innodb_buffer_pool_read_requests 22500
Innodb_buffer_pool_reads 5000
Innodb_buffer_pool_write_requests 5000
Innodb_data_fsyncs 1250
Innodb_data_read 175000000 2000
Innodb_data_reads 6000
Innodb_data_writes 2500
Innodb_data_written 50000000 600
Innodb_dblwr_pages_written 1500
Innodb_dblwr_writes 150
Innodb_log_write_requests 1250
Innodb_log_writes 500
Innodb_os_log_fsyncs 700
Innodb_os_log_written 700000 7
Innodb_pages_created 500
Innodb_pages_read 5000
Innodb_pages_written 1500
Innodb_rows_inserted 175
Open_table_definitions 1250
Opened_files 3500 4
Opened_table_definitions 1250
Opened_tables 1250
Qcache_not_cached 400 1
Queries 2250 4
Questions 2250 4
Rpl_semi_sync_master_clients 35
Rpl_semi_sync_master_net_avg_wait_time 60000
Rpl_semi_sync_master_net_wait_time 400000 4
Rpl_semi_sync_master_net_waits 250
Rpl_semi_sync_master_no_times 35
Rpl_semi_sync_master_no_tx 35
Rpl_semi_sync_master_tx_avg_wait_time 35000
Rpl_semi_sync_master_tx_wait_time 35000
Rpl_semi_sync_master_tx_waits 35
Rpl_semi_sync_master_yes_tx 225
Select_scan 500
Table_locks_immediate 1500
Threads_created 70
Uptime 90000 1 1
# Table cache ################################################
Size | 256
Usage | 10%
# Key Percona Server features ################################
Table & Index Stats | Not Supported
Multiple I/O Threads | Enabled
Corruption Resilient | Not Supported
Durable Replication | Not Supported
Import InnoDB Tables | Not Supported
Fast Server Restarts | Not Supported
Enhanced Logging | Not Supported
Replica Perf Logging | Not Supported
Response Time Hist. | Not Supported
Smooth Flushing | Not Supported
HandlerSocket NoSQL | Not Supported
Fast Hash UDFs | Unknown
# Percona XtraDB Cluster #####################################
# Plugins ####################################################
InnoDB compression | ACTIVE
# Query cache ################################################
query_cache_type | ON
Size | 16.0M
Usage | 0%
HitToInsertRatio | 0%
# Semisynchronous Replication ################################
master semisync status |
master trace level | 32, net wait (more information about network waits)
master timeout in milliseconds | 1000
master waits for slaves | ON
master clients |
master net_avg_wait_time |
master net_wait_time |
master net_waits |
master no_times |
master no_tx |
master timefunc_failures |
master tx_avg_wait_time |
master tx_wait_time |
master tx_waits |
master wait_pos_backtraverse |
master wait_sessions |
master yes_tx |
Slave | Disabled
# Schema #####################################################
(4).慢查詢日誌分析統計
1
[root@node1 ~]# pt-query-digest /mydata/data/mysql-slow.log
(5).主從狀態監測，提供給它一臺mysql伺服器的IP使用者名稱密碼，就可以分析出整個主從架構中每臺伺服器的資訊，包括但不限於mysql版本，IP地址，server ID，mysql服務的啟動時間，角色（主/從），Slave Status(落後於主伺服器多少秒,有沒有錯誤，slave有沒有在執行)。
[root@node1 ~]# pt-slave-find --host=localhost --user=root
Cannot connect to h=192.168.1.202,u=root
localhost
Version 5.5.33-log
Server ID 1
Uptime 42:09 (started 2013-08-24T22:37:38)
Replication Is not a slave, has 1 slaves connected, is not read_only
Filters
Binary logging MIXED
Slave status
Slave mode STRICT
Auto-increment increment 1, offset 1
InnoDB version 5.5.33
(6).mysql死鎖監測
1
[root@node1 ~]# pt-duplicate-key-checker --database=world h='127.0.0.1' --user=root --password=123456
(7).監測從庫的複製延遲
1
[root@node1 ~]# pt-slave-delay --host 192.168.1.202 --user=root --password=123456
注，簡單說明就到這裡，想學習更詳細的內容，命令的使用可以通過--help獲知
九、Mysql 複製注意事項
注，在主-從架構上建議使用的配置
master:
sync_binlog=1 # 立刻同步binlog
innodb_flush_logs_at_trx_commit=1 #立刻重新整理innodb日誌
slave:
skip_slave_start=1 #設定開機不同步
read_only=1 #設定為只讀
十、Mysql 複製過濾
master:
binlog-do-db=mydb
binlog-ignore-db=mysql
slave:
replicate_do_db
rpplicate_ignore_db
replicate_do_table
replicate_ignore_table
replicate_wild_do_table
replicate_wild_ignore_table
測試一下：
在從伺服器上只複製testdb一個資料庫
slave:
[root@node2 ~]# vim /etc/my.cnf
[mysqld]
replicate_do_db=testdb
replicate_do_db=mysql
[root@node2 ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
master:
mysql> create database mydb1;
Query OK, 1 row affected (0.34 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
注，大家可以看到沒有同步mydb1，再測試一下。
master:
mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mydb1 |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
slave:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
注，大家可以看到同步了testdb，好了到這裡所有演示全部完成，希望大家有所收穫。^_^……
相關文章
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- MySQL主從複製之半同步複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- mysql5.5半同步複製探究MySql
- MySQL半同步使用注意事項MySql
- MySQL5.7主從複製-半同步複製搭建MySql
- mysql5.5中的半同步複製MySql
- 主從複製、雙主複製及半同步複製、以及基於SSL的複製
- MySQL5.5半同步複製實現原理MySql
- MySQL 半同步複製MySql
- MySQL半同步複製MySql
- MySQL 5.5 Semi-sync 半同步複製測試MySql
- MySQL 5.5半同步複製的配置與監控MySql
- 【MySQL】半同步與增強半同步複製MySql
- MySQL的非同步複製和半同步複製MySql非同步
- MySQL主從複製之非同步複製MySql非同步
- mysql 5.7半同步複製MySql
- MySQL的半同步複製MySql
- MySQL(二)：主從複製結構、半同步複製、雙主複製結構、利用SSL實現安全的MySQL主從複製MySql
- MySQL主從雙向同步複製MySql
- MySQL 8 複製（二）——半同步複製MySql
- mysql線上建立半同步複製的從庫MySql
- Mysql5.7半同步複製MySql
- MySQL生產庫主從重新同步操作注意事項薦MySql
- MySQL-主從複製之同步主從資料MySql
- MySQL 5.7半同步複製after sync和after commit詳解MySqlMIT
- mysql半同步複製的設定MySql
- MySQL 半同步複製+MMM架構MySql架構
- MySQL半同步複製--after_rollbackMySql
- MySQL 5.7的安裝及主從複製(主從同步)MySql主從同步
- mysql 5.6 半同步主從安裝MySql
- 主從複製--非同步篇非同步
- MySQL主從複製的原理和注意事項都在這兒了！！MySql
- 半同步複製報錯mysql8.0.25MySql
- MySQL半同步複製--after_commitMySqlMIT
- mysql5.5.9半同步複製功能部署MySql