【mysql】【03】mysql主从复制

1.机器

主机
192.168.56.19
从机
192.168.56.20
192.168.56.21

2.原理

主节点binlog日志、从节点io线程、主节点binlog dump线程、从节点relaylog、从节点sql thread

1.从节点io线程和master建立连接
2.主节点启动一个binlog dump线程和这个io线程对接
3.binlog发生改变，通知到binlog dump线程，dump线程写到io线程
4.io线程写到relaylog中
5.sqlthread线程读取relaylog写从数据库

3.上锁

flush tables with lock;

4.在主机上配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema,test
replicate-ignore-db=mysql,information_schema,sys

4.1主机上执行

创建复制账号

grant replication slave on *.* to 'copyer'@'192.168.1.26' IDENTIFIED BY '123';
grant replication slave on *.* to 'copyer'@'192.168.1.27' IDENTIFIED BY '123';
flush privileges;

查看复制的位置
service mysqld restart
mysql -uroot -pxxx
show master status\G;

记录下来log_file 和 log_pos

4.2备份主库的数据到从库上

备份数据
mysqldump -u root -p123456 test1>d:\test1.sql

恢复数据
mysql -u root -p123456
create database test1;
use test1;
source d:\test1.sql

或者
mysql -u root -p123456 test1<d:\test1.sql

5.在从机上配置

vim /etc/my.cnf
[mysqld]
server-id=2
read_only = 1

5.1从机上执行

查看是否可以连接上
mysql -h192.168.56.19 -u copyer -p123
连接从机
mysql -uroot -pxxxx
停止复制
stop slave;
设置复制位置
change master to master_host='192.168.1.25' ,master_user='copyer' ,master_password='123', master_log_file='mysql-bin.000001',master_log_pos=154;

开始复制
start slave;

6.解锁

unlock tables;

7.查看复制是否成功

show slave status\G;