一、為什麼要做Mysql的主從複制(讀寫分離)？
通俗來講，如果對資料庫的讀和寫都在同一個資料庫伺服器中操作，業務系統性能會降低。
為了提升業務系統性能，優化使用者體驗，可以通過做主從複制(讀寫分離)來減輕主資料庫的負載。
而且如果主資料庫當機，可快速将業務系統切換到從資料庫上，可避免資料丢失。
二、MySQL主從複制 Mysql主從複制(讀寫分離)和叢集的差別
我對MySQL也是剛開始研究，不是很專業。我的了解是：
1、主從複制(讀寫分離)：一般需要兩台及以上資料庫伺服器即可(一台用于寫入資料，一台用于同步主的資料并用于資料查詢操作)。
局限性：
(1)配置好主從複制之後，同一張表，隻能對一個伺服器寫操作。如果在從上執行了寫操作，而之後主也操作了這張表，或導緻主從不同步；據說可以配置成主主方式，但我還沒有研究到。
(2)主資料庫伺服器當機，需要手動将業務系統切換到從資料庫伺服器。無法做到高可用性(除非再通過部署keepalive做成高可用方案)。
2、叢集是由N台資料庫伺服器組成，資料的寫入和查詢是随機到任意一台資料庫伺服器的，其他資料庫伺服器會自動同步資料庫的操作。
任何一台資料庫當機，不會對整個叢集造成大的影響。
局限性：我經過測試才知道目前mysql叢集版本(MySQL Cluster)隻能對NDB存儲引擎的資料進行叢集同步，如果是INNODB或其他的MySQL存儲引擎是不行的。這個也導緻了我放棄了在業務系統中應用這種方案。
三、回歸正題，接下來開始MySQL5.5.12的主從複制教程：
1、MySQL5.5開始主從複制有兩種方式：基于日志(binlog)；基于GTID(全局事務标示符)。
需要注意的是：GTID方式不支援臨時表！是以如果你的業務系統要用到臨時表的話就不要考慮這種方式了，至少目前最新版本MySQL5.5.12的GTID複制還是不支援臨時表的。
是以此篇教程主要是告訴大家如何通過日志(binlog)方式做主從複制！
2、MySQL官方提供的MySQL Replication教程：
http://dev.mysql.com/doc/refman/5.6/en/replication.html
這個官方教程強烈建議大家閱讀(需要一定的英語閱讀能力哦！不行就google翻譯後再閱讀吧~)。
是以，一般來說都是通過 主從複制(Master-Slave)的方式來同步資料，再通過讀寫分離(MySQL-Proxy)來提升資料庫的并發負載能力 這樣的方案來進行部署與實施的。
如下圖所示：
三、MySQL主從複制
場景描述：
主資料庫伺服器：42.51.153.157，MySQL已經安裝，并且無應用資料。
從資料庫伺服器：42.51.155.219，MySQL已經安裝，并且無應用資料。
系統：CentOS6.4 32位
Mysql：5.5版本
selinux關閉 防火牆關閉
setenforce 0service iptables stop
2.就是要确定伺服器上的Mysql
yum install mysql mysql-server -y
3.确認時間同步
[[email protected] ~]# yum install ntp -y
[[email protected]~]# vi /etc/ntp.conf #添加以下2行
server127.127.1.0fudge127.127.1.0 stratum 8
[[email protected] ~]# /etc/init.d/ntpd restart #重新開機ntpd服務
2)在節點伺服器上進行時間同步
[[email protected] ~]# /etc/init.d/iptables stop
[[email protected]~]# chkconfig iptables off
[[email protected]~]# yum install -y ntpdate
[[email protected]~]# /usr/sbin/ntpdate 42.51.153.157
四：配置Mysql
1.首先修改Mysql主配置的配置檔案(Master)
[[email protected] ~]# \cp /etc/my.cnf /etc/my.cnf.old ##修改之前備份
[[email protected]~]# vi /etc/my.cnf ##修改
server-id = 11 //修改
log-bin=master-bin //修改
log-slave-updates=true //增加
[[email protected]~]# /etc/init.d/mysqld restart ##重新開機Mysql
[[email protected]~]# mysql -u root –ppassword //登入資料庫，給從伺服器授權mysql> create user 'yanghongfei'@'localhost' identified by '123456';
mysql> grant replication slave on *.* to 'yanghongfei'@'42.51.155.219' identified by '123456';
mysql>flush privileges;
mysql>show master status; ##這個日志後面會用的到+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 343 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2.配置從伺服器 (Slave)
[[email protected] ~]# \cp /etc/my.cnf /etc/my.cnf.old ##修改之前備份
[[email protected] shell]# vi /etc/my.cnf
server-id = 33 //修改 此處ID隻要比Master大就可以
relay-log=relay-log-bin //增加
relay-log-index=slave-relay-bin.index //增加
修改完成之後重新開機mysql服務
登入Mysql，配置同步
[[email protected] shell]# service mysqld restart
[[email protected]~]# mysql -u root –ppassword
mysql>stop slave; ##首先停止
mysql> change master to master_host='42.51.153.157',master_user='yanghongfei',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=343;
mysql>start slave;
mysql> show slave status \G;
檢視是否成功~
上面2處都為Yes的時候表示已經成功了~~
3. 測試主從是否複制成功，主伺服器建立資料庫看是不是被同步過去了
Master：
Slave：
下面我來裝一個Discuz論壇 我們來測試下資料表會不會被同步進去，這裡安裝網站不再詳細說了 我這面用的環境是lnmp 首先下載下傳一個Discuz的包 網上有很多
[[email protected] ~]# unzip Discuz_7.2_FULL_SC_UTF8.zip[[email protected]~]# mv upload/ /usr/share/nginx/html/[[email protected]~]# cd /usr/share/nginx/html/[[email protected] html]#mv upload/Discuz
[[email protected] html]#chmod 777 -R ./Discuz/[[email protected]~]# service nginx restart
Master：
Slave：
上面可以看出主從複制成功~~
Mysql主從複制常用指令
1.停止主節點複制
mysql> stop master;
2.清除主節點複制檔案
mysql> reset master;
3.停止從節點複制
mysql> stop slave;
4.清除從節點複制檔案
mysql> reset slave;
5.開啟從節點複制
mysql> start slave;
6.檢視主資料庫的Master複制資訊
mysql> show master status;
7.檢視主節點的複制狀态
mysql> show master status \G;
8.檢視從節點的複制狀态
mysql> show slave status \G;
五、MySQL讀寫分離的實作
搭建MySQL讀寫分離
環境：主資料庫伺服器：42.51.153.157
從1資料庫伺服器：42.51.155.219
從2資料庫伺服器：42.51.157.217
代理Amoeba伺服器：42.51.152.166
software： amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u45-linux-x64.bin
下載下傳位址為：ftp://42.51.152.2/linux環境搭建
1)在Amoeba主機安裝Java環境
[[email protected] ~]# ./jdk-6u45-linux-x64.bin
[[email protected]~]# mv jdk1.6.0_45/ /usr/local/jdk1.6[[email protected]/]# vim /etc/profile //增加以下配置
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
[[email protected]/]# source /etc/profile
[[email protected]/]# java -version
java version"1.6.0_14"Java(TM) SE Runtime Environment (build1.6.0_14-b08)
Java HotSpot(TM)64-Bit Server VM (build 14.0-b16, mixed mode)
2)安裝并配置Amoeba軟體 修改以下帶下劃線的内容
[[email protected] ~]# mkdir /usr/local/amoeba
[[email protected] ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
[[email protected] ~]# chmod -R 755 /usr/local/amoeba/[[email protected]~]# /usr/local/amoeba/bin/amoeba
The stack size specified is too small, Specify at least 160k
Could not create the Java virtual machine.
3)配置Amoeba讀寫分離，兩個Slave讀負載均衡
Master、Slave1、Slave2中開放權限給Amoeba通路
Master:
mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
Query OK,0 rows affected (0.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00sec)
Slave1:
mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
Query OK,0 rows affected (0.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00sec)
Slave2:
mysql> grant all on *.* to test@'42.51.152.166' identified by '123.com';
Query OK,0 rows affected (0.00sec)
mysql>flush privileges;
Query OK,0 rows affected (0.00sec)
編輯amoeba.xml配置檔案
8066
127.0.0.1
true
${clientConnectioneManager}
20
30
500
${amoeba.home}/conf/dbServers.xml
${amoeba.home}/conf/rule.xml
${amoeba.home}/conf/ruleFunctionMap.xml
${amoeba.home}/conf/functionMap.xml
1500
master
master
slaves
true
編輯dbServers.xml配置檔案 同樣是以下帶下劃線的内容
${defaultManager}
64
128
3306
test
test
123.com
500
500
10
600000
600000
true
true
true
42.51.153.157
42.51.155.219
42.51.157.217
1
slave1,slave2
配置無誤後 啟動Amoeba軟體 預設的TCP端口号：8066. 隻有看到Amoeba軟體監聽到了其他的Mysql主機才算是成功了~
[[email protected] conf]# netstat -antp | grep 'java'tcp0 0 ::ffff:127.0.0.1:60672 :::* LISTEN 5121/java
tcp0 0 :::8066 :::* LISTEN 5121/java
tcp0 0 ::ffff:42.51.152.166:55874 ::ffff:42.51.153.157:3306 ESTABLISHED 5121/java
tcp0 0 ::ffff:42.51.152.166:36135 ::ffff:42.51.157.217:3306 ESTABLISHED 5121/java
tcp0 0 ::ffff:42.51.152.166:40687 ::ffff:42.51.155.219:3306 ESTABLISHED 5121/java
4).用戶端測試 通過代理來通路Mysql
[[email protected]_node1 ~]# mysql -u amoeba -p123456 -h 42.51.152.166 -P8066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connectionid is 414297791Server version:5.1.45-mysql-amoeba-proxy-2.2.0MySQL Community Server (GPL) by Atomicorp
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 clearthe current input statement.
mysql>mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yanghongfei |
+--------------------+
5 rows in set (0.02sec)
mysql>
在Master建立一個表，同步到其他的2個從伺服器上，然後關閉從伺服器的Slave功能，再插入其他的差別語句。
Master：
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yanghongfei |
+--------------------+
5 rows in set (0.00sec)
mysql>mysql>use yanghongfei;
Database changed
mysql> create table yang (id int (10), name varchar(10),address varchar(20));
Query OK,0 rows affected (0.26sec)
mysql>desc yang;+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Slave1； 可以看出來同步了
mysql>use yanghongfei;
Database changed
mysql>mysql>show tables;+-----------------------+
| Tables_in_yanghongfei |
+-----------------------+
| yang |
+-----------------------+
1 row in set (0.00sec)
mysql>mysql>desc yang;+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Slave2: 同上
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| yanghongfei |
+--------------------+
5 rows in set (0.06sec)
mysql>use yanghongfei;
Database changed
mysql>show tables;+-----------------------+
| Tables_in_yanghongfei |
+-----------------------+
| yang |
+-----------------------+
1 row in set (0.03sec)
mysql>desc yang;+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
關閉從1和從2 的Slave功能
mysql>stop slave;
Query OK,0 rows affected (0.00 sec)
關閉之後在主伺服器上插入差別語句 Master操作：
mysql> insert into yang values('1','yang','this_is_master');
Query OK,1 row affected (0.01 sec)
剛才上面操作是我們關閉了Slave功能 所有從伺服器隻是同步了表，沒有同步到插入的差別語句，我們在2個從資料庫上分别建立差別語句；
Slave1：
mysql> insert into yang values('2','yang','this_is_slave1');
Query OK,1 row affected (0.00sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | yang | this_is_slave1 |
+------+------+----------------+
1 row in set (0.00 sec)
Slave2:
mysql> insert into yang values ('3','yang','this_is_slave2');
Query OK,1 row affected (0.05sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 3 | yang | this_is_slave2 |
+------+------+----------------+
1 row in set (0.02sec)
mysql>
最後：在剛才連接配接上的用戶端上操作
第一次查詢操作為：
mysql>use yanghongfei;
Reading table informationforcompletion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
mysql>show tables;+-----------------------+
| Tables_in_yanghongfei |
+-----------------------+
| yang |
+-----------------------+
1 row in set (0.00sec)
mysql>desc yang;+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | yang | this_is_slave1 |
+------+------+----------------+
1 row in set (0.01 sec)
第二次查詢操作為：
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 3 | yang | this_is_slave2 |
+------+------+----------------+
1 row in set (0.01 sec)
第三次查詢操作為：
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | yang | this_is_slave1 |
+------+------+----------------+
1 row in set (0.00 sec)
從上面可以看出查詢操作是 都是在Slave1和Slave2執行的 還可以看出來我們的2台Slave起到了負載均衡
接下來我們在Client上再寫一條insert語句
Client 寫操作 下面可以看出我們插入了一條寫操作 查詢的話還是查不到的.證明是隻能在Master才能查詢到~
mysql> insert into yang values ('4','yang','This_is_wirte_test');
Query OK,1 row affected (0.02sec)
mysql>mysql>mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 3 | yang | this_is_slave2 |
+------+------+----------------+
1 row in set (0.01sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | yang | this_is_slave1 |
+------+------+----------------+
1 row in set (0.01sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 3 | yang | this_is_slave2 |
+------+------+----------------+
1 row in set (0.00sec)
mysql> select *from yang;+------+------+----------------+
| id | name | address |
+------+------+----------------+
| 2 | yang | this_is_slave1 |
+------+------+----------------+
1 row in set (0.00 sec)
Master查詢剛才Client的Write Test
mysql>mysql> select *from yang;+------+------+--------------------+
| id | name | address |
+------+------+--------------------+
| 1 | yang | this_is_master |
| 4 | yang | This_is_wirte_test |
+------+------+--------------------+
2 rows in set (0.00sec)
mysql>
Mysql讀寫分離常用指令
1.切換資料庫
mysql> use yanghongfei;
2.檢視資料表
mysql> show tables;
3.檢視表結構
mysql> desc yang;
4.檢視表内容
mysql> select * from yang;
5.插入一條表語句
mysql> insert into yang values ('4','yang','This_is_wirte_test');
6.删除一條表語句
mysql> delete from yang where id=4;
實作結果： 可以看出Mysql的讀寫分離，目前所有的寫操作都在Master資料庫伺服器上，所有的寫操作都在Slave1和Slave2 資料庫伺服器上。進而實作了MySql讀寫分離，負載均衡。
備注：i/o線程的端口号是沒辦法鎖定的，若是要開啟iptables的話、就在Cilent執行insert到Master上，然後在Slave 用tcpdum抓Master的資料包，記錄下來tcp端口号，添加iptables ACCEPT即可。
重新開機後端口号會跟這變化，這時候需要根據tcpdump抓到的包分析來修改iptables的政策~ Amoeba的iptables上隻開啟Java的8066端口号就OK..