centos安装配置mysql

mysql远程连接指定端口和ip地址

mysql -u root -P 3307 -h 101.200.152.192 -p

使用如下命令安装MySql以及它的相关依赖项.

sudo dnf install @mysql

安装完成后，启动MySql服务并且让它在Centos服务器启动时自动启动,命令如下所示.

sudo systemctl enable --now mysqld

使用如下命令检测MySql是否在运行.结果如下所示.

sudo systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2020-11-11 09:41:34 CST; 3 weeks 5 days ago
 Main PID: 30897 (mysqld)
   Status: "Server is operational"
    Tasks: 44 (limit: 10979)
   Memory: 403.9M
   CGroup: /system.slice/mysqld.service
           └─30897 /usr/libexec/mysqld --basedir=/usr

Nov 11 09:41:26 iZ2ze3u71xuet709kawlyiZ systemd[1]: Starting MySQL 8.0 database server...
Nov 11 09:41:26 iZ2ze3u71xuet709kawlyiZ mysql-prepare-db-dir[30815]: Initializing MySQL database
Nov 11 09:41:34 iZ2ze3u71xuet709kawlyiZ systemd[1]: Started MySQL 8.0 database server.

使用如下命令进入

sudo mysql_secure_installation

会有如下顺序直接,按照如下顺序执行.

选择密码验证策略等级，我这里选择0 （low），回车
输入新密码两次
确认是否继续使用提供的密码？输入y ，回车
移除匿名用户？输入y ，回车
不允许root远程登陆？我这里需要远程登陆，所以输入n ，回车
移除test数据库？输入y ，回车
重新载入权限表？输入y ，回车
具体栗子如下所示.

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
 
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MySQL to secure it, we'll need the current
 
password for the root user. If you've just installed MySQL, and
 
you haven't set the root password yet, the password will be blank,
 
so you should just press enter here.
 
Enter current password for root (enter for none):<–初次运行直接回车
 
OK, successfully used password, moving on…
 
Setting the root password ensures that nobody can log into the MySQL
 
root user without the proper authorisation.
 
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
 
New password: <– 设置root用户的密码  
 
Re-enter new password: <– 再输入一次你设置的密码
 ###这里如果出现密码... Failed! Error: Your password does not satisfy the current policy requirements
Password updated successfully!   则重新输入,这时可以先设置一个MySQL安全配置向导通过的密码  比如 Root_12root  先设置成功,后续我们更改

Reloading privilege tables..
 
… Success!
 
By default, a MySQL installation has an anonymous user, allowing anyone
 
to log into MySQL without having to have a user account created for
 
them. This is intended only for testing, and to make the installation
 
go a bit smoother. You should remove them before moving into a
 
production environment.
 
Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车
 
… Success!
 
Normally, root should only be allowed to connect from 'localhost'. This
 
ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
 
… Success!
 
By default, MySQL comes with a database named 'test' that anyone can
 
access. This is also intended only for testing, and should be removed
 
before moving into a production environment.
 
Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车
 
- Dropping test database…
 
… Success!
 
- Removing privileges on test database…
 
… Success!
 
Reloading the privilege tables will ensure that all changes made so far
 
will take effect immediately.
 
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车
 
… Success!
 
Cleaning up…
 
All done! If you've completed all of the above steps, your MySQL
 
installation should now be secure.
 
Thanks for using MySQL!

在当前终端使用如下命令登陆MySql.

mysql -uroot -p <上面步骤中设置的密码>

登陆使用如下mysql命令,设置root用户的 host 为任意IP.

use mysql; ##切换到mysql数据库
update user set host='%' where user='root'; ##设置可以远程访问 
flush privileges;##配置立即生效
quit; ##退出 mysql

使用如下命令关闭3306防火墙.如果不行,请到服务器控制台在安全组中设置3306端口.

sudo firewall-cmd --add-port=3306/tcp --permanent ##打开3306端口

sudo firewall-cmd --reload ##防火墙配置立即生效

修改mysql的端口或者其他信息

修改完之后需要重启服务器哦

image.png

5.7开启二进制

#5.7以下
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index

三个参数来指定，
第一个参数是打开binlog日志
第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件
第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

image.png

8.0开启二进制日志

在my.cnf主配置文件中直接添加

#8.0
log_bin=/var/run/mysqld/binlog

mysql5.X，需要手动写log_bin=ON
mysql8.0开始，默认就开户bin_log，所以不需要再配置开关log_bin=ON
show variables like ‘%log_bin%’;查询得到的字段信息是log_bin_basename=xxx和log_bin_index=xxx，实际上只要写log_bin就可以了
如果需要修改bin_log日志的存储位置需要先停止mysql，再把原bin_log路径的下的bin_log*全部移到新路径，再启动mysql
mysql5.7以上需要添加server-id，不然启动报错，mysql5.7以下不需要添加server-id。

重新启动我们的mysql

启动成功之后，我们可以登陆查看我们的配置是否起作用

show variables like '%log_bin%'

修改文件夹的所有者

chown -R jay:fefjay my #修改my文件所属用户为jay，所属用户组为fefjay

mysql的启动关闭

#开启
systemctl start  mysqld
service mysqld start
#关闭
systemctl stop mysqld
service mysqld stop
#重启
service mysqld restart
#查看服务状态
service mysqld status

mysql5.7以上修改密码

修改 /etc/my.cnf,加入 skip-grant-tables;

vim /etc/my.cnf

在[mysqld]添加如下内容，没有就自己加一个[mysqld]，并保存退出；

[mysqld]
          
skip-name-resolve
skip-grant-tables

重启服务

systemctl restart mysqld

空密码直接进入mysql

[root@centos7 ~]# mysql -u root -p
Enter password: (这里是空密码，直接回车)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 
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>

查看mysql的密码规则

SHOW VARIABLES LIKE 'validate_password%';