PhxSQL 测试

目前已构建一个集群,集群里有三台主机 4.115, 2.183, 2.182。其中,4.115 是 master,其它两台作为 slave。
为测试集群的可用性,进行测试。

基本功能测试

添加成员

继续尝试往集群中添加一台新机器 IPC。整个流程大致如下:

  1. python install.py …
  2. AddMember
  3. killall -9 phxbinlogsvr
  4. mysql -uroot -h127.0.0.1 -P11111 -e “set global super_read_only=off; set global read_only=off; reset master;”
  5. phxsql/percona.src/bin/mysqldump -uroot -hIP -P54322 –set-gtid-purged=on –all-databases > all.sql
  6. mysql -uroot -h127.0.0.1 -P11111 < all.mysql
  7. python restart.py -p phxbinlogsvr
  8. python restart.py -p mysql

安装完成之后,将主机添加到集群中:

1
2
3
xx@hah:/home/happy/workspace/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f AddMember -hIPA -p17000 -mIPC
get master IPA expire time 1473301623
AddMember IPC done

执行GetMemberList命令,查看集群中的机器:

1
2
3
4
5
xx@hah:/home/happy/workspace/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -hIPA -p17000
get master IPA expire time 1473301723
ip IPA port 17000
ip IPB port 17000
ip IPC port 17000

如果需要同步数据到新加入集群的机器中,那么还需要依次执行步骤 4-8 (可以参考PhxSQL 成员管理)。

简单测试

可以执行

1
./test_phxsql.sh 54321 IPA IPB

添加数据。

接下来,通过只读端口连接到 IPC 的数据库。看看里面是否有数据,结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
xx@hah:/home/happy/workspace/phxsql/tools# mysql -uroot -hIPC -P54322
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 932
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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 |
| test_phxsql |
+--------------------+
5 rows in set (0.00 sec)
mysql> show global variables like '%uuid%'
-> ;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | b0a08df6-7568-11e6-9076-c81f66c7561b |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
mysql> use test_phxsql;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_test_phxsql |
+-----------------------+
| test_phxsql |
+-----------------------+
1 row in set (0.00 sec)
mysql> select * from test_phxsql;
+----------------+
| name |
+----------------+
| 20160908102213 |
| 20160908102249 |
+----------------+
2 rows in set (0.00 sec)

可以观察到,test_phxsql 数据已经被同步到新加入集群的 IPC 之上了。
还可以在 MySQL 中执行:

1
show slave status\G;

查看同步状态,结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replica
Master_Port: 6000
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 3041
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 3251
Relay_Master_Log_File: mysql-bin.000003
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: 1953
Relay_Log_Space: 3449
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: 3232236659
Master_UUID: bfe6a2af-7568-11e6-9077-b65d9af99ebd
Master_Info_File: mysql.slave_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: bfe6a2af-7568-11e6-9077-b65d9af99ebd:1-14
Executed_Gtid_Set: bfe6a2af-7568-11e6-9077-b65d9af99ebd:1-14
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified

移除成员

执行RemoveMember命令移除集群中的成员。

移除 master

目前,集群中有三台机器,master 为 4.115。

1
2
3
4
5
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.4.115 -p17000
get master 192.168.4.115 expire time 1473757269
ip 192.168.4.115 port 17000
ip 192.168.2.182 port 17000
ip 192.168.2.183 port 17000

