查看系统版本

[root@mysql ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
 

mariadb

查看系统是否存在mariadb

[root@mysql ~]# rpm -qa |grep mariadb

[root@mysql ~]# yum install mariadb-server mariadb      # 安装


systemctl enable --now mariadb  # 启动数据库，并配置开机自动启动
  
mysql_secure_installation       # 使用以下命令初始化mariadb并配置root密码为123456
  
# 执行命令：mysql_secure_installation
# 直接回车，空
# 是否设置密码：y
# 输入设置密码：123456
# 再次输入密码：123456
# 是否移除anonymous用户：y
# 是否不允许root远程登录：n
# 是否移除test数据库：y

 mysql

1.官网下载安装mysql-server

[root@mysql ~]# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
[root@mysql ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
[root@mysql ~]# yum install mysql-community-server


2.安装成功后重启mysql服务。

[root@mysql ~]# service mysqld restart 或 systemctl enable --now mysqld


3.初次安装mysql，root账户没有密码。直接回车

[root@mysql ~]# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


4.mysql设置密码

mysql> set password for 'root'@'localhost' =password('password');    # 括号中password是要改的密码，引号保留
Query OK, 0 rows affected (0.00 sec)

5.编码
mysql配置文件为/etc/my.cnf

最后加上编码配置
[mysql]
default-character-set =utf8

这里的字符编码必须和/usr/share/mysql/charsets/Index.xml中一致。

6.远程连接设置

把在所有数据库的所有表的所有权限赋值给位于所有IP地址的root用户。
mysql> grant all privileges on *.* to root@'%'identified by 'password';

如果是新用户而不是root，则要先新建用户
mysql>create user 'username'@'%' identified by 'password';  
此时就可以进行远程连接了。

查看MySQL版本的四种方法

1.[root@mysql ~]# mysql -V
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper

2.[root@mysql ~]# mysql --help | grep Distrib
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper

3.mysql> select version();           #  登入数据库执行这个命令select version();
+-----------+
| version() |
+-----------+
| 5.6.51    |
+-----------+
1 row in set (0.00 sec)

4.mysql> status;               #  登入数据库执行这个命令status;
--------------
mysql  Ver 14.14 Distrib 5.6.51, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.51 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 21 min 5 sec

Threads: 1  Questions: 19  Slow queries: 0  Opens: 67  Flush tables: 1  Open tables: 60  Queries per second avg: 0.015

 

mysql8.0安装

rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

yum --enablerepo=mysql80-community install mysql-community-server -y  #安装

service mysqld start  # 启动服务

grep "A temporary password" /var/log/mysqld.log # 查看临时密码


mysql_secure_installation  # 配置Mysql安全策略，回车复制粘贴上面命令的查看的临时密码


[root@mysql ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password:               # 密码要改复杂点a9peEwhEK(>q

Re-enter new password: 
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n   # 密码强度100分，选N跳过

 ... skipping.
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? (Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : y
 - 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? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 


配置远程访问

创建新的远程用户
CREATE USER '用户名'@'%' IDENTIFIED BY '密码';
授权给远程用户
GRANT ALL ON *.* TO '[用户名]'@'%'; # ALL表示授予所有权限、*.*表示所有数据库中的所有表、%表示任意IP可以远程连接


使用navicat连接

连接时将会出现如下错误："2059 - authentication plugin 'caching_sha2_password' cannot be loaded: 乱码"
以旧版的方式重新设置远程用户的密码。

ALTER USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '密码';