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 的密码;添加非管理员账号,并授权等一些操作;

ubuntu 系统,需要执行命令:

mysql_secure_installation

来初始化安装过程,这个过程中需要进行几项配置:

root@localhost:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1
Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

设置管理员:

  • 如果是第一次安装 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+**
    

    ubuntu 系统查看默认账号密码:

    cat /etc/mysql/debian.cnf
    
  • 接下来需要连接到 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模式:

GTID模式也是基于bin-log的,要启用该模式,每个节点都需要进行配置,具体配置如下:

server-id = 1
log-bin = mysql-bin
# gtid 配置
gtid-mode = ON
# 配置gtid一致性
enforce_gtid_consistency = 1

配置中 enforce_gtid_consistency 配置项的作用解决在 ROW 格式下,CREATE TABLE ... SELECT 语句将会被记录为具有不同 GTID 的两个事务,此时从服务器将无法正确处理

配置完成后,重启服务,GTID模式将生效,然后需要在从节点中执行从节点的关系建立,示例代码如下:

CHANGE MASTER TO
     >     MASTER_HOST = 172.10.0.1,
     >     MASTER_PORT = 3306,
     >     MASTER_USER = repl,
     >     MASTER_PASSWORD = 'L001@leafage.top',
     >     MASTER_AUTO_POSITION = 1; # 这里是和普通模式不一样的地方

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

start slave;
show slave status \G;

在状态信息中,会比普通模式多以下几项信息:

Master_UUID          : d0xxxxf-37bc-11ec-xxxx-b4xxxxxx686
# GITD 下的状态信息项
Retrieved_Gtid_Set   : d0xxxxf-37bc-11ec-xxxx-b4xxxxxx686:1-2
Executed_Gtid_Set    : d0xxxxf-37bc-11ec-xxxx-b4xxxxxx686:1-2

MySQL 主从复制原理:


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

Technology 1/26/2022, 12:15:34 PM
814
2
4
slavegtidmysqlbinlog主从配置
如有帮助,点赞鼓励一下吧!