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 -pstop slave;CHANGE MASTER TOMASTER_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 eventMaster_Host: 192.168.200.70Master_User: replica_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 405Relay_Log_File: relay-bin.000002Relay_Log_Pos: 283Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 405Relay_Log_Space: 450Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: e5ff0850-6c2e-11ef-844d-000c294bfda3Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_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)