mysql的主从复制与半同步复制

展开全文

mysql的主从复制与半同步复制（一）

作者：小郑师傅发布日期：2013-10-10 09:02:24

我来说两句(0)收藏本文

一、mysql的主从复制
复制过程需要至少两个mysql服务节点，mysql的主从复制框架一般都为一主多从，从多机房汇总到数据中心的模式，而复制过程只能从主服务器到从服务器实现。
复制过程中，主服务器负责读/写操作，而从服务器只负责读操作
二、主从复制的功能
1、实现数据冗余、异地灾备恢复、备份
2、实现数据的读/写分离
3、实现数据库服务的负载均衡
4、实现高可用与故障切换
5、实现MySQL的升级测试
三、主从复制过程中注意的事项
1、不要混合shi用不同的存储引擎
2、主从服务器的server-id 要保持不同
3、尽力避免修改从服务器的数据库
4、尽可能的使用基于行或基于混合模式的复制，避免使用基于语句的复制
5、主服务器要启动二进制日志，而从节点则是开启中继日志，而且要确保从服务器的复制线程时刻开启着
6、注意因为磁盘及内存的大小不足而导致的复制崩溃
四、主从复制的具体实现过程
mysql主从复制拓扑图

1、准备两个安装过mysql服务的节点xz：172.16.200.5,node2：172.16.200.7,修改主机名并同步时间；

view source print ?
1.[root@xz ~]# hostname master
2.[root@master ~]# crontab -e
3.*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
4.[root@node2 ~]# hostname slave
5.[root@slave ~]# crontab -e
6.*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
2、在主节点上创建有复制权限的用户，于从节点上使用授权用户连接测试；

view source print ?
01.[root@master ~]# mysql
02.Welcome to the <a href="http://www./database/dbmy/" target="_blank"class="keylink">MySQL</a> monitor. Commands end with ; or \g.
03.Your <a href="http://www./database/dbmy/" target="_blank"class="keylink">MySQL</a> connection id is 3
04.Server version: 5.5.33-log MySQL Community Server (GPL)
05.。。。。。
06.mysql> grant replication slave,replication client on *.* to 'zly'@'172.16.200.7' identified by 'mypass';
07.Query OK, 0 rows affected (0.35 sec)
08.mysql> flush privileges;
09.Query OK, 0 rows affected (0.09 sec)
10.mysql> show grants for 'zly'@'172.16.200.7';
11.+-----------------------------------------------------------------------------------------------------------------------------------------------+
12.| Grants forzly@172.16.200.7                                                                                                                   |
13.+-----------------------------------------------------------------------------------------------------------------------------------------------+
14.| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zly'@'172.16.200.7' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4' |
15.+-----------------------------------------------------------------------------------------------------------------------------------------------+
16.1 row in set (0.03 sec)
17............
18.[root@slave ~]# mysql -uzly -pmypass -h 172.16.200.5
19.Welcome to the MySQL monitor. Commands end with ; or \g.
20.Your MySQL connection id is 4
21.Server version: 5.5.33-log MySQL Community Server (GPL)
22.Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
23.Oracle is a registered trademark of Oracle Corporation and/or its
24.affiliates. Other names may be trademarks of their respective
25.owners.
26.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
27.mysql>
3、修改主从两个节点的配置文件，并重新启动mysql服务;

view source print ?
01.[root@master ~]# vim /etc/my.cnf
02.log-bin=mysql-bin
03.log_bin_index = mysql_bin.index
04.binlog_format=mixed
05.server-id       = 5
06.[root@master ~]# killall mysqld
07.[root@master ~]# ps aux | grep mysqld
08.root      7700 0.0 0.1 103244   832 pts/3    S+   09:27   0:00 grep mysqld
09.[root@master ~]# service mysqld restart
10.MySQL server PID file could not be found!                  [FAILED]
11.Starting MySQL...                                          [ OK ]
12.[root@master ~]# service mysqld restart
13.Shutting down MySQL.                                       [ OK ]
14.Starting MySQL..                                           [ OK ]
15................
16.[root@slave ~]# vim /etc/my.cnf
17.#log-bin=mysql-bin
18.# binary logging format - mixed recommended
19.#binlog_format=mixed
20.skip_slave_start = 1
21.read_only = 1
22.relay_log = relay_log
23.relay_log_index = relay_log.index
24.# required unique id between 1 and 2^32 - 1
25.# defaults to 1 if master-host is not set
26.# but will not function as a master if omitted
27.server-id       = 7
28.[root@slave ~]# killall mysqld
29.[root@slave ~]# ps aux | grep mysqld
30.root      8796 0.0 0.1 103244   832 pts/4    S+   23:45   0:00 grep mysqld
31.[root@slave ~]# service mysqld restart
32.MySQL server PID file could not be found!                  [FAILED]
33.Starting MySQL..                                           [ OK ]
34.[root@slave ~]# service mysqld restart
35.Shutting down MySQL.                                       [ OK ]
36.Starting MySQL..                                           [ OK ]
4、查看主节点的二进制日志及其事件位置;

