一、系统准备
1、RHEL关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
2、关闭Selinux
##重启后生效
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
##重启后检查
getenforce
3、/etc/hosts解析（示例如下）
vim /etc/hostname
mysql8
vim /etc/hosts
192.168.11.11 mysql8
4、挂载Yum源
mount /dev/cdrom /mnt
mkdir /etc/yum.repos.d/bak -p
mv /etc/yum.repos.d/* /etc/yum.repos.d/bak
cat> /home/mysql/.bash_profile +apoP?g
2022-09-19T08:25:47.675052Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --sha256_password_auto_generate_rsa_keys as key files are present in data directory.
2022-09-19T08:25:47.675084Z 0 [Note] [MY-010308] [Server] Skipping generation of RSA key pair through --caching_sha2_password_auto_generate_rsa_keys as key files are present in data directory.
mysql -uroot -p'yyy=SB-3f/hf' -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql> select version();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
8、修改用户密码
alter user root@'localhost' identified by 'Okdd3adacxaf##';
9、配置root可远程登陆
create user root@'%' identified by 'Okdd3adacxaf##';
grant all privileges on *.* to root@'%' with grant option;
flush privileges;
使用如下语句创建 root 用户是无法通过 navicat 等客户端登录的，由于从 MySQL8 开始，身份验证插件发生改变，默认的 “caching_sha2_password” 不允许远程登录，故需将此插件修改为 “mysql_native_password” 便可登录。
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Okdd3adacxaf##';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user | host | plugin |
+------------------+-----------+-----------------------+
| root | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
10、设置socket软连接
[mysql@node1 ~]$ ln -s /mysql/data/mysql3306/socket/mysql.sock /tmp/mysql.sock
[mysql@node1 ~]$ mysql -uroot -p'Okdd3adacxaf##'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 8
Server version: 8.0.33 MySQL Community Server - GPL
Copyright (c) 2000, 2023, 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>
三、设置开机自启动
1、设置配置
[root@node1 system]# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description = MySQL Server
Documentation = man:mysqld(8)
Documentation = http://dev.mysql.com/doc/refman/en/using-systemd.html
After = network.target
After = syslog.target
[Install]
WantedBy = multi-user.target
[Service]
User=mysql
Group=mysql
Type=notify
Timeout=0
ExecStart=/mysql/app/mysql8.0.33/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf $MYSQLD_OPTS
EnvironmentFile=-/etc/sysconfig/mysql
LimitNOFILEE=65536
Restart=on-failure
RestartPreventExitStatus=1
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
2、执行命令让配置生效
systemctl daemon-reload
3、mysql服务相关命令
启动
systemctl start mysqld
关闭
systemctl stop mysqld
查看
systemctl status mysqld
4、设置MySQL为开机自启动
[root@node1 ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
四、备份
1、逻辑备份
crontab -e
0 1 * * * /backup/backup.sh > /tmp/backup_$(date +"\%Y-\%m-\%d").log
[root@mysql backup]# cat backup.sh
#!/bin/sh
# File: /data/backup/mysql_backup.sh
# Database info
DB_USER="root"
DB_PASS="Okdd3adacxaf##"
DB_HOST="192.168.11.11"
DB_PORT="3306"
BIN_DIR="/mysql/app/mysql8.0.33/bin"
BCK_DIR="/backup"
DATE=`date +%Y%m%d_%H`
#REMOTE_SERVER="remote_server_address"
#REMOTE_DIR="/db_backup/mysql"
# 创建备份目录
#if [ ! -d $BACKUP_DIR ]; then
# mkdir -p $BACKUP_DIR
#fi
echo '开始备份。。。。。。。。。。。。。。'
$BIN_DIR/mysqldump -u$DB_USER -h$DB_HOST -p$DB_PASS -P$DB_PORT -F --flush-privileges --single-transaction --max_allowed_packet=256M --set-gtid-purged=OFF -B lowdata -S /data/mysql/mysql3306/tmp/mysql3306.sock > $BCK_DIR/mysqlbackup_data_$DATE.sql;
echo '结束备份。。。。。。。。。。。。。。'
#上传备份文件到远程服务器
#scp $BACKUP_DIR/$DB_NAME-$(date +%Y%m%d).tar.gz $REMOTE_SERVER:$REMOTE_DIR
#删除过期文件
find /backup -mtime +7 -name "*.sql" -exec rm -f {} \;
find /backup -mtime +7 -name "mysqlbackup_data_$DATE.sql" -exec rm -f {} \;
这篇文章杂揉了 强哥的MySQL数据库安装 + MySQL DBA精英实战课 + MySQL实战，写出来的一篇文章，感觉可以当我的MySQL安装传家宝了
