站長資訊網(wǎng)MySQL：
大規(guī)模,高并發(fā)web服務器體系結構:
MySQL復制,Nginx,LNMP,Memcached,Tomcat(java,servlet,集群),varnish(squid)
NoSQL(redis,mongodb)
MySQL日志類型:二進制日志,事務日志,錯誤日志,一般查詢?nèi)罩?中繼日志,慢查詢?nèi)罩尽?/p>
二進制日志:一般放在數(shù)據(jù)目錄, mysql-bin.xxxxx, 滾動:達到最大上線滾動,flush logs滾動,服務器重啟，清除二進制(不建議直接用rm命令刪除)mysql>PURGE
二進制日志的格式:
statement語句),row(行),mixed(混合)
mysql-bin.index:二進制日志文件索引文件
mysql> SHOW MASTER STATUS; 查看當前正在使用的二進制文件
mysql> SHOW BINARY LOGS; 查看當前mysql上所仍然存在的二進制文件列表
mysql> SHOW BINLOG EVENTS IN "file"; 查看某個二進制文件的內(nèi)容
每一個操作稱為時間event:
timestamp(產(chǎn)生時間),position,offset(位置)
(二進制)及時點還原:
MySQL隔離級別(從低到高):READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE
MySQL Replication:
主服務器每執(zhí)行一個寫操作,把事件保存到數(shù)據(jù)庫和日志中,每保存一個事件,通過3306端口發(fā)送到另一臺服務器，另一臺服務器接收下來,先保存到本地的日志文件里面，在從日志中每次讀一個事件,執(zhí)行一下，結果保存在數(shù)據(jù)庫中
假設主服務器有多顆cpu(并行執(zhí)行),每次執(zhí)行先保存到二進制日志的緩存(一條一條寫)中,在同步到binary log中,主服務器比較快,
復制的作用： 輔助實現(xiàn)備份,高可用,異地容災,scale out：分攤負載
多級復制：(從服務器使用blackhole存儲引擎)
主從架構中，不使用MySQL代理，如何讓主的負責寫，從的負責讀?
循環(huán)復制,server-id
https://www.cnblogs.com/ygqygq2/p/6045279.html
scale out 分庫(根據(jù)需求業(yè)務分庫,垂直拆分,水平分隔)
scale on(增加更強的服務器)
讀寫分離:mysqll-proxy,amoeba,cobar(數(shù)據(jù)拆分)
MySQL-5.5異步、半同步配置及其注意事項
master: slave
1–>N
slave: master
1–>N X
一個從只能屬于一個主服務器
配置MySQL復制基本步驟：
一、master
1、啟用二進制日志
log-bin = master-bin
log-bin-index = master-bin.index
2、選擇一個惟一server-id
server-id = {0-2^32}
3、創(chuàng)建具有復制權限的用戶
REPLICATION SLAVE
REPLICATION CLIENT
二、slave
1、啟用中繼日志
relay-log = relay-log
relay-log-index =
2、選擇一個惟一的server-id
server-id = {0-2^32}
3、連接至主服務器，并開始復制數(shù)據(jù); mysql> CHANGER MASTER TO MASTER_HOST='',MASTER_PORT='',MASTER_LOG_FILE='',MASTER_LOG_FIEL_POS='',MASTER_USER='',MASTER_PASSWORD='';
mysql> START SLAVE;
mysql> START SLAVE IO_Thread;
mysql> START SLAVE SQL_Thread;
復制線程：
master: dump
slave: IO_Thread, SQL_Thread
[root@localhost ~]# mkdir -pv /mydata/data
mkdir: 已創(chuàng)建目錄 "/mydata"
mkdir: 已創(chuàng)建目錄 "/mydata/data"
[root@localhost ~]# useradd -r mysql
[root@localhost ~]# chown -R mysql.mysql /mydata/data/
[root@localhost src]# tar xf mysql-5.5.28-linux2.6-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.5.28-linux2.6-x86_64/ mysql
"mysql" -> "mysql-5.5.28-linux2.6-x86_64/"
[root@localhost local]# chown -R root.mysql ./*
[root@localhost local]# ls
bin games lib libexec mysql-5.5.28-linux2.6-x86_64 share
etc include lib64 mysql sbin src
[root@localhost local]# cd mysql
[root@localhost mysql]# ll
總用量 76
drwxr-xr-x. 2 root mysql 4096 3月 17 06:50 bin
-rw-r–r–. 1 root mysql 17987 8月 29 2012 COPYING
drwxr-xr-x. 4 root mysql 4096 3月 17 06:50 data
drwxr-xr-x. 2 root mysql 4096 3月 17 06:50 docs
drwxr-xr-x. 3 root mysql 4096 3月 17 06:50 include
-rw-r–r–. 1 root mysql 7604 8月 29 2012 INSTALL-BINARY
drwxr-xr-x. 3 root mysql 4096 3月 17 06:50 lib
drwxr-xr-x. 4 root mysql 4096 3月 17 06:50 man
drwxr-xr-x. 10 root mysql 4096 3月 17 06:50 mysql-test
-rw-r–r–. 1 root mysql 2552 8月 29 2012 README
drwxr-xr-x. 2 root mysql 4096 3月 17 06:50 scripts
drwxr-xr-x. 27 root mysql 4096 3月 17 06:50 share
drwxr-xr-x. 4 root mysql 4096 3月 17 06:50 sql-bench
drwxr-xr-x. 2 root mysql 4096 3月 17 06:50 support-files
[root@localhost mysql]# scripts/mysql_install_db –user=mysql –datadir=/mydata/data/
Installing MySQL system tables…
OK
Filling help tables…
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'
Alternatively you can run:
./bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
Please report any problems with the ./bin/mysqlbug script!
[root@localhost mysql]# cp support-files/my-large.cnf /etc/my.cnf
cp：是否覆蓋"/etc/my.cnf"? y
[root@localhost mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@localhost mysql]# chkconfig –add mysqld
vim /etc/my.cnf
log-bin=master-bin 啟用二進制日志
log-bin-index=master-bin.index
server-id=1
innodb_file_per_table=1
datadir=/mydata/data
service mysqld restart
vim /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
. /etc/profile.d/mysql.sh
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.240.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
從:
relay-log=relay-log
relay-log-index=relay-log.index
server-id = 11
主:
mysql> show master status;
+——————-+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| master-bin.000002 | 342 | | |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)
mysql> show binlog events in 'master-bin.000002';
+——————-+—–+————-+———–+————-+—————————————————————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————-+—–+————-+———–+————-+—————————————————————————————+
| master-bin.000002 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.28-log, Binlog ver: 4 |
| master-bin.000002 | 107 | Query | 1 | 267 | GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.240.%' IDENTIFIED BY 'replpass' |
| master-bin.000002 | 267 | Query | 1 | 342 | FLUSH PRIVILEGES |
+——————-+—–+————-+———–+————-+—————————————————————————————+
3 rows in set (0.00 sec)
從
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to master_host='192.168.240.131',master_user='repluser',master_password='replpass',master_log_file='master-bin.000002',master_log_pos=342; Query OK, 0 rows affected, 2 warnings (0.09 sec)
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.240.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 342
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
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: 342
Relay_Log_Space: 120
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
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
1 row in set (0.00 sec)
start slave;
出現(xiàn)：Slave_IO_Running:Connecting，需要關閉防火墻和selinux
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.240.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 342
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 271
Relay_Master_Log_File: master-bin.000002
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: 342
Relay_Log_Space: 438
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:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
在主服務器上操作
mysql> create database magedudb;
Query OK, 1 row affected (0.00 sec)
從：
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.240.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 433
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 362
Relay_Master_Log_File: master-bin.000002
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: 433
Relay_Log_Space: 529
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:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| laravel54 |
| magedudb |
| mysql |
| performance_schema |
+——————–+
禁止從服務寫:vim /etc/my.cnf
read-only=on
mysql> show global variables like 'read_only';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | ON |
+—————+——-+
1 row in set (0.00 sec)
[root@localhost ~]# cat /var/lib/mysql/master.info
23
master-bin.000002
433
192.168.240.131
repluser
replpass
3306
60
0
0
1800.000
0
86400
0
[root@localhost ~]# cat /var/lib/mysql/relay-log.info
7
./relay-log.000004
271
master-bin.000002
433
0
0
1
read-only = YES
在從服務器上設定，但對具有SUPER權限的用戶不生效;
sync-binlog = ON
在主服務器上設定，用于事務安全;
stop slave
start slave io_thread;
設置半同步步驟：
在Master和Slave的mysql命令行運行如下代碼：
# On Master
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
# On Slave
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
在Master和Slave的my.cnf中編輯：
# On Master
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
# On Slave
[mysqld]
rpl_semi_sync_slave_enabled=1
# 也可通過設置全局變量的方式來設置,如下：
set global rpl_semi_sync_master_enabled=1
# 取消加載插件
mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
==============================================
查看從服務器上的semi_sync是否開啟:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
查看主服務器上的semi_sync是否開啟，注意clients 變?yōu)? ，證明主從半同步復制連接成功:
mysql> SHOW GLOBAL STATUS LIKE 'rpl_semi%';
6、主服務器崩潰，事務已經(jīng)提交–>寫入二進制日志;
在主-從架構上建議使用的配置：
主服務器：
sync_binlog=1
innodb_flush_logs_at_trx_commit=1
從服務器：
skip_slave_start=1
read_only=1
設置主-主復制：
1、在兩臺服務器上各自建立一個具有復制權限的用戶;
2、修改配置文件：
# 主服務器上
[mysqld]
server-id = 10
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 1
# 從服務器上
[mysqld]
server-id = 20
log-bin = mysql-bin
relay-log = relay-mysql
relay-log-index = relay-mysql.index
auto-increment-increment = 2
auto-increment-offset = 2
3、如果此時兩臺服務器均為新建立，且無其它寫入操作，各服務器只需記錄當前自己二進制日志文件及事件位置，以之作為另外的服務器復制起始位置即可
server1|mysql> SHOW MASTER STATUSG
************************** 1. row ***************************
File: mysql-bin.000001
Position: 710
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
server2|mysql> SHOW MASTER STATUSG
mysql> SHOW MASTER STATUSG
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 811
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
4、各服務器接下來指定對另一臺服務器為自己的主服務器即可：
server1|mysql> CHANGE MASTER TO …,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=811
server2|mysql> CHANGE MASTER TO …,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=710
A: 查看B的二進制日志文件及位置，并以及作為自己的復制起點;
B：
MySQL-5.6基于GTID及多線程的復制
MySQL: 數(shù)據(jù)庫復制過濾
主:
MySQL 5.6引入的GTID(Global Transaction IDs)使得其復制功能的配置、監(jiān)控及管理變得更加易于實現(xiàn)，且更加健壯。
要在MySQL 5.6中使用復制功能，其服務配置段[mysqld]中于少應該定義如下選項：
binlog-format：二進制日志的格式，有row、statement和mixed幾種類型;
需要注意的是：當設置隔離級別為READ-COMMITED必須設置二進制日志格式為ROW，現(xiàn)在MySQL官方認為STATEMENT這個已經(jīng)不再適合繼續(xù)使用;但mixed類型在默認的事務隔離級別下，可能會導致主從數(shù)據(jù)不一致;
log-slave-updates、gtid-mode、enforce-gtid-consistency、report-port和report-host：用于啟動GTID及滿足附屬的其它需求;
master-info-repository和relay-log-info-repository：啟用此兩項，可用于實現(xiàn)在崩潰時保證二進制及從服務器安全的功能;
sync-master-info：啟用之可確保無信息丟失;
slave-paralles-workers：設定從服務器的SQL線程數(shù);0表示關閉多線程復制功能;
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：啟用復制有關的所有校驗功能;
binlog-rows-query-log-events：啟用之可用于在二進制日志記錄事件相關的信息，可降低故障排除的復雜度;
log-bin：啟用二進制日志，這是保證復制功能的基本前提;
server-id：同一個復制拓撲中的所有服務器的id號必須惟一;
report-host：
The host name or IP address of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server.
report-port:
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration.
master-info-repository:
The setting of this variable determines whether the slave logs master status and connection information to a FILE (master.info), or to a TABLE (mysql.slave_master_info)
relay-log-info-repository：
This option causes the server to log its relay log info to a file or a table.
log_slave_updates：
Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.
enforce_gtid_consistency：
一、簡單主從模式配置步驟
1、配置主從節(jié)點的服務配置文件
1.1、配置master節(jié)點：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=master.magedu.com
1.2、配置slave節(jié)點：
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=slave.magedu.com
2、創(chuàng)建復制用戶
mysql> GRANT REPLICATION SLAVE ON *.* TO repluser@172.16.100.7 IDENTIFIED BY 'replpass';
說明：172.16.100.7是從節(jié)點服務器;如果想一次性授權更多的節(jié)點，可以自行根據(jù)需要修改;
3、為備節(jié)點提供初始數(shù)據(jù)集
鎖定主表，備份主節(jié)點上的數(shù)據(jù)，將其還原至從節(jié)點;如果沒有啟用GTID，在備份時需要在master上使用show master status命令查看二進制日志文件名稱及事件位置，以便后面啟動slave節(jié)點時使用。
4、啟動從節(jié)點的復制線程
如果啟用了GTID功能，則使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST='master.magedu.com', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_AUTO_POSITION=1;
沒啟用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST='172.16.100.6',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=1174;
二、半同步復制
1、分別在主從節(jié)點上安裝相關的插件
master> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
slave> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
2、啟用半同步復制
在master上的配置文件中，添加
rpl_semi_sync_master_enabled=ON
在至少一個slave節(jié)點的配置文件中添加
rpl_semi_sync_slave_enabled=ON
而后重新啟動mysql服務即可生效。
或者，也可以mysql服務上動態(tài)啟動其相關功能：
master> SET GLOBAL rpl_semi_sync_master_enabled = ON;
slave> SET GLOBAL rpl_semi_sync_slave_enabled = ON;
slave> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
3、確認半同步功能已經(jīng)啟用
master> CREATE DATABASE magedudb;
master> SHOW STATUS LIKE 'Rpl_semi_sync_master_yes_tx';
slave> SHOW DATABASES;
三、MySQL Proxy
讀寫分離:
1、源碼安裝時，MySQL proxy的依賴關系：
libevent 1.x or higher (1.3b or later is preferred).
lua 5.1.x or higher.
glib2 2.6.0 or higher.
pkg-config.
libtool 1.5 or higher.
MySQL 5.0.x or higher developer files.
2、安裝
# tar zxf mysql-proxy-0.8.2.tar.gz
# cd mysql-proxy-0.8.2
# ./configure
# make
# make check
如果管理員有密碼，上面的步驟則需要使用如下格式進行：
# MYSQL_PASSWORD=root_pwd make check
# make install
默認情況下， mysql-proxy安裝在/usr/local/sbin/mysql-proxy，而Lua示例腳本安裝在/usr/local/share目錄中。
3、配置指令
mysql proxy的各配置參數(shù)請參見官方文檔，http://dev.mysql.com/doc/refman/5.6/en/mysql-proxy-configuration.html
使用rpm包在rhel6上安裝mysql-proxy-0.8.2，其會提供配置文件及服務腳本，但沒有提供讀寫分享的腳本。
/etc/sysconfig/mysql-proxy文件用于定義mysql-proxy的啟動參數(shù)。
ADMIN_USER – the user for the proxy's admin interface. You can leave the default admin user.
ADMIN_PASSWORD – the password for the admin user in clear text. Change the default password for better security.
ADMIN_LUA_SCRIPT – the admin script in the Lua programming language. Without this script the admin interface cannot work. You can leave the default value.
PROXY_USER – the system user under which the proxy will work. By default it is mysql-proxy, and it's safe to leave it as is.
PROXY_OPTIONS – proxy options such as logging level, plugins, and Lua scripts to be loaded.
其中PROXY_OPTIONS是最常用的一個選項，用于定義mysql-proxy工作時的重要參數(shù)，例如：
PROXY_OPTIONS="–daemon –log-level=info –log-use-syslog –plugins=proxy –plugins=admin –proxy-backend-addresses=192.168.1.102:3306 –proxy-read-only-backend-addresses=192.168.1.105:3306 –proxy-lua-script=/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua"
四、安裝配置mysql-proxy:
4.1 下載所需要的版本，這里的系統(tǒng)平臺為rhel6.4 32位系統(tǒng)，因此就以mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz為例。
# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local
# cd /usr/local
# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit mysql-proxy
添加代理用戶
# useradd mysql-proxy
grant all on *.* to 'root'@'172.16.%.%' identified by 'redhat'
msyql -uroot -h172,16.100.201 –port=4040
真正實現(xiàn)讀寫分離:增加腳本
4.2 為mysql-proxy提供SysV服務腳本，內(nèi)容如下所示
#!/bin/bash
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: – 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon for mysql
# Source function library.
. /etc/rc.d/init.d/functions
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
# Source networking configuration.
if [ -f /etc/sysconfig/network ]; then
. /etc/sysconfig/network
fi
# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="–daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi
RETVAL=0
start() {
echo -n $"Starting $prog: "
daemon $prog $PROXY_OPTIONS –pid-file=$PROXY_PID –proxy-address="$PROXY_ADDRESS" –user=$PROXY_USER –admin-username="$ADMIN_USER" –admin-lua-script="$ADMIN_LUA_SCRIPT" –admin-password="$ADMIN_PASSWORD"
RETVAL=$
echo
if [ $RETVAL -eq 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
}
stop() {
echo -n $"Stopping $prog: "
killproc -p $PROXY_PID -d 3 $prog
RETVAL=$
echo
if [ $RETVAL -eq 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
}
# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
stop
start
;;
condrestart|try-restart)
if status -p $PROXY_PIDFILE $prog >&/dev/null; then
stop
start
fi
;;
status)
status -p $PROXY_PID $prog
;;
*)
echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
RETVAL=1
;;
esac
exit $RETVAL
將上述內(nèi)容保存為/etc/rc.d/init.d/mysql-proxy，給予執(zhí)行權限，而后加入到服務列表。
# chmod +x /etc/rc.d/init.d/mysql-proxy
# chkconfig –add mysql-proxy
4.3 為服務腳本提供配置文件/etc/sysconfig/mysql-proxy，內(nèi)容如下所示：
# Options for mysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="–daemon –log-level=info –log-use-syslog"
其中最后一行，需要按實際場景進行修改，例如：
PROXY_OPTIONS="–daemon –log-level=info –log-use-syslog –plugins=proxy –plugins=admin –proxy-backend-addresses=172.16.100.6:3306 –proxy-read-only-backend-addresses=172.16.100.7:3306 –proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
其中的proxy-backend-addresses選項和proxy-read-only-backend-addresses選項均可重復使用多次，以實現(xiàn)指定多個讀寫服務器或只讀服務器。
4.4 mysql-proxy的配置選項
mysql-proxy的配置選項大致可分為幫助選項、管理選項、代理選項及應用程序選項幾類，下面一起去介紹它們。
–help
–help-admin
–help-proxy
–help-all ———— 以上四個選項均用于獲取幫助信息;
–proxy-address=host:port ———— 代理服務監(jiān)聽的地址和端口;
–admin-address=host:port ———— 管理模塊監(jiān)聽的地址和端口;
–proxy-backend-addresses=host:port ———— 后端mysql服務器的地址和端口;
–proxy-read-only-backend-addresses=host:port ———— 后端只讀mysql服務器的地址和端口;
–proxy-lua-script=file_name ———— 完成mysql代理功能的Lua腳本;
–daemon ———— 以守護進程模式啟動mysql-proxy;
–keepalive ———— 在mysql-proxy崩潰時嘗試重啟之;
–log-file=/path/to/log_file_name ———— 日志文件名稱;
–log-level=level ———— 日志級別;
–log-use-syslog ———— 基于syslog記錄日志;
–plugins=plugin,.. ———— 在mysql-proxy啟動時加載的插件;
–user=user_name ———— 運行mysql-proxy進程的用戶;
–defaults-file=/path/to/conf_file_name ———— 默認使用的配置文件路徑;其配置段使用[mysql-proxy]標識;
–proxy-skip-profiling ———— 禁用profile;
–pid-file=/path/to/pid_file_name ———— 進程文件名;
5、復制如下內(nèi)容建立admin.lua文件，將其保存至/usr/local/mysql-proxy/share/doc/mysql-proxy/目錄中。
–[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ –]]
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == "select * from backends" then
fields = {
{ name = "backend_ndx",
type = proxy.MYSQL_TYPE_LONG },
{ name = "address",
type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients",
type = proxy.MYSQL_TYPE_LONG },
}
for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, — configured backend address
states[b.state + 1], — the C-id is pushed down starting at 0
types[b.type + 1], — the C-id is pushed down starting at 0
b.uuid, — the MySQL Server's UUID if it is managed
b.connected_clients — currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = {
{ name = "command",
type = proxy.MYSQL_TYPE_STRING },
{ name = "description",
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
6、測試
6.1 管理功能測試
# mysql -uadmin -padmin -h172.16.100.107 –port=4041
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> SELECT * FROM backends;
+————-+——————-+——-+——+——+——————-+
| backend_ndx | address | state | type | uuid | connected_clients |
+————-+——————-+——-+——+——+——————-+
| 1 | 172.16.100.6:3306 | up | rw | NULL | 0 |
| 2 | 172.16.100.7:3306 | up | ro | NULL | 0 |
+————-+——————-+——-+——+——+——————-+
2 rows in set (0.00 sec)
6.2 讀寫分離測試
# mysql -uroot -pmagedu.com -h172.16.100.107 –port=4040
下面的讀寫分享腳本是由mysql-proxy-0.8.3提供了，將其復制保存為/usr/lib/mysql-proxy/lua/proxy/rw-splitting.lua，就可以啟動服務了。
–[[ $%BEGINLICENSE%$
Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA
$%ENDLICENSE%$ –]]
—
— a flexible statement based load balancer with connection pooling
—
— * build a connection pool of min_idle_connections for each backend and maintain
— its size
— *
—
—
local commands = require("proxy.commands")
local tokenizer = require("proxy.tokenizer")
local lb = require("proxy.balance")
local auto_config = require("proxy.auto-config")
— config
—
— connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,
is_debug = false
}
end
—
— read/write splitting sends all non-transactional SELECTs to the slaves
—
— is_in_transaction tracks the state of the transactions
local is_in_transaction = false
— if this was a SELECT SQL_CALC_FOUND_ROWS … stay on the same connections
local is_in_select_calc_found_rows = false
—
— get a connection to a backend
—
— as long as we don't have enough connections in the pool, create new connections
—
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
— make sure that we connect to each backend at least ones to
— keep the connections to the servers alive
—
— on read_query we can switch the backends again to another backend
if is_debug then
print()
print("[connect_server] " .. proxy.connection.client.src.name)
end
local rw_ndx = 0
— init all backends
for i = 1, #proxy.global.backends do
local s = proxy.global.backends[i]
local pool = s.pool — we don't have a username yet, try to find a connections which is idling
local cur_idle = pool.users[""].cur_idle_connections
pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections
if is_debug then
print(" [".. i .."].connected_clients = " .. s.connected_clients)
print(" [".. i .."].pool.cur_idle = " .. cur_idle)
print(" [".. i .."].pool.max_idle = " .. pool.max_idle_connections)
print(" [".. i .."].pool.min_idle = " .. pool.min_idle_connections)
print(" [".. i .."].type = " .. s.type)
print(" [".. i .."].state = " .. s.state)
end
— prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RO and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then
proxy.connection.backend_ndx = i
break
elseif s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
rw_ndx == 0 then
rw_ndx = i
end
end
if proxy.connection.backend_ndx == 0 then
if is_debug then
print(" [" .. rw_ndx .. "] taking master as default")
end
proxy.connection.backend_ndx = rw_ndx
end
— pick a random backend
—
— we someone have to skip DOWN backends
— ok, did we got a backend
if proxy.connection.server then
if is_debug then
print(" using pooled connection from: " .. proxy.connection.backend_ndx)
end
— stay with it
return proxy.PROXY_IGNORE_RESULT
end
if is_debug then
print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle")
end
— open a new connection
end
—
— put the successfully authed connection into the connection pool
—
— @param auth the context information for the auth
—
— auth.packet is the packet
function read_auth_result( auth )
if is_debug then
print("[read_auth_result] " .. proxy.connection.client.src.name)
end
if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then
— auth was fine, disconnect from the server
proxy.connection.backend_ndx = 0
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then
— we received either a
—
— * MYSQLD_PACKET_ERR and the auth failed or
— * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent
print("(read_auth_result) … not ok yet");
elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then
— auth failed
end
end
—
— read/write splitting
function read_query( packet )
local is_debug = proxy.global.config.rwsplit.is_debug
local cmd = commands.parse(packet)
local c = proxy.connection.client
local r = auto_config.handle(cmd)
if r then return r end
local tokens
local norm_query
— looks like we have to forward this statement to a backend
if is_debug then
print("[read_query] " .. proxy.connection.client.src.name)
print(" current backend = " .. proxy.connection.backend_ndx)
print(" client default db = " .. c.default_db)
print(" client username = " .. c.username)
if cmd.type == proxy.COM_QUERY then
print(" query = " .. cmd.query)
end
end
if cmd.type == proxy.COM_QUIT then
— don't send COM_QUIT to the backend. We manage the connection
— in all aspects.
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
}
if is_debug then
print(" (QUIT) current backend = " .. proxy.connection.backend_ndx)
end
return proxy.PROXY_SEND_RESULT
end
— COM_BINLOG_DUMP packet can't be balanced
—
— so we must send it always to the master
if cmd.type == proxy.COM_BINLOG_DUMP then
— if we don't have a backend selected, let's pick the master
—
if proxy.connection.backend_ndx == 0 then
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
return
end
proxy.queries:append(1, packet, { resultset_is_needed = true })
— read/write splitting
—
— send all non-transactional SELECTs to a slave
if not is_in_transaction and
cmd.type == proxy.COM_QUERY then
tokens = tokens or assert(tokenizer.tokenize(cmd.query))
local stmt = tokenizer.first_stmt_token(tokens)
if stmt.token_name == "TK_SQL_SELECT" then
is_in_select_calc_found_rows = false
local is_insert_id = false
for i = 1, #tokens do
local token = tokens[i]
— SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed
— on the same connection
— print("token: " .. token.token_name)
— print(" val: " .. token.text)
if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
is_in_select_calc_found_rows = true
elseif not is_insert_id and token.token_name == "TK_LITERAL" then
local utext = token.text:upper()
if utext == "LAST_INSERT_ID" or
utext == "@@INSERT_ID" then
is_insert_id = true
end
end
— we found the two special token, we can't find more
if is_insert_id and is_in_select_calc_found_rows then
break
end
end
— if we ask for the last-insert-id we have to ask it on the original
— connection
if not is_insert_id then
local backend_ndx = lb.idle_ro()
if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print(" found a SELECT LAST_INSERT_ID(), staying on the same backend")
end
end
end
— no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
— we don't have a backend right now
—
— let's pick a master as a good default
—
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end
— by now we should have a backend
—
— in case the master is down, we have to close the client connections
— otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end
local s = proxy.connection.server
— if client and server db don't match, adjust the server-side
—
— skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print(" server default db: " .. s.default_db)
print(" client default db: " .. c.default_db)
print(" syncronizing")
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db, { resultset_is_needed = true })
end
— send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print(" sending to backend : " .. b.dst.name);
print(" is_slave : " .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(" server default db: " .. s.default_db)
print(" server username : " .. s.username)
end
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" COM_QUERY : " .. tostring(cmd.type == proxy.COM_QUERY))
end
return proxy.PROXY_SEND_QUERY
end
—
— as long as we are in a transaction keep the connection
— otherwise release it so another client can use it
function read_query_result( inj )
local is_debug = proxy.global.config.rwsplit.is_debug
local res = assert(inj.resultset)
local flags = res.flags
if inj.id ~= 1 then
— ignore the result of the USE
— the DB might not exist on the backend, what do do
—
if inj.id == 2 then
— the injected INIT_DB failed as the slave doesn't have this DB
— or doesn't have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "can't change DB ".. proxy.connection.client.default_db ..
" to on slave " .. proxy.global.backends[proxy.connection.backend_ndx].dst.name
}
return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end
is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))
if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
— release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print("(read_query_result) staying on the same backend")
print(" in_trans : " .. tostring(is_in_transaction))
print(" in_calc_found : " .. tostring(is_in_select_calc_found_rows))
print(" have_insert_id : " .. tostring(have_last_insert_id))
end
end
—
— close the connections if we have enough connections in the pool
—
— @return nil – close connection
— IGNORE_RESULT – store connection in the pool
function disconnect_client()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print("[disconnect_client] " .. proxy.connection.client.src.name)
end
— make sure we are disconnection from the connection
— to move the connection into the pool
proxy.connection.backend_ndx = 0
end
