【mysql】linux下mysql的安装与使用

关联文章：

《linux下mysql的安装与使用》
《用SpringBoot创建mysql工程》
【mysql】 入门例子

Windows下MySQL-5.7版本下载与安装教程

1、下载mysql安装包

安装包：mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

下载地址：https://dev.mysql.com/downloads/mysql/

下载完毕后上传到linux服务器

2、解压

#进入此文件夹
cd /usr/local
#解压安装包
tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C ./
#将解压文件重命名为mysql
mv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql

3、创建用户和组

#创建mysql用户组
groupadd mysqlg
#创建mysql用户
useradd -r -g mysqlg mysql

4、将安装目录所有者及所属组改为mysql

#修改mysql文件夹的归属用户组以及用户
chown -R mysql:mysqlg mysql/

5、创建data文件夹，用于存放数据库表之类的数据

创建目录 /usr/local/mysql/data，供后面使用

cd /mysql
mkdir data
#
chown mysql:mysqlg data

这个目录可以是任意目录，但是要注意权限，如果报权限错误，就修改该目录的归属组和赋值755权限(可能不合理，但能保证运行起来)，尽量在/usr/local下，并且如果后续有多个目录，则保证每个层级都要设置归属组合权限。

第六步初始化可能的报错信息：

mysqld: Can't change dir to '/usr/local/mysql/data/' (Errcode: 13 - Permission denied)

6、初始化

初始化的作用是构建实例基础环境，表空间，权限库等。

--datadir=/usr/local/mysql/data 指定初始化的存储目录，之后，该目录下会生成相关的默认库，如果需要重新初始化，清空该目录，重新执行初始化语句即可：

[root@localhost local]# /usr/local/mysql/bin/mysqld --user=mysql --basedir=/root/test/mysql/ --datadir=/usr/local/mysql/data --initialize
2021-01-08T01:06:06.523415Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-08T01:06:07.438465Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-08T01:06:07.585008Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-08T01:06:07.673112Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b0475e0f-514d-11eb-b6c3-fa163e09a184.
2021-01-08T01:06:07.677519Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-08T01:06:08.454518Z 0 [Warning] CA certificate ca.pem is self signed.
2021-01-08T01:06:08.763588Z 1 [Note] A temporary password is generated for root@localhost: *HEP*k<su8Sg

此处需要注意记录生成的临时密码，如上文结尾处的*HEP*k<su8Sg

7、编辑配置文件

vi /etc/my.cnf，作用提供端口号等配置。

在配置文件中写入以下内容：

[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
user=mysql
port=3306
character-set-server=utf8
# 取消密码验证
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# skip-grant-tables
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

8 将mysql加入到服务中

配置启动脚本，拷贝mysql.server ：

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

启动原理：《linux下/etc/init.d的简单介绍》

9、设置开机启动(可跳过)

[root@localhost mysql]# chkconfig mysql on

10、启动mysql服务

[root@localhost mysql]# service mysql start
Starting MySQL. SUCCESS!

11、登陆mysql

提示输入密码，第六步中的*HEP*k<su8Sg：

[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

如果开启了免密登陆，skip-grant-tables是放开的，省略用户名和密码，直接登陆：

[root@localhost mysql]# /usr/local/mysql/bin/mysql

修改密码

set password for root@'localhost' = password('test123');报错：

mysql> set password for root@'localhost' = password('test123');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决办法：先执行 flush privileges;

mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> set password for root@'localhost' = password('test123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改完成后，可以exit退出，再执行连接命令，验证修改的密码是否正确。

验证查询操作

查看当前存在的数据库，说明可以正常工作了：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

切换至mysql实例，并查询下面的所有表：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
.....

启用远程主机登录

默认没有开启远程，会报MySQL 1130错误，无法远程连接：

use mysql;
update user set user.Host='%' where user.User='root';
flush privileges;

详细参见 MySQL 1130错误，无法远程连接

《Linux下安装MySQL》
《linux下mysql的安装与使用》