Elvea Huang

Appearance

主从热备 ​

RHEL ​

安装目录

假设有2台服务器 192.168.0.101 主库 192.168.0.102 从库

|---data
    |---app
    |---|---mysql             
    |---|---|---data                        (数据目录)
    |---|---|---logs                        (日志目录)
    |---|---|---|---mysql.log               (运行日志)
    |---|---|---|---mysql-slow.log          (慢查询日志)

初始化

参考单机安装

Windows ​

单机安装目录

|---D
|---Tools
|---|---MySQL             
|---|---|---Server                          (安装目录)
|---|---|---Data                            (安装目录)
|---|---|---|---Master                      (主库数据目录)
|---|---|---|---Slave                       (从库数据目录)
|---|---|---Logs                            (日志目录)
|---|---|---|---mysql-master.log            (主库运行日志)
|---|---|---|---mysql-master-slow.log       (主库慢查询日志)
|---|---|---|---mysql-master.pid            (PID)
|---|---|---|---mysql-master.sock           (Sock)
|---|---|---|---mysql-slave.log             (主库运行日志)
|---|---|---|---mysql-slave-slow.log        (主库慢查询日志)
|---|---|---|---mysql-slave.pid             (PID)
|---|---|---|---mysql-slave.sock            (Sock)
|---|---|---master.ini                      (主库配置文件)
|---|---|---slave.ini                       (从库配置文件)

初始化

bash
# 初始化主库和从库
mysqld --initialize-insecure --basedir=D:\Tools\MySQL\Server --datadir=D:\Tools\MySQL\Data\Master
mysqld --initialize-insecure --basedir=D:\Tools\MySQL\Server --datadir=D:\Tools\MySQL\Data\Slave
# 安装系统服务
mysqld --install MySQL_Master_Server --defaults-file=D:\Tools\MySQL\master.ini
mysqld --install MySQL_Slave_Server --defaults-file=D:\Tools\MySQL\slave.ini
# 手工启动服务
mysqld --defaults-file=D:\Tools\MySQL\master.ini
mysqld --defaults-file=D:\Tools\MySQL\slave.ini
# 移除系统服务
mysqld –remove MySQL_Master_Server
mysqld –remove MySQL_Slave_Server
# 启动
net start MySQL_Master_Server
net start MySQL_Slave_Server
# 停止
net stop MySQL_Master_Server
net stop MySQL_Slave_Server

主库配置文件

# 主库配置
[mysqld]
# ID
server-id=1
# 端口
port=3306
#
log-bin=mysql-master-log-bin
log_bin_index=mysql-master-log-bin.index
# 记录日志的数据库
binlog-do-db=java-samples
# 不记录日志的数据库
binlog-ignore-db=information_schema,performance_schema,mysql,sys
# 
pid-file=D:\Tools\MySQL\Logs\mysql-master.pid
socket=D:\Tools\MySQL\Logs\mysql-master.sock
# 安装目录和数据目录
basedir=D:\Tools\MySQL\Server
datadir=D:\Tools\MySQL\Master
# 表名不区分大小写
lower_case_table_names=1
# 编码
character-set-server=utf8mb4 
# 字符集
collation-server=utf8mb4_0900_ai_ci
#
init_connect='SET NAMES utf8mb4'
# 时区
default-time-zone='+8:00'
# 启用传统密码策略认证
# default_authentication_plugin=mysql_native_password
# 错误日志
log-error=D:\Tools\MySQL\Logs\mysql-master.log
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=D:\Tools\MySQL\Logs\mysql-master-slow.log
# 修改为记录5秒内的查询，默认为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

从库配置文件

# 从库配置
[mysqld]
# ID
server-id=2
# 端口
port=3308
# 
relay-log=mysql-slave-relay-bin
relay-log-index=mysql-slave-relay-bin.index
# 需要复制的数据库
replicate-do-db=java-samples
# 无需复制的数据库
replicate-ignore-db=information_schema,performance_schema,mysql,sys
# 
pid-file=D:\Tools\MySQL\Logs\mysql-slave.pid
socket=D:\Tools\MySQL\Logs\mysql-slave.sock
# 安装目录和数据目录
basedir=D:\Tools\MySQL\Server
datadir=D:\Tools\MySQL\Slave
# 表名不区分大小写
lower_case_table_names=1
# 编码
character-set-server=utf8mb4 
# 字符集
collation-server=utf8mb4_0900_ai_ci
#
init_connect='SET NAMES utf8mb4'
# 时区
default-time-zone='+8:00'
# 启用传统密码策略认证
# default_authentication_plugin=mysql_native_password
# 错误日志
log-error=D:\Tools\MySQL\Logs\mysql-slave.log
# 慢查询
# 开启慢查询日志
slow-query-log=on
# 慢查询日志路径
slow_query_log_file=D:\Tools\MySQL\Logs\mysql-slave-slow.log
# 修改为记录5秒内的查询，默认为记录10秒内的查询
long_query_time=5
# 记录未使用索引的查询
log-queries-not-using-indexes=on
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

主从热备 ​

查看主库信息和位置 ​
sql
show master status;

结果如下

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-master-log-bin.000002
         Position: 1294
     Binlog_Do_DB: java-samples
 Binlog_Ignore_DB: information_schema,performance_schema,mysql,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)

把结果里面的File和Position记录下来，后面会用到。

创建从库链接主库的用户 ​
sql
# 创建用户
CREATE USER 'slave'@'192.168.%' IDENTIFIED WITH mysql_native_password BY 'Slave@007';
# 授权
GRANT replication slave, replication client on *.* TO 'slave'@'192.168.%' WITH GRANT OPTION;
# 刷新权限，一定要刷新，一定要刷新，一定要刷新。
flush privileges;
从库操作 ​

用新建的用户从主库连到主库

sql
change master to
    master_host ='192.168.0.6',
    master_port =3306,
    master_user ='slave',
    master_password ='Slave@007',
    master_log_file ='mysql-master-log-bin.000005',
    master_log_pos =156;

启动复制

sql
# ===============================================================
# 旧版本
# ===============================================================
start slave;
#
START SLAVE USER ='slave' PASSWORD ='Slave@007';
# ===============================================================
# 新版本
# ===============================================================
start replica;

查看从库信息

sql
show slave status;

结果如下

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.6
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master-log-bin.000001
          Read_Master_Log_Pos: 1437
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 625
        Relay_Master_Log_File: mysql-master-log-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: information_schema,performance_schema,mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
.......

停止同步

sql
stop slave;

重置所有链接

sql
reset slave all;

动态增加从库 ​

备份主库

bash
# 参数说明
# --routines
# 导出存储过程和函数
# --single_transaction
# 导出开始时设置事务隔离状态，并使用一致性快照开始事务，然后unlock tables
# 而lock-tables是锁住一张表不能写操作，直到dump完毕

# --master-data
# 等于1，将change master to写到结果中
# 等于2，将change master to写到结果中并注释
#
mysqldump -u root -p --routines --single_transaction --master-data=2 --databases dbname > db.sql
#
mysqldump -u root -p --databases dbname > db.sql

查看binlog和pos值

bash
head -25 backup.sql

还原数据库

bash
mysql -u root -p backup.sql

开启复制

sql
# 停止同步
stop slave;
# 重置所有主库连接
reset slave all;
# 连接到主库
change master to
    master_host ='192.168.0.6',
    master_port =3306,
    master_user ='slave',
    master_password ='Slave@007',
    master_log_file ='mysql-master-log-bin.000005',
    master_log_pos =156;
# 启用同步
START SLAVE USER ='slave' PASSWORD ='Slave@007';
# 查看从库信息
show slave status;