从集群中移除master,即移除主机4.115

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -h
Usage: ./phxbinlogsvr_tools_phxrpc [-c <config>] [-f <func>] [-v]
-f PHXEcho -s <string>
-f GetMasterInfoFromGlobal -h <host> -p <port>
-f GetLastSendGtidFromGlobal -h <host> -p <port> -u<gtid>
-f SetExportIP -h <host> -p <port>
-f AddMember -h <host> -p <port> -m <member ip>
-f RemoveMember -h <host> -p <port> -m <member ip>
-f SetMySqlAdminInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new admin username> -D <new admin pwd>
-f SetMySqlReplicaInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new replica username> -D <new replica pwd>
-f GetMemberList -h <host> -p <port>
-f InitBinlogSvrMaster -h <ip1,ip2,ip3(ip1 is master, others are slaves)> -p <port>
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f RemoveMember -h 192.168.4.115 -p17000 -m 192.168.4.115
get master 192.168.4.115 expire time 1473757348
RemoveMember 192.168.4.115 done
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.4.115 -p17000
get master 192.168.4.115 expire time 1473757467
ip 192.168.2.182 port 17000
ip 192.168.2.183 port 17000
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.4.115 -p17000
get master 192.168.2.182 expire time 1473757395
ip 192.168.2.182 port 17000
ip 192.168.2.183 port 17000

移除之后,发现 2.182 成为 master。
尝试从不同端口连接 4.115 的数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h192.168.4.115 -P54321
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0
root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h192.168.4.115 -P54322
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21412
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2000, 2016, 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> exit;
Bye
root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h127.0.0.1 -P11111
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21472
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2000, 2016, 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>

可以发现,从 54321 端口连接数据库失败。
重新将 4.115 加入集群

1
2
3
4
5
6
7
8
root@test-web2:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f AddMember -h192.168.2.182 -p17000 -m 192.168.4.115
get master 192.168.2.182 expire time 1473757959
AddMember 192.168.4.115 done
root@test-web2:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.2.182 -p17000
get master 192.168.2.182 expire time 1473757962
ip 192.168.2.182 port 17000
ip 192.168.2.183 port 17000
ip 192.168.4.115 port 17000

从三个端口均能连接到2.183上的数据库。集群数据数据同步正常。

移除 slave

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.4.115 -p17000
get master 192.168.4.115 expire time 1473755592
ip 192.168.4.115 port 17000
ip 192.168.2.183 port 17000
ip 192.168.2.182 port 17000
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc --h
Usage: ./phxbinlogsvr_tools_phxrpc [-c <config>] [-f <func>] [-v]
-f PHXEcho -s <string>
-f GetMasterInfoFromGlobal -h <host> -p <port>
-f GetLastSendGtidFromGlobal -h <host> -p <port> -u<gtid>
-f SetExportIP -h <host> -p <port>
-f AddMember -h <host> -p <port> -m <member ip>
-f RemoveMember -h <host> -p <port> -m <member ip>
-f SetMySqlAdminInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new admin username> -D <new admin pwd>
-f SetMySqlReplicaInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new replica username> -D <new replica pwd>
-f GetMemberList -h <host> -p <port>
-f InitBinlogSvrMaster -h <ip1,ip2,ip3(ip1 is master, others are slaves)> -p <port>
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f RemoveMember -h 192.168.4.115 -p17000 -m 192.168.2.183
get master 192.168.4.115 expire time 1473755730
RemoveMember 192.168.2.183 done
root@Vm-template-100G:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.4.115 -p17000
get master 192.168.4.115 expire time 1473755735
ip 192.168.4.115 port 17000
ip 192.168.2.182 port 17000

从集群中移除 2.183,能连接 4.115 和 2.182 的数据库。
而且,在 2.183 上从端口 54322 和 11111 能连接数据库。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54321
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0
root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54322
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18873
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2000, 2014, 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.
root@(none) 16:42>exit;
Bye
root@test-db:~/phxsql/sbin# mysql -uroot -h127.0.0.1 -P11111
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18895
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2000, 2014, 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.
root@(none) 16:42>

在 master 中更新数据,新的数据会被同步到 2.182 上,且能同步到 2.183 上。
重新执行 AddMember,将 2.183 加入集群,从三个端口均能连接到 2.183 上的数据库。集群数据数据同步正常。

修改密码

尝试修改密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
xx@hah:/home/happy/workspace/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f SetMySqlAdminInfo -hIPC -p17000 -uroot -d'' -Uhappy -Dhappy
get master IPA expire time 1473306682
terminate called after throwing an instance of 'std::logic_error'
what(): basic_string::_S_construct null not valid
Aborted
xx@hah:/home/happy/workspace/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f SetMySqlAdminInfo -hIPC -p17000 -uroot -d '' -Uhappy -Dhappy
get master IPA expire time 1473306698
SetMySqlAdminInfo fail ret -202
Usage: ./phxbinlogsvr_tools_phxrpc [-c <config>] [-f <func>] [-v]
-f PHXEcho -s <string>
-f GetMasterInfoFromGlobal -h <host> -p <port>
-f GetLastSendGtidFromGlobal -h <host> -p <port> -u<gtid>
-f SetExportIP -h <host> -p <port>
-f AddMember -h <host> -p <port> -m <member ip>
-f RemoveMember -h <host> -p <port> -m <member ip>
-f SetMySqlAdminInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new admin username> -D <new admin pwd>
-f SetMySqlReplicaInfo -h <host> -p <port> -u <admin username> -d <admin pwd> -U <new replica username> -D <new replica pwd>
-f GetMemberList -h <host> -p <port>
-f InitBinlogSvrMaster -h <ip1,ip2,ip3(ip1 is master, others are slaves)> -p <port>
xx@hah:/home/happy/workspace/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f SetMySqlAdminInfo -hIPC -p17000 -uroot -d '' -Uhappy -Dhappy
get master IPA expire time 1473306757
SetMySqlAdminInfo admin username root -> new admin username happy

改好密码之后,尝试用新的账号与密码连接数据库,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
xx@hah:/home/happy/workspace/phxsql/tools# clear
xx@hah:/home/happy/workspace/phxsql/tools# mysql -uroot -hIPC -P54321
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 34907
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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> exit;
Bye
xx@hah:/home/happy/workspace/phxsql/tools# mysql -uroot -hIPC -P54322 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'IPC' (using password: YES)
xx@hah:/home/happy/workspace/phxsql/tools# mysql -uhappy -hIPC -P54322 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4361
Server version: 5.6.31-77.0-log Source distribution
Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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>

异常测试

