数据库备份、主从、集群等配置-1 MySQL

服务器配置：

操作系统类型	IP	MySQL版本	主从类型
7.9.2009	192.168.1.151	8.0.21	主
7.9.2009	192.168.1.152	8.0.21	从

1.1 docker安装MySQL

在192.168.1.151、192.168.1.152安装MySQL。

mkdir -p /opt/soft/mysql/{conf,data,log}

拉取镜像

docker pull mysql:8.0.21

docker-compose.yaml（主节点的容器名称：dc_mysql_master、dc_mysql_slave）

version: '3'
services:
  mysql:
    image: mysql:8.0.21
    container_name: dc_mysql_master
    restart: always
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    ports:
       - 4306:3306
    volumes:
      - /etc/localtime:/etc/localtime:ro # 将外边时间直接挂载到容器内部，权限只读
      - /opt/soft/mysql/data/:/var/lib/mysql/
      - /opt/soft/mysql/conf/my.cnf:/etc/mysql/my.cnf
      - /opt/soft/mysql/log/:/var/log/mysql/
    logging:
      driver: json-file
      options:
        max-size: 10m
        max-file: 5
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1

创建配置文件：

vim /opt/soft/mysql/conf/my.cnf

my.cnf配置：

[client]
#设置客户端默认字符集utf8mb4
default-character-set=utf8mb4
[mysql]
#设置服务器默认字符集为utf8mb4
default-character-set=utf8mb4
[mysqld]
# 解决MySQL8.0版本GROUP BY问题
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 限制数据导入导出操作的目录
secure_file_priv=/var/lib/mysql
# 允许任何IP访问
bind-address = 0.0.0.0

创建mysql容器

docker-compose up -d

开放端口（4306）

# 开放4306端口的命令
firewall-cmd --zone=public --add-port=4306/tcp --permanent

# 重启防火墙
firewall-cmd --reload

# 查看开放的端口
firewall-cmd --list-port

1.2 主从复制

1.2.1 主节点配置

修改my.cnf，在[mysqld]下添加以下内容：

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#[可选]指定binlog和binglog index的文件名
log_bin=mysql-bin
log_bin_index=binlog.index
#[可选]启用中继日志
relay-log=mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
#max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

完整my.cnf

[client]
#设置客户端默认字符集utf8mb4
default-character-set=utf8mb4
[mysql]
#设置服务器默认字符集为utf8mb4
default-character-set=utf8mb4
[mysqld]
# 解决MySQL8.0版本GROUP BY问题
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 限制数据导入导出操作的目录
secure_file_priv=/var/lib/mysql
# 允许任何IP访问
bind-address = 0.0.0.0

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#[可选]指定binlog和binglog index的文件名
log_bin=mysql-bin
log_bin_index=binlog.index
#[可选]启用中继日志
relay-log=mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
#max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

修改配置后重启数据库。

1.2.2 从节点配置

主从节点配置的差异：由于后续需要演示主从切换，所以无论是主从节点，都需要提前开启binlog和relaylog。故而这里主从配置基本一致，具体配置选项差异只有：server_id、read-only选项。

修改my.cnf，在[mysqld]下添加以下内容：

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=2
#[可选]指定binlog和binglog index的文件名
log_bin=mysql-log
log_bin_index=binlog.index
#[可选]启用中继日志
relay-log=mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
#max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

完整my.cnf

[client]
#设置客户端默认字符集utf8mb4
default-character-set=utf8mb4
[mysql]
#设置服务器默认字符集为utf8mb4
default-character-set=utf8mb4
[mysqld]
# 解决MySQL8.0版本GROUP BY问题
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
# 限制数据导入导出操作的目录
secure_file_priv=/var/lib/mysql
# 允许任何IP访问
bind-address = 0.0.0.0

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=2
#[可选]指定binlog和binglog index的文件名
log_bin=mysql-log
log_bin_index=binlog.index
#[可选]启用中继日志
relay-log=mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
#max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

修改配置后重启数据库。

1.2.3 创建用于主从同步的用户

主、从节点都需要进行以下操作：
主节点：

# 进入容器
docker exec -it dc_mysql_master /bin/sh

# 登录
mysql -uroot -p

#创建slave1用户
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';

#给slave1用户授予数据同步的权限
GRANT replication slave on *.* to 'slave1'@'%';

#刷新权限
flush privileges;

从节点：

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

#创建slave1用户
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';

#给slave1用户授予数据同步的权限
GRANT replication slave on *.* to 'slave1'@'%';

#刷新权限
flush privileges;

1.2.4 开启主从同步

开启主从同步过程中，不要再去操作数据了，以免出现数据不一致情况。最好是数据库安装好，还未使用时旧开启主从同步。

查看主节点binlog执行位置（主节点192.168.1.151来执行以下命令）：

# 进入容器
docker exec -it dc_mysql_master /bin/sh

# 登录
mysql -uroot -p

# 查看binglog执行位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从节点开启主节点同步操作（从节点192.168.1.152来执行以下命令，注意端口）：

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

#从节点设置主节点信息
CHANGE MASTER TO MASTER_HOST='192.168.1.151', MASTER_PORT=4306, MASTER_USER='slave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156;
  
#从节点开启数据同步
start slave;

#查看主从数据同步情况
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.151
                  Master_User: slave1
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1459
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 1627
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            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: 1459
              Relay_Log_Space: 1832
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 54db2059-a589-11ef-a788-0242ac120002
             Master_Info_File: mysql.slave_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:
     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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

注意：从节点使用show slave status;命令检查主从同步时，Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，并且 Last_Error 为空，说明主从同步成功启动并正常运行。
如果发现其中有存在No的情况，那么检查防火墙是否关闭、主节点的slave1用户是否创建成功（可以在从节点上执行“mysql -h 主节点ip -P 4306 -u slave1 -p123456”看是否能登录到主节点上）。

