一.主从复制
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。
Mysql主从复制的实现原理图大致如下(来源网络)：
MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。
下边我进行的实验是一个互相为主从，即server1是server2的master ，server2是server3的master，同时server2又是server1的slave
Mysql的server2链接server1， server3 链接server2，实现主从复制
主机（master）：server1 172.25.24.1
从机（slave）：server2 172.25.24.2
1.mysql数据库的安装
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm[root@server1 ~]# yum install *.rpm -y
[root@server2 ~]# yum install *.rpm -y
[root@server3 ~]# yum install *.rpm -y
2.开启mysql并初始化
[root@server1 ~]# /etc/init.d/mysqld start
Initializing MySQL database:
[ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: 初始化密码可以通过日志来查看：
Cat /vatlog/nginx.log
[root@server1 ~]# mysql_secure_installation ##安全初始化脚本
Enter password for user root:
The existing password for the user account root has expired. Please set a new password.
New password:
Re-enter new password: 以上操作完了数据库的安装就结束了
1.修改mysql配置
找到主数据库的配置文件f /etc/my.cnf
server1配置文件加入:
server-id=1 ###服务器IP，要保证唯一性
log-bin=mysql-bin ###开启二进制日志
gtid_mode=ON ###gtid
enforce_gtid_consistency=true ###强制gtid一致性，开启后对于特定create table不被支持## 重启服务[root@server1 ~]# /etc/init.d/mysqld restart ##重启服务
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]server2配置文件加入:
Server2:
server-id=2
log-bin=mysql-bin
log-slave-updates ###默认情况下是OFF，这个参数时为了让server2去同步server3
gtid_mode=ON
enforce_gtid_consistency=true[root@server2 ~]# /etc/init.d/mysqld restart ##重启服务
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]server3配置文件加入：
server-id=3
log-bin=mysql-bin[root@server2 ~]# /etc/init.d/mysqld restart ##重启服务
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]2.创建同步帐号
server1：
mysql> grant replication slave on *.* to 'repl'@'172.25.24.%' identified by 'Westos+007'; #分配权限
Query OK, 0 rows affected, 1 warning (0.43 sec)
mysql> flush privileges; #刷新权限server2（连server1并作master）
mysql> change master to master_host='172.25.24.1' ,master_user='repl', master_password='Westos+007', MASTER_AUTO_POSITION=1; # 连接server1数据库
Query OK, 0 rows affected, 2 warnings (0.55 sec)
mysql> start slave; #打开slave
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G ##查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.24.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 447
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 660
Relay_Master_Log_File: mysql-bin.000001
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: 447
Relay_Log_Space: 909
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
mysql> grant replication slave on *.* to 'repl'@'172.25.24.%' identified by 'Westos+007'; #分配权限
Query OK, 0 rows affected, 1 warning (0.34 sec)server3（连server2）：
mysql> change master to master_host='172.25.24.2' ,master_user='repl', master_password='Westos+007', MASTER_AUTO_POSITION=1; #连接
Query OK, 0 rows affected, 2 warnings (0.15 sec)
mysql> start slave; #打开slave
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G #查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.24.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1033
Relay_Log_File: server3-relay-bin.000002
Relay_Log_Pos: 1246
Relay_Master_Log_File: mysql-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: 1033
Relay_Log_Space: 1455
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: 2
Master_UUID: 0138abca-9c46-11e8-a12a-52540022235b
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 0138abca-9c46-11e8-a12a-52540022235b:1-2,
1f74a753-9c46-11e8-9fd6-525400b9dbc1:1
Executed_Gtid_Set: 0138abca-9c46-11e8-a12a-52540022235b:1-2,
1f74a753-9c46-11e8-9fd6-525400b9dbc1:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)测试：
Server1建立表
mysql> create database test;
Query OK, 1 row affected (0.05 sec)
mysql> use test;
Database changed
mysql> create table userlist (
-> username varchar(15) not null,
-> password varchar(25) not null);
Query OK, 0 rows affected (0.40 sec)
mysql> desc userlist;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(25) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into userlist values ('user1','147');
Query OK, 1 row affected (0.10 sec)
mysql> insert into userlist values ('user2','258');
Query OK, 1 row affected (0.08 sec)
mysql> insert into userlist values ('user3','369');
Query OK, 1 row affected (0.36 sec)Server2：
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from userlist;
Empty set (0.00 sec)
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 147 |
| user2 | 258 |
| user3 | 369 |
+----------+----------+
3 rows in set (0.00 sec)Server3：
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 147 |
| user2 | 258 |
| user3 | 369 |
+----------+----------+
3 rows in set (0.00 sec)
二.半同步复制
从MySQL5.5开始，MySQL以插件的形式支持半同步复制。如何理解半同步呢？首先我们来看看异步，全同步的概念
异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
原理图(源于网络):
1.安装服模块
Server1:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'
-> ;
Query OK, 0 rows affected (0.06 sec)Server2:
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.11 sec)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.10 sec)Server3:
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.12 sec)测试：
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
-> FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE PLUGIN_NAME LIKE ‘%semi%’;
+———————-+—————+
| PLUGIN_NAME | PLUGIN_STATUS |
+———————-+—————+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+———————-+—————+
2.添加服务（可以选择再命令行输入这样的是临时的加载，重启之后会消失 或者可以再配置文件中添加这样是永久的）
Server1: 开启master半复制服务
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)Server2: 开启master和slave模块打开
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)Server3: 开启slave半复制模块
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)重启slave：（server2和server3）
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.08 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)3.查看状态
Server1:查看
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 | 启动了一个服务
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC | 启动服务
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql> SHOW STATUS LIKE 'Rpl_semi_sync%'; 查看服务列表
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| 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 |
+--------------------------------------------+-------+
Server2：
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |##接受几个ack才会提交
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)Server3:
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)4.测试
Server1:
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into userlist values('user4','111');
Query OK, 1 row affected (0.11 sec)
mysql> insert into userlist values('user5','555');
Query OK, 1 row affected (0.11 sec)Server2:
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| 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 | 498 |
| Rpl_semi_sync_master_tx_wait_time | 997 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
| Rpl_semi_sync_slave_status | ON |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)Server3:
mysql> use test
Database changed
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 147 |
| user2 | 258 |
| user3 | 369 |
| user4 | 111 |
| user5 | 555 |
+----------+----------+
5 rows in set (0.00 sec)为什么叫半同步：
server2:
mysql> STOP SLAVE IO_THREAD; 停止io线程
Query OK, 0 rows affected (0.06 sec)Server1：
mysql> insert into userlist values ('user6','666'); 在建立新的表格数据时候，会有10s延迟
Query OK, 1 row affected (10.09 sec)server3：
mysql> mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 147 |
| user2 | 258 |
| user3 | 369 |
| user4 | 111 |
| user5 | 555 |
+----------+----------+
5 rows in set (0.00 sec)再开启server2的IO线程
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)Server3： 数据会同步。
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 147 |
| user2 | 258 |
| user3 | 369 |
| user4 | 111 |
| user5 | 555 |
| user6 | 666 |
+----------+----------+
6 rows in set (0.00 sec)
扫一扫
3676
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
