1.一主两从数据库配置
克隆原来的环境，清理mysql环境
克隆的主机需要主机auto.cnf文件，直接删除，启动时自动创建
初始化两台从库数据库
2.打开主从二进制和中继日志路径
mkdir -p /mysql/log/3306/{binlog,relaylog}
chown -R mysql:mysql /mysql
chmod -R 775 /mysql
3.配置主数据库参数
bind-address=192.168.0.200
server_id=1
skip_name_resolve=ON
expire_logs_days=7
innodb_support_xa =1
binlog_cache_size=10M
max_binlog_size=2048M
log_bin_trust_function_creators=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction-isolation = READ-COMMITTED
gtid_mode =ON
enforce_gtid_consistency =1
log-slave-updates=1
binlog_gtid_simple_recovery=1
log_bin=/mysql/log/3306/binlog/hdb-binlog
log_bin_index=/mysql/log/3306/binlog/hdb-binlog.index
binlog_format=ROW
binlog_rows_query_log_events=ON
#relay_log = /mysql/log/3306/binlog/hdb-relay.log
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_enable=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
4.配置从数据库参数
bind-address=192.168.0.201
server_id=2
skip_name_resolve=ON
expire_logs_days=7
innodb_support_xa =1
binlog_cache_size=10M
max_binlog_size=2048M
log_bin_trust_function_creators=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction-isolation = READ-COMMITTED
gtid_mode =ON
enforce_gtid_consistency =1
log-slave-updates=1
binlog_gtid_simple_recovery=1
log_bin=/mysql/log/3306/binlog/hdb-binlog
log_bin_index=/mysql/log/3306/binlog/hdb-binlog.index
binlog_format=ROW
binlog_rows_query_log_events=ON
relay_log = /mysql/log/3306/binlog/hdb-relay.log
read_only=1
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_enable=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
5.主库创建复制用并授权，从库会自动同步过去
create user 'rep'@'%' identified by 'rep' ;
grant replication client,replication slave on *.* to 'rep'@'%';
flush privileges;
6.在从库上配置主从连接
stop slave;
reset master;
reset slave;
CHANGE MASTER TO
MASTER_HOST='192.168.0.200',
MASTER_USER='rep',
MASTER_PASSWORD='rep',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
start slave;
7.检查主从配置是否正常
主库
show processlist;
从库
show processlist;
show slave status \G;
需要保证这两个进程都是yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8.验证主从同步
主库创建用户，数据库，表，数据
create database testdb;
create user 'hdbtest'@'%' identified by 'hdbtest';
grant all privileges on testdb.* to 'hdbtest'@'%';
flush privileges;
use testdb;
create table testtab (id int,name varchar(20));
insert into testtab(id,name) values (1,'hdb1');
insert into testtab(id,name) values (2,'hdb2');
insert into testtab(id,name) values (3,'hdb3');
insert into testtab(id,name) values (5,'hdb5');
commit;
select * from testtab;
从库
use testdb;
select * from testtab;
