MySQL主从数据库配置

Mysql 的复制有：

1.一主一从 
2.一主多从 
3.级联复制（A->B->C） 
4.多主多从(环形复制 A->B->C->A) 
5.multi-master（多主一从 5.7版本后才支持）

一、MYSQL主从配置（一主一从，一主多从）

1.创建主数据库用于数据同步用户

Create user 'master'@'%' IDENTIFIED BY 'master';

2.分配同步权限

同步帐号必须要有 REPLICATION SLAVE 权限才能完成同步（复制权限）

GRANT REPLICATION SLAVE ON . TO 'master'@'%' ;

(1,2步合并简写 GRANT REPLICATION SLAVE ON . TO 'master'@'%' IDENTIFIED BY 'master' ;)

3.配置master数据库

编辑my.cnf 在 [mysqld]下添加以下代码

server-id=1 //给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号 
log-bin=mysql-bin 
#log-bin-index=mysql-bin.index 
read-only=0 
#binlog-do-db=test 
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema 
auto-increment-offset=1 
auto-increment-increment=2

4.重启master数据库并查看master 状态

Service mysql restart 
Mysql -uroot -proot 
Show master status;

从状态中查看到如下信息

File|Position| Binlog_Do_DB | Binlog_Ignore_DB|Executed_Gtid_Set | 
+-------+----------+--------------+-----------------+-------------------+ 
| mysql-bin.000001 |1753 | | mysql,information_schema,performance_schema |

File :当前正在记录的二进制日志文件 
Position ：记录偏移量，日志 mysql-bin.000006 所记录到的位置。 
Binlog_Do_DB ：要记录日志的数据库 
Binlog_Ignore_DB ：不记录日志的数据库 
Executed_Gtid_Set ：已执行的事务ID

File 和Position 信息在配置从数据库连接主数据库的的时候需要

5.配置从数据库（slave）

server-id=2 
#relay-log-index=slave-relay-bin.index 
#relay-log=slave-relay-bin 
binlog_format = mixed 
read-only=0 
#replicate-do-db=test 
replicate-ignore-db=mysql 
replicate-ignore-db=information_schema 
replicate-ignore-db=performance_schema 
relay_log=mysql-relay-bin 
log-slave-updates=on 
#auto-increment-offset=2 
#auto-increment-increment=2

6.重启从数据库 并连接主数据库

Service mysql restart

下面连接Master

Mysql -uroot -proot 
change master to master_host='192.168.0.104', //Master 服务器Ip 
master_port=3306, 
master_user='repl', 
master_password='mysql',  
master_log_file='master-bin.000001',//Master服务器产生的日志 
master_log_pos=0;

7.启动从数据库复制

Start slave;

配置参数说明： 
log-bin ：需要启用二进制日志 
server_id : 用于标识不同的数据库服务器

binlog-do-db : 需要记录到二进制日志的数据库 
binlog-ignore-db : 忽略记录二进制日志的数据库 
auto-increment-offset :该服务器自增列的初始值。 
auto-increment-increment :该服务器自增列增量。

replicate-do-db ：指定复制的数据库 
replicate-ignore-db ：不复制的数据库 
relay_log ：从库的中继日志，主库日志写到中继日志，中继日志再重做到从库。 
log-slave-updates ：该从库是否写入二进制日志，如果需要成为多主则可启用。只读可以不需要。

如果为多主的话注意设置 auto-increment-offset 和 auto-increment-increment 
如上面为双主的设置： 
服务器 152 自增列显示为：1,3,5,7,……（offset=1，increment=2） 
服务器 153 自增列显示为：2,4,6,8,……（offset=2，increment=2）

注意：自己搭建的虚拟主机中 mysql的 auto.cnf中的uuid会重复，请删除auto.cnf(/var/lib/mysql/auto.cnf),然后重启mysql

二、两个主机mysql 互相

1.同时修改两个数据库配置信息（my.cnf）

server_id=176 
log-bin=mysql-bin 
binlog_format = mixed

binlog-ignore-db=mysql 
binlog-ignore-db=information_schema 
binlog-ignore-db=performance_schema 
#binlog-do-db=test

#replicate-do-db=test 
replicate-ignore-db=mysql 
replicate-ignore-db=information_schema 
replicate-ignore-db=performance_schema

log-slave-updates #必加(两个数据库都加，将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中)

slave-skip-errors=all 
skip-name-resolve 
sync_binlog=1 #必加(两个数据库都加) 
auto-increment-offset=2（两个服务器值设置不同） 
auto-increment-increment=2

2.重启数据库服务器

见 一主一从 配置步骤说明

3.分别为两台服务器数据库创建同步用户,并设置好权限

见 一主一从 配置步骤说明

4.同步数据库数据

见 一主一从 配置步骤说明

5.分别查看两台服务器数据库的master信息

见 一主一从 配置步骤说明

6.分别设置master连接信息

见 一主一从 配置步骤说明

7.分别启动slave;

见 一主一从 配置步骤说明

可能出现的问题

1.同步失败，通过show slave status; 看到 Could not find first log file name in binary log index file信息

解决方法： 
(1)先进入slave中执行:"stop slave;"来停止从库同步； 
(2)进入master数据库中执行 flush logs; 
(3)在master数据库中查看日志信息 
show master status; 
(4)在slave数据库中修改同步日志名称和位置 ，从新启动同步；

2.不重要的错误导致同步失败，并停止同步

解决方法： 
设置跳过同步错误方法：每个错误都有对应的错误code，可以通过show slave status;查看 
修改slave数据库的my.cnf 在[mysqd]中添加 
slave_skip_errors=1032,1064 #1032和1064就是两个同步错误的code 
如果设置为all 则所 有错误都将跳过

3.start slave;时出现

Slave failed to initialize relay log info structure from the repository 
解决方法： 
reset slave all清空所有的复制信息，然后重置gtid_purged和master.infor