view source print ?
1.mysql> show master status;
2.+------------------+----------+--------------+------------------+
3.| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
4.+------------------+----------+--------------+------------------+
5.| mysql-bin.000011 |      107 |              |                  |
6.+------------------+----------+--------------+------------------+
7.1 row in set (0.00 sec)
5、与从节点上复制主节点上的数据;

view source print ?
01.[root@slave ~]# mysql
02.mysql> help change master to
03.Name: 'CHANGE MASTER TO'
04.Description:
05.Syntax:
06.CHANGE MASTER TO option [, option] ...
07.option:
08.MASTER_BIND = 'interface_name'
09.| MASTER_HOST = 'host_name'
10.| MASTER_USER = 'user_name'
11.| MASTER_PASSWORD = 'pass<a href="http://www./edu/ebg/" target="_blank"class="keylink">word</a>'
12.| MASTER_PORT = port_num
13.| MASTER_CONNECT_RETRY = interval
14.| MASTER_HEARTBEAT_PERIOD = interval
15.| MASTER_LOG_FILE = 'master_log_name'
16.| MASTER_LOG_POS = master_log_pos
17.| RELAY_LOG_FILE = 'relay_log_name'
18.| RELAY_LOG_POS = relay_log_pos
19.| MASTER_SSL = {0|1}
20.| MASTER_SSL_CA = 'ca_file_name'
21.| MASTER_SSL_CAPATH = 'ca_directory_name'
22.| MASTER_SSL_CERT = 'cert_file_name'
23.| MASTER_SSL_KEY = 'key_file_name'
24.| MASTER_SSL_CIPHER = 'cipher_list'
25.| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
26.| IGNORE_SERVER_IDS = (server_id_list)
27.server_id_list:
28.[server_id [, server_id] ... ]
29.........................................
30.mysql> change master to
31.-> master_host='172.16.200.5',master_user='zly',master_pass<a href="http://www./edu/ebg/" target="_blank"class="keylink">word</a>='mypass
32.',master_port=3306,master_log_file='mysql-bin.000011',master_log_pos=107;
33.Query OK, 0 rows affected (0.07 sec)
6、启动从服务器复制线程、查看状态，并查看启动的线程;

view source print ?
01.mysql> start slave;
02.Query OK, 0 rows affected (0.01 sec)
03.mysql> show slave status\G
04.*************************** 1. row ***************************
05.Slave_IO_State: Waiting for master to send event
06.Master_Host: 172.16.200.5
07.Master_User: zly
08.Master_Port: 3306
09.Connect_Retry: 60
10.Master_Log_File: mysql-bin.000011
11.Read_Master_Log_Pos: 107
12.Relay_Log_File: relay_log.000002
13.Relay_Log_Pos: 253
14.Relay_Master_Log_File: mysql-bin.000011
15.Slave_IO_Running: Yes
16.Slave_SQL_Running: Yes
17.Replicate_Do_DB:
18.Replicate_Ignore_DB:
19.Replicate_Do_Table:
20.Replicate_Ignore_Table:
21.Replicate_Wild_Do_Table:
22.Replicate_Wild_Ignore_Table:
23.Last_Errno: 0
24.Last_Error:
25.Skip_Counter: 0
26.Exec_Master_Log_Pos: 107
27.Relay_Log_Space: 403
28.Until_Condition: None
29.Until_Log_File:
30.Until_Log_Pos: 0
31.Master_SSL_Allowed: No
32..................................
33.mysql> show processlist\G
34.*************************** 1. row ***************************
35.Id: 1
36.User: root
37.Host: localhost
38.db: NULL
39.Command: Query
40.Time: 0
41.State: NULL
42.Info: show processlist
43.*************************** 2. row ***************************
44.Id: 2
45.User: system user
46.Host:
47.db: NULL
48.Command: Connect
49.Time: 276
50.State: Waiting for master to send event
51.Info: NULL
52.*************************** 3. row ***************************
53.Id: 3
54.User: system user
55.Host:
56.db: NULL
57.Command: Connect
58.Time: 275
59.State: Slave has read all relay log; waiting for the slave I/O thread to update it
60.Info: NULL
61.3 rows in set (0.00 sec)
7、在主节点上创建数据库查看从节点是否已经复制;

