时间：2021-07-01 10:21:17 帮助过：8人阅读
或者查询INFORMATION_SCHEMA.PLUGINS 表
[root@master1 ~]# cd /usr/lib64/mysql/plugin/
[root@master1 plugin]# ll
-rwxr-xr-x. 1 root root 103728 Apr 13 10:36 adt_null.so -rwxr-xr-x. 1 root root 356976 Apr 13 10:36 authentication_ldap_sasl_client.so -rwxr-xr-x. 1 root root 43552 Apr 13 10:36 auth_socket.so -rwxr-xr-x. 1 root root 940312 Apr 13 10:36 connection_control.so drwxr-xr-x. 2 root root 4096 Jul 3 11:31 debug -rwxr-xr-x. 1 root root 21640312 Apr 13 10:36 group_replication.so -rwxr-xr-x. 1 root root 483520 Apr 13 10:36 ha_example.so -rwxr-xr-x. 1 root root 968432 Apr 13 10:36 innodb_engine.so -rwxr-xr-x. 1 root root 957088 Apr 13 10:36 keyring_file.so -rwxr-xr-x. 1 root root 460064 Apr 13 10:36 keyring_udf.so -rwxr-xr-x. 1 root root 1184680 Apr 13 10:36 libmemcached.so -rwxr-xr-x. 1 root root 8973984 Apr 13 10:36 libpluginmecab.so -rwxr-xr-x. 1 root root 21424 Apr 13 10:36 locking_service.so -rwxr-xr-x. 1 root root 53928 Apr 13 10:36 mypluglib.so -rwxr-xr-x. 1 root root 41088 Apr 13 10:36 mysql_no_login.so -rwxr-xr-x. 1 root root 22243648 Apr 13 10:37 mysqlx.so -rwxr-xr-x. 1 root root 49504 Apr 13 10:36 rewrite_example.so -rwxr-xr-x. 1 root root 590936 Apr 13 10:36 rewriter.so -rwxr-xr-x. 1 root root 933904 Apr 13 10:36 semisync_master.so #主库安装 -rwxr-xr-x. 1 root root 159928 Apr 13 10:36 semisync_slave.so #备库安装 -rwxr-xr-x. 1 root root 209520 Apr 13 10:36 validate_password.so -rwxr-xr-x. 1 root root 506320 Apr 13 10:36 version_token.so
查看是否支持动态加载的MySQL 服务器
mysql> show variables like ‘%dynamic%‘; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_dynamic_loading | YES | +----------------------+-------+ 1 row in set (0.00 sec) mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘; #安装库 Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%rpl_semi%‘; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | OFF | #修改为on状态 | rpl_semi_sync_master_timeout | 10000 | #修改为1s | 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 | +-------------------------------------------+------------+
修改my.cnf
[root@master1 ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 1000
[root@master1 ~]# systemctl restart mysqld
mysql> show variables like ‘%rpl_semi%‘; +-------------------------------------------+------------+ | Variable_name | Value | +-------------------------------------------+------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000 | | 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 | +-------------------------------------------+------------+ 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 | +--------------------------------------------+-------+
rpl_semi_sync_master_timeout
一个以毫秒为单位的值，用于控制主服务器等待来自从服务器的确认提交并恢复到异步复制的时间，超过这个值就是超时。 默认值是10000（10 秒）。超时之后，就从半同步复制，返回到异步复制。
Rpl_semi_sync_master_yes_tx:从库成功确认的提交数量。
Rpl_semi_sync_master_no_tx:从库未成功确认的提交数量。
mysql> show variables like ‘%dynamic%‘; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_dynamic_loading | YES | +----------------------+-------+ mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘; mysql> show variables like ‘%rpl_semi%‘; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | OFF | #打开为on | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+
修改my.cnf
[root@slave ~]# vim /etc/my.cnf
rpl_semi_sync_slave_enabled = 1
[root@slave ~]# systemctl restart mysqld
mysql> show variables like ‘%rpl_semi%‘; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ mysql> show status like ‘%semi%‘; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+
mysql> show status like ‘%semi%‘; +--------------------------------------------+-------+ | 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 | 1 | | Rpl_semi_sync_master_no_tx | 1 | | 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 | +--------------------------------------------+-------+ mysql> insert into test values (2); mysql> insert into test values (2); mysql> insert into test values (2); mysql> show status like ‘%semi%‘; +--------------------------------------------+-------+ | 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 | 5 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 1013 | | Rpl_semi_sync_master_tx_wait_time | 3041 | | Rpl_semi_sync_master_tx_waits | 3 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 3 | #增加数据.这个之会增加 +--------------------------------------------+-------+
从端数据已经同步
mysql> select * from master1.test; +------+ | id | +------+ | 1 | | 2 | | 2 | | 2 | +------+
主库设置超时时间为1000 秒，备库停掉复制，模拟timeout
mysql> set global rpl_semi_sync_master_timeout=1000000; mysql> stop slave; mysql> insert into test values (11); #会一直卡住 mysql> select * from master1.test; +------+ | id | +------+ | 1 | | 2 | | 2 | | 2 | +------+
重启主库数据库,模拟主库宕机,从看数据记录
[root@master1 ~]# systemctl start mysqld [root@master1 ~]# mysql -uroot -p123456 mysql> select * from master1.test; +------+ | id | +------+ | 1 | | 2 | | 2 | | 2 | | 11 | #主库有记录 +------+
备库开启slave
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> select * from master1.test; +------+ | id | +------+ | 1 | | 2 | | 2 | | 2 | | 11 | #数据已经同步,没有丢失 +------+
无损的半同步复制是在write binlog 之后。需要得到备库的确认。所以这时候主库宕机，不会发生丢数据。当主库启动后，插入的数据重新可见。
将rpl_semi_sync_master_wait_point 设置为AFTER_COMMIT，
再次测试:
主库设置超时时间为1000 秒，备库停掉复制，模拟timeout
mysql> set global rpl_semi_sync_master_wait_point=AFTER_COMMIT; mysql> set global rpl_semi_sync_master_timeout=1000000; mysql> show variables like ‘%semi%‘; +-------------------------------------------+--------------+ | Variable_name | Value | +-------------------------------------------+--------------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 1000000 | | 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_COMMIT | +-------------------------------------------+--------------+
mysql> insert into master1.test values (12); #一直卡住
[root@master1 ~]# mysql -uroot -p123456 #另开一个窗口,发现已经有12这个数据 mysql> select * from master1.test; +------+ | id | +------+ | 1 | | 2 | | 2 | | 2 | | 11 | | 12 | +------+
这样当从库起来之后,数据已经提交,从库就会缺少这个数据
再开一个窗口查询这条数据，发现可以查询到。这时候主库宕机，会发生数据丢失。
主库重新启动，备库启动slave 会同步到备库。
mysql主从之半同步复制和lossless无损复制
标签：UNC 一个 优点 单位 有一个 metrics null binlog column