MySQL主从配置

准备条件

主服务器：192.168.10.10
从服务器：192.168.10.11

1、主服务器SQL配置

找到 MySQL 配置文件 /etc/my.cnf 或者 /etc/my.ini

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #设置server-id
binlog-do-db=test #配置要同步的数据库

切换到 MySQL Shell

# 添加一个mysql用户
create user 'repl'@'192.168.10.11' identified by 'IF#PnBM@FN!Wm2OY';

# 分配权限
grant Replication slave on *.* to 'repl'@'192.168.10.11';

# 刷新
Flush privileges;

# 查看主服务MySQL的binlog位置
SHOW MASTER STATUS；

# 当前主服务器 MySQL binlog信息
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1674 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从服务器SQL

同样找到 my.cnf 配置文件

[mysqld]
server-id=2 #设置server-id， 必须唯一

切换到 MySQL Shell,通过刚才建立的mysql用户关联主服务器

change master to
master_host='192.168.10.10',
master_user='repl',
master_password='IF#PnBM@FN!Wm2OY',
master_log_file='mysql-bin.000003',
master_log_pos=156;
# 后面的逗号一定不能少

# 启动同步
start slave;
# 如需修改主从关联信息，需要先 stop slave；

# 查看同步信息
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.10.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2022
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: