mysql 主从配置

分别安装主数据库和从数据库

##下载源安装包
wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

##安装mysql源
yum localinstall mysql57-community-release-el7-11.noarch.rpm

##检查 yum 源是否安装成功
yum repolist enabled|grep "mysql.*-community.*"

##安装服务
yum install mysql-community-server

##启动MySQL

systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

修改密码之类步骤掠过

配置主数据库

vi /etc/my.cnf

[mysqld]

# 添加如下配置
# 参数必须唯一, 本例主库设置为 11 ，从库设置为 12
server_id=101
log_bin=/var/log/mysql/mysql-bin

记得检查日志目录是否存在，不存在子主动建立并授权

重启数据库

检查主库配置

mysql> show master;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

配置从库

vi /etc/my.cnf

[mysqld]

# 添加如下配置
# 参数必须唯一, 本例主库设置为 11 ，从库设置为 12
server_id=12
log_bin=/var/log/mysql/mysql-bin

重启数据库

进入从库数据库配置


mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.136',
    -> MASTER_USER='admin',
    -> MASTER_PASSWORD='admin',
    #此选项初始化设置时需要跟主库中的一致。设置好后，如果主
    #库发生重启等，不需再次设置，从库会跟着更新
    -> MASTER_LOG_FILE='mysql-bin.000001',
    # master Position的值
    -> MASTER_LOG_POS=154;

检查从库配置

mysql> show slave status \G;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.136
                  Master_User: admin
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 307
               Relay_Log_File: relay.000003
                Relay_Log_Pos: 473
        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: 307
              Relay_Log_Space: 670
              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: 101
                  Master_UUID: 22e13cbe-fc77-11e9-bb6f-000c2917f163
             Master_Info_File: mysql.slave_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: 3f897ce8-ea3b-11e9-adfc-9c5a4443c08a:1-2575
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

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

错误提示

The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON.

解决方案：这个报错是因为主库和从库配置了不同的GTID策略，修改相同即可。

启动从库

mysql> start slave;

效果演示

主库创建数据库hello2,从库自动同步创建了hello2

从库数据库示意：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bjkfq              |
| docker_mysql       |
| hello2             |
| mqtt               |
| mqtt2              |
| mqtt3              |
| my                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
11 rows in set (0.00 sec)

重点难点总结

主库和从库都要配置server_id
注意在主库中为从库设置远程访问账户和密码

over