Linux部署Mysql5.7-8.0

一、Mysql安装

 1.1 mysql5.7.36

先卸载mariadb
yum remove mariadb* -y
rpm -e mysql　　// 普通删除模式
rpm -e --nodeps mysql　　// 强力删除模式，
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/


再下载安装
wget https://repo.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar && \

tar -xf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar ./mysql5.7

rpm -ivh ./mysql5.7/mysql-community-common-5.7.36-1.el7.x86_64.rpm && \
rpm -ivh ./mysql5.7/mysql-community-libs-5.7.36-1.el7.x86_64.rpm && \
rpm -ivh ./mysql5.7/mysql-community-devel-5.7.36-1.el7.x86_64.rpm && \
rpm -ivh ./mysql5.7/mysql-community-libs-compat-5.7.36-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql5.7/mysql-community-client-5.7.36-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql5.7/mysql-community-server-5.7.36-1.el7.x86_64.rpm

systemctl enable mysqld

 1.2 mysql8.0.24

先卸载mariadb
yum remove mariadb* -y
yum remove mariadb* -y
rpm -e mysql　　// 普通删除模式
rpm -e --nodeps mysql　　// 强力删除模式，
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/
rm -rf /etc/my.cnf
rm -rf /var/lib/mysql/


再下载安装
wget https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
 
tar -xf mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar ./mysql8
 
rpm -ivh ./mysql8/mysql-community-common-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-libs-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-devel-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-libs-compat-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-client-8.0.24-1.el7.x86_64.rpm --nodeps --force && \
rpm -ivh ./mysql8/mysql-community-server-8.0.24-1.el7.x86_64.rpm --nodeps --force
 
systemctl enable mysqld

二、Mysql配置

vi /etc/my.cnf

最后加入下面两行

lower_case_table_names=1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

systemctl start mysqld

yum -y install libncurses*

firewalld开启防火墙mysql 3306端口的外部访问
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

三、修改密码与远程访问

查看初始密码  

cat /var/log/mysqld.log | grep password

修改密码与远程访问  mysql -u root -p mysql

MySQL 5.7 调整密码

set global validate_password_policy=0;

set global validate_password_length=1;

alter user 'root'@'localhost' identified by '123456';

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

flush privileges; 

exit;

MySQL 8.0 调整密码

验证规则：

set global validate_password.policy=0;

set global validate_password.length=1;  (实际最小为4)

然后退出后再执行(我没有执行这句话，直接重置了)  mysql_secure_installation

alter user 'root'@'localhost' identified by '123456';

update user set host = '%' where user = 'root';

alter user 'root'@'%' identified by '123456';

grant all privileges on *.* to 'root'@'%';

flush privileges;

四、备份还原（导出导入）

# 导出数据库 db1
mysqldump -uroot -proot db1 | gzip > /home/user/db1_20220517.sql.gz
mysqldump -uroot -proot db1 > /home/user/db1_20220517.sql

# 导入数据库 (windows上不能包含中文路径)
mysql -uroot -p
> use db1;
> source /home/user/db1_20220517.sql;

# 查看mysql的数据文件存储路径
show variables like '%datadir%';