基于centos7.9安装mysql并配置主从复制

一、部署前准备

1master+1node
design-master：192.168.0.151，2C/4G/50G
design-node1：192.168.0.152，1C/2G/50G

注意：一定要关闭SELinux！！！一定要关闭SELinux！！！一定要关闭SELinux！！！

# vi /etc/selinux/config
设置SELINUX为disabled
SELINUX=disabled

二、MySQL部署

2.1、安装文件下载

官网：https://www.mysql.com/downloads/
清华镜像网站：https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/

下载的文件列表：

将下载的安装文件上传到服务器指定位置

mkdir /usr/local/mysql

2.2、安装配置

2.2.1、软件安装

2.2.1.1安装前检测：

检测是否已经安装mysql

rpm -qa | grep mysql

如果存在则关闭服务并以此删除服务

# 关闭服务
systemctl stop mysqld
# 依次删除服务
rpm -e mysql-libs-5.1.73-8.el6_8.x86_64 --nodeps

2.2.1.2安装

由于mariadb与mysql会有冲突，centos7中会默认安装mariadb，此处需要进行卸载操作

[root@mysql-design-node1 mysql]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@mysql-design-node1 mysql]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps

按照顺序依次安装五个安装包

yum install -y mysql-community-common-5.7.34-1.el7.x86_64.rpm
yum install -y mysql-community-libs-5.7.34-1.el7.x86_64.rpm
yum install -y mysql-community-client-5.7.34-1.el7.x86_64.rpm
yum install -y mysql-community-server-5.7.34-1.el7.x86_64.rpm
yum install -y mysql-community-devel-5.7.34-1.el7.x86_64.rpm 

开启mysql服务

systemctl start mysqld
systemctl status mysqld

2.2.2、软件配置

2.2.2.1、查看默认密码

[root@mysql-design-node1 mysql]# grep 'temporary password' /var/log/mysqld.log
2024-04-19T06:49:17.971849Z 1 [Note] A temporary password is generated for root@localhost: bAkyjpL+j2i3

登录数据库

[root@mysql-design-node1 mysql]# mysql -uroot -p
Enter password:

2.2.2.2、修改默认密码

mysql> set password for 'root'@'localhost'=password('xxxx');
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.2.2.3、添加远程登录用户

mysql> GRANT ALL PRIVILEGES ON *.* TO 'nancal@sz'@'%' IDENTIFIED BY 'Nancal@mysql123' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.2.2.4、开启防火墙指定端口

[root@mysql-design-node1 mysql]# firewall-cmd --add-port=38762/tcp --permanent
success

2.2.2.5、修改大小写敏感

在my.cnf文件中mysqld标签下添加属性

vi /etc/my.cnf
lower_case_table_names=1

重启数据库

systemctl restart mysqld

三、MySQL主从配置

3.1、主库配置

3.1.1、主库文件配置

# mysql配置文件修改
vi /etc/my.cnf
# 在[mysqld]节点下添加一下内容
# 设置节点服务id，主从不能够相同
server-id=129
# 开启binlog，二进制日志，文件名为：master-bin
log-bin=master-bin

3.1.2、重启MySQL

systemctl restart mysqld

3.1.3、登录MySQL，主库为从库授权

# 登录mysql
[root@mysql-design-master ~]# mysql -uroot -p
Enter password:

# 授权
mysql> grant replication slave on *.* to 'root'@'%' identified by 'root123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

3.1.4、查看主库状态

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

3.2、从库配置

3.2.1、从库文件配置

# mysql配置文件修改
vi /etc/my.cnf
# 在[mysqld]节点下添加一下内容
# 设置节点服务id，主从不能够相同
server-id=131

3.2.2、重启MySQL

systemctl restart mysqld

3.2.3、登录MySQL

参照主库

3.2.4、执行如下命令

# master_log_file 和 master_log_pos 就是上述主库show master status查询出来的值；
mysql> change master to master_host='192.168.0.150',master_port=3306,master_user='root',master_password='root123',master_log_file='master-bin.000001',master_log_pos=591;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

# 启动从服务器复制功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

# 查看下从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.150
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 591
               Relay_Log_File: mysql-design-node1-relay-bin.000005
                Relay_Log_Pos: 1324
        Relay_Master_Log_File: master-bin.000002
             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: 1109
              Relay_Log_Space: 2497
              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: 129
                  Master_UUID: befd6591-fe31-11ee-8955-000c29930b21
             Master_Info_File: /var/lib/mysql/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的状态均为Yes即表明一主一从配置成功。