1、异步复制构建
2、半同步复制构建
1、安装相同mysql数据版本5.5.42
192.168.56.101 master
192.168.56.102 slave
192.168.56.103 vhost
主库配置:
2、在主库上创建一个用作复制使用的账户repl,授予REPLICATION SLAVE权限;
同时授权限制从slave节点连接访问。在master主机上执行:
mysql>grant REPLICATION SLAVE on *.* to 'repl'@'192.168.56.102' identified by 'repl';
mysql>grant REPLICATION SLAVE on *.* to 'repl'@'192.168.56.103' identified by 'repl';
3、修改master主机my.cnf配置文件,开启 BINLOG,并设置server-id的值。这两个参数的修改需要重新启动数据库服务才生效。
vi my.cnf
[mysqld]
log-bin=/fsystem1/mylogbin/mysql-bin.log
server-id = 1
4、在master上设置读锁定有效,这是为了确保没有数据库操作,以便获得一个一致性快照。
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
5、从master获取当前二进制日志名称和偏移量值。这是为了在从数据库启动后,从这个点开始进行数据的恢复;
mysql> show master status ;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
一致性备份:
cd /fsystem1/
tar -cvf data.tar data
scp data.tar slave:/fsystem1/
scp data.tar vhost:/fsystem1/
6、开锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
从库配置:
两个从库未开启。将data.tar恢复到指定的路径下:tar -xvf data.tar
7、配置从库配置文件,确保没个节点的server-id不重复。
#on slave
vi my.cnf
[mysqld]
server-id = 2
#on vhost
vi my.cnf
[mysqld]
server-id = 3
8、在从库上使用-skip-salve-start参数启动,这样不会立即启动从数据库上的复制进程,方便对从库进一步配置。
或者直接启动 service mysql start
9、对slave库做相应设置,指定复制用户,主数据库服务器胡ip,端口,以及开始执行复制胡日志文件和位置等
on slave/vhost执行:
mysql>
change master to
master_host='192.168.56.101',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos=107;
10、在从库启动slave IO线程。
mysql>start slave ;
mysql>show slave status \G;查看slave相关信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: vhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
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: 107
Relay_Log_Space: 107
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: 2003
Last_IO_Error: error connecting to master 'repl@192.168.56.101:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show processlist \G;
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 114
State: Connecting to master
Info: NULL
*************************** 3. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 114
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
ERROR:
No query specified
原因是由于master防火墙没有关闭。
配置半同步复制(在异步复制基础上配置)
mysql> select @@have_dynamic_loading ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
[root@master plugin]# pwd
/mysql/mysql-5.5.42/lib/plugin
[root@master plugin]# ls -l | grep semisync_
-rwxr-xr-x. 1 root mysql 170522 Jan 7 2015 semisync_master.so
-rwxr-xr-x. 1 root mysql 88911 Jan 7 2015 semisync_slave.so
1、在主库上安装semisync_master.so,从库上安装semisync_slave.so
语句成分是固定的。
MySQL>install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
2、安装完成后,需要打开半同步复制开关,默认是关闭的。
mysql>set global rpl_semi_sync_master_enabled=1;
mysql>set global rpl_semi_sync_master_timeout=30000;
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
由于是在异步复制的基础上配置的,需要重新启动从库的I/O线程。
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>set global rpl_semi_sync_slave_enabled=1;
mysql>stop slave IO_THREAD ;start slave IO_THREAD;
配置完成。
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |----半同步复制打开。
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |------------从库数量
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |----半同步复制打开。
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)