Mysql主从复制原理及详细步骤

1)mysql主从复制原理

1.从库的IO线程向主库的主进程发送请求，主库验证从库，交给主库IO线程负责数据传输;

2.主库IO线程对比从库发送过来的master.info里的信息，将binlog文件信息，偏移量和binlog文件名等发送给从库

3.从库接收到信息后，将binlog信息保存到relay-bin中，同时更新master.info的偏移量和binlog文件名

4.从库的SQL线程不断的读取relay-bin的信息，同时将读到的偏移量和文件名写道relay-log.info文件，binlog信息写进自己的数据库，一次同步操作完成。

5.完成上次同步后，从库IO线程不断的向主库IO线程要binlog信息

6.从库如果也要做主库，也要打开log_bin 和log-slave-update参数

2)详细步骤

Master:

1.打开binlog

log-bin= mysqlbin_log #主库一定要打开binlog,从库可开可不开

server-id = 1 #每个mysql实例最好要有不同的server-id

mysql -uroot -phaha123 -e "show variables like 'log_bin';" #检查

mysql -uroot -phaha123 -e "show variables like 'server_id';"

2.建立从库备份账户

grant replication slave on *.* to 'rep'@'192.168.1.%' identified by 'haha123';

flush privileges;

3.做全备份

mysqldump -uroot -phaha123 -A -B --master-data=1 -x > /opt/all.sql

Slave：

1.全备份恢复

mysql -uroot -phaha123 < /opt/all.sql

2.查看binlog的位置状态，执行以下命令生成master.info

show master status;

CHANGE MASTER TO

MASTER_HOST='192.168.200.131',

MASTER_PORT=3306,

MASTER_USER='re0',

MASTER_PASSWORD='haha123',

MASTER_LOG_FILE='mysql-bin.00008',#全备master-data=1,这两项可以没有

MASTER_LOG_POS=338;

3.start slave

4.检查show slave status;

完毕

**如果主库重启，从库需要重新开启slave

stop slave;start slave;

3)故障总结

故障1：CHANGE MASTER 时多了空格错误

Last_IO_Error:Got fatal error 126 from master when reading data from binary log:‘Could

not find first log file name in binary log index file'

MATER_LOG_FILE=' mysqlbin-00001 '

故障2: IO线程 YES

SQL线程 NO

stop slave;

set global sql_slave_skip_counter=n;

start slave;

4)忽略Mysql主库授权表同步

在主库master的配置文件my.cnf中添加以下内容

binlog-ignore-db = mysql

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

5)从库做主库级联从库

如果从库需要做另一个从库的主库，则需要在配置文件中设置如下参数

log-slave-updates

log-bin = /data/3307/mysql-bin

expire_logs_days = 7

slave端正常配置即可

6)master宕机，slave切换master步骤

1.选择slave

选择跟master数据最接近的slave作为master

看master.info

在每个slave上执行stop slave io_thread;

show processlist;

直到看到has read all relay log;表示从库更新执行完毕

2.登录slave

stop slave;

retset master;

3.进入slave数据库data目录，删除master.info relay-log.info

4.提升slave为master

log-bin = mysql-bin #打开binlog

如果存在log-slave-updates read-only 等一定注释它

5.如果master宕机，则需要去master拉取binlog补全

6.其他slave操作

登录slave

stop slave;

CHANGE MASTER TO MASTER_HOST = '192.168.1.112';如果不同步，指定位置点

start slave;

show slave status\G;

7.修改程序配置文件从主库指向新主库

平时访问数据库用域名，则可以直接修改hosts解析

7)Mysql主从复制读写分离多种方案案例详解

1.连接用户权限控制(只有读权限)

法一:

主库:web oldboy123 10.0.0.1 3306 (select，insert,delete,update)

从库:主库的web用户同步到从库，然后回收insert,delete,update权限或者

不回收从库权限，设置read-only参数确保从库只读

法二:

主库:web_w oldboy123 10.0.0.1 3306 (select，insert,delete,update)

主库:web_r oldboy123 10.0.0.2 3306 (select)

风险;web_w连接从库，设置read-only参数确保从库只读，对super,all privileges权限无效

法三:

主库:web_w oldboy123 10.0.0.1 3306 (select，insert,delete,update)

主库:web_w oldboy123 10.0.0.2 3306 (select)从库不同步主库的mysql授权表

2.web程序写操作指向主库，读操作指向从库

3.让从库只能读不能写

4.代理

https://www.2cto.com/database/201712/705020.html