MySQL主写从读

从库生成两个线程，一个I/O线程，一个SQL线程； i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中； 主库会生成一个 log dump 线程

1）主库添加账户 

GRANT REPLICATION SLAVE,FILE ON *.* TO 'mstest'@'47.92.27.140' IDENTIFIED BY '123456';
flush privilige
2）主库修改配置文件   vim /etc/mysql/mysql.conf.d/mysqld.cnf
查看状态：SHOW MASTER STATUS;
server-id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size= 100M
binlog_do_db        = wenquan
binlog_ignore_db    = mysql
设置读锁：FLUSH TABLES WITH READ LOCK;
导出master所有数据：mysqldump --all-databases --master-data -uroot -p > /tmp/dbdump.db
解锁：UNLOCK TABLES;
3）从库修改配置文件   vim /etc/mysql/mysql.conf.d/mysqld.cnf
查看状态：show slave status 
<!-- CHANGE MASTER TO
MASTER_HOST='IP',
MASTER_USER='mysqlUser',
MASTER_PASSWORD='mysqlPassword',
MASTER_LOG_FILE='日志File',
MASTER_LOG_POS=日志Position; -->
skip-slave-start=true
read_only=ON
relay-log=relay-bin
relay-log-index=relay-bin.index
4）启动从库
change master to master_host='120.77.244.96', master_user='mstest',master_password='123456';
start slave;
stop slave;reset slave;取消从库