centos7.9搭建mysql5.6主从

搭建数据库

官网下载软件包后上传

基于centos7.9搭建mysql5.6.42

[root@mysql02 ~]# ls
anaconda-ks.cfg  init.sh  MySQL-5.6.42-1.el7.x86_64.rpm-bundle.tar

解压

tar -xf MySQL-5.6.42-1.el7.x86_64.rpm-bundle.tar -C /opt/

[root@mysql02 ~]# ls /opt/
MySQL-client-5.6.42-1.el7.x86_64.rpm    MySQL-server-5.6.42-1.el7.x86_64.rpm         MySQL-test-5.6.42-1.el7.x86_64.rpm
MySQL-devel-5.6.42-1.el7.x86_64.rpm     MySQL-shared-5.6.42-1.el7.x86_64.rpm
MySQL-embedded-5.6.42-1.el7.x86_64.rpm  MySQL-shared-compat-5.6.42-1.el7.x86_64.rpm

检测是否有其他版本mysql，否则卸载

rpm -qa | grep -i mysql

使用yum进行安装，注意搭配centos源

[root@mysql02 ~]# yum clean all;yum repolist
Loaded plugins: fastestmirror
Cleaning repos: c
Cleaning up list of fastest mirrors
Loaded plugins: fastestmirror
Determining fastest mirrors
c                                                                                                | 3.6 kB  00:00:00
(1/2): c/group_gz                                                                                | 153 kB  00:00:00
(2/2): c/primary_db                                                                              | 3.3 MB  00:00:00
repo id                                                     repo name                                             status
c                                                           c                                                     4,070
repolist: 4,070
[root@mysql02 ~]#

yum install -y /opt/*

配置my.cnf，并且需要跳过验证，设置密码，或者命令也可，二者选一

sudo mysqld_safe --skip-grant-tables &

sudo echo > /etc/my.cnf <<eof
[mysqld]
lower_case_table_names = 1
skip-grant-tables
eof
sudo chmod 644 /etc/my.cnf

启动mysql

service mysql restart

mysql -uroot -p

密码自行修改

USE mysql;
UPDATE user SET authentication_string=PASSWORD('000000') WHERE User='root';
FLUSH PRIVILEGES;

停止数据库

service mysql stop

注释参数；skip-grant-tables，重启再次进入数据库

service mysql start

mysql -uroot -p000000
SET PASSWORD = PASSWORD('000000');
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '000000' WITH GRANT OPTION;
FLUSH PRIVILEGES;

关闭防火墙和selinux

setenforce 0
systemctl stop firewalld

即可测试远程访问

PS C:\Users\huhy> mysql -h 192.168.200.80 -uroot -p000000
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.42 MySQL Community Server (GPL)

Copyright (c) 2000, 2024, 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> 

配置主从

实验使用centos6.5搭建mysql5.6.42作为主库，centos7.9搭建的mysql5.6.42作为从库

配置主库

cat  >> /etc/my.cnf <<EOF 
server-id = 1  
log-bin = mysql-bin  
binlog-ignore-db = mysql 
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
EOF

重启数据库

service mysql restart

并创建一个用于复制的用户

mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%' IDENTIFIED BY '000000';
FLUSH PRIVILEGES;

获取主库状态；记下输出的 File 和 Position 值

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000002 |      405 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

从库配置；编辑 MySQL 配置文件

cat  >> /etc/my.cnf <<EOF 
server-id=2                   # 从库的唯一ID，必须不同于主库
relay-log=relay-bin           # 启用relay日志
EOF

重启 MySQL 服务

systemctl restart mysql

从库上登录 MySQL 并配置它连接到主库

mysql -u root -p

stop slave;

CHANGE MASTER TO
  MASTER_HOST='192.168.200.70',
  MASTER_USER='replica_user',
  MASTER_PASSWORD='000000',
  MASTER_LOG_FILE='mysql-bin.000002',   
  MASTER_LOG_POS=405;    
  
start slave;              

查看状态

show slave status\G;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.70
                  Master_User: replica_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 405
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-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: 405
              Relay_Log_Space: 450
              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: e5ff0850-6c2e-11ef-844d-000c294bfda3
             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 the slave I/O thread to update it
           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
1 row in set (0.00 sec)