主从复制
原理：
从库生成两个线程，一个I/O线程，一个SQL线程；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；
作用：
1.实时灾备，用于故障切换
2.读写分离，提供查询服务
3.备份，避免影响业务
配置主从复制 - 异步复制
基于偏移量的主从复制
主库开启binlog功能并授权从库连接主库，从库通过change master得到主库的相关同步信息然后连接主库进行验证，主库IO线程根据从库slave线程的请求，从master.info开始记录的位置点向下开始取信息，同时把取到的位置点和最新的位置与binlog信息一同发给从库IO线程,从库将相关的sql语句存放在relay-log里面，最终从库的sql线程将relay-log里的sql语句应用到从库上，至此整个同步过程完成，之后将是无限重复上述过程
tar xf mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y
systemctl start mysqld
cat /var/log/mysqld.log | grep password ##查看初始化密码
mysql_secure_installation
vim /etc/my.cnf
server-id=1 ##设置ID不能与别的主机重复
log-bin=mysql-bin ##打开二进制日志
server1进入mysql:
grant replication slave on *.* to rep@'172.25.24.%' identified by 'Zcx971203.';
mysql> show master status; ##查看日志文件和position
server2中：
mysql> change master to
-> master_host='172.25.24.1',
-> master_user='rep',
-> master_password='Zcx971203.',
-> master_log_file='mysql-bin.000001', ##根据在主数据库中查看的文件
-> master_log_pos=154; #根据在主数据库中查看的position
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.24.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
GTID复制原理：
基于GTID的复制是MySQL 5.6后新增的复制方式
GTID (global transaction identifier) 即全局事务ID, 保证了在每个在主库上提交的事务在集群中有一个唯一的ID.
在原来基于日志的复制中, 从库需要告知主库要从哪个偏移量position进行增量同步, 如果指定错误会造成数据的遗漏, 从而造成数据的不一致.
而基于GTID的复制中, 从库会告知主库已经执行的事务的GTID的值, 然后主库会将所有未执行的事务的GTID的列表返回给从库. 并且可以保证同一个事务只在指定的从库执行一次.
server1在/etc/my.cnf加入
gtid_mode=ON
enforce-gtid-consistency=true
server2相同
在server2中
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.25.24.1',master_user='rep',master_password='Zcx971203.',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> start slave;
Query OK, 0 rows affected (0.30 sec)
mysql> show slave status\G
Auto_Position: 1
如果主库突然宕机了，会造成数据丢失，那么怎么解决这个问题呢？
半同步复制
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。
server1:
install plugin rpl_semi_sync_master soname 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled =1;
show variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON | ##可看到服务打开
| rpl_semi_sync_master_timeout | 10000 | ##延迟10000毫秒该参数为10000毫秒，即10秒，不过，这个参数是动态动态可调，表示如果主库在某次十五中的等待时间超过10秒，则降级为异步复制模式，不在等待slave从库，如果主库再次探测slave从库恢复，则会自动回到半同步复制模式
| 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 |
+-------------------------------------------+------------+
server2:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled =1;
组复制
高容错性
只要不是大多数节点坏掉就可以继续工作
高扩展性
节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
高一致性
基于原生复制及 paxos 协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
高灵活性
有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；
多主模式下，所有 server 都可以同时处理更新操作。
server1:
systemctl stop mysqld
cd /var/lib/mysql
rm -rf *
vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="a5038e3c-6c85-11e9-a044-52540023f8a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.24.1:24901"
loose-group_replication_group_seeds= "172.25.24.1:24901,172.25.24.2:24901,172.25.24.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.24.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql -p
mysql> alter user root@localhost identified by 'Zcx971203.';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Zcx971203.';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='Zcx971203.' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 6a8e4133-6cae-11e9-959d-525400d72bc0 | server2 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)
server2:
systemctl stop mysqld
cd /var/lib/mysql
rm -rf *
vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="a5038e3c-6c85-11e9-a044-52540023f8a1"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.24.2:24901"
loose-group_replication_group_seeds= "172.25.24.1:24901,172.25.24.2:24901,172.25.24.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.24.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF
mysql -p
mysql> alter user root@localhost identified by 'Zcx971203.';
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'Zcx971203.';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='Zcx971203.' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
mysql> SET GLOBAL group_replication_allow_local_disjoint_gtids_join=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (4.90 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 6a8e4133-6cae-11e9-959d-525400d72bc0 | server2 | 3306 | ONLINE |
| group_replication_applier | fb4eb11a-6caf-11e9-8c56-525400065008 | server1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
server3:同上
读写分离
将server1 server2 改为主从或gtid
重新初始化
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
mysql> alter user root@localhost identified by 'Zcx971203.';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repl@'172.25.24.%' identified by 'Zcx971203.';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 | 691 | | | 062f7d3d-6cba-11e9-ac10-525400065008:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
server2:
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
mysql> alter user root@localhost identified by 'Zcx971203.';
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='172.25.24.1',master_user='repl',master_password='Zcx971203.',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
server3:
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir log
修改数据库发生读写分离时的最大最小值
cd /usr/local/mysql-proxy/share/doc/mysql-proxy
vim rw-splitting.lua
创建配置文件：
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-read-only-backend-addresses=172.25.24.2:3306
proxy-backend-addresses=172.25.24.1:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
plugins=proxy
log-level=debug
keepalive=true
daemon=true
设置文件权限 再启动mysql-proxy
[root@server3 conf]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 conf]# 2019-05-02 17:29:33: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=11705 alive
2019-05-02 17:29:33: (debug) chassis-unix-daemon.c:157: waiting for 11705
2019-05-02 17:29:33: (debug) chassis-unix-daemon.c:121: we are the child: 11705
2019-05-02 17:29:33: (critical) plugin proxy 0.8.5 started
2019-05-02 17:29:33: (debug) max open file-descriptors = 1024
2019-05-02 17:29:33: (message) proxy listening on port 0.0.0.0:3306
2019-05-02 17:29:33: (message) added read/write backend: 172.25.24.1:3306
2019-05-02 17:29:33: (message) added read-only backend: 172.25.24.2:3306
2019-05-02 17:29:33: (debug) now running as user: root (0/0)
查看端口3306
netstat -anltp
通过物理机登陆server3的3306检测
1373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
