一、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%';