mysql主从复制配置与问题解决 - 好文

    主从复制是为了加强系统数据库的可用性，当主库挂掉时，从数据库保存数据，数据不会丢失，将从库切换为主库，等主库弄好之后再替换回来，提高了项目的可用性。当然我们也可以读写分离等操作，提高系统的并发性。本博文只记录了如何进行主从复制的配置和过程中的一些问题的解决方法。

    原理：MySQL使用3个线程来执行复制功能（其中1个在【主服务器】上，另两个在【从服务器】上）
      当【从服务器】发出START SLAVE时，【从服务器】创建一个I/O线程，以连接【主服务器】并让它发送记录在其二进制日志中的语句。
    【主服务器】创建一个线程将二进制日志中的内容发送到【从服务器】。该线程可以识别为【主服务器】上SHOW
PROCESSLIST的输出中的Binlog Dump线程。

     【从服务器】I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到【从服务器】数据目录中的本地文件中，即中继日志。

      第3个线程是SQL线程，是【从服务器】创建用于读取中继日志并执行日志中包含的更新

一：安装环境
操作系统：CentOS 数据库版本：MySQL 5.7 主机A：192.168.1.1 （Master）主机B：192.168.1.2 （Slave）
这里强调的数据库的版本，是因为MySQL在5.6之前和之后的安装方式是不一样的。这里提前说明，解决方法在后面给出。

二：Master的配置

在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ，在该文件下指定Master的配置如下：
log-bin=mysql-bin #二进制日志文件，master产生，slave使用进行复制操作。 server-id=2
#给数据库服务的唯一标识，一般为大家设置服务器Ip的末尾号 binlog-ignore-db=information_schema #不复制的数据库
binlog-ignore-db=gjxy_zd binlog-ignore-db=mysql binlog-ignore-db=dk_qj_db
binlog-ignore-db=designpattern_zd binlog-ignore-db=designpattern
binlog-ignore-db=card_db binlog-ignore-db=book_db binlog-do-db=test
#复制的数据库（当该数据库有改变时，在从库中会有相对应的修改）
完整配置截图如下：

1、然后重启mysql：service mysqld restart

2、进入mysql：[root@VM_221_4_centos ~]# mysql -u root -p 回车，输入mysql密码进入。

3、赋予从库权限帐号，允许用户在主库上读取日志，赋予192.168.1.2也就是Slave机器有File权限和REPLICATION SLAVE的权限。

在Master数据库命令行中输入：
mysql>GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql
password'; mysql>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2'
IDENTIFIED BY 'mysql password'; mysql>FLUSH PRIVILEGES
其中：mysql password：为前面对应用户(此例中为root)的密码。

        192.168.1.2：为从库服务器地址。

这里使用的仍是 root 用户作为同步的时候使用到的用户，可以自己设定。

4、重启mysql，登录mysql，显示主库信息
mysql> show master status;

这里的 File 、Position 是在配置Salve的时候要使用到的，Binlog_Do_DB表示要同步的数据库，Binlog_Ignore_DB
表示Ignore的数据库，这些都是在配置的时候进行指定的。

另外：如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。

三：Slave的配置

1、从库的配置，首先也是修改配置文件：/etc/my.cnf 如下：
log-bin=mysql-bin server-id=3 binlog-ignore-db=information_schema
binlog-ignore-db=sjj binlog-ignore-db=mysql binlog-ignore-db=cardapp

2、这里可以看到，在MySQL5.6之后的版本中没有指定
master-host=192.168.1.1 #Master的主机IP master-user=root master-password=mysql
password #Master的MySQL密码 #以上的这几个语句在mysql5.6之前写在配置文件中，mysql5.6之后采用下面所介绍方式。
3、这也是在网上很多搜索的配置过程，他们也都指定了数据库的版本，但是并没有说出来新版本的配置这种方式是不适用的。

4、如果，你在MySQL5.6和之后的版本中配置从库的时候，设置到了上边的内容，即指定了master-host、master-user等信息的话，重启MySQL的时候就会报错

5、在5.6以及后续版本的配置如下：

修改完/etc/my.cnf 文件之后（不将master-*写入），重启一下MySQL（service mysqld restart）

