mysql主从分离

一、原理

1.主数据库,对数据的操作记录到Binary log中
2.从数据库开启工作线程I/O thread ，将Binary log拷贝到Relay log
3.SQL thread将数据写入从数据库

 

二、操作配置

主服务器(192.168.1.14)配置：
/etc/my.cnf
[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

从服务器(192.168.1.17)配置
/etc/my.cnf
[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

主库
mysql>create user repl;
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.7' IDENTIFIED BY 'mysql';
mysql>flush privileges;

从库(如果从库挂了，修改master_log_pos获取)
mysql>change master to master_host='192.168.1.14',master_port=3306,master_user='repl',master_password='mysql',master_log_file='master-bin.000003',master_log_pos=2355;
mysql>start slave;
mysql>show slave status \G;