mysql 主从配置及原理

Technology 11/15/2021
114
0
0
slave , gtid , mysql , binlog , 主从配置
mysql 主从配置及原理

mysql 安装:

有通过命令行来安装、源码编译安装两种方式,更推荐使用命令行来安装,原因是安装简单,问题少,另外日志、数据、配置等相关文件的目录的分配路径都是很清晰的,日志在 /var/log 目录下,配置文件在 /etc 目录下,数据在 /var/lib 目录下;并且可以很好的进行更新和升级,还能够很容易的配置服务的自启动,可以直接全局命令等诸多优点;

  1. 添加 mysql 的 repository,执行命令如下:

    sudo yum install mysql80-community-release-el7-{version-number}.noarch.rpm
    

    这里需要注意的是:
    i. 当前服务器为 CentOS 7.x 版本,所以 release-el7-, 如果是 CentOS 6.x 版本,则是 release-el6-, 同样的如果系统版本是 CentOS 8.x 版本,则是 release-el8-;
    ii. 安装最新版本的 mysql-server,这里的 {version-number} 修改为8.0;

  2. 安装 mysql,执行命令如下:

    sudo yum install mysql-community-server
    

    如果是更新 mysql,执行命令如下:

    sudo yum update mysql-server
    
  3. 启动 mysql 并设置开启自启动:
    i. 启动 mysql:

    systemctl start mysqld
    

    ii. 设置自启动:

    systemctl enable mysqld
    
  4. 关闭 mysql 服务,有两种方式:
    i. 使用 systemctl 工具,执行命令如下:

    systemctl stop mysqld
    

    ii. 使用 msyqladmin,执行命令如下:

    mysqladmin -u root -p shutdown
    

    然后输入密码即可。

注: 步骤 3、4中使用的 systemctl 命令在 ubuntu 系统中是没有的,对应的是 service 工具,例如启动 msyql 执行命令为:

service mysqld start

其他相应的命令,依此类推,执行即可;

mysql 初始化配置:

mysql 安装完成后,需要进行一些基础的配置:设置管理员 root 的密码;添加非管理员账号,并授权等一些操作;

设置管理员:

  • 如果是第一次安装 mysql 的话,安装完成后 mysql 会给 root 账号设定一个初始化密码,这个密码在日志中记录,可以打开日志文件进行查看并登录 mysql 服务进行操作,具体执行命令如下:
sudo grep 'temporary password' /var/log/mysqld.log

得到的结果示例如下:

[root@node3 ~]# grep 'temporary password' /var/log/mysqld.log
2021-10-08T07:57:17.922375Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Bm/P&j0o6+**
  • 接下来需要连接到 mysql 服务(root 账号默认仅本地可连接,即限制为 localhost 或 127.0.0.1),执行命令如下:
mysql -uroot -p
  • 然后就可以修改账号密码了,这里需要注意的是,在8.0之后 mysql 使用了新的密码模式 caching_sha2_password 校验更加严格,所以密码需要大、小写字母+数字+特殊符号 的组合,修改命令如下:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

添加非管理员:

  • 首先需要添加一个账号,设定密码,并授权相关的数据库和操作权限,添加账号命令如下:
CREATE USER 'leafage'@'localhost' IDENTIFIED BY 'L001@leafage.top'; #仅本地客户端可连接
CREATE USER 'leafage'@'172.10.0.1' IDENTIFIED BY 'L001@leafage.top'; #仅指定IP客户端可连接
CREATE USER 'leafage'@'%' IDENTIFIED BY 'L001@leafage.top'; #所有IP客户端可连接
  • 给账号授权,授权命令如下:
GRANT ALL ON db1.* TO 'leafage'@'localhost'; #授权 db1 数据库的所有表的所有权限给 leafage 账号
GRANT 'role1', 'role2' TO 'leafage'@'localhost', 'leaf'@'localhost';  #授权角色 role1、role2 的操作权限给 leafage 和 leaf 账号
GRANT SELECT ON db1.* TO 'role1';  #授权 db1数据库的所有表的 select 权限给 role1 角色
  • 授权完成后,需要刷新一下 privileges,执行命令如下:
flush prifileges;

mysql 主从配置:

mysql 自5.6之后主从同步有两种选择,传统的基于bin-log + pos的模式,和 GTID 模式,先说一下传统的 bin-log + pos 的模式。

binary log + position模式:

  1. 主节点和从的节点都必须要配置唯一的 server-id(不配置,默认为 1),主从之间不能重复;
  2. 主节点必须开启 bin-log;
  3. [可选]如果主从同步需要认证,那还需要设置角色为 REPLICATION SLAVE 的用于数据同步的账号,用于 8.0.23 以前配置主节点 CHANGE MASTER TO 的命令(自 8.0.23 开始有了新的配置方式,指定从节点 CHANGE REPLICATION TO );

主节点:

主节点的配置文件示例为:

[mysqld]
# 配置 servier-id
server-id=10
# 开启 bin-log 
log-bin=mysql-bin

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

创建主从同步的账户,并授予 REPLICATION SLAVE 角色,执行命令如下:

CREATE USER 'repl'@'172.10.0.2' IDENTIFIED BY 'L001@leafage.top';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.10.0.1';

授权完成后,记得需要刷新一下 privileges;

flush prifileges;

配置完成后,重启 mysql,如果主节点已经在运行,且有数据变化,需要先锁定数据库写操作(然后将主节点的数据导出,导入到从节点中),执行命令如下:

FLUSH TABLES WITH READ LOCK;

如果没有数据变化,则可以不用,直接执行后面的步骤,然后连接到主节点,查看当前 bin-log 的信息,执行命令如下:

 SHOW MASTER STATUS;

执行结果示例为:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

可以看到当前执行的 bin-log 文件名和 position 的具体位置,这两个信息用于在从节点中建立主从关系时使用;完了之后,需要解除写操作的锁定,执行命令如下:

UNLOCK TABLES;

在8.0.23版本之后,可以在主节点端设置主从关系,执行命令如下:

CHANGE REPLICATION SOURCE TO
    ->     SOURCE_HOST='172.10.0.2',
    ->     SOURCE_USER='repl',
    ->     SOURCE_PASSWORD='L001@leafage.top',
    ->     SOURCE_LOG_FILE='mysql-bin.000003',
    ->     SOURCE_LOG_POS=73;

从节点:

从节点的配置文件示例:

[mysqld]
# 配置 servier-id
server-id=10

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置完后,重启 msyql,然后连接到从节点(如果不是安全连接到 mysql,需要在使用 建立主从关系之前,在从节点服务器上获取主节点的 RSA key pair-based, 连接主节点的 mysql 命令中添加 --get-server-public-key 的参数);

mysql -h 172.10.0.11 -urepl -pL001@leafage.top -P3306 --getserver-public-key

然后,使用从节点的 root 账号登录到从节点的 mysql, 执行主从关系的建立,执行命令如下:

CHANGE MASTER TO
    ->     MASTER_HOST='172.10.0.1',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='L001@leafage.top',
    ->     MASTER_LOG_FILE='mysql-bin.000003',
    ->     MASTER_LOG_POS=73;

然后启动 slave,并查看 slave 状态执行命令如下:

start slave;
show slave status \G;

结果示例如下:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.10.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 8976490
               Relay_Log_File: node-relay-bin.000004
                Relay_Log_Pos: 8975819
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes  #表明bin-log传送成功
            Slave_SQL_Running: Yes  #表明读取bin-log成功
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
           ......
           ......
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: d0xxxxf-37bc-11ec-xxxx-b4xxxxxx686
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
           ......
           ......
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.02 sec)

ERROR: 
No query specified

从 mysql 5.6 开始,增加了一种新的模式 GTID (Global Transaction Identifiers),

gtid模式:


[1]. 参考文档:MySQL 8.0 Reference Manual