Linux下配置Mysql主从复制（一主一从）

注：CentOS7 Mysql5.7

1. 主从服务器概况

• 主：IP:192.168.31.7 PORT:3306

• 从：IP:192.168.31.99 PORT:3306

2. 修改主服务器master

# vi /etc/my.cnf

[mysqld]
server-id = 1              # 节点ID,确保唯一

# log config
log-bin = mysql-bin        # 开启mysql的binlog日志功能
sync_binlog = 1            # 控制数据数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

3. 修改从服务器slave

server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
#以下参数为过滤掉某些表以不记录到从服务器
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%

4. master数据库，创建复制用户并授权

• root进入master数据库，为master创建复制用户

CREATE USER repl_user IDENTIFIED BY 'repl_passwd';

• 授权

grant replication slave on *.* to 'repl_user'@'192.168.31.99'  identified by 'repl_passwd';

FLUSH PRIVILEGES;

• 查看master状态

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

注意：注意结果中File和Position

5. slave数据库

• root进入slave数据库

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.31.7',              #这里填master的IP
    -> MASTER_USER = 'repl_user', 
    -> MASTER_PASSWORD = 'repl_passwd',           
    -> MASTER_PORT = 3306,                        #这里填master的PORT
    -> MASTER_LOG_FILE='mysql-bin.000001',        #这里填第4步中的File
    -> MASTER_LOG_POS=1928,                       #这里填第4步中的Position
    -> MASTER_RETRY_COUNT = 60,
    -> MASTER_HEARTBEAT_PERIOD = 10000; 
Query OK, 0 rows affected, 3 warnings (0.02 sec)

• 启动slave

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

• 检查slave服务器复制功能状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.7          
                  Master_User: repl_user
                  Master_Port: 3306                  
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001       
          Read_Master_Log_Pos: 1928                 
               Relay_Log_File: mysql-relay-bin.000144
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
        #************************下面必须是两个YES**********
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        #*************************************************

测试

master

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

mysql> use test1;
Database changed
mysql> create table test(id int(3),name char(10));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values(001,'aaa');
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.01 sec)

mysql> exit
Bye

slave

[root@localhost src]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
+------+------+
1 row in set (0.00 sec)

mysql> exit
Bye

问题处理

如果slave状态检查不是两个YES，则按照如下步骤排查

• 网络问题

  两服务器是否互相ping通，3306端口是否telnet通

• 账户密码问题

  检查master服务器是否可以登录repl_user用户

• 防火墙

#  开放端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#  重新载入
firewall-cmd --reload

#  检查是否生效
ssh -v -p 3306 root@192.168.31.7   # 看到debug1: Connection established.即为生效。

• mysql配置文件问题

  • 根据第3 、4步检查配置文件是否正确

• 配置文件中注释掉bind-address

• 修改skip-networking参数为1

• 连接master时参数

  • 根据5.1步骤检查参数是否正确

• 权限

  尝试检查master中用户权限

# 首次检查结果如下，如果前面都没问题，可按照下面修改权限
mysql> show grants for repl_user;
+---------------------------------------+
| Grants for repl_user@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'repl_user'@'%' |
+---------------------------------------+
1 row in set (0.00 sec)

# 授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' identified by 'repl_passwd';
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 再次检查结果如下
mysql> show grants for repl_user;
+---------------------------------------------------+
| Grants for repl_user@%                            |
+---------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' |
+---------------------------------------------------+
1 row in set (0.00 sec)