如何使用MySQL的主从复制

一、说明

1. 查找my.cnf的地址

   [root@houxin etc]# whereis my.cnf
   my: /etc/my.cnf
   

   可以得知，我的my.cnf路径为：/etc/my.cnf

2. 主从服务器配置
   主服务器[121.58.66.17]：
   mysql版本5.7

   server-id=1
   

   从服务器[210.129.115.121]：
   mysql版本5.7

   server-id=2
   

3. mysql的用户账号
   在没有说明的情况下，都是使用root用户登录的。
   这里不讲初始数据的同步，所以建议，将主服务器需要同步的数据库内容进行备份一份，上传到从服务器上，保证始初时两服务器中数据库内容一致。或者两个数据库都是空的。

二、主服务器Master配置

1. 在主服务器为从服务器创建一个用户，并授予REPLICATION SLAVE权限。
   进入mysql操作界面

   mysql> grant replication slave on *.* to 'replicate'@'%' identified by '123456';
   

   赋予权限

   mysql> flush privileges;
   

   这里创建的用户名为replicate，密码为123456，不限制主机IP。

2. 使用从服务器测是否登录成功

   [root@houxin etc]# mysql -h121.58.66.17 -ureplicate -p123456
   

3. 修改主服务器的配置
   配置文件地址：/etc/my.cnf

   [mysqld]
   
   server-id = 1
   

   server-id默认为1的，如果是1的话，就不用改动。

4. 重启mysql服务

   [root@houxin etc]# service mysql restart
   Redirecting to /bin/systemctl restart mysql.service
   

5. 查看主服务器状态

   mysql> show master statusG
   *************************** 1. row ***************************
                File: mysql-bin.000019
            Position: 1646
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
   Executed_Gtid_Set: 
   1 row in set (0.00 sec)
   

   File：是指当前保存的二进制日志文件
   Position：表示当前写入该日志文件的位置

   由于我们没有旧数据，所以我们就从当前的该日志文件的当前位置开始同步就行了。记录当前的文件名File和Position，配置从服务器的时候，需要这些参数。

   Position 如果不写。你的从服务器与主服务器断开，又重新链接的时候，就会从当前位置同步数据，这样就可能丢失，之前的数据。有Position的时候，从服务器知道自己是以哪个日志文件的哪个位置开始同步的。不会丢失期间的数据。

三、从服务器Slave配置

1. 修改从服务器配置文件
   直接打开配置文件my.cnf进行修改

   [mysqld]
   server-id = 2
   log-bin=mysql-bin
   replicate-do-db = master
   replicate-ignore-db = mysql,information_schema,performance_schema
   

   这里修改的内容比主服务器多了几项。

   server-id : 服务器id，此值唯一。
   log-bin: 同步的日志文件
   replicate-do-db: 同步的数据库名
   replicate-ignore-db：忽略的数据库

2. 重启mysql服务

   [root@ebs-31911 etc]# service mysql restart
   Redirecting to /bin/systemctl restart mysql.service
   

3. 指定从服务器同步主服务器的位置

   • 先停止slave服务线程

     mysql> stop slave;
     Query OK, 0 rows affected (0.00 sec)
     

   • 配置主服务器

     mysql> change master to master_host='121.58.66.17', master_user='replicate',master_password='123456', master_log_file='mysql-bin.000019',master_log_pos=1646;
     

     注：master_log_file, master_log_pos由主服务器查出的状态值中确定。就是主服务器配置的第5步骤的参数
     master_log_file对应File, master_log_pos对应Position。
     Mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。

   • 启动slave服务线程

     mysql> start slave;
     Query OK, 0 rows affected, 1 warning (0.00 sec)
     

4. 查看从服务器的状态

   mysql> show slave statusG
   *************************** 1. row ***************************
                  Slave_IO_State: Waiting for master to send event
                     Master_Host: 121.58.66.17
                     Master_User: replicate
                     Master_Port: 3306
                   Connect_Retry: 60
                 Master_Log_File: mysql-bin.000019
             Read_Master_Log_Pos: 9719
                  Relay_Log_File: ebs-31911-relay-bin.000002
                   Relay_Log_Pos: 8393
           Relay_Master_Log_File: mysql-bin.000019
                Slave_IO_Running: Yes
               Slave_SQL_Running: Yes
                 Replicate_Do_DB: master
             Replicate_Ignore_DB: mysql,information_schema,performance_schema
              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: 9719
                 Relay_Log_Space: 8604
                 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: 1
                     Master_UUID: 6197e1ca-60f3-11ea-b495-00163e02b6df
                Master_Info_File: /home/server/data/master.info
                       SQL_Delay: 0
             SQL_Remaining_Delay: NULL
         Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              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
            Replicate_Rewrite_DB: 
                    Channel_Name: 
              Master_TLS_Version: 
   1 row in set (0.00 sec)
   

   查看下面两项值均为Yes，即表示设置从服务器成功。
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes

   如果发现测试不同步，下面四个选项会有响应错误提示

   Last_Errno: 1051
   Last_Error: Error 'Unknown table 'master.runoob_tbl'' on query. Default database: 'master'. Query: 'DROP TABLE `runoob_tbl` /* generated by server */'
   ...
   Last_SQL_Errno: 1051
   Last_SQL_Error: Error 'Unknown table 'master.runoob_tbl'' on query. Default database: 'master'. Query: 'DROP TABLE `runoob_tbl` /* generated by server */'
   

5. 测试同步
   可以在主服务器添加或者修改数据，看看从服务器是否同步成功。
   如果同步成功，就说明配置成功。
   否则的话，可以在从服务器查看先同步状态，也能发现问题。

   mysql> show slave statusG
   

四、扩展

1. 低版本的数据可能存在的问题
   在同步过程中，如果从服务器的数据，被自己擅自改动过的话。那么在同步的过程中，被改动的数据，则不受主服务器的控制了。
   解决办法：
   需要使用如下命令，在从服务器新建一个仅供查询的用户:

   GRANT Select ON *.* TO reader@"%" IDENTIFIED BY "123456"
   

   之后，从服务器对数据的操作，就使用这个用户来操作。这样杜绝了从服务器的数据被写导致同步失效的问题。保证了数据的一致性。

   在我的mysql5.7版本中，并未出现这种事情。所以mysql5.7版本的话，就不用这样操作了。
   在mysql5.7版本中，如果你私自修改了从服务器的数据，它是不会影响主服务器的数据同步哦的。即使你修改从服务器的一条记录的值，导致与主服务器不一致，但当主服务器的该条记录修改的时候，从服务器会强制同步主服务器的该条记录的值，达到主从一致的效果。