关于Mysql数据库半同步主从架构模式。
（因为本人目前已经有一套已经配置完成的mysql集群架构，所以本人需要先将目前集群内的cak数据库做个dump备份一下再把现在的集群给删除重新做 ‘可惜了现在的环境，要配置好久’ ）；
先把需要的数据库给备份：
备份我的cak库
使用 mysqldump命令备份cak数据库
mysqldump -u[用户名] -p[密码] [数据库名] > [保存的路径和保存的备份名]
我现在将我的cak库已经保存到了D盘内的文件夹中。
目前的主从是正常的：
现在的IO和SQL状态都是YES，证明我的主从是正常的状态，现在把环境全部重新做，给数据库卸载掉。
…
安装数据库
开始新装INSTALLER安装包数据库：
选择server服务：
在线更新下载组件
点击Execute
默认点击两次Next进入到配置页面：
进入选择默认
development主机：
选择设置密码方式：
设置密码
一路下一步
可以看到日志里的信息，和已经成功安装完成后显示的finish按钮。
查看一下我默认的安装路径有没有mysql文件夹：
我的是在C盘下
查看一下是否有安装后的组件
查看一下ProgramData下有没有INSTALLER版本的配置文件
这个my文件需要我们用到主从的配置，先让我们打开mysql做一些远程的配置
打开DOS界面开启一下mysql服务，开启的mysql服务名是刚刚上面安装的时候设置的默认的mysql80服务名（自定义的服务名就输入自己的服务名）
服务已经启动，在开始里找到mysql的命令窗口进入mysql配置：
输入配置的密码登陆mysql
查看目前数据库内的所有库实例
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql>
切换进mysql库实例内
mysql> use mysql;
Database changed
mysql>
查看user表内容：
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
33 rows in set (0.04 sec)
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql>
可以看到里面有root用户，但是只能够local登陆，需要将其更改为全部登陆方式
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql>
查看一下防火墙的端口3306是否开启
这里我用本机的Navicat测试是否能够连接数据库
到此，远程连接是正常的。
配置主从
下面就开始配置我们的主从数据库：
到上面的my文件位置打开my文件，修改里面的server id等数
server-id=1 （数值往前为主，数值往后为从）
以下根据实际需要自行配置：
max_connections=15100
tmp_table_size=116M
innodb_log_file_size=128M
max_allowed_packet=1G
主服务器配置复制账户：
mysql> create user 'master_user'@'%' identified WITH 'mysql_native_password' by
'123456';
Query OK, 0 rows affected (0.07 sec)
mysql> grant all privileges on *.* to 'master_user'@'%' with grant option;
Query OK, 0 rows affected (0.10 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: WIN-NI46FVCM674-bin.000003
Position: 847
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> use mysql;
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| master_user | % |
| root | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql>
打开从服务器（安装与配置与以上配置相同）
查看一下slave状态：
输入 show slave status\G;
Slave_IO_Running: No
Slave_SQL_Running: No
change主服务器的复制账户：
mysql> CHANGE MASTER TO
MASTER_HOST='（主服务器IP地址）',
MASTER_USER='master_user',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='WIN-NI46FVCM674-bin.000003（刚才查看的master服务器file文件号）',
MASTER_LOG_POS=847（刚才查看的master服务器position号）;
Query OK, 0 rows affected, 1 warning (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: （主服务器IP地址）
Master_User: master_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: WIN-NI46FVCM674-bin.000003
Read_Master_Log_Pos: 847
Relay_Log_File: WIN-ESCN1RS3ISI-relay-bin.000002
Relay_Log_Pos: 334
Relay_Master_Log_File: WIN-NI46FVCM674-bin.000003
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: cak.sty_access_event,cak.sty_access_event_his,cak
.sty_access_event_pool,cak.sty_access_json_pool,cak.sty_device_master_cmd,cak.st
y_access_data,cak.sty_site_card,cak.sty_site_card_del,cak.sty_site_card_to_maste
r,cak.sty_site_card_to_master_saved,cak.sty_system_event,cak.sty_system_state,ca
k.sty_system_log
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 847
Relay_Log_Space: 553
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: 1
Master_UUID: 02e5884e-b9d4-11ea-a694-902b34ce9dae
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more up
dates
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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
可以看到，到此，主从已经成功。
（测试是否可用可以随个人意愿，插入一些数据创建一些数据存储来查看一下从库的数据复制）。
安装配置半同步插件
已经完成了数据库的主从同步配置之后，默认的数据库复制模式是异步数据库复制模式（不管从库是否能够完整接收到主库传输的数据，只要主库自身传输完所有数据，就会终止传输。），这种模式数据传输快，但是数据 不完整性概率很大，所以配置半同步复制（多集群服务器情况下，一个从库完整接收主库的数据传输后即返回cak确认消息完成复制）。
1.查看目前的插件状态：
安装主从插件
在Windows系统下的Mysql半同步插件后缀名为.dll文件（不要搞错或者找不着）：
主从插件的路径在mysql配置目录lib\plugin下
配置插件
主库插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.dll';
Query OK, 0 rows affected (0.08 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 |
+----------------------+---------------+
1 row in set (0.01 sec)
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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, 1 warning (0.00 sec)
mysql>
可以看到主库的semi插件处于OFF状态，等一下再开启主库的ON状态。
从库插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.dll';
查看从库的插件状态：
mysql> select PLUGIN_NAME,PLUGIN_STATUS
-> from information_schema.plugins where PLUGIN_NAME like '%semi%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| rpl_semi_sync_slave | ACTIVE |
+---------------------+---------------+
1 row in set (0.01 sec)
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set, 1 warning (0.01 sec)
mysql>
将semi半同步插件启用
将rpl_semi_sync_slave_enabled 的OFF状态更改成ON状态：
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | OFF |
| 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, 1 warning (0.00 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| 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, 1 warning (0.01 sec)
mysql>
同理，将从库的OFF状态也变成ON状态：
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set, 1 warning (0.01 sec)
查看master状态：
mysql> show status like '%semi%';
+--------------------------------------------+-------+
| 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.01 sec)
查看slave状态：
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql>
从库的状态还是异步复制状态，并没有启用半同步复制状态，此时需要先重启一下从库上的I/O线程
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> show status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
半同步复制已经启用。
下面可以由小伙伴们增加数据来做测试啦。
扫一扫
692
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
