mysql主从切换原理 mysql主从怎么切换

环境：

mysql版本：5.7.15

一：主从配置

1.配置主库

a.首先查看主服务器的版本是否是支持热备的版本。然后查看my.cnf(类unix)或者my.ini(windows)中Mysqld配置块的配置有没有log-bin(记录数据库更改日志)，因为Mysql的复制机制是基于日志的复制机制，所以主服务器一定要支持更改日志才行。然后设置要写入日志的数据库或者不要写入日志的数据库。这样只有您感兴趣的数据库的更改才写入到数据库的日志中。

2 备份主库数据

linux位置：vim /etc/my.cnf 

 win7位置：D:\server\mysql\my.ini #mysql安装目录下 

 server-id=1 //要求不同数据库要唯一 

 log-bin=log_name //日志文件的名称，也可以设置为：log_bin 

 binlog-do-db=db_name //记录日志的数据库 

binlog-ignore-db=db_name //不记录日志的数据库 
binlog_format=mixed //指定记录格式(STATEMENT，ROW，MIXED)，推荐混合模式mixed
 以上的如果有多个数据库用","分割开 

 service mysql restart(重启,让配置文件生效) 


 设置同步数据库的用户帐号 

 Mysql> GRANT REPLICATION SLAVE ON *.* TO 'user_slaver_235'@'192.168.1.235' IDENTIFIED BY 'uei123'; 

 flush privileges;

3，设置从服务器

Mysql> FLUSH TABLES WITH READ LOCK; #加只读锁 

 Mysql> show master status; 


 +------------------+----------+--------------+------------------+-------------------+ 

 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 

 +------------------+----------+--------------+------------------+-------------------+ 

 | mysql-bin.000002 |      120 | ueitest      |                  |                   | 

 +------------------+----------+--------------+------------------+-------------------+ 

 记录File 和 Position 项目的值，以后要用的。 


 root> mysqldump -h192.168.1.241 --hex-blob -uroot -p123456 --opt -q -R --add-locks --default-character-set=[gbk,UTF8] --master-data  ueitest>/data/ueitest20091118.sql #文件默认会生成在当前操作文件夹,如果sql导入时出现错误，考虑字符集 
(或者多个数据库用mysqldump -uroot -ppassword  --databases uei uei2> dbnames.sql) 



 Mysql> unlock tables;  #解锁

a.修改从库配置文件 

 vim /etc/my.cnf 

 server-id=11 #要求不同数据库要唯一 


 root> service mysql restart #让配置文件生效 
#以下配置推荐
 master-connect-retry=60 //如果从服务器发现主服务器断掉，重新连接的时间差(从库可不配) 

 report-host=db-slave.mycompany.com //报告错误的服务器 


 #以下配置选配(5.5等高版本已不支持该方法，建议废弃) 用CHANGE MASTER TO 来配置C步骤 

 master-host=192.168.1.220 //主服务器的IP地址或者域名 

 master-port=3306 //主数据库的端口号 

 master-user=pertinax //同步数据库的用户 

master-password=freitag //同步数据库的密码

b.导入数据
1,把从主数据库服务器备份出来的数据库导入到从服务器中
从库上运行： scp root@192.168.1.241:/data/ueitest20091118.sql  /data/ 
2,Mysql> slave stop; 或者 stop slave;//停止slave的服务 

3,mysql -h192.168.1.235 -uroot -p --default-character-set=[gbk,UTF8] ueitest</data/ueitest20091118.sql #如提示没有该数据库，请新建
(多个数据库还原用登录mysql后直接执行 	mysql> source /data/uei10161018.sql，不用create database语句） 
导入过程出现异常，应先处理好异常，在继续操作： 

   1，ERROR 2006 (HY000) at line 4426: MySQL server has gone away 

   修改参数： 

   mysql> set global max_allowed_packet=1024*1024*16; 

   mysql> show global variables like 'max_allowed_packet'; 

   2，其他数据异常，考虑字符集原因。 


 c.设置主服务器的各种参数 

 Mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.241', MASTER_USER='user_slaver_235', MASTER_PASSWORD='uei123', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120; 


 d.启动同步数据库的线程 

 Mysql> slave start;  或者 start slave; 



 5,主库，从库查看状态和测试： 

 1、查看master的状态 

 show master status;  //Position不应该为0 

 show processlist; 

 //state状态应该为Has sent all binlog to slave; waiting for binlog to be updated 

 2、查看slave状态 

 show slave status \G 

 //Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes 

 show processlist; 

 //应该有两行state值为： 

 Has read all relay log; waiting for the slave I/O thread to update it 

 Waiting for master to send event 



以上操作完成主从配置XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
其他配置：启动从库的时候不想启动slave，在配置文件中mysqld下添加：skip-slave-start
 #修复MYISAM表
 myisamchk -r -q jir_businesssurveys.MYI

 CHANGE MASTER TO MASTER_HOST='211.192.168.229', MASTER_USER='userup', MASTER_PASSWORD='data100back', MASTER_LOG_FILE='mysqlbinlog.000315','MASTER_LOG_POS=633755384;


 ALTER TABLE jir_surveyresponses CHANGE xml xml longtext CHARACTER SET GBK;//modify table colum charset


 grant select on survey to survey@'%' IDENTIFIED BY 'survey' WITH GRANT OPTION;

 grant select on survey to survey@%,IDENTIFIED BY "survey";

 set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;


 GRANT ALL ON *.* TO 'survey'@'%' IDENTIFIED BY 'survey' WITH GRANT OPTION;
 GRANT REPLICATION SLAVE ON *.* TO 'userup'@'211.192.168.229' IDENTIFIED BY 'data100backup';

 mysqlfront create table 能同步 增加字段能同步

 mysqladministrator　　增加，不能同步



 mysql> show master status;
 +--------------------+----------+--------------+------------------+
 | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +--------------------+----------+--------------+------------------+
 | mysqlbinlog.000026 |     9143 | survey       |                  |
 +--------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

 CHANGE MASTER TO MASTER_HOST='211.192.168.232', MASTER_USER='userup', MASTER_PASSWORD='data100back', MASTER_LOG_FILE='mysqlbinlog.000026','MASTER_LOG_POS=9143;


 mysqlbinlog mysqlbinlog.000027

二，主从切换

A主，B、C从。A故障，B替换为主。

1，A故障后，确保B、C已执行relay log中的全部更新。

B执行:   stop slave IO_THREAD; #停止slave线程继续读取新日志；

              show processlist \G #看到State:Has read all ……. 说明日志已经更新完成。

2,B上执行：

    stop slave;

    reset master; #此时报Binlog没有设置，执行失败。关闭B库，修改/etc/my.cnf ,在配置文件中添加log-bin选项([mysql]后面)：log-bin=/usr/local/var/mysql/mysql-bin 。配置后重启，登录，继续执行：stop salve; reset master; #此时B库已为主库。

3，B库上添加replication权限用户repl,查询主库状态：

    

grant replication slave on uei.* to   'repl'@'localhost' identified by '123';
    show master status;

4,C从库上配置参数

   

change master to 
    --> master_host='127.0.0.1',
    -->master_user='repl',
    -->master_password='123',
    -->master_port=3307,
    -->master_log_file='mysql-bin.000002',
    -->master_log_pos=98;
    start slave;

5,C从库执行：

 

show slave status \G       #查看状态

6，sql语句校验，B库执行修改语句，C库查看数据变更。

(主从切换完成)