centos7.9搭建mysql5.6主从
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)