mysql主从复制配置

mysql主从复制配置

准备两台服务器，一台作为主库，一台作为从库。

主库：192.168.1.1
从库：192.168.1.2

#确保主数据库服务器的mysql可以通过外网访问,也就是可以用navicat连接到。

主数据库配置：

1. 创建一个用户
   create user rep1;

2. 给用户赋予权限

GRANT REPLICATION SLAVE ON . to ‘rep1’@‘192.168.1.2’ identified by ‘root’;

rep1: 新创建用户名称
192.168.1.2：是使用这个用户的服务器ip地址
root:设置新创建用户访问的密码

3. 修改mysql的配置文件，my.cnf（window系统时my.ini）
   server-id=1
   binlog-do-db=test

test:这是需要同步的数据库，test是一个数据库，需要自己创建
server-id：用于区分主从库，主从库设置的值不能相同
修改完配置之后，需要重新启动mysql服务，systemctl restart mysql
重启之后，可以通过 show master status 命令去查看是否成功

从库配置

如果mysql的版本大于5.5
打开mysql.in

server-id=2 				//不能与主库配置的值相同
　　　　master-host=192.168.1.1  //主数据库的ip
　　　　master-user=rep1     　　//第一步创建账号的用户名
　　　　master-password=root   //第一步创建账号的密码
　　　　master-port=3306
　　　　master-connect-retry=60
　　　　replicate-do-db=test        //要同步的mstest数据库,要同步多个数据库，就多加几个replicate-db-db=数据库名

mysql的版本小于5.5

打开mysql.ini添加一个server_id，随便写一个不重复的id
因为版本小于5.5的不能使用直接配置的方法，只能采用命令行的方式配置

CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='root',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=107;

slave start;
show slave status\G;

mysql> show slave status \G:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: VM_0_12_centos-relay-bin.000005
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           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: 107
              Relay_Log_Space: 564
              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
1 row in set (0.00 sec)

Slave_IO_Running Slave_SQL_Running 都为 YES，则代表，配置成功

Slave_IO_Running 显示为NO,可能存在原因为server-id主从库配置的值一致，或者配置未生效、配置完后未重启mysql服务所导致

Slave_SQL_Running 显示为NO
可能是程序在slave上进行了写操作，也可能是重启后事物回滚导致的
解决办法一：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二：
在从库服务器：slave stop; 停掉slave服务

到主服务器上查看主机状态：
记录File和Position对应的值

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后回调从库服务器，将对应的值，改为主服务器所对应的值后，start slave; 启动slave服务即可。