主从复制，无损复制

这个实验：是对现有的单机数据库，做一个无损的主从复制

第一种方法：手工安装  ，重启失效，只是基于生产上有数据的情况下操作

登入数据库,主从库都操作，如果有从库，要先stopslave

install plugin rpl_semi_sync_master soname 'semisync_master.so';

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

set global rpl_semi_sync_master_enabled = 1;

set global rpl_semi_sync_slave_enabled = 1;

set global rpl_semi_sync_master_timeout = 5000;

set global rpl_semi_sync_master_wait_point = AFTER_SYNC;

set global rpl_semi_sync_master_wait_for_slave_count = 1 ;

show plugins;   最后两个都是ACTIVE状态

显示插件路径   show variables like "plugin_dir";

show variables like 'rpl_%';

show global status like 'rpl_semi%';

 

第二种方法：没有数据，直接写入配置文件

######semi sync replication settings########

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000               #5秒

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

 

show variables like 'rpl_%';

show global status like 'rpl_semi%';

 

参数配置：

## 192.168.0.50(master)

vi /mysql/data/3306/my.cnf

#master add parameter

log_bin=/mysql/log/3306/binlog/mysqldb-binlog

log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

#master modify parameter

bind-address=0.0.0.0

server_id=503306

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

#--------------------------

#slave parameter

#--------------------------

#relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log

#log-slave-updates = 1

#read_only=1

#slave-parallel-type=LOGICAL_CLOCK

#slave-parallel-workers=4

#master_info_repository=TABLE

#relay_log_info_repository=TABLE

#relay_log_recovery=1

#slave_skip_errors = ddl_exist_errors

#slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

#------------------------------------

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

## 192.168.0.51(slave)

vi /mysql/data/3306/my.cnf

#slave add parameter

log_bin=/mysql/log/3306/binlog/mysqldb-binlog

log_bin_index=/mysql/log/3306/binlog/mysqldb-binlog.index

binlog_format=row

binlog_rows_query_log_events=on

#slave modify parameter

bind-address=0.0.0.0

server_id=513306

skip_name_resolve = on

expire_logs_days = 7

innodb_support_xa =1

binlog_cache_size = 1M

max_binlog_size = 2048M

log_bin_trust_function_creators = 1

innodb_flush_log_at_trx_commit =1

sync_binlog = 1

transaction-isolation = read-committed

#------------------------------------

#slave parameter

#------------------------------------

relay_log = /mysql/log/3306/relaylog/mysqldb-relay.log

log-slave-updates = 1

read_only=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=4

master_info_repository=table

relay_log_info_repository=table

relay_log_recovery=1

slave_skip_errors = ddl_exist_errors

slave_preserve_commit_order=1

#------------------------------------

#semi sync replication settings

#------------------------------------

plugin_dir=/mysql/app/mysql/lib/plugin/

plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"  

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1       # show plugins;   可以查看到最后有安装的两个插件，active状态

loose_rpl_semi_sync_master_timeout = 5000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count = 1

 

cd /mysql/backup 

包含 所有的库，所有的触发器

主库：

mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --flush-privileges --events --routines --all-databases >db_fullbackup.sql

备库：

mysql -uroot -proot < /mysql/backup/db_fullbackup.sql

 

做主备的连接

 

此时，可以阅读下，中继日志

mysqlbinlog mysqldb-relay.000002 -vvv | more 

查看参数，after_commit; 半同步   after_sync 无损

show variables like "%semi_sync%";

show global status like "%rpl_semi%";

图解：主库看 master_status ,备库看slave_status