CentOS 7.8离线安装MySQL（以MySQL5.7为例）

CentOS 7.8离线安装MySQL（以MySQL5.7为例）

一、下载MySQL5.7 rpm组合包

mysql5.7下载地址
mysql8.0下载地址

二、软件安装及配置

1.安装依赖包

挂载iso镜像，并安装以依赖包；mysql-community-server依赖net-tools、perf，若已经安装过vim，可不用额外安装perf。（vim依赖perf）

mkdir /media/CentOS
mount -t iso9660 -o ro,loop /opt/CentOS-7-x86_64-DVD-1810.iso /media/CentOS
ls /media/CentOS
yum --disablerepo=\* --enablerepo=c7-media install -y net-tools perf

2.安装MySQL

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
rpm -ivh ./mysql-community-common-5.7.31-1.el6.x86_64.rpm
rpm -ivh ./mysql-community-libs-compat-5.7.31-1.el7.x86_64.rpm
rpm -ivh ./mysql-community-libs-5.7.31-1.el6.x86_64.rpm
rpm -ivh ./mysql-community-client-5.7.31-1.el6.x86_64.rpm
rpm -ivh ./mysql-community-server-5.7.31-1.el6.x86_64.rpm

3.防火墙配置

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

4.关闭SeLinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
sed -i 's/SELINUX=permissive/SELINUX=disabled/g' /etc/selinux/config
setenforce 0
getenforce

5.开机启动

systemctl enable mysqld

三、MySQL配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

user=mysql
skip-name-resolve

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/lib/mysql/mysql.sock

mysql登录时报socket找不到终极解决方案

四、修改MySQL数据目录

cd /home
mkdir -p /home/data/mysql
chown -R mysql:mysql data/mysql

vi /etc/my.cnf

# 修改datadir数据目录(记得关闭SeLinux)
# datadir=/var/lib/mysql
datadir=/home/data/mysql

注意： 若已经初始化过MySQL之后，需要复制原始数据目录到新的数据目录，然后启动MySQL服务。

cd /home/data/mysql
cp -pr /var/lib/mysql/* .

五、用户管理

首次启动MySQL时，会对MySQL进行初始化。所以，请尽量一次性的配置好/etc/my.cnf文件。避免额外的工作步骤。

• MySQL 5.7

#查看自动生成的临时密码
cat /var/log/mysqld.log |grep password
查询结果如下：
2020-09-03T07:51:34.045157Z 1 [Note] A temporary password is generated for root@localhost: ZuoOejf5Vj#d

#登录
mysql -uroot -p'ZuoOejf5Vj#d'

mysql> grant all privileges on *.* to 'root'@'localhost' identified by '123456' with grant option;
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
mysql> flush privileges;

• MySQL 8.0
  MySQL8.0创建用户的操作已经不支持grant的同时创建用户的方式，需先创建用户再进行授权。

修改密码：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
修改密码并指定加密方式：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> flush privileges;

创建用户：
mysql> create user  'root'@'%' identified by '123456';
mysql> grant all on *.* to 'root'@'%' WITH GRANT OPTION;
mysql> flush privileges;

• 密码策略

MySQL为了安全，默认密码策略较为复杂，测试环境下我们可以将密码策略调低（生产环境不建议调整）。

-- 查看密码策略
show VARIABLES like 'validate_%';
-- 修改密码策略 0（LOW）1（MEDIUM）2（STRONG）
set GLOBAL validate_password.policy=0;
set GLOBAL validate_password.length=1;
set GLOBAL validate_password.mixed_case_count = 0;
set GLOBAL validate_password.special_char_count = 0;
set GLOBAL validate_password.check_user_name = OFF;

六、备份与恢复

1.单库备份与恢复

mysqldump -uroot -proot test > test.sql
mysql -uroot -proot test  < test.sql

2.全库备份脚本

cat > /opt/backup/backup.sh <<EOF
#!/bin/bash
backup_user=root
backup_password='xxxxxx'
backup_path=/opt/backup
mysql -e "show databases;" -u$backup_user -p$backup_password| grep -Ev "Database|information_schema|performance_schema|mysql|sys"|xargs mysqldump -u$backup_user -p$backup_password --set-gtid-purged=OFF --triggers --routines --events --databases > $backup_path/all`date +\%Y\%m\%d\%H\%M`.sql
# del 7 days
find /opt/backup -mtime +7 -name "*.sql" -exec rm -f {} \;

EOF

3.定时备份

crontab -e

追加以下内容

10 0 * * * /opt/backup/backup.sh >/dev/null 2>&1

crontab -l