简体中文README

PhxSQL is a high-availability and strong-consistency MySQL cluster built on Paxos and Percona.

Authors: Junchao Chen, Haochuan Cui, Duokai Huang, Ming Chen and Sifan Liu

Contact us: phxteam@tencent.com

#PhxSQL features:

• high availability by automatic failovers: the cluster works well when more than half of cluster nodes work and are interconnected.
• guarantee of data consistency among cluster nodes: replacing loss-less semi-sync between MySQL master and MySQL slaves with Paxos, PhxSQL ensures zero-loss binlogs between master and slaves and supports linearizable consistency, which is as strong as that of Zookeeper.
• complete compliance with MySQL and MySQL client: PhxSQL supports up to serializable isolation level of transaction.
• easy deployment and easy maintenance: PhxSQL, powered by in-house implementation of Paxos, has only 4 components including MySQL and doesn't depend on zookeeper or etcd for anything. PhxSql supports automated cluster membership hot reconfiguration.

This project includes

• Source codes
• Third party submodules
• Pre-compiled binaries for Ubuntu 64bit system.

Projects on which this project depends are also published by Tencent( phxpaxos, phxrpc, libco ). You can download or clone them with --recurse-submodule.

phxpaxos： http://github.com/Tencent/phxpaxos

phxrpc： http://github.com/Tencent/phxrpc

libco： http://github.com/Tencent/libco

If you prefer pre-compiled binaries, just skip this part.

• PhxSQL
  • phxsqlproxy
  • phxbinlogsvr
  • percona
  • phx_percona
    • plugin
    • phxsync_phxrpc
    • semisync
  • third_party
    • glog
    • leveldb
    • protobuf
    • phxpaxos
    • colib
    • phxrpc
  • tools
  • phxrpc_package_config

PhxSQL needs 6 third party libs(glog, leveldb, protobuf, phxpaxos, colib, phxrpc). Please install them in phxsql/third_party directory or just link to third_party.

NOTE: Please make sure -fPIC is added while executing configure in GLOG and Protobuf as well as specifying --prefix=/the/current/absolute/path.

For example: ./configure CXXFLAGS=-fPIC --prefix=/home/root/phxsql/third_party/glog.

Then download percona-server-5.6.31-77.0.tar.gz

Move percona-server-5.6\_5.6.31-77.0 to PhxSQL directory, rename or link as 'percona' (NOTE: Only percona-server-5.6_5.6.31-77.0 is available)

1. Execute ./autoinstall.sh && make && make install
2. Execute 'make package' to generate a tar.gz package so you can transfer to your target hosts.

(NOTE: We put the binaries in install_package/sbin, configuration files in install_package/tools/etc_template, install scripts in install_package/tools. The 'make package' command will pack 'install_package' into 'phxsql-$version.tar.gz'. Please specify -prefix=/the/path/you/want/to/install while executing ./autoinstall.sh)

PhxSQL needs to run on more than 2 hosts. We suggest N >= 3 and N is an odd number, where N means the number of hosts.

1. Transfer phxsql.tar.gz to all of the hosts you want to install. Then do as the following steps:

   1. Execute tar -xvf phxsq.tar.gz .

   2. Enter phxsql/tools, Execute python install.py --help to get the help of installation.

      (For example:python2.7 install.py -i"your_inner_ip" -p 54321 -g 6000 -y 11111 -P 17000 -a 8001 -f/tmp/data/)

2. After executing 'install.py' on all the hosts, Execute './phxbinlogsvr_tools_phxrpc -f InitBinlogSvrMaster -h"ip1,ip2,ip3" -p 17000' in any one hosts. 17000 should be replaced with the port on which phxbinlogsvr is listening.

3. The cluster is active while a message shows master initialization is finished.

4. You can execute some SQLs to check the status of cluster through mysql -uroot -h"your_inner_ip" -P$phxsqlproxy_port