slave 异常

  1. slave phxbinlogsvr 异常
    为模拟 phxbinlogsvr 异常,执行命令终止该进程。

    • 终止进程后,立即重启该进程,更新数据库数据,观察数据同步是否正常
      终止 2.183 主机上的 phxbinlogsvr 进程,然后立即重启。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      root@Vm-template-100G:~/phxsql/sbin# ps -ef | grep phxbinlogsvr
      root 13813 1 7 Sep12 ? 01:03:55 /root/phxsql//sbin/phxbinlogsvr_phxrpc
      root 16858 16840 0 11:36 pts/6 00:00:00 grep --color=auto phxbinlogsvr
      root@Vm-template-100G:~/phxsql/sbin# kill 13813
      root@Vm-template-100G:~/phxsql/sbin# ps -ef | grep phxbinlogsvr
      root 16860 16840 0 11:38 pts/6 00:00:00 grep --color=auto phxbinlogsvr
      root@Vm-template-100G:~/phxsql/sbin# cd ../tools/
      root@Vm-template-100G:~/phxsql/tools# ls
      binary_installer.py binary_operator.py config_generator.py director_operator.py etc_template name_config.py nohup.out test_phxsql.sh
      binary_installer.pyc binary_operator.pyc config_generator.pyc director_operator.pyc install.py name_config.pyc restart.py
      root@Vm-template-100G:~/phxsql/tools# python restart.py -h
      usage: restart.py [-h] -p PROCESS_NAME [-b BASE_DIR] [-n NEW_PROCESS]
      optional arguments:
      -h, --help show this help message and exit
      -p PROCESS_NAME, --process_name PROCESS_NAME
      which binary you want to
      restart[mysql|phxbinlogsvr|phxsqlproxy]
      -b BASE_DIR, --base_dir BASE_DIR
      where phxsql locate, no need to specify it
      -n NEW_PROCESS, --new_process NEW_PROCESS
      where new process locate, we will copy it
      root@Vm-template-100G:~/phxsql/tools# python restart.py -p phxbinlogsvr
      Namespace(base_dir='/root/phxsql/', new_process='', process_name='phxbinlogsvr')
      nohup: appending output to `nohup.out'
      root@Vm-template-100G:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 16876 1 0 11:41 pts/6 00:00:00 /root/phxsql//sbin/phxbinlogsvr_phxrpc
      root 16880 16840 0 11:42 pts/6 00:00:00 grep --color=auto phxbinlogsvr

      重启该进程

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      root@test-db:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 2096 47269 0 15:02 pts/5 00:00:00 grep --color=auto phxbinlogsvr
      root@test-db:~/phxsql/tools# python restart.py -p phxbinlogsvr
      Namespace(base_dir='/root/phxsql/', new_process='', process_name='phxbinlogsvr')
      nohup: appending output to `nohup.out'
      root@test-db:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 2103 1 1 15:02 pts/5 00:00:00 /root/phxsql//sbin/phxbinlogsvr_phxrpc
      root 2107 47269 0 15:02 pts/5 00:00:00 grep --color=auto phxbinlogsvr
      root@test-db:~/phxsql/tools# lsof -i:6000
      root@test-db:~/phxsql/tools# lsof -i:8000
      root@test-db:~/phxsql/tools# lsof -i:17000
      root@test-db:~/phxsql/tools# lsof -i:11111
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      mysqld 2003 root 17u IPv6 87937782 0t0 TCP *:11111 (LISTEN)
      root@test-db:~/phxsql/tools# lsof -i:54321
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      phxsqlpro 489 root 3u IPv4 87801814 0t0 TCP 192.168.2.183:54321 (LISTEN)
      phxsqlpro 490 root 3u IPv4 87801814 0t0 TCP 192.168.2.183:54321 (LISTEN)
      root@test-db:~/phxsql/tools# lsof -i:8000
      root@test-db:~/phxsql/tools# lsof -i:8000
      root@test-db:~/phxsql/tools# lsof -i:6000
      root@test-db:~/phxsql/tools# clear
      root@test-db:~/phxsql/tools# ls
      binary_installer.py binary_operator.py config_generator.py director_operator.py etc_template name_config.py nohup.out test_phxsql.sh
      binary_installer.pyc binary_operator.pyc config_generator.pyc director_operator.pyc install.py name_config.pyc restart.py
      root@test-db:~/phxsql/tools# lsof -i:6000
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      mysqld 2003 root 43u IPv4 88026597 0t0 TCP test-db:63762->test-db:x11 (ESTABLISHED)
      phxbinlog 2103 root 23u IPv4 87929703 0t0 TCP test-db:x11 (LISTEN)
      phxbinlog 2103 root 36u IPv4 88033651 0t0 TCP test-db:x11->test-db:63762 (ESTABLISHED)

      同时,连接 2.183 的 PhxSQL 成功,且数据同步能正常工作。

    • 终止进程后,更新数据库数据,然后重启该进程,观察数据同步是否正常
      终止 2.183 上 phxbinlogsvr_phxrpc 进程,在 master 中更新数据,然后重启 2.183 上的 phxbinlogsvr_phxrpc

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      root@test-db:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 2103 1 2 15:02 pts/5 00:00:07 /root/phxsql//sbin/phxbinlogsvr_phxrpc
      root 2257 47269 0 15:08 pts/5 00:00:00 grep --color=auto phxbinlogsvr
      root@test-db:~/phxsql/tools# kill 2103
      root@test-db:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 2259 47269 0 15:08 pts/5 00:00:00 grep --color=auto phxbinlogsvr
      root@test-db:~/phxsql/tools# python restart.py -p phxbinlogsvr
      Namespace(base_dir='/root/phxsql/', new_process='', process_name='phxbinlogsvr')
      nohup: appending output to `nohup.out'
      root@test-db:~/phxsql/tools# ps -ef | grep phxbinlogsvr
      root 2270 1 0 15:09 pts/5 00:00:00 /root/phxsql//sbin/phxbinlogsvr_phxrpc
      root 2274 47269 0 15:10 pts/5 00:00:00 grep --color=auto phxbinlogsvr
      root@test-db:~/phxsql/tools# lsof -i:6000
      root@test-db:~/phxsql/tools# lsof -i:8001
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      phxbinlog 2270 root 18u IPv4 88046126 0t0 UDP *:8001
      phxbinlog 2270 root 19u IPv4 88046127 0t0 TCP 192.168.2.183:8001 (LISTEN)
      root@test-db:~/phxsql/tools# lsof -i:8001
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      phxbinlog 2270 root 18u IPv4 88046126 0t0 UDP *:8001
      phxbinlog 2270 root 19u IPv4 88046127 0t0 TCP 192.168.2.183:8001 (LISTEN)
      root@test-db:~/phxsql/tools# lsof -i:6000
      root@test-db:~/phxsql/tools# lsof -i:8001
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      phxbinlog 2270 root 18u IPv4 88046126 0t0 UDP *:8001
      phxbinlog 2270 root 19u IPv4 88046127 0t0 TCP 192.168.2.183:8001 (LISTEN)
      root@test-db:~/phxsql/tools# lsof -i:6000
      COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
      mysqld 2003 root 51u IPv4 87871086 0t0 TCP test-db:17045->test-db:x11 (ESTABLISHED)
      phxbinlog 2270 root 23u IPv4 88026833 0t0 TCP test-db:x11 (LISTEN)
      phxbinlog 2270 root 36u IPv4 88056916 0t0 TCP test-db:x11->test-db:17045 (ESTABLISHED)

      2.183 上的 phxbinlogsvr_phxrpc 重启后,它的 PhxSQL 工作正常(重启后可能需要等待一段时间)。而且,phxbinlogsvr_phxrpc 停止工作期间的数据在重启后也被成功同步。
      为了观察集群中所有机器都在正常运行,再次在 master 中更新数据,发现数据成功同步到其它 slave 主机之上。

  2. slave phxsqlpro 异常

    • 终止进程后,立即重启该进程,更新数据库数据,观察数据同步是否正常
      终止 phxsqlpro 进程

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54321
      ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.183' (111)
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54322
      ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.183' (111)
      root@test-db:~/phxsql/sbin# mysql -uroot -h127.0.0.1 -P11111
      Welcome to the MySQL monitor. Commands end with ; or \g.
      Your MySQL connection id is 5078
      Server version: 5.6.31-77.0-log Source distribution
      Copyright (c) 2000, 2014, 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.

      从端口 54321 和 54322 无法连接数据库。
      重启该进程,更新数据库,集群数据同步正常工作。

    • 终止进程后,更新数据库数据,然后重启该进程,观察数据同步是否正常

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      root@test-db:~/phxsql/tools# ps -ef | grep phxsqlpro
      root 2538 1 24 15:23 pts/5 00:00:39 /root/phxsql//sbin/phxsqlproxy_phxrpc /root/phxsql//etc/phxsqlproxy.conf daemon
      root 2539 1 23 15:23 pts/5 00:00:39 /root/phxsql//sbin/phxsqlproxy_phxrpc /root/phxsql//etc/phxsqlproxy.conf daemon
      root 2785 47269 0 15:26 pts/5 00:00:00 grep --color=auto phxsqlpro
      root@test-db:~/phxsql/tools# kill -9 2538 2539
      root@test-db:~/phxsql/tools# ps -ef | grep phxsqlpro
      root 2787 47269 0 15:27 pts/5 00:00:00 grep --color=auto phxsqlpro
      root@test-db:~/phxsql/tools# python restart.py -p phxsqlpro
      Namespace(base_dir='/root/phxsql/', new_process='', process_name='phxsqlpro')
      the process_name should be mysql or phxsqlproxy or phxbinlogsvr
      root@test-db:~/phxsql/tools# python restart.py -p phxsqlproxy

      终止进程后,更新数据库数据,然后重启该进程,集群数据同步正常工作。

  3. slave mysql 异常

    • 终止进程后,立即重启该进程,更新数据库数据,观察数据同步是否正常

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54321
      Welcome to the MySQL monitor. Commands end with ; or \g.
      Your MySQL connection id is 14550
      Server version: 5.6.31-77.0-log Source distribution
      Copyright (c) 2000, 2014, 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.
      root@(none) 15:33>exit
      Bye
      root@test-db:~/phxsql/sbin# mysql -uroot -h127.0.0.1 -P11111
      ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54322
      ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

      终止进程后,只能从端口 54321 连接数据库。
      重启 mysql,其它两个端口也能连接数据库,且集群数据同步正常工作。

    • 终止进程后,更新数据库数据,然后重启该进程,观察数据同步是否正常
      终止 mysql 进程后,更新 master 数据库的数据。然后重启 mysql 进程,数据同步正常工作。

master 异常

  1. master phxbinlogsvr 异常

    • 终止进程,立即重启该进程

      1
      2
      3
      4
      5
      6
      7
      8
      root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h192.168.4.115 -P54321
      ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
      root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h192.168.4.115 -P54322
      ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
      root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h127.0.0.1 -P11111
      Welcome to the MySQL monitor. Commands end with ; or \g.
      Your MySQL connection id is 21660
      Server version: 5.6.31-77.0-log Source distribution

      经过查询,发现 master 变为 2.183

      1
      2
      3
      4
      5
      root@test-db:~/phxsql/sbin# ./phxbinlogsvr_tools_phxrpc -f GetMemberList -h192.168.2.183 -p17000
      get master 192.168.2.183 expire time 1473753619
      ip 192.168.4.115 port 17000
      ip 192.168.2.183 port 17000
      ip 192.168.2.182 port 17000

      经过一段时间,4.115 主机上的 PhxSQL 开始正常工作。

  2. master phxsqlpro 异常

    • 终止进程,立即重启该进程
      终止 phxsqlpro 进程

      1
      2
      3
      4
      5
      6
      7
      root@test-db:~/phxsql/tools# ps -ef | grep phxsqlpro
      root 2799 1 22 15:27 pts/5 00:10:49 /root/phxsql//sbin/phxsqlproxy_phxrpc /root/phxsql//etc/phxsqlproxy.conf daemon
      root 2800 1 22 15:27 pts/5 00:10:50 /root/phxsql//sbin/phxsqlproxy_phxrpc /root/phxsql//etc/phxsqlproxy.conf daemon
      root 5843 47269 0 16:15 pts/5 00:00:00 grep --color=auto phxsqlpro
      root@test-db:~/phxsql/tools# kill -9 2799 2800
      root@test-db:~/phxsql/tools# ps -ef | grep phxsqlpro
      root 5848 47269 0 16:16 pts/5 00:00:00 grep --color=auto phxsqlpro

      尝试连接数据库

      1
      2
      3
      4
      5
      6
      7
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54322
      ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.183' (111)
      root@test-db:~/phxsql/sbin# mysql -uroot -h192.168.2.183 -P54321
      ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.2.183' (111)
      root@test-db:~/phxsql/sbin# mysql -uroot -h127.0.0.1 -P11111
      Welcome to the MySQL monitor. Commands end with ; or \g.
      Your MySQL connection id is 12607

      发现在2.183上,端口54321和54322不能连接数据库,而11111则可以。

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      root@Vm-template-100G:~/phxsql/tools# mysql -uroot -h192.168.4.115 -P54321
      Welcome to the MySQL monitor. Commands end with ; or \g.
      Your MySQL connection id is 3930
      Server version: 5.6.31-77.0-log Source distribution
      Copyright (c) 2000, 2016, 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>

      发现在4.115上,上述三个端口均可以连接数据库。而且,现在 master 有变回 4.115了。
      重启 phxsqlpro 进程,数据库正常工作。

  3. master mysql 异常

总结

整个测试过程涵盖了异常测试和基本操作,异常测试如:slave和master的异常;基本操作如:添加和移除主机。测试结果能体现 PhxSQL 一主多备的可用性。
master 异常与移 master 相似,二者均会导致集群变更 master。