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数据库data目录，删除master.info relay-log.info


3.提升slave为master
log-bin = mysql-bin #打开binlog
如果存在log-slave-updates read-only 等一定注释它

4.登陆slave

stop slave;

reset master;


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.代理

mysql主从同步原理要点:
1.异步方式同步
2.逻辑同步模式，可采取多种模式进行同步，默认是通过SQL语句执行
3.主库通过记录binlog实现对从库的同步
4.主库一个线程，从库2个线程完成
5.从库关键文件master.info,relay-bin,relay-log
6.从库还想级联从库，要打开log_bin和log-slave-update参数