Linux环境MySQL5.7主从同步配置

主服务器IP：1.1.1.1
从服务器IP：2.2.2.2

1.修改主服务器的主数据库/etc/my.cf配置

[mysqld]
#打开日志(主机需要打开)
log-bin=mysql-bin
#服务器id
server-id=1
#给从机同步的库
binlog-do-db=shenxianyun_console
binlog-do-db=shenxianyun_portal
binlog-do-db=test
#自动清理1天前的log文件
expire_logs_days=1

2.修改从服务器的从数据库/etc/my.cf配置

[mysqld]
#服务器id
server-id=2
#要从主机同步的库
replicate-do-db=shenxianyun_console
replicate-do-db=shenxianyun_portal
replicate-do-db=test

3.修改之后，重启MySQL主数据库和MySQL从数据库的服务

service mysqld restart

4.配置主服务器的主数据库

主数据库授权同步账户

GRANT REPLICATION SLAVE ON *.* TO 'root'@'2.2.2.2' IDENTIFIED BY 'XXXXXX';

刷新权限

FLUSH PRIVILEGES;

查看主服务状态

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+---------------------------------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB                                | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------------------------------------+------------------+-------------------+
| mysql-bin.000001 |      594 | shenxianyun_console,shenxianyun_portal,test |                  |                   |
+------------------+----------+---------------------------------------------+------------------+-------------------+
1 row in set (0.00 sec)

5.配置从服务器的从数据库

配置和改变slave服务器用于连接master服务器的参数【注意，MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=594是通过前面的主数据库SHOW MASTER STATUS;得到】

CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_USER='root', MASTER_PASSWORD='XXXXXX',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=594;

开启SLAVE同步

start slave;

查看下slave状态

show slave status \G;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 123.56.217.82
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 594
               Relay_Log_File: iZu1xmovyagZ-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: shenxianyun_console,shenxianyun_portal,test
          Replicate_Ignore_DB:

           Replicate_Do_Table:

当Slave_IO_Running和Slave_SQL_Running都为Yes，才说明主从复制成功

6.停止SLAVE同步

stop slave;

7.撤销已经赋予给MySQL同步账户的权限

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

GRANT REPLICATION SLAVE ON *.* TO 'root'@'2.2.2.2' IDENTIFIED BY 'XXXXXX';

REVOKE REPLICATION SLAVE ON *.* FROM 'root'@'2.2.2.2';