docker exec -it dc_mysql_slave /bin/sh

mysql -h 192.168.1.151 -P 4306 -u slave1 -p123456;

如果发现其中有存在No的情况，进行以下排查：
1、先稍等一下，启动slave后，不一定马上就会变为Yes，可能还需要等一下
2、检查主从节点服务器的防火墙是否关闭
3、主节点的slave1用户是否创建成功(可以在从节点上执行“mysql -h 主节点ip -uslave1 -p123456”看是否能登录到主节点上)
4、如果发现是上面执行"change master to …"命令时参数写错导致的，那么在从节点上，先执行“stop slave;”停止主从，接着在主节点上重新执行“show master status;”来获取主节点最新binlog日志以及偏移位置，然后在从节点重新执行“change master to …”命令，最后在从节点上执行 “start slave;”。

取消主从复制：
- 停止从服务器上的复制进程：

STOP SLAVE;

这个命令会停止从服务器上的复制线程，包括I/O线程和SQL线程。
- 移除从服务器上的复制配置：
如果你想要彻底取消主从同步，并且不再需要从服务器作为复制的一部分，你可以移除复制相关的配置。在从服务器上执行以下命令：

RESET MASTER;

这个命令会重置从服务器上的二进制日志，并清除所有与复制相关的配置信息。请注意，这个操作会丢失从服务器上所有的二进制日志文件，所以如果你还需要保留这些日志，请先进行备份。

1.2.4 主从同步验证

在主节点192.168.1.151上建库、建表、插入表数据，每一步操作都会实时同步到从节点上。
检查从节点192.168.1.152是否也都同步成功。

至此，主从同步就算开启成功了。

1.3 主从切换

概要：
其实就是将主从节点上的配置互换。

切换两个节点的读写权限；

切换两个节点的读写配置；

前提需要是主备模式，搭建过程见前两个小节。
服务器配置：

操作系统类型	IP	MySQL版本	切换前	切换后
7.9.2009	192.168.1.151	8.0.21	主	从
7.9.2009	192.168.1.152	8.0.21	从	主

1.3.1 主节点设置只读（在192.168.1.151上操作）

主节点设置只读模式，避免进行主从切换过程中还有写操作，导致切换后主从数据不一致问题。
注意：用SQL命令设置的只读模式是临时的，重启后失效。如果想让MySQL重启后也能生效，可以将read_only相关选项配置到my.conf文件里面。

# 进入容器
docker exec -it dc_mysql_master /bin/sh

# 登录
mysql -uroot -p

#查看只读相关配置
show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)


#开启全局只读(包括普通用户、超级管理员root也都不能写)
set global super_read_only='on';

#开启全局只读(普通用户不能写)，理论来说开启了super_read_only后，就无需设置当前参数
set global read_only='on';

#查看只读相关配置
show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)

1.3.2 检查主从数据是否同步完毕(在192.168.1.152操作)

在从节点上执行"show slave status\G;"命令，查看控制台打印结果，要求参数值要和下面的一致：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
注意：Slave_IO_Running和Slave_SQL_Running都为true代表主、从是正常同步，其次Seconds_Behind_Master为0代表当前主、从节点数据一致。

具体操作如下：

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

show slave status\G;

1.3.3 停止并重置从节点(在192.168.1.152操作)

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

#停止从节点
stop slave;

#重置掉从节点的相关主从同步信息，同时将relaylog文件进行删除重置
reset slave all;

1.3.4 修改原从节点的只读配置(在192.168.1.152操作)

注：用SQL命令设置的只读模式是临时的，重启后失效。如果想让mysql重启后也能生效，可以将read_only相关选项配置到my.conf文件里面或者从my.conf进行删除，以为默认就是只读关闭。

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

#查看只读相关配置
show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)


#关闭全局只读(让超级管理员root能进行写操作)
set global super_read_only='off';

#关闭全局只读(让普通用户也能写操作)
set global read_only='off';

#查看只读相关配置
show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)

1.3.5 主从切换

进行主从同步的过程不要任何写操作，避免导致切换后主从数据不一致。

查看原从节点的最新日志以及偏移量(在192.168.1.152操作)。

# 进入容器
docker exec -it dc_mysql_slave /bin/sh

# 登录
mysql -uroot -p

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

将原主节点的主节点设置为原从节点（在192.168.1.151操作）

# 进入容器
docker exec -it dc_mysql_master /bin/sh

# 登录
mysql -uroot -p

#设置主节点信息（注意日志文件名称和1.2里的是不一样的，不是只修改索引）
CHANGE MASTER TO MASTER_HOST='192.168.1.152', MASTER_PORT=4306, MASTER_USER='slave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-log.000001', MASTER_LOG_POS=3096;

#开启slave
start slave;

#查看主从同步信息
show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.152
                  Master_User: slave1
                  Master_Port: 4306
                Connect_Retry: 60
              Master_Log_File: mysql-log.000001
          Read_Master_Log_Pos: 3096
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-log.000001
             Slave_IO_Running: Yes
            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: 3096
              Relay_Log_Space: 529
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 220f1fd5-a620-11ef-a9f5-0242ac120002
             Master_Info_File: mysql.slave_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:
     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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

注意：使用show slave status;命令检查主从同步时，Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，并且 Last_Error 为空，说明主从同步成功启动并正常运行。

1.3.6 验证

在新主节点(192.168.1.152)插入表数据。
在新从节点(192.168.1.151)查看表数据，发现在新主节点插入的数据已经自动同步到新从节点上了。

秒客网