进入Slave mysql控制台，执行：
mysql> stop slave; #关闭Slave mysql> change master to
master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',
master_log_pos=28125; mysql> start slave; #开启Slave
在这里指定Master的信息，master_log_file是在配置Master的时候的File选项，
master_log_pos是在配置Master的Position 选项，这里要进行对应。其中
master_password #该选项是主mysql的密码，一定不要写错，不然会出现错误！
然后可以通过mysql> show slave status; 查看配置的信息：
mysql> show slave status \G; *************************** 1. row
*************************** Slave_IO_State: Waiting for master to send event
Master_Host: 192.167.1.1 Master_User: root Master_Port: 3306 Connect_Retry: 60
Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 28125 Relay_Log_File:
VM_128_194_centos-relay-bin.000004 Relay_Log_Pos: 26111 Relay_Master_Log_File:
mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:
ufind_db Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table:
Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error:
Skip_Counter: 0 Exec_Master_Log_Pos: 28125 Relay_Log_Space: 26296
Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No
Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher:
Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:
Replicate_Ignore_Server_Ids: Master_Server_Id: 2 Master_UUID:
8ac3066a-9680-11e5-a2ec-5254007529fd Master_Info_File:
/data/mysqldb/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave
I/O thread to update it Master_Retry_Count: 86400 Master_Bind:
Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl:
Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1
row in set (0.00 sec) ERROR: No query specified mysql>
也可能是下面这种形式：

这样的话，我们可以将输出的信息，复制到一个高级笔记本中，就可以看到如下形式：

其中的信息和第一种内容相同，知识表达形式不同而已。

如果信息中的下面两项都是Yes的话，表示配置成功！如果是No或者Connecting请看下面的错误解答。
Slave_IO_Running: Yes Slave_SQL_Running: Yes
四：添加需要同步的从库Slave

由于种种原因，测试的时候使用test库，这里我按照上述的方式，修改Master的my.cnf的配置文件，新增同步的数据库test，重启MySQL,执行Master的：
show master status如下：

相应的,要修改Slave从库的信息在my.cnf 增加 replicate-do-db=test，重启Mysql，根据上述的show master
status，在Slave从库中执行下边的内容：
mysql>stop slave mysql>change master to master_host='192.168.1.1',
master_user='root', master_password='123456',
master_log_file='mysql-bin.000005', master_log_pos=120; mysql>start slave
其中的master_password与master_log_file选项要与新查询到的master中的对应！！！

五：测试

    在master中创建一个数据库（在配置好之后再创建，如果在配置好之前创建的，我们可以手动在slave中创建对应
的数据库，当我们对master的该数据库添加新表时，slave也会创建相同的表），并在该库中创建一个表格，输入一些数据，若配置没有问题的话，slave会有相同的数据产生。

六：错误解决

1.在master中新查询的Position选项一直变化


首先，这是正常的，该值表示File中显示的二进制文件的偏移值，你只要在你配置的需要同步赋值的数据库进行操作时，该偏移值就会变化，只要注意在初始配置slave的时候对应就可以，其他不用管这方面，slave会动态的跟随变化。

2.Slave_IO_Running选项一直为：Connecting或者No

    可能4方面原因：1.网络有问题。

                              2.在配置slave的时候我们设置的用户（类似于上述的root的用户）没有开启远程连接。

                             3.配置slave的时候密码或者用户名书写错误（多一个空格，字母顺序颠倒啥的。。）。


4.slave配置过程中，master_log_file\master_log_pos没有与master中的对应！

3.配置成功并且测验成功后，不要在slave中手动删除表之类的操作。

例如：master和slave中的表"name"正在同步中，当你手动在slave将name表删除后，在master对name表操作后，会导致连接断开，没办法进行主从复制，因为在slave已经找不到name表了，解决方法是：重新查新master中的show
master status;在slave用change语句进行重新配置一遍(不需要修改配置文件！！！)。

4.有一个特别坑的地方，如果将你在项目中使用的数据库设置为了从库，可能你的项目就连不上数据库了，报错如下：java.sql.SQLException:
Access denied for user 'root'@'*******' (using password: YES),就是一个权限问题。

解决方法是1.换库2.将本库设置为主库3.删除mysql数据库中的user表中的******（代表的你的服务器）的哪一行，当然先备份再操作，万一不行还可以恢复。然后再删除你的slave设置,给出步骤：

mysql>stop slave;

mysql>change master to master_host=' ';

重启数据库。

热门工具换一换