mysql 主从配置详细流程

如何安装mysql

mysql-5.7.20 源码安装

如何配置mysql 主从同步

MySQL的一主二从异步复制(半同步复制)

以下为配置主从半同步示例：

//创建主从同步的用户，10.168.1.217为从服务器的IP
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.168.1.217' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;


//全量备份
mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases ismarthome > ismarthome.sql


//从主上下载testDb.sql
scp root@10.168.1.216:/root/testDb.sql /root/


//导入到10.168.1.216数据库中
//导入之前先删除testDb数据库
mysql -uroot -p'123456' < testDb.sql




//配置主从同步
stop slave;


change master to
master_host='10.168.1.216',
master_user='repl_user',
master_password='123456',
master_log_file='mysql_bin.000005',
master_log_pos=154;


start slave;
show slave status\G;


查看
//Master
mysql -uroot -p'123456'
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SHOW GLOBAL VARIABLES LIKE '%semi%';
show status like 'rpl_semi%';


//Slave
mysql -uroot -p'123456'
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
stop slave;
start slave;
SHOW GLOBAL VARIABLES LIKE '%semi%';
show status like 'rpl_semi%';
show slave status\G;


//设置延迟
stop slave;
change master to master_delay=0;
start slave;