Centos7搭建Mysql8.0.29集群：一主多备

wangzhongyudie

已于 2022-08-01 22:19:35 修改

阅读量621

点赞数

分类专栏： Mysql数据库文章标签： mysql 数据库 dba

于 2022-06-08 15:27:22 首次发布

本文链接：https://blog.csdn.net/wangzhongyudie/article/details/125185504

版权

Mysql数据库专栏收录该内容

7 篇文章 1 订阅

订阅专栏

该文章详细介绍了如何在两台服务器上配置MySQL集群的主从复制。包括安装MySQL服务、更改初始密码、设置远程登录、解决权限问题、调整data目录、配置主从节点的my.cnf文件，以及创建复制用户并重置主节点记录binlog偏移量。通过这些步骤，实现了数据的实时同步。

摘要由CSDN通过智能技术生成

集群规划

主机	角色
k8s-node7	master
k8s-node8	slave

安装包准备

官网地址:https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

 wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

安装配置步骤

一、安装mysql服务

分别在主节点和从节点执行如下操作，安装mysql服务

rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm

yum remove mysql-libs -y
rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm

yum install openssl-devel -y
rpm -ivh mysql-community-devel-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm

二、更改初始密码

第一步：先使用临时密码登录进去后，修改root用户的密码，第一次需要设置一个比较复杂的密码，长度最少8位，还需要有大小写字母和特殊符号

mysql> alter user 'root'@'localhost' identified by 'MysqlRoot@123';
Query OK, 0 rows affected (0.00 sec)
mysql>

第二步：更改mysql的密码验证策略

set global validate_password.policy=LOW;
set global validate_password.mixed_case_count=0;
set global validate_password.number_count=0; 
set global validate_password.special_char_count=0; 
set global validate_password.length=1;
set global validate_password.check_user_name='OFF';

一般只需要设置第一个就够了

mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by 'mysqlroot';
Query OK, 0 rows affected (0.01 sec)

三、设置root用户允许远程登录

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

四、更改mysql的data目录

mysql的默认的数据目录是/var/lib/mysql，但是很多时候我们的服务器的根路径 / 的磁盘空间不够，所以需要指定到其他的路径。

[root@k8s-node7 mysql-8.0.29]# mv /var/lib/mysql /home/mysql-8.0.29/ 
[root@k8s-node7 mysql-8.0.29]# ls
mysql
[root@k8s-node7 mysql-8.0.29]# cd mysql/
[root@k8s-node7 mysql]# ls
auto.cnf    client-cert.pem    #ib_16384_1.dblwr  ib_logfile0   mysql             mysql-bin.index     private_key.pem  server-key.pem  undo_002
ca-key.pem  client-key.pem     ib_buffer_pool     ib_logfile1   mysql-bin.000001  mysql.ibd           public_key.pem   sys
ca.pem      #ib_16384_0.dblwr  ibdata1            #innodb_temp  mysql-bin.000002  performance_schema  server-cert.pem  undo_001

[root@k8s-node7 mysql]# ln -s /home/mysql-8.0.29/mysql/ /var/lib/ 
[root@k8s-node7 mysql]# 
[root@k8s-node7 lib]# systemctl start mysqld
[root@k8s-node7 lib]#

权限不够的错误

在这一步很多人都会碰到，更改了data的目录后，再启动时会报权限不够的错误，如下所示：

[root@localhost /]# systemctl status mysqld.service

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: failed (Result: exit-code) since 日 2020-06-14 22:13:45 CST; 1min 16s ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 19271 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)

Process: 19241 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID: 19271 (code=exited, status=1/FAILURE)

Status: "Server startup in progress"

Error: 13 (权限不够)

解决方式

但是我们设定的目录的权限都是足够的，这时只需要执行setenforce 0，就可以解决

[root@k8s-node7 lib]# setenforce 0
[root@k8s-node7 lib]# systemctl start mysqld
[root@k8s-node7 lib]#

主节点配置文件/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#开启binlog日志
log-bin=mysql-bin

server-id=201

#默认的认证方式改为nativate，兼容mysql5.7
default-authentication-plugin=mysql_native_password

#跳过SSL
skip_ssl

#数据库存放目录
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#创建函数时防止报错
log_bin_trust_function_creators=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#设置数据库表名称大小写不敏感
lower_case_table_names = 1

从节点配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
server-id=202

#默认的认证方式改为nativate，兼容mysql5.7
default-authentication-plugin=mysql_native_password

#跳过SSL
skip_ssl

#数据库存放目录
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#创建函数时防止报错
log_bin_trust_function_creators=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#设置数据库表名称大小写不敏感
lower_case_table_names = 1
Readonly=1

Tips:注意，server-id不能和主节点相同，从节点不需要开启binlog模式，可以设置为Readonly=1

创建主从复制数据的用户，用于slave从master复制数据

mysql> create user 'copy'@'%' identified by 'mysqlcopy';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all privileges on *.* to 'copy'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

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

五、重置主节点，记录binlog的偏移量

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

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

mysql>

六、注册从节点

mysql> stop slave;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='k8s-node7',master_user='copy',master_port=3306,master_password='mysqlcopy',master_log_file='mysql-bin.000001',master_log_pos=157;
Query OK, 0 rows affected, 9 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: k8s-node7
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 157
               Relay_Log_File: k8s-node8-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.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: 157
              Relay_Log_Space: 540
              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: 201
                  Master_UUID: 98e64dfe-e6e3-11ec-b131-f01fafd2f4c3
             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
                  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, 1 warning (0.01 sec)

mysql>

这两项为 YES说明搭建成功了: