【Mysql主從複製】
解決的問題
資料分佈：比如一共150臺機器，分別往電信、網通、移動各放50臺，這樣無論在哪個網路訪問都很快。其次按照地域，比如國內國外，北方南方，這樣地域性訪問解決了。
負載均衡：Mysql讀寫分離，讀寫分開了，解決了部分伺服器的壓力，均衡分開。
資料備份：比如100臺機器，實際資料是一樣的，這樣可以說每臺機器都是資料備份。
高可用性和容錯性：1臺機器掛掉了無所謂，因為還有99臺機器。
實現原理：
Mysql支援單向、非同步複製，複製過程中一個伺服器充當主伺服器，而一個或者多個其他伺服器充當從伺服器
Mysql複製基於主伺服器在二進位制日誌中跟蹤所有對資料庫的更改等操作。其實就是儲存的SQL語句，只不過被二進位制化。
每個從伺服器從主伺服器接收主伺服器已經記錄到其二進位制日誌的儲存和更新。比如主伺服器執行了插入操作，那麼從伺服器你就會把這個指令拿過來，在它自己的伺服器執行插入操作。
實現步驟：
Master將改變日誌記錄到二進位制日誌中（binary log）, 再然後Slave重做中繼日誌中的事件，將改變反應它自己的資料，也就是執行一遍日誌。
流程圖解說：
首先，如果主機有資料改變，會寫到主機的bin-log日誌中，然後從機會監聽主機的bin-log日誌，這時候會讀主機的bin-log，然後寫入自己（從機）的relay-log（中繼日誌）中，然後再從中繼日誌中讀出來執行SQL事件。這樣就會執行了主機的SQL操作。
開始搭建主從複製
192.168.9.164 主伺服器，讀操作在164
192.168.9.165 從伺服器，寫操作在165
因為主需要開啟bin-log，從伺服器需要監聽主伺服器的bin-log，那麼首先去主伺服器開啟bin-log。
先編輯主伺服器164的Mysql配置檔案
#vim /etc/my.cnf //對照下面三行
log_bin = mysql-bin #是開啟的
binlog_format = mixed #日誌檔名稱
Server-id = 164 #以為server-id是惟一的，用ip最合適。
先去mysql資料的存放目錄，如果不知道的情況下，可以在/etc/my.cnf中檢視 datadir的位置datadir = /data/mysql
看到這些檔案，就是mysql的binlog檔案，說明bin-log已經開啟，重啟Mysql服務。
然後進入主伺服器客戶端，執行命令檢視主伺服器狀態
MySQL [(none)]> show master status;
然後配置從伺服器：
同樣編輯/etc/my.cnf檔案，把server-id修改一下，暫時把兩個選項註釋掉，還需要加一個relay-log（中繼日誌）
#vim /etc/my.cnf
#log_bin = mysql-bin
#binlog_format = mixed
relay_log = mysql-relay-bin
Server-id = 165 #以為server-id是惟一的，用ip最合適。
配置為重啟Mysql。
下面就需要讓從伺服器監聽主伺服器的bin-log日誌檔案，這樣需要做的話就是主伺服器允許授權。先去主伺服器授權，回到164主伺服器mysql客戶端執行命令授權，
MySQL [(none)]> grant replication slave on *.* to slave@192.168.9.165 identified by '123456';
MySQL [(none)]> flush privileges; //重新整理許可權
這句話的意思是，允許slave這個使用者通過密碼123456在192.168.9.165這臺伺服器上訪問164這臺master。這時候就可以允許165監聽164的bin-log了。
進入從伺服器，先確定從伺服器是否能ping的通主伺服器。然後進入從伺服器客戶端，設定要監聽的機器。進入從伺服器mysql客戶端習慣性執行 stop slave 命令。
然後準備在從伺服器mysql客戶端執行授權命令，but，在執行之前先看看主伺服器的狀態
這裡由154變成了602，因為剛才在主伺服器執行過命令。
MySQL [(none)]> change master to
-> master_host='192.168.9.164',
-> master_port=3306,
-> master_user='slave',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=450;
MySQL [(none)]> start slave; //OK沒問題的話，開啟監聽。
MySQL [(none)]> show slave status\G; //檢視狀態
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.164
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 450
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
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: 629
Relay_Log_Space: 527
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
Master_UUID: d8d23a1f-341f-11e8-8102-000c29f3aa69
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
然後在主伺服器做相應的Mysql增刪改，同樣再檢視一下從伺服器的資料，都發生了相應的變化~
檢查主從複製通訊狀態
Slave_IO_State #從站的當前狀態
Slave_IO_Running： Yes #讀取主程式二進位制日誌的I/O執行緒是否正在執行
Slave_SQL_Running： Yes #執行讀取主伺服器中二進位制日誌事件的SQL執行緒是否正在執行。與I/O執行緒一樣
Seconds_Behind_Master #是否為0，0就是已經同步了
必須都是 Yes
如果不是原因主要有以下 4 個方面：
1、網路不通
2、密碼不對
3、MASTER_LOG_POS 不對 ps
4、mysql 的 auto.cnf server-uuid 一樣（可能你是複製的mysql）
$ find / -name 'auto.cnf'
$ cat /data/mysql/auto.cnf
[auto]
server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照這個16進位制格式，修改server-uuid，重啟mysql即可
OK，這樣主從複製的架構就配置完成了。但是彆著急，接著往下看。
現有架構問題
現有架構問題有一個Master，可以擴充套件多個Slave，如果Master掛掉，那麼影響現有的架構，如果Master一旦掛掉，寫資料不知道往哪裡寫了，Slave也會失去監聽的Master。
解決問題
給現有的Master再去備份另外的Master，這可以實現心跳感應，其中一個Master掛掉，切換到另外一個Master。
【Mysql雙主熱備】
流程圖解說：
現在要做的是讓Slave也作為Master，首先給Slave也開啟bin-log，也就是做主機的話，必須開啟bin-log。而且要開始log_slave_updates，這個配置代表的是，當我們的relay_log發生改變的時候，它會通過log_slave_updates寫入到從機的bin-log日誌中，也就是說實現了Slave中的bin-log和Master的bin-log日誌同步，保證資料一致。然後再讓Master監聽Slave的bin-log，這樣的話必須Master也必須開啟relay-log，讓它作為Slave的從，這樣Master和Slave互為主從。這樣我們往其中任意一臺資料庫寫資料，另外一個資料也會發生相應改變，其中一個掛掉，我們另外一個機器可以作為主機提供服務，最終達到雙主熱備。
達到的架構圖：
兩個Master，雙主。然後各自的Master都有各自的Slave叢集，其中一個Master掛掉，另一個Master可以提供服務，並且下面還有很多Slave，然後可以將掛掉的Master下面的Slave切換到正常的Mater上。
開始搭建雙主熱備
現在要做的是把Slave作為主，需要開啟Slave的bin-log，編輯從機的配置檔案
#vim /etc/my.cnf //按照下面的配置
log_bin = mysql-bin
binlog_format = mixed
relay_log = mysql-relay-bin
log_slave_updates = 1 #表示將relay通過這樣的配置寫入到bin-log中，主掛掉，從立馬作為主上位。
Server-id = 165 #以為server-id是惟一的，用ip最合適。
配置完之後重啟從機的Mysql服務，這樣從就有作為主的能力。
接下來給主機授權，Slave要讓主監聽從的bin-log，剛才是在主給從授權，現在是在從給主授權
grant replication slave on *.* to master@192.168.9.164 identified by 'wt000000';
授權完成之後，讓主機監聽從機
編輯主機的配置檔案，加上
# vim /etc/y.cnf //按照下面的配置在主機加上
relay_log = mysql-relay-bin
log_slave_updates = 1 #同理
下面重啟主的Mysql伺服器。
接下來做的就是讓之前的主來監聽從了。
進入從的Mysql終端，檢視作為Master的狀態，進入終端先執行reset master命令，再檢視狀態
接下來就可以在192.168.9.164（之前的主機）上執行change master to命令了。
MySQL [(none)]> change master to
-> master_host='192.168.9.165',
-> master_user='master',
-> master_password='wt000000',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
此時，164就成了165的從了。然後執行命令
MySQL [(none)]> start slave;
MySQL [(none)]> show slave status\G
MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.165
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //ok
Slave_SQL_Running: Yes //ok
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: 154
Relay_Log_Space: 527
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: 165
Master_UUID: 6b831bf3-8ae7-11e7-a178-000c29cb5cbc
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
現在兩臺分別是對方的主，也是對方的從。檢查一下狀態，沒問題~
然後分別在主和從修改資料，每次的修改發現，兩個伺服器都會發生相應的變化。OK~雙主熱備配置完成~只有從還不是很完善，往下面接著看。
要實現每個主伺服器下面都有從伺服器。
【雙主雙從】
192.168.9.164 //原Master(一主)
192.168.9.165 //原Slave(二主)
192.168.9.166 //做為192.168.9.164的從
192.168.9.167 //做為192.168.9.165的從
分別編輯兩個新從機的配置檔案
編輯192.168.9.166的Mysql配置檔案
#og_bin = mysql-bin
#binlog_format = mixed
relay_log = mysql-relay-bin
Server-id = 166 #以為server-id是惟一的，用ip最合適。
編輯192.168.9.167的Mysql配置檔案
#log_bin = mysql-bin
#binlog_format = mixed
relay_log = mysql-relay-bin
Server-id = 167 #以為server-id是惟一的，用ip最合適。
接下來分別給兩個新從機授權
先讓192.168.9.164給192.168.9.166授權。
進入192.168.9.164的Mysql客戶端授權。
MySQL [hb]> grant replication slave on *.* to slave166@192.168.9.166 identified by 'wt000000';
然後檢視本機的Master狀態
然後進入從機192.168.9.166,執行change master 命令
MySQL [(none)]> change master to
-> master_host='192.168.9.164',
-> master_user='slave166',
-> master_password='wt000000',
-> master_log_file='mysql-bin.000005',
-> master_log_pos=1034;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
MySQL [(none)]> start slave;
MySQL [(none)]> show slave status\G
MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.164
Master_User: slave166
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1034
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
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: 1034
Relay_Log_Space: 527
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: 164
Master_UUID: d8d23a1f-341f-11e8-8102-000c29f3aa69
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
再讓192.168.9.165給192.168.9.167授權。
這裡就不重複說明了。。。。
OK，每個主伺服器的從伺服器也搭建完成了。。經過測試，操作其中一臺主機的資料庫，其他三個伺服器的資料庫資料也跟著發生相應的變化~
【補充】
至此我們mysql伺服器的主從複製架構已經完成，但是我們現在的主從架構並不完善，因為我們的從服務上還可以進行資料庫的寫入操作，一旦使用者把資料寫入到從伺服器的資料庫內，然後從伺服器從主伺服器上同步資料庫的時候，會造成資料的錯亂，從而會造成資料的損壞，所以我們需要把從伺服器設定成只讀~如果加入了Mycat中介軟體，在主從架構中，如果主庫當機的情況下，從庫也要立馬做主庫角色，那麼這裡最好也是關掉。方法如下：
注意：read-only = ON ，這項功能只對非管理員組以為的使用者有效！
注意：read-only = ON ，這項功能只對非管理員組以為的使用者有效！
上圖檢視出沒有開啟只讀。那麼我們修改從伺服器的配置檔案my.cnf，加一行程式碼
read-only = ON
新增完成之後重啟服務即可。
注意，mysql主從架構中，從伺服器要比主伺服器速度慢是正常的，因為主伺服器是並行的，從伺服器是佇列逐行執行命令操作。（特別是在事務操作中，首先會把執行事務的操作放到快取區，然後執行成功之後才會把日誌寫入主伺服器日誌，然後主伺服器把日誌傳送給從伺服器，從伺服器中繼日誌儲存後，再逐行讀取操作從伺服器），Mysql預設是非同步的。
事務安全配置：
比如，在主伺服器上，某一個事務已經提交了，這個事務提交相關的二進位制日誌應該寫進日誌檔案，二進位制日誌有緩衝區，意味著事務提交有些事件在緩衝區沒有寫進二進位制日誌，萬一這個時候主伺服器崩潰了，從伺服器得不到相關的事件，那麼從伺服器實現不了將事務完整的結束，所以在主伺服器完成的事務，從伺服器有可能完成不了，如果我們能讓主伺服器的事務一提交，那麼立即寫到從伺服器中，不在緩衝區停留，那麼就會降低主從不一致的可能性。那麼在主伺服器怎麼配置呢？
SHOW VARIABLES LIKE 'log%';
找到sync_binlog,為了事務安全，啟動該選項
-----------------------------------------------------------
要注意的是，在確定完從庫之後，要保證從伺服器和主伺服器的資料一致性，需要在備份主資料庫資料之前，先臨時鎖定主資料庫，保證資料一致性。
flush tables with read lock;
另外要注意的是，如果涉及到函式或者儲存過程的複製，需要在/etc/my.cnf中的[mysqld]段中增加配置log_bin_trust_function_creators=true,或者在客戶端設定set_log_bin_trust_function_creators =1
【Mysql讀寫分離】
配置讀寫分離在這裡用的是中介軟體Mycat，下面簡單介紹。官方地址：http://www.mycat.io/
Mycat特性
支援SQL92標準
支援Mysql、Oracle、DB2、SQL Server、PostgreSQL等資料庫的常見SQL語法
遵循Mysql原生協議，跨語言、跨平臺誇資料庫通用中介軟體代理
基於心跳檢查自動故障切換、支援讀寫分離、支援主從複製、支援分庫分表等
下面開始在新的伺服器下載安裝Mycat，這裡的IP是192.168.9.168。
下載安裝Mycat
# cd /usr/local/src
# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# mv mycat/ /usr/local/mycat
一步步配置Mycat
進入conf目錄下，檢視一下檔案裡列表，需要配置的僅僅是這兩個檔案
# vim server.xml //編輯server.xml檔案，按照如下配置
<!--連線mycat需要用賬號root，密碼就是下面的密碼，還有"mydb"的庫，這並不一個真正的庫，只是實際庫的一個對映-->
<user name="root">
<property name="password">123456</property>
<property name="schemas">mydb</property><!--如果多個庫，可以用逗號隔開-->
<!-- 表級 DML 許可權設定 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<!--下面的User暫時註釋掉，這裡的意思是隻能寫-->
<!--<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>-->
# vim schema.xml //對應下面的配置
<!--name="mydb" 和serverxml配置一致,要管理的庫。checkSQLschema="false" ，如果為true的話，意思是查詢的時候帶庫.表名 。/sqlMaxLimit="100" 意思是如果sql語句沒有加limit，mycat會在sql語句後面自動加limit
100。dataNode="dn1" 就是關聯下面的dataNode-->
<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<!-- //database="hb4" ，這是真實的物理資料庫名稱-->
<!--***剩下的配置不在這裡一一說明，對應配置好即可。想要了解去看手冊：http://www.mycat.io/document/Mycat_V1.6.0.pdf**--->
<dataNode name="dn1" dataHost="localhost1" database="hb4" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat><!--心跳檢測 show slave status///詳細瞭解去看手冊-->
<!-- can have multi write hosts -->
<!--<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
</writeHost>-->
<!--下面的配置是對應著在不同的伺服器上授權的賬號配置的，配置會在授權部分介紹-->
<!--第一臺主機和第一臺主機的從機-->
<writeHost host="hostM1" url="192.168.9.164:3306" user="mycat" password="123456" ><!--主：負責寫-->
<readHost host="hostS1" url="192.168.9.166:3306" user="mycat" password="123456" /><!--從：負責讀-->
</writeHost>
<!--第二臺主機和第二臺主機的從機-->
<writeHost host="hostM2" url="192.168.9.165:3306" user="mycat" password="123456" ><!--主：負責寫-->
<readHost host="hostS2" url="192.168.9.167:3306" user="mycat" password="123456" /><!--從：負責讀-->
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
配置完成之後開啟mycat服務
[root@localhost mycat]# bin/mycat start //命令啟動
Starting Mycat-server...
[root@localhost mycat]# bin/mycat status //命令檢視狀態
Mycat-server is running (3593).
[root@localhost mycat]# netstat -tunlp | grep 8066 //檢視埠
tcp 0 0 :::8066 :::* LISTEN 3595/java
授權配置
MySQL [hb4]> grant insert,update,delete,select on *.* to mycat@192.168.9.168 identified by '123456'; //在兩臺主伺服器的Mysql終端分別執行
MySQL [hb4]> grant select on *.* to mycat@192.168.9.168 identified by '123456'; //在兩臺從伺服器Mysql終端分別執行
確定啟動成功，接下來通過專案開發機連結一下試試
# mysql -uroot -p123456 -h192.168.9.168 -P8066
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| mydb |
+----------+
1 row in set (0.01 sec)
然後在之前的主機上運算元據表，然後通過此連結繼續檢視資料表，資料也發生了相應的變化。這樣我們在專案中連線的Mysql地址埠做好更改，那麼就連線到了mycat伺服器了。然後分別在兩臺主伺服器做寫入測試；在開發機連結做讀資料測試。再分別檢視mycat目錄下logs目錄下的mycat.log檔案，發現不同的讀寫分發到不同的伺服器~最後試著掛掉一臺伺服器，重新做寫操作，和查詢操作，隨時跟蹤每臺伺服器的資料和mycat.log日誌。發現，ok~沒任何問題！
【Mysql分庫分表】
如果資料量達到了億級別，那麼這樣管理的話，壓力會很大，解決這個問題可以把庫裡資料量比較大的表給單獨分散，分散到其他庫中，這樣庫承受的壓力就會小很多了。拿一個表做演示
確定要分庫的表
拿庫裡的hp_user來演示，首先檢視一下表結構。
MySQL [hb4]> show create table hp_user;
| hp_user | CREATE TABLE `hp_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL COMMENT '使用者名稱',
`nickname` varchar(40) DEFAULT NULL COMMENT '使用者暱稱',
`password` varchar(40) DEFAULT NULL COMMENT '密碼',
`email` varchar(40) DEFAULT NULL COMMENT '郵箱',
`avatar` varchar(150) DEFAULT NULL COMMENT '頭像',
`zip_avatar` varchar(150) DEFAULT NULL COMMENT '壓縮頭像',
`birthday` int(11) DEFAULT '0' COMMENT '出生日期',
`gender` tinyint(4) DEFAULT '1' COMMENT '性別, 1:男, 2:女',
`slogan` varchar(255) NOT NULL DEFAULT '' COMMENT '標語',
`level` tinyint(4) DEFAULT NULL COMMENT '等級',
`total_score` int(11) DEFAULT '0' COMMENT '使用者的積分總額',
`score` int(11) NOT NULL DEFAULT '0' COMMENT '積分',
`freeze_score` int(11) NOT NULL DEFAULT '0' COMMENT '購買商品時凍結積分',
`point` int(11) DEFAULT '0' COMMENT '點數(預留)',
`invite_code` char(6) DEFAULT NULL COMMENT '邀請碼(自己的）',
`invite_by` int(11) NOT NULL DEFAULT '0' COMMENT '被邀請id(user_info.id)',
`status` tinyint(4) DEFAULT '1' COMMENT '使用者狀態, 1:可用, 0: 不可用',
`create_time` int(11) DEFAULT NULL COMMENT '建立時間',
`register_ip` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '註冊ip',
`login_time` int(11) DEFAULT NULL COMMENT '登入時間',
`forbidden_time` int(11) DEFAULT NULL COMMENT '禁用時間',
`operator_id` int(11) DEFAULT '0' COMMENT '操作人',
`is_delete` tinyint(4) DEFAULT '0' COMMENT '是否刪除',
`reason` varchar(100) DEFAULT NULL COMMENT '稽核不通過請備註原因',
`is_examine` tinyint(4) DEFAULT '0' COMMENT '是否通過,1：否,0:是',
`token` varchar(32) DEFAULT NULL COMMENT '使用者token',
`alipay_code` varchar(255) DEFAULT '' COMMENT '支付寶賬戶',
`alipay_name` varchar(255) DEFAULT '' COMMENT '支付寶名字',
`wx_id` char(50) NOT NULL DEFAULT '' COMMENT '微信oppenid',
`level_time` varchar(20) NOT NULL COMMENT '成為等級時間',
`assessment_time` varchar(20) NOT NULL COMMENT '上次考核時間',
`level_type` int(11) NOT NULL COMMENT '等級狀態1首次2不是首次',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `invite_code` (`invite_code`),
KEY `status` (`status`) USING BTREE,
KEY `is_delete` (`is_delete`) USING BTREE,
KEY `invite_by` (`invite_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資訊表' |
建立要分的三個庫的對應的表
MySQL [hb4]> create database user_db1;
Query OK, 1 row affected (0.00 sec)
MySQL [hb4]> create database user_db2;
Query OK, 1 row affected (0.10 sec)
MySQL [hb4]> create database user_db3;
Query OK, 1 row affected (0.02 sec)
建立好庫之後，分別use 三個庫，複製建表語句，建立資料表，以後有資料需要往表裡寫了，那麼做一個hash處理，做分配，將入庫資料分散開，減小單庫壓力。
配置分庫分表
#vim schemaxml //對應好下面的配置
<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<!--name="hp_user":管理的表,primaryKey="id":表的主鍵id，dataNode="user1,user2,user3",因為建立了三個庫,rule="sharding-by-murmur"：對錶分片的管理規則-->
<table name="hp_user" primarykey="id" dataNode="user1,user2,user3" rule="sharding-by-murmur" />
</schema>
<!--分別對應的三個庫--->
<dataNode name="user1" dataHost="localhost1" database="user_db1" />
<dataNode name="user2" dataHost="localhost1" database="user_db2" />
<dataNode name="user3" dataHost="localhost1" database="user_db3" />
這樣配置完畢，但是在做分片資料之前，思考一下，插入資料的時候怎麼能保證不發生衝突呢？也就是說，比如第一條資料進入user_db1這個庫，資料主鍵id從1開始，第二條的時候進入user_db2，也是從1開始，將來查的時候肯定會查出多條id為1的，怎麼避免呢？可以在插入的時候指定id，如果不指定主鍵id呢？
mycat提供了一個自動生成主鍵id的配置解決這個問題。
找到conf下的rule.xml，每個tableRule就代表一個規則。
找到sharding-by-murmur，一致性雜湊。
# vim rulexml //對應配置
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns> <!--這裡是id，表示對id一致性雜湊處理-->
<algorithm>murmur</algorithm>
</rule>
</tableRule>
vim server.xml //對應配置
<property name="sequnceHandlerType">2</property> //預設是2，改為0,0代表本地檔案進行配置
# vim sequence_conf.properties //
#default global sequence //這裡是自帶的說明
GLOBAL.HISIDS= //隱藏的
GLOBAL.MINID=10001 //最小id
GLOBAL.MAXID=20000 //最大id
GLOBAL.CURID=10000 //當前id
# self define sequence
#配置自己的 //這裡是配置的user節點
USER.HISIDS=
USER.MINID=10001
USER.MAXID=20000
USER.CURID=10000
配置好重啟mycat。
資料測試
執行SQL還是在專案伺服器上，之前連線Mycat的伺服器終端執行SQL
INSERT INTO `hp_user` (
`id`,
`username`,
`nickname`,
`password`,
`email`,
`avatar`,
`zip_avatar`,
`birthday`,
`gender`,
`slogan`,
`level`,
`total_score`,
`score`,
`freeze_score`,
`point`,
`invite_code`,
`invite_by`,
`status`,
`create_time`,
`register_ip`,
`login_time`,
`forbidden_time`,
`operator_id`,
`is_delete`,
`reason`,
`is_examine`,
`token`,
`alipay_code`,
`alipay_name`,
`wx_id`,
`level_time`,
`assessment_time`,
`level_type`
)
VALUES
(
next value for MYCATSEQ_USER, //這裡一定要注意，是mycat的雜湊規則
'12323232323',
'測試使用者9',
'14e1b600b1fd579f47433b88e8d85291',
NULL,
'[\"\\/upload\\/2017\\/03\\/29\\/2eqcj0jh10skfgtv.jpg\"]',
'[\"\\/upload\\/2017\\/03\\/29\\/zip_2eqcj0jh10skfgtv.jpg\"]',
'19910201',
'1',
'nonononononononononononononononono',
'2',
'111117',
'111117',
'0',
'0',
'PRNLT',
'66',
'1',
'1483002221',
'124.202.200.186',
'1483002221',
NULL,
'0',
'0',
NULL,
'0',
'6b05171019a33823aa182364a1e643fa',
'18798048650',
'測試9',
'',
'1496723153',
'',
'0'
);
寫入4條資料，然後檢視。
MySQL [mydb]> select id,username,nickname,password,email,alipay_name from hp_user;
+-------+-------------+---------------+----------------------------------+-------+---+
| id | username | nickname | password | email | alipay_name |
+-------+-------------+---------------+----------------------------------+-------+---+
| 10006 | 13434343434 | 測試使用者6 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試6 |
| 10008 | 14455554444 | 測試使用者8 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試8 |
| 10007 | 16565656565 | 測試使用者7 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試7 |
| 10009 | 12323232323 | 測試使用者9 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試9 |
+-------+-------------+---------------+----------------------------------+-------+----+
然後再在真實資料庫伺服器上（主庫）檢視真實資料
MySQL [user_db2]> use user_db3;
Database changed
MySQL [user_db3]> select id,username,nickname,password,email,alipay_name from hp_user;
Empty set (0.00 sec)
MySQL [user_db3]> select id,username,nickname,password,email,alipay_name from hp_user;
Empty set (0.00 sec)
MySQL [user_db3]> use user_db2;
Database changed
MySQL [user_db2]> select id,username,nickname,password,email,alipay_name from hp_user;
+-------+-------------+---------------+----------------------------------+-------+-------------+
| id | username | nickname | password | email | alipay_name |
+-------+-------------+---------------+----------------------------------+-------+-------------+
| 10006 | 13434343434 | 測試使用者6 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試6 |
| 10008 | 14455554444 | 測試使用者8 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試8 |
+-------+-------------+---------------+----------------------------------+-------+-------------+
2 rows in set (0.00 sec)
MySQL [user_db2]> use user_db1;
Database changed
MySQL [user_db1]> select id,username,nickname,password,email,alipay_name from hp_user;
+-------+-------------+---------------+----------------------------------+-------+-------------+
| id | username | nickname | password | email | alipay_name |
+-------+-------------+---------------+----------------------------------+-------+-------------+
| 10007 | 16565656565 | 測試使用者7 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試7 |
| 10009 | 12323232323 | 測試使用者9 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 測試9 |
+-------+-------------+---------------+----------------------------------+-------+-------------+
2 rows in set (0.00 sec)
OK~配置完畢