Linux rocky 9.2 安装mysql-8.0.39-linux-glibc2.28-x86

Linux rocky 9.2 安装mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz

数据库官方下载：MySQL :: Download MySQL Community Server

本文也绑定该资源包，免费提供下载学习。

1.系统版本

2.新建目录，存放数据库安装包，并且上传

需要用到的工具：yum -y install vim lrzsz tar

上传解压：tar -xvf mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz

3.修改目录名字

mv mysql-8.0.39-linux-glibc2.28-x86_64 /usr/local/mysql

4.新建mysql 用户，用于启动mysql

groupadd mysql

useradd -r -g mysql mysql

5.新建mysql所需目录

[root@localhost local]# mkdir -pv /usr/local/mysql/{data,log}
mkdir: created directory '/usr/local/mysql/data'
mkdir: created directory '/usr/local/mysql/log'
[root@localhost local]# touch /usr/local/mysql/log/error.log

6.授权/usr/local/mysql/所属为mysql ,以便启动服务有权限。

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/*

7.新建vim /etc/my.cnf

[root@localhost bin]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server_id=1
port=3306
socket=/tmp/mysql.sock
symbolic-links=0

lower_case_table_names=1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
socket=/tmp/mysql.sock

[mysqld_safe]
log-error=/usr/local/mysql/log/error.log
pid-file=/usr/local/mysql/mysql.pid

[client]
port=3306
socket=/tmp/mysql.sock

8.配置systemctl 管理mysql 数据库服务

vim /etc/systemd/system/mysqld.service

[Unit]
Description=Mysql
After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]
Type=forking
#PIDFile=/usr/local/mysql/mysql8420/data/localhost.localdomain.pid
ExecStart=/usr/local/mysql/support-files/mysql.server start
#ExecReload=/bin/kill -s HUP $MAINPID
ExecReload=/usr/local/mysql/support-files/mysql.server restart
#ExecStop=/bin/kill -s QUIT $MAINPID
ExecStop=/usr/local/mysql/support-files/mysql.server stop
PrivateTmp=false

User=mysql
Group=mysql

[Install]

9.配置环境变量

vim /etc/profile
在最后一行添加，mysql所在的bin 目录

export PATH=/usr/local/mysql/bin:$PATH

刷新环境变量

source /etc/profile

10.初始化数据库

./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

初始化成功，数据库密码默认： root@localhost: IKK,BNIaF6js

11.启动数据库

12.要修改数据库密码，才能使用该数据库，不然报错

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

修改数据库密码

登录数据库，修改数据库密码：ALTER USER ‘username’@‘localhost’ IDENTIFIED BY ‘new_password’;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345@Admin';
Query OK, 0 rows affected (0.01 sec)

配置主从同步

13.按照上一步骤，再新建一台数据库服务器

a. 主机名 master103 ip:192.168.0.103 主数据库

b. 主机名 slave106 ip:192.168.0.106 从数据库

14.修改vim /etc/my.cnf

server_id 取值范围1至2的32次方减一，只要集群唯一值即可

log-bin=master-mysql_bin

修改两台的my.cnf，需要重启数据库服务

systemctl restart mysqld && systemctl status mysqld

15.在主数据库中新建一个用户，用于从数据库同步主数据库。

-- 创建用户 slave
CREATE USER 'slave'@'%' IDENTIFIED BY '12345678';
-- 授权
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

查询主库bin-log日志文件和position端口

mysql> show master status;

从库直接执行（方法一）

CHANGE REPLICATION SOURCE TO
     SOURCE_HOST='192.168.0.103',
     SOURCE_PORT=3306,
     SOURCE_USER='slave',
     SOURCE_PASSWORD='12345678',
     SOURCE_LOG_FILE='master-mysql-bin.000001',
     SOURCE_LOG_POS=1635;

（方法二）

change master to master_host='192.168.0.103',master_user='slave',master_password='12345@Admin',master_log_file='masert-mysql_bin.000006',master_log_pos=157;

停止同步

mysql> stop slave;

开启同步

mysql> start slave;
查看同步状态

SHOW REPLICA STATUS\G;

报错内容

Last_IO_Error: Error connecting to source 'slave@192.168.0.103:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '192.168.0.103:3306' (113)

这边尝试用新建的用户在从库远程登录到主库，看看用户密码权限三个是否正确，在登录过程，提醒新的报错

猜测应该是8版本以上，数据库用户必须用复杂性密码，就不会报这个错

报错内容

[root@slave106 bin]# mysql -uslave -p'12345678' -h 192.168.0.103
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.103:3306' (113)

解决办法：关闭两台数据库服务器的防火墙和selinux