1未命名0

Mysql主从复制

柳三千

文章最后更新时间：2025年06月12日

前言：

主从复制原理基于二进制日志实现，主库需开启二进制日志。从库IO线程将主库二进制日志拷贝到自身中继日志，SQL线程从中继日志读取主库操作并执行，实现数据一致性。

主、从服务器各自安装数据库

# 环境清理
[root@localhost src]# rpm -qa | grep -i mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost src]# rpm -qa | grep -i mysql
[root@localhost src]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
[root@localhost src]# rm -rf /etc/my.cnf

# 解压安装包
[root@localhost src]# tar zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /usr/local/mysql5.7

# 创建mysql用户、目录并授权
[root@localhost src]# useradd -r -s /sbin/nologin mysql
[root@localhost src]# mkdir -p /data/mysql57/data
[root@localhost src]# mkdir -p /data/mysql57/log
[root@localhost src]# chown -R mysql.mysql /data
[root@localhost src]# chown -R mysql.mysql /usr/local/mysql5.7/

# 进入安装目录，创建配置文件
[root@localhost src]# cd ..
[root@localhost local]# cd mysql5.7/
[root@localhost mysql5.7]# vim my.cnf
[client]
port=3306
socket=/tmp/mysql57.sock

[mysqld]
socket=/tmp/mysql57.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql5.7/
datadir=/data/mysql57/data
tmpdir=/tmp
log-error=/data/mysql57/log/mysqld57.log
pid-file=/data/mysql57/mysqld57.pid

# 初始化数据库
[root@localhost mysql5.7]# /usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql5.7/ --datadir=/data/mysql57/data

# 修改启动脚本
[root@localhost mysql5.7]# vim /usr/local/mysql5.7/support-files/mysql.server
# 安装目录
basedir='/usr/local/mysql5.7'
# 数据目录
datadir='/usr/local57/data'
# 配置文件路径
conf=/usr/local/mysql5.7/my.cnf

# 拷贝启动脚本
[root@localhost mysql5.7]# cp -a ./support-files/mysql.server /etc/init.d/mysql57
[root@localhost mysql5.7]# /etc/init.d/mysql57 start
Starting MySQL.  SUCCESS! 

# 获取数据库的临时root密码
[root@localhost mysql5.7]# cat /data/mysql57/log/mysqld57.log  | grep password
2022-10-25T02:04:02.438103Z 1 [Note] A temporary password is generated for root@localhost: g&Us;SGfu9RL

[root@localhost mysql5.7]# ln -s /usr/local/mysql5.7/bin/mysql /usr/local/bin/

# 报错处理
[root@localhost mysql5.7]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

# 指定mysql.sock路径并输入临时密码
[root@localhost mysql5.7]# mysql -S /tmp/mysql57.sock -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39
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> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

# 检验密码是否修改成功
[root@localhost mysql5.7]# mysql -S /tmp/mysql57.sock -uroot -p 
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 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> quit
Bye

mysql指定sock优化

# 新建别名
[root@localhost ~]# vim .bashrc 
[root@localhost ~]# cat .bashrc 
# .bashrc
# User specific aliases and functions
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias mysql57='mysql -S /tmp/mysql57.sock'
# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

[root@localhost ~]# source .bashrc

# 无需再手动指定sock路径
[root@localhost ~]# mysql57 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-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.

二、主库相关配置

主库配置文件：

# 主从配置
log-bin=/data/mysql57/log/bin/mybin.log          # 开启二进制日志
binlog_cache_size=1M                            # 二进制日志内存大小
binlog_format=row                              # 二进制日志格式
expire_logs_days=7                              # 日志过期清理时间
slave_skip_errors=1062                          # 跳过主从复制错误
server-id=1                                    # 服务器ID（唯一）
read-only=0                                    # 读写模式（0=读写，1=只读）
# super-read-only=1                              # 管理员账户只读
binlog-ignore-db=mysql                          # 忽略备份的数据库
# binlog-do-db=数据库名                          # 指定同步的数据库

主库创建远程连接用户并授权

# 新建slave用户（允许192.168.140网段连接）
mysql> create user 'slave'@'192.168.140.%' identified by "Qwe.12345";
Query OK, 0 rows affected (0.00 sec)

# 授予主从复制权限
mysql> grant replication slave on *.* to 'slave'@'192.168.140.%';
Query OK, 0 rows affected (0.01 sec)

查看主库的pos节点

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mybin.000003 |      879 |              | mysql            |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

三、从库相关的配置

从库配置文件

[root@localhost relay]# cat /usr/local/mysql5.7/my.cnf 
[client]
port=3306
socket=/tmp/mysql57.sock
[mysqld]
socket=/tmp/mysql57.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql5.7/
datadir=/data/mysql57/data
tmpdir=/tmp
log-error=/data/mysql57/log/mysqld57.log
pid-file=/data/mysql57/mysqld57.pid

# 主从配置
server-id=2                                    # 服务器ID（唯一）
read-only=1                                    # 只读模式
relay-log=/data/mysql57/log/relay/myrelay.log    # 开启中继日志
relay-log-recovery=1                           # 保证中继日志完整性
sync-relay-log=1                               # 中继日志写入系统缓存

重启数据库

[root@localhost relay]# /etc/init.d/mysql57 restart
Shutting down MySQL....  SUCCESS! 
Starting MySQL.  SUCCESS!

MySQL主从复制参数对比表

Mysql8.0.23后的版本 参数含义 Mysql8.0.23之前版本
SOURCE_HOST 主库IP地址 master_host
SOURCE_USER 连接主库的用户名 master_user
SOURCE_PASSWORD 连接主库的密码 master_password
SOURCE_LOG_FILE 主库日志文件名 master_log_file
SOURCE_LOG_POS 主库日志POS点 master_log_pos

mysql8.0.23数据库操作

CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库地址', SOURCE_USER='连接主库的用户', SOURCE_PASSWORD='密码', SOURCE_LOG_FILE='二进制日志文件名', SOURCE_LOG_POS=二进制日志文件的pod点;
START REPLICA;
SHOW REPLICA STATUS\G;

# 从库配置主库连接
mysql> change master to master_host='192.168.140.130',master_user='slave',master_password='QWE.12345',master_log_file='mybin.000003',master_log_pos=879;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.140.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mybin.000003
          Read_Master_Log_Pos: 879
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mybin.000003
             Slave_IO_Running: Connecting
            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: 879
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@192.168.140.130:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /data/mysql57/data/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: 221025 22:50:25
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

故障排查

  1. 连接问题： 

    Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
    • 检查主库防火墙和SELinux是否关闭
    • 测试从库能否手动连接主库： 
      [root@localhost ~]# mysql57 -h 192.168.140.130 -uslave -p
Enter password:

  2. 密码错误：

    • 主库错误日志提示密码错误： 
      2022-10-26T02:54:25.374182Z 4 [Note] Access denied for user 'slave'@'192.168.140.132' (using password: YES)
    • 重新配置从库（修正密码）： 
      mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.140.130',master_user='slave',master_password='Qwe.12345',master_log_file='mybin.000003',master_log_pos=879;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

  3. 成功状态： 

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes

四、最终测试

从库查询

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

主库建库建表并插入数据

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

# 创建数据库
mysql> create database student charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use student;
Database changed
mysql> create table stu (
    -> id int primary key auto_increment,
    -> sname varchar(10) not null default '',
    -> gender char(1) not null default '',
    -> company varchar(20) not null default '',
    -> salary decimal(6,2) not null default 0.00
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.01 sec)

# 查看表结构
mysql> desc stu;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10)  | NO   |     |         |                |
| gender  | char(1)      | NO   |     |         |                |
| company | varchar(20)  | NO   |     |         |                |
| salary  | decimal(6,2) | NO   |     | 0.00    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)

# 插入数据
mysql> insert into stu 
    -> values
    -> (01,'张三','男','百度',8888.88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu 
    -> values
    -> (02,'李四','女','新浪',7000.98),
    -> (03,'刘备','男','皇族后裔',2500);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu 
    -> (sname,gender,company)
    -> values
    -> ('孙策','男','江东集团');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+----+--------+--------+--------------+---------+
| id | sname  | gender | company      | salary  |
+----+--------+--------+--------------+---------+
|  1 | 张三   | 男     | 百度         | 8888.88 |
|  2 | 李四   | 女     | 新浪         | 7000.98 |
|  3 | 刘备   | 男     | 皇族后裔     | 2500.00 |
|  4 | 孙策   | 男     | 江东集团     |    0.00 |
+----+--------+--------+--------------+---------+
4 rows in set (0.00 sec)

最终结果图：

  • 主库图：
  • 从库图：
文章版权声明：除非注明，否则均为柳三千运维录原创文章，转载或复制请以超链接形式并注明出处。

相关阅读

  • mysqldump备份
  • Mysql主从复制
  • Mysql 常用日志介绍
  • Mysql重置root密码
  • Mysql用户、密码、权限管理
    • 取消
    微信二维码
    微信二维码
    支付宝二维码