1. Enter phxsql/tools/
2. Execute test_phxsql.sh phxsqlproxy_port ip1 ip2 ip3

PhxSQL have 3 configuration files in total.

NOTE:Modify tools/etc_temlate/my.cnf before installation, Modify etc/my.cnf after installation

phxsqlproxy is the surrogate of PhxSQL, all requests will be sent to phxsqlproxy and then be forwarded to MySQL.

It is the port configured in phxsqlproxy.conf. Every requests sent to this port will be forwarded to the master node to excute.

It is (MasterPort + 1). You can also specify it by setting SlavePort = xxxxx in phxsqlproxy.conf.
Every requests will be executed on the local MySQL. A master node will make a redirection to another slave nodes if MasterEnableReadPort = 0 (this will save the CPU/IO resource for write requests)

1. Using mysql -u$user -h$phxsqlproxyip -P$phxsqlproxyport -p$pwd to connect to phxsqlproxy
2. Execute SQL command.

$phxsqlproxyip can be any one IP of hosts in a clusters and $phxsqlproxyport can be MasterPort or SlavePort.

PhxSQL provides a tool phxbinlogsvr_tools_phxrpc to help the mangerment of PhxSQL.

PhxSQL cluster needs 1 MySQL admin accounts and 1 synchronization account. The default admin account is (root, "" ), the default synchronization account is ( replica, replica123 ), They can be modified( and only be modifyed ) via phxbinlogsvr_tools_phxrpc. DON'T DO THIS MANUALLY.

Following is some commands you may used frequently.

**Function:**Get the current master info from quorum nodes( IP and timeout ).

Arguments:

• Host: Any one IP of clusters nodes
• Port: Port which phxbinlogsvr is listening. like 17000

Function: Set the user and password of admin account.

Arguments:

• Host: Any one IP of clusters nodes
• Port: Port which phxbinlogsvr is listening. like 17000
• Admin username: Current account user( default is root )
• Admin pwd: Current account password( default is "" )
• New admin username: New user
• New admin pwd: New password

Function: Set the user and password of synchronization account.

Arguments：

• Host: Any one IP of clusters nodes
• Port: Port which phxbinlogsvr is listening. like 17000
• Admin username: Current account user( default is root )
• Admin pwd: Current account password( default is "" )
• New replica username: New user
• New replica pwd: New password

Function: Membership of this cluster, all IPs and Ports included.

Arguments:

• Host: Any one IP of clusters nodes
• Port: Port which phxbinlogsvr is listening. like 17000

Execute phxbinlogsvr_tools_phxrpc -f RemoveMember -h<host> -p<port> -m <ip_of_nodeA> to delete node A. Once it is succesfully executed, A will not learn binlog after a small period.

1. Execute phxbinlogsvr_tools -f AddMember -h<host> -p<port> -m <ip_of_nodeA> to add node A into the membership.
2. Install PhxSQL on A.
3. A will begin to learn data after installation is finished.
4. Copy a snapshot of MySQL from any other nodes to A.
5. Kill phxbinlogsvr and access MySQL through the local port( or socket ). then execute set global super_read_only = 0; set global read_only = 0;
6. Dump the snapshot into MySQL.
7. A will begin to work after a while.

Phxbinlogsvr will pull the checkpoint in another node to reboot during reinstallation. It will self-kill after pulling is over(to make sure the consistency). You can reboot phxbinlogsvr after a message like "All sm load state ok, start to exit" appears.

CPU : Intel(R) Xeon(R) CPU E5-2420 0 @ 1.90GHz * 24

Memory : 32G

Disk : SSD Raid10

Master -> Slave : 3 ~ 4ms

Client -> Master : 4ms

sysbench --oltp-tables-count=10 --oltp-table-size=1000000 --num-threads=500 --max-requests=100000 --report-interval=1 --max-time=200

NOTE:The 2 Response times means average and 95% percentile