view source print ?
01.[root@master ~]# mysql -e "create database classdb;"
02.[root@master ~]# mysql -e "show databases;"
03.+--------------------+
04.| Database           |
05.+--------------------+
06.| information_schema |
07.| classdb            |
08.| mysql              |
09.| performance_schema |
10.| testdb             |
11.+--------------------+
12.[root@slave ~]# mysql -e "show databases;"
13.+--------------------+
14.| Database           |
15.+--------------------+
16.| information_schema |
17.| classdb            |
18.| mysql              |
19.| performance_schema |
20.+--------------------+
8、与主从节点查看已经更新的状态及二进制日志位置

view source print ?
01.[root@master ~]# mysql -e "show master status;"
02.+------------------+----------+--------------+------------------+
03.| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
04.+------------------+----------+--------------+------------------+
05.| mysql-bin.000011 |      453 |              |                  |
06.+------------------+----------+--------------+------------------+
07.[root@slave ~]# mysql -e "show slave status\G"
08.*************************** 1. row ***************************
09.Slave_IO_State: Waiting for master to send event
10.Master_Host: 172.16.200.5
11.Master_User: zly
12.Master_Port: 3306
13.Connect_Retry: 60
14.Master_Log_File: mysql-bin.000011
15.Read_Master_Log_Pos: 453
16.Relay_Log_File: relay_log.000002
17.Relay_Log_Pos: 599
18.Relay_Master_Log_File: mysql-bin.000011
19.Slave_IO_Running: Yes
20.Slave_SQL_Running: Yes
至此mysql的主从复制已经完成
五、mysql的半同步复制的具体实现
半同步复制：主服务器只要收到从服务器中的一台的返回信息，就会提交，否则需等待直至达到超时时间然后切换成异步再提交。可以使主从服务器的数据库数据的延迟较小，可以在损失很小的性能的前提下提高数据的安全性。
要进行半同步复制，只需要在主从服务器上安装上半同步复制的插件，并开启半同步复制功能，便可以进行主从复制了。
1、查看主从节点上的半同步插件，并安装插件；

view source print ?
01.[root@master ~]# cd /usr/local/mysql/lib/plugin/
02.[root@master plugin]# ls
03.adt_null.so          debug                 qa_auth_server.so
04.auth.so              libdaemon_example.so semisync_master.so
05.auth_socket.so       mypluglib.so          semisync_slave.so
06.auth_test_plugin.so qa_auth_client.so
07.daemon_example.ini   qa_auth_interface.so
08.....................
09.[root@slave ~]# cd /usr/local/mysql/lib/plugin/
10.[root@slave plugin]# ls
11.adt_null.so          debug                 qa_auth_server.so
12.auth.so              libdaemon_example.so semisync_master.so
13.auth_socket.so       mypluglib.so          semisync_slave.so
14.auth_test_plugin.so qa_auth_client.so
15.daemon_example.ini   qa_auth_interface.so
16.......................
17.[root@master plugin]# mysql
18.mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
19.Query OK, 0 rows affected (0.03 sec)
20.mysql> set global rpl_semi_sync_master_enabled = 1;
21.Query OK, 0 rows affected (0.00 sec)
22.mysql> set global rpl_semi_sync_master_timeout = 1000;
23.Query OK, 0 rows affected (0.00 sec)
24...................................
25.[root@slave plugin]# mysql
26.mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
27.Query OK, 0 rows affected (0.04 sec)
28.mysql> set global rpl_semi_sync_slave_enabled = 1 ;
29.Query OK, 0 rows affected (0.01 sec)
30.mysql> stop slave io_thread;
31.Query OK, 0 rows affected (0.01 sec)
32.mysql> start slave io_thread;
33.Query OK, 0 rows affected (0.01 sec)
2、查看半同步开启的状态，并查看从节点的线程启动状态;

