mysql数据库主从配置与主从切换（在文章底）

环境：

mysql版本：5.7.15

一：主从配置

1.配置主库

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


2 备份主库数据
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;  #解锁



3，设置从服务器

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库查看数据变更。

(主从切换完成)