文章目录
关联文章:
《linux下mysql的安装与使用》
《用SpringBoot创建mysql工程》
【mysql】 入门例子
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
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错误,无法远程连接