view source print ?
01.mysql> show global status like 'rpl_semi%';
02.+--------------------------------------------+-------+
03.| Variable_name                              | Value |
04.+--------------------------------------------+-------+
05.| Rpl_semi_sync_master_clients               | 1     |
06.| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
07.| Rpl_semi_sync_master_net_wait_time         | 0     |
08.| Rpl_semi_sync_master_net_waits             | 0     |
09.| Rpl_semi_sync_master_no_times              | 0     |
10.| Rpl_semi_sync_master_no_tx                 | 0     |
11.| Rpl_semi_sync_master_status                | ON    |
12.| Rpl_semi_sync_master_timefunc_failures     | 0     |
13.| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
14.| Rpl_semi_sync_master_tx_wait_time          | 0     |
15.| Rpl_semi_sync_master_tx_waits              | 0     |
16.| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
17.| Rpl_semi_sync_master_wait_sessions         | 0     |
18.| Rpl_semi_sync_master_yes_tx                | 0     |
19.+--------------------------------------------+-------+
20.14 rows in set (0.00 sec)
21.mysql> show global variables like '%rpl%';
22.+------------------------------------+-------+
23.| Variable_name                      | Value |
24.+------------------------------------+-------+
25.| rpl_recovery_rank                  | 0     |
26.| rpl_semi_sync_master_enabled       | ON    |
27.| rpl_semi_sync_master_timeout       | 1000 |
28.| rpl_semi_sync_master_trace_level   | 32    |
29.| rpl_semi_sync_master_wait_no_slave | ON    |
30.+------------------------------------+-------+
31.5 rows in set (0.00 sec)
32................
33.mysql> show global status like 'rpl_semi%';
34.+----------------------------+-------+
35.| Variable_name              | Value |
36.+----------------------------+-------+
37.| Rpl_semi_sync_slave_status | ON    |
38.+----------------------------+-------+
39.1 row in set (0.00 sec)
40.mysql> show global variables like '%rpl%';
41.+---------------------------------+-------+
42.| Variable_name                   | Value |
43.+---------------------------------+-------+
44.| rpl_recovery_rank               | 0     |
45.| rpl_semi_sync_slave_enabled     | ON    |
46.| rpl_semi_sync_slave_trace_level | 32    |
47.+---------------------------------+-------+
48.3 rows in set (0.00 sec)
49.................
50.[root@slave ~]# mysql -e 'show slave status\G'
51.*************************** 1. row ***************************
52.Slave_IO_State: Waiting for master to send event
53.Master_Host: 172.16.200.5
54.Master_User: zly
55.Master_Port: 3306
56.Connect_Retry: 60
57.Master_Log_File: mysql-bin.000011
58.Read_Master_Log_Pos: 453
59.Relay_Log_File: relay_log.000003
60.Relay_Log_Pos: 253
61.Relay_Master_Log_File: mysql-bin.000011
62.Slave_IO_Running: Yes
63.Slave_SQL_Running: Yes
3、主从双方节点都存在新建的class数据库，于主服务器上删除该数据库，然后查看从服务器是否也同步删除了;

view source print ?
01.[root@master ~]# mysql -e 'show databases;'
02.+--------------------+
03.| Database           |
04.+--------------------+
05.| information_schema |
06.| classdb            |
07.| mysql              |
08.| performance_schema |
09.| testdb             |
10.+--------------------+
11.[root@slave ~]# mysql -e 'show databases;'
12.+--------------------+
13.| Database           |
14.+--------------------+
15.| information_schema |
16.| classdb            |
17.| mysql              |
18.| performance_schema |
19.+--------------------+
20.。。。。。。。。。。。。。
21.[root@master ~]# mysql -e 'drop database classdb;'
22.[root@master ~]# mysql -e 'show databases;'
23.+--------------------+
24.| Database           |
25.+--------------------+
26.| information_schema |
27.| mysql              |
28.| performance_schema |
29.| testdb             |
30.+--------------------+
31.[root@slave ~]# mysql -e 'show databases;'
32.+--------------------+
33.| Database           |
34.+--------------------+
35.| information_schema |
36.| mysql              |
37.| performance_schema |
38.+--------------------+
至此，mysql的半同步复制已经完成。