MySQL集群搭建-主备搭建

/ 0评 / 0

单点数据库永远不应该出现在生产环境,我们已经目睹过太多由于单点、备份缺失造成的损失,所以,搭建高可用 MySQL 集群是非常有必要的。

搭建集群有以下几点好处:

  1. 高可用性,在主节失效时自动切换,不需要技术人员紧急处理
  2. 高吞吐,可以多个节点同时提供读取数据服务,降低主节点负载,实现高吞吐
  3. 可扩展性强,支持在线扩容
  4. 无影响备份,在备节点进行备份操作不会对业务产生影响

要说缺点,有以下几点:

  1. 架构复杂,在部署、管理方面对技术人员有要求
  2. 备节点拉取主节点日志时会对主节点服务器性能有一定影响
  3. 如果配置了半同步复制,会对事务提交有一点影响

因为主备搭建数据库是非常有必要的.

1.环境准备

1.1 启动数据库

在两台机器分别启动 MySQL 实例, MySQL 搭建方式可以参考: [Mysql集群搭建-MySQL 安装(二进制版).md](Mysql集群搭建-MySQL 安装(二进制版).md)

IP 系统 端口 MySQL版本 节点
10.143.87.21 红帽7.4 3306 5.7.34 Master
10.143.87.22 红帽7.4 3306 5.7.34 Salve

关键配置:

Master:
# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 21330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%


#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
Salve:
# my.cnf
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 3306
# 编码
default-character-set = utf8
# 用于本地连接的socket套接字,这个不同于上面mysqld中的socket,如果这个不写执行mysql -uroot -p会报错
socket = /data4/mysql_db/mysql.sock

[mysqld]
# 指定basedir,启动时就不用指定basedir了
basedir = /usr/local/mysql
# 指定数据存储目录
datadir = /data4/mysql_db
tmpdir = /tmp
socket = /data4/mysql_db/mysql.sock
# user=mysql
pid-file = /data4/mysql_db/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 22330601

default-storage-engine = InnoDB
character_set_server=utf8
#character-set-server = utf8
default_password_lifetime=0

#### log ####
log_timestamps=system
# binlog_cache_size = 16M
# 表示启用二进制日志
log_bin = /data4/mysql_log/mysql-bin
log_bin_index = /data4/mysql_log/mysql-bin.index
binlog_format = row
# binlog保留时间15天
expire_logs_days = 15

relay_log_recovery=ON
relay_log=/data4/mysql_log/mysql-relay-bin
relay_log_index=/data4/mysql_log/mysql-relay-bin.index

log_error = /data4/mysql_log/mysql-error.log
log_queries_not_using_indexes = /data4/mysql_log/nouseindex.log

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data4/mysql_log/mysql-slow.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

#### salve ####
read_only=1
配置解析:

1.2 插入数据

在生产中,我们都会有一个正在运行的生产数据库。现在我们假设 Master 是正在使用的数据库,而新的需求是要在线搭建salve备库。我们现在模拟往 Master 节点插入一些测试数据

[root@wydsj-gpapp03 init.d]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> create database mytest;
Query OK, 1 row affected (5.03 sec)

mysql> use mytest;
Database changed
mysql> create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default '',
    -> age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

mysql> 

2.在线搭建主从

现在我们的环境如下

2.1 创建同步用户

我们创建一个用户名为 repl 的用户,授予 REPLICATION SLAVE 权限专门用来同步

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

或者:

grant replication slave on *.* TO 'repl'@'%' identified by 'repl';

2.2 备份数据(可省略)

常用的备份数据的方式有 innobackupexmysqldump,这里数据量少,我们用 mysqldump 进行全备

/usr/local/mysql/bin/mysqldump -S /data4/mysql_db/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql -pyourpasswd
参数解析:

数据备份完毕后,把数据文件直接传输到 Slave 机器上

scp mysql_backup_full.sql mysql@wydsj-gpapp04:~

2.3 数据恢复

在 Slave 机器上直接执行 sql 文件导入数据

/usr/local/mysql/bin/mysql -pyourpasswd -S /data4/mysql_db/mysql.sock < mysql_backup_full.sql 

导入成功后,查看数据是否基本一致...

2.4 开启同步

下面配置文件中的MASTER_LOG_FILEMASTER_LOG_POS

是在 Master 执行 show master status 得到的二进制位置信息。

image-20210924002028653

现在,我们执行同步命令(在slave数据库中):

-- 重置复制
reset slave;

-- 同步配置
CHANGE MASTER TO
MASTER_HOST='10.143.87.21',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=154;

-- 开启同步
start slave

---实际执行结果如下:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.143.87.21',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000005',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

查看同步状态:

show slave status \G;

执行结果:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.143.87.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        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: information_schema.%,performance_schema.%,sys.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              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: 21330601
                  Master_UUID: ff0f9dbc-1c7f-11ec-8cfb-ac1f6bcf601f
             Master_Info_File: /data4/mysql_db/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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

我们可以从 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 这三个参数可以判断出同步状态是否正常

从输出结果可以看到我们的同步是正常的,下面我们来测试一下

2.5 同步测试

在 Master 节点插入新数据

mysql> insert into test1 values(0, 'glj', 25);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | glj     |  25 |
+----+---------+-----+
5 rows in set (0.00 sec)

---salve

mysql> select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | glj     |  25 |
+----+---------+-----+
5 rows in set (0.00 sec)

可以看到数据已经同步到备节点,本次主备搭建完成

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注