用binlog恢复数据
MySQL的binlog是数据恢复，主从同步的关键日志；
binlog相关配置：
binlog_format 日志格式
log-bin 日志文件
expire_logs_days 过期清理时间
max_binlog_size 每个日志文件大小
binlog_cache_size 缓存大小
max_binlog_cache_size 最大缓存大小
binlog_do_db 指定要生成binlog的库
binlog_ignore_db 指定不要生成binlog的库
binlog_format 有3种模式：
STATEMENT 每一条会修改数据的sql语句会记录到binlog中；
ROW 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了；
MIXED 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式；
启用binlog之前清空表
mysql> truncate basic_info;
Query OK, 0 rows affected (0.02 sec)启用binlog
[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
...略
server-id=11
log-bin=master-bin
binlog_format=MIXED
[root@node1 ~]# systemctl restart mysqld确认是否开启
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 154 |
+-------------------+-----------+
1 row in set (0.00 sec)写入数据，修改数据，删除数据，清空表，后面恢复修改之前的数据
mysql> insert into basic_info(name,age) values('LiLei',18);
Query OK, 1 row affected (0.01 sec)
mysql> insert into basic_info(name,sex,age) values('HanMM','female',17);
Query OK, 1 row affected (0.01 sec)
mysql> insert into basic_info(name,age,hobby) values('Jhon',17,'basketball');
Query OK, 1 row affected (0.01 sec)
mysql> insert into basic_info(name,sex,age,profession,hobby) values('Sarah','female',19,'student','wrestle');
Query OK, 1 row affected (0.00 sec)
mysql> insert into basic_info(name,age,address) values('Ethan',16,'Woodlands');
Query OK, 1 row affected (0.00 sec)
mysql> update basic_info set profession='student',address='Red Hill' where name='Jhon';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from basic_info where age>18;
Query OK, 1 row affected (0.01 sec)
mysql> truncate basic_info;
Query OK, 0 rows affected (0.12 sec)查看binlog日志
mysql> show master logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 2835 |
+-------------------+-----------+
1 row in set (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 2835 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'master-bin.000001';
+-------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------+
...略
| master-bin.000001 | 340 | Query | 11 | 475 | use `customers`; insert into basic_info(name,age) values('LiLei',18)
...略
| master-bin.000001 | 1829 | Query | 11 | 1984 | use `customers`; insert into basic_info(name,age,address) values('Ethan',16,'Woodlands') |
| master-bin.000001 | 1984 | Xid | 11 | 2015 | COMMIT /* xid=17 */ |
...略根据position值来恢复数据
[root@node1 ~]# mysqlbinlog --start-position=340 --stop-position=1984 --database=customers /var/lib/mysql/master-bin.000001 | mysql -u root -p customers
Enter password:查看数据
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> select * from customers.basic_info;
+-----+-------+--------+-----+------------+------------+---------+
| uid | name | sex | age | profession | hobby | address |
+-----+-------+--------+-----+------------+------------+---------+
| 1 | LiLei | male | 18 | NULL | NULL | NULL |
| 2 | HanMM | female | 17 | NULL | NULL | NULL |
| 3 | Jhon | male | 17 | NULL | basketball | NULL |
| 4 | Sarah | female | 19 | student | wrestle | NULL |
+-----+-------+--------+-----+------------+------------+---------+
4 rows in set (0.00 sec)主从数据库
从库会自动从主库同步数据，使MySQL高可用，数据更不易丢失；
这里用node1作为MySQL主库，node2和node3作为从库；
node1 配置不变，开启binlog即可；
node2 安装配置
[root@node2 ~]# yum install -y mysql-community-server
...略
[root@node2 ~]# systemctl start mysqld
[root@node2 ~]# systemctl enable mysqld
[root@node2 ~]# grep password /var/log/mysqld.log
2020-07-04T00:09:06.627041Z 1 [Note] A temporary password is generated for root@localhost: 7iEtGlgrq3=4
[root@node2 ~]# mysql_secure_installation
...略
[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server-id=12
log_slave_updates=1
relay_log=slave-relay-bin
relay_log_recovery=1
read_only=1
[root@node2 ~]# systemctl restart mysqldnode3 跟 node2 一样安装配置， 除了server-id 改为13
node1 主库设置
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 895 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)node2 和 node3 从库设置
[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> change master to master_host='10.0.0.11',master_port=3306,master_user='replicer',master_password='Replica111!',master_log_file='master-bin.000002',master_log_pos=895;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.11
Master_User: replicer
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 895
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes # slave 状态
Slave_SQL_Running: Yes # slave 状态
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: 895
Relay_Log_Space: 528
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 # slave 状态
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: 11
Master_UUID: a739b13a-bc42-11ea-92fb-080027e88dbb
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified可以通过上面表示的几个状态判定从库状态
主库创建数据库，用于保存网站状态数据
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> create database websites default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use websites;
Database changed
mysql> create table site_status( wid int not null auto_increment primary key, name char(30) not null, address char(50) not null, acquired_time datetime not null, status char(10) not null)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.03 sec)写入数据
mysql> use websites;
Database changed
mysql> insert into site_status(name,address,acquired_time,status) values("阿里巴巴开源镜像站","https://developer.aliyun.com/mirror/","2020-07-04 10:19:57","online");
Query OK, 1 row affected (0.01 sec)
mysql> insert into site_status(name,address,acquired_time,status) values("网易开源镜像站","http://mirrors.163.com/","2020-07-04 10:19:58","online");
Query OK, 1 row affected (0.01 sec)
mysql> insert into site_status(name,address,acquired_time,status) values("清华大学开源软件镜像站","https://mirrors.tuna.tsinghua.edu.cn/","2020-07-04 10:20:25","online");
Query OK, 1 row affected (0.01 sec)
mysql> select * from site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name | address | acquired_time | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| 1 | 阿里巴巴开源镜像站 | https://developer.aliyun.com/mirror/ | 2020-07-04 10:19:57 | online |
| 2 | 网易开源镜像站 | http://mirrors.163.com/ | 2020-07-04 10:19:58 | online |
| 3 | 清华大学开源软件镜像站 | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)查看是否同步到从库
[root@node3 ~]# mysql -u root -p
Enter password:
...略
mysql> select * from websites.site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name | address | acquired_time | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| 1 | 阿里巴巴开源镜像站 | https://developer.aliyun.com/mirror/ | 2020-07-04 10:19:57 | online |
| 2 | 网易开源镜像站 | http://mirrors.163.com/ | 2020-07-04 10:19:58 | online |
| 3 | 清华大学开源软件镜像站 | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)成功同步数据。
主从切换
模拟主库故障，将从库升级为主库；
关闭主库
[root@node1 ~]# systemctl stop mysqld从库(node2和node3)关闭io线程
[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)查看relay log是否被完全读取并执行完毕
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 607 | Slave has read all relay log; waiting for more updates | NULL |
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
2 rows in set (0.00 sec)对比两个从库的Master_log_pos值，值较大的作为新的主库
[root@node2 ~]# cat /var/lib/mysql/master.info
25
master-bin.000004
3538 # Master_log_pos
10.0.0.11
replicer
Replica111!
3306
60
...略我这里值是一样的，将node2选为新的主库
将node2切换成主库
[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table mysql.slave_master_info;
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'replicer2'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@node2 ~]# rm -f /var/lib/mysql/relay-log.info
[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server-id=12
log-bin=new-master-bin
#log_slave_updates=1
#relay_log=slave-relay-bin
#relay_log_recovery=1
#read_only=1
[root@node2 ~]# systemctl restart mysqld
[root@node2 ~]# mysql -uroot -p
Enter password:
...略
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| new-master-bin.000001 | 601 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)修改node3从库的主库信息
[root@node3 ~]# mysql -u root -p
Enter password:
...略
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='10.0.0.12',master_port=3306,master_user='replicer2',master_password='Replica111!',master_log_file='new-master-bin.000001',master_log_pos=601;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.12
Master_User: replicer2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: new-master-bin.000001
Read_Master_Log_Pos: 601
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: new-master-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: 601
Relay_Log_Space: 532
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: 12
Master_UUID: 933d7e7d-bd8a-11ea-bd03-080027b7d17f
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified原本的主库node1此时将其修改为从库
[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
..略
server-id=11
#log-bin=master-bin
#binlog_format=MIXED
log_slave_updates=1
relay_log=slave-relay-bin
relay_log_recovery=1
read_only=1
[root@node1 ~]# systemctl start mysqld
[root@node1 ~]# mysql -u root -p
Enter password:
..略
mysql> change master to master_host='10.0.0.12',master_port=3306,master_user='replicer2',master_password='Replica111!',master_log_file='new-master-bin.000001',master_log_pos=601;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.12
Master_User: replicer2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: new-master-bin.000001
Read_Master_Log_Pos: 601
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: new-master-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: 601
Relay_Log_Space: 532
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: 12
Master_UUID: 933d7e7d-bd8a-11ea-bd03-080027b7d17f
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified主库写入数据测试
[root@node2 ~]# mysql -uroot -p
Enter password:
...略
mysql> use websites;
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 site_status(name,address,acquired_time,status) values("中国科学技术大学开源镜像站","https://mirrors.ustc.edu.cn/","2020-07-04 10:25:34","online");
Query OK, 1 row affected (0.01 sec)查看是否同步
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> select * from websites.site_status;
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
| wid | name | address | acquired_time | status |
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
| 1 | 阿里巴巴开源镜像站 | https://developer.aliyun.com/mirror/ | 2020-07-04 10:19:57 | online |
| 2 | 网易开源镜像站 | http://mirrors.163.com/ | 2020-07-04 10:19:58 | online |
| 3 | 清华大学开源软件镜像站 | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
| 4 | 中国科学技术大学开源镜像站 | https://mirrors.ustc.edu.cn/ | 2020-07-04 10:25:34 | online |
+-----+-----------------------------------------+---------------------------------------+---------------------+--------+
4 rows in set (0.00 sec)读写分离
使用工具MaxScale实现读写分离，把读操作分散到从服务器中，并且对多个从服务器能实现负载均衡；
下载地址:https://downloads.mariadb.com/MaxScale/
[root@node2 ~]# wget https://downloads.mariadb.com/MaxScale/2.4.9/centos/7/x86_64/maxscale-2.4.9-1.centos.7.x86_64.rpm在 node2 为主库，node1,node3 为从库的基础上实验；
创建用于监控和路由的MySQL用户
[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> grant replication slave,replication client on *.* to 'maxmon'@'10.0.0.%' identified by 'Maxscale111!';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant select on mysql.* to 'maxrou'@'10.0.0.%' identified by 'Maxscale111!';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)安装配置MaxScale
[root@node2 ~]# rpm -ivh maxscale-2.4.9-1.centos.7.x86_64.rpm
warning: maxscale-2.4.9-1.centos.7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 28c12247: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:maxscale-2.4.9-1 ################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/maxscale.service to /usr/lib/systemd/system/maxscale.service.
[root@node2 ~]# cp /etc/maxscale.cnf /etc/maxscale.cnf.bak
[root@node2 ~]# vim /etc/maxscale.cnf
[root@node2 ~]# cat /etc/maxscale.cnf
[maxscale]
threads=auto
[server1]
type=server
address=10.0.0.12
port=3306
protocol=MySQLBackend
[server2]
type=server
address=10.0.0.11
port=3306
protocol=MySQLBackend
[server3]
type=server
address=10.0.0.13
port=3306
protocol=MySQLBackend
[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxmon
password=Maxscale111!
monitor_interval=2000
detect_replication_lag=1
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxrou
password=Maxscale111!
max_slave_replication_lag=60
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006
[CLI]
type=service
router=cli
[CLI-Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603
[root@node2 ~]# systemctl start maxscale
[root@node2 ~]# systemctl enable maxscale查看maxscale集群状态
[root@node2 ~]# maxadmin --user=admin --password=mariadb
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1 | 10.0.0.12 | 3306 | 0 | Master, Running
server2 | 10.0.0.11 | 3306 | 0 | Slave, Running
server3 | 10.0.0.13 | 3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> show dbusers Read-Write-Service
User names (Read-Write-Listener): mysql.session@localhost mysql.sys@localhost webmonitor@10.0.0.% replicer2@10.0.0.% maxmon@10.0.0.% maxrou@10.0.0.% mysql.session@localhost maxrou@10.0.0.%
MaxScale> exit测试
[root@node1 ~]# mysql -u webmonitor -h 10.0.0.12 -P 4006 -p
Enter password:
...略
mysql> select @@hostname;
+--------------------+
| @@hostname |
+--------------------+
| node1.newbie.local |
+--------------------+
1 row in set (0.01 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+--------------------+
| @@hostname |
+--------------------+
| node2.newbie.local |
+--------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+--------------------+
| @@hostname |
+--------------------+
| node1.newbie.local |
+--------------------+
1 row in set (0.00 sec)组复制
MySQL Group Replication 是一个高可用与高扩展的解决方案，提供了高可用、高扩展、高可靠的MySQL集群服务；
特点：
高一致性，基于原生复制及paxos协议的组复制技术，并以插件的方式提供，提供一致数据安全保证；
高容错性，只要不是大多数节点坏掉就可以继续工作，有自动检测机制，当不同节点产生资源争用冲突时，不会出现错误，按照先到者优先原则进行处理，并且内置了自动化脑裂防护机制；
高扩展性，节点的新增和移除都是自动的，新节点加入后，会自动从其他节点上同步状态，直到新节点和其他节点保持一致，如果某节点被移除了，其他节点自动更新组信息，自动维护新的组信息；
高灵活性，有单主模式和多主模式，单主模式下，会自动选主，所有更新操作都在主上进行；多主模式下，所有server都可以同时处理更新操作。
恢复初始快照；
node1 node2 node3重新安装，及初始化MySQL；
以单主模式为例，部署MGR
node1 上操作：
配置MySQL服务
[root@node1 ~]# uuidgen
ac2b81bb-09aa-45b3-87b6-65541ed5da4b
[root@node1 ~]# vim /etc/my.cnf
[root@node1 ~]# cat /etc/my.cnf
...略
server_id=11
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 # MGR的binlog只能是ROW格式
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name='ac2b81bb-09aa-45b3-87b6-65541ed5da4b'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='10.0.0.11:33061'
loose-group_replication_group_seeds='10.0.0.11:33061,10.0.0.12:33062,10.0.0.13:33063'
loose-group_replication_bootstrap_group=off
[root@node1 ~]# systemctl restart mysqld配置MySQL
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (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='replicer',master_password='Replica111!' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
...略
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
46 rows in set (0.00 sec)
mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> start group_replication;
Query OK, 0 rows affected (2.09 sec)
mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
1 row in set (0.00 sec)node2 上操作：
配置MySQL服务
[root@node2 ~]# vim /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
...略
server_id=12
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='ac2b81bb-09aa-45b3-87b6-65541ed5da4b'
loose-group_replication_start_on_boot=off
loose-group_replication_local_address='10.0.0.12:33062'
loose-group_replication_group_seeds='10.0.0.11:33061,10.0.0.12:33062,10.0.0.13:33063'
loose-group_replication_bootstrap_group=off
[root@node2 ~]# systemctl restart mysqld配置MySQL
[root@node2 ~]# mysql -u root -p
Enter password:
...略
mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'replicer'@'10.0.0.%' identified by 'Replica111!';
Query OK, 0 rows affected, 1 warning (0.01 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='replicer',master_password='Replica111!' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> install PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
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 (5.71 sec)在node1上查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local | 3306 | ONLINE |
| group_replication_applier | 78028e34-bf50-11ea-9a94-0800275623eb | node2.newbie.local | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
2 rows in set (0.00 sec)node3 操作跟node2差不多，
不一样的就是server_id和loose-group_replication_local_address两个配置的值；
完成node3的配置之后，再在node1上查看mgr状态
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 675c9ea7-bf4d-11ea-b1db-080027c01ae1 | node1.newbie.local | 3306 | ONLINE |
| group_replication_applier | 78028e34-bf50-11ea-9a94-0800275623eb | node2.newbie.local | 3306 | ONLINE |
| group_replication_applier | 7a96e212-bf50-11ea-9b0f-08002787181c | node3.newbie.local | 3306 | ONLINE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
3 rows in set (0.00 sec)写入数据
注意单主模式下，只有主节点可以写入数据
[root@node1 ~]# mysql -u root -p
Enter password:
...略
mysql> create database websites default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use websites;
Database changed
mysql> create table site_status( wid int not null auto_increment primary key, name char(30) not null, address char(50) not null, acquired_time datetime not null, status char(10) not null)engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into site_status(name,address,acquired_time,status) values("阿里巴巴开源镜像站","https://developer.aliyun.com/mirror/","2020-07-04 10:19:57","online");
Query OK, 1 row affected (0.01 sec)
mysql> insert into site_status(name,address,acquired_time,status) values("网易开源镜像站","http://mirrors.163.com/","2020-07-04 10:19:58","online");
Query OK, 1 row affected (0.01 sec)
mysql> insert into site_status(name,address,acquired_time,status) values("清华大学开源软件镜像站","https://mirrors.tuna.tsinghua.edu.cn/","2020-07-04 10:20:25","online");
Query OK, 1 row affected (0.01 sec)
mysql> select * from site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name | address | acquired_time | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| 1 | 阿里巴巴开源镜像站 | https://developer.aliyun.com/mirror/ | 2020-07-04 10:19:57 | online |
| 2 | 网易开源镜像站 | http://mirrors.163.com/ | 2020-07-04 10:19:58 | online |
| 3 | 清华大学开源软件镜像站 | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)node2 和 node3 查看数据是否同步
mysql> select * from websites.site_status;
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| wid | name | address | acquired_time | status |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
| 1 | 阿里巴巴开源镜像站 | https://developer.aliyun.com/mirror/ | 2020-07-04 10:19:57 | online |
| 2 | 网易开源镜像站 | http://mirrors.163.com/ | 2020-07-04 10:19:58 | online |
| 3 | 清华大学开源软件镜像站 | https://mirrors.tuna.tsinghua.edu.cn/ | 2020-07-04 10:20:25 | online |
+-----+-----------------------------------+---------------------------------------+---------------------+--------+
3 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
