ubuntu 安装与使用mysql

常用命令

#安装MySQL服务器
apt-get install mysql-server
#按照MySQL客户端
apt-get install mysql-client


#启动MySQL数据库服务
service mysql start
systemctl start mysql.service

#重启MySQL数据库服务
service mysql restart
systemctl restart mysql.service

#停止MySQL数据库服务
service mysql stop
systemctl stop mysql.service

#查看MySQL运行状态
service mysql status
systemctl status mysql.service

#设置MySQL服务开机自启动
systemctl enable mysql.service
#停止MySQL服务开机自启动
systemctl disable mysql.service

MySQL的配置文件

配置文件位置：/etc/mysql/mysql.conf.d/mysqld.cnf

配置MySQL远程登录

需要配置绑定地址来控制是否允许远程登录，我一般会将bind-address配置为 0.0.0.0，除此之外，还要注意远程登录的用户host字段不能是localhost，通常设置为%。另外用户远程登录，需要注意配置防火墙允许数据库默认端口3306的访问。

 配置启用存储过程或函数

mysql默认用户不得创建或修改存储函数，可以通过配置文件配置如下参数来启用：

log_bin_trust_function_creators = 1 

root用户登录

MySQL会默认创建root用户，且没有设置密码，但是该用户需要用操作系统的root用户来登录才行。用其他用户登录会被拒绝。

使用其他用户失败的登录：

[sams@pobosrv:/var/log]$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

使用root用户成功登录：

[root@pobosrv:~]$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.35-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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创建用户与授权

使用root用户登录

# 登录mysql并输入密码
mysql -u root -p
# mysql8 修改密码方式
alter user 'root'@'localhost' identified by '这里填你要的密码';

另注：在root初始密码为空时，设置密码始终失败。这里解决方式是，先创建另外一个用户比如说test，然后给test所有权限，这时可以通过navicat使用test用户登录，然后修改root用户的插件为：caching_sha2_password，并设置密码。

mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'test';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

无需重启数据库即可生效（且mariadb自动会被替换，不再生效）

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：你将创建的用户名

host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

注意，使用%通配符创建外网访问用户后，登录时需要明确访问的IP地址，例如

mysql -u test -h 115.28.203.224 -p

设置与更改用户密码

ALTER USER 'username'@'host' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;  

删除用户

DROPUSER'username'@'host';

查看用户信息

查询用户表

SELECT User, Host FROM mysql.user;
 
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| diming           | %         |
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
————————————————

查看用户权限

show grants for'username'@'host';

用户授权

GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL

databasename：数据库名

tablename：表名

如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

注意:用以上命令授权的用户不能给其它用户授权，如果想让被授权的用户可以将他的拥有的权限授给其他用户，用以下命令

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

刷新授权

flush privileges;

这一步一定要做，不然无法成功！这句表示从mysql数据库的grant表中重新加载权限数据，因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。

例子

GRANT SELECT, INSERT privileges ON test.user TO 'pig'@'%';
GRANT ALL privileges ON *.* TO 'pig'@'%';
GRANT ALL privileges ON maindataplus.* TO 'pig'@'%';

撤销用户权限

REVOKE privileges ON databasename.tablename FROM 'username'@'host';

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL

databasename：数据库名

tablename：表名

如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

例子

REVOKE SELECT privileges ON *.* FROM 'pig'@'%';

注意

假如你在给用户'pig'@'%'授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO 'pig'@'%'，则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作。

相反，如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select权限。

具体信息可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

查看加密的规则

select Host,User,plugin from mysql.user;

修改加密方式

ALTER USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

数据库常用命令

#创建数据库
CREATE DATABASE 数据库名;
#​​​​​​​删除数据库
DROP DATABASE 数据库名称;


#查看显示所有数据库
show databases;

#查看当前使用的数据库
select database();

#查看数据库使用端口
show variables like 'port';

#查看数据库的表信息
show tables;

#查看表结构
show columns from table_name; 
#或
describe table_name;

#查看表生成的DDL
show create table table_name;
#DDL（data definition language）数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化。
#该命令把创建表的DDL，表结构、类型，外键，备注等全部显示出来。

查看Mysql数据库信息

进入information_schema 数据库（存放了其他的数据库的信息）

#查询所有数据的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables;
#查看指定数据库的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='数据库名称';
#查看指定数据库的某个表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='数据库名称' and table_name='表名称';
#查看索引所占的空间大小
select concat(round(sum(index_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='mysql';

查看数据库参数连接等信息

#查看数据文件存放路径
show variables like '%datadir%';
#查看数据库编码
show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

#character_set_client 为客户端编码方式；
#character_set_connection 为建立连接使用的编码；
#character_set_database 为数据库的编码；
#character_set_results 为结果集的编码；
#character_set_server 为数据库服务器的编码；


#查看数据库的最大连接数
show variables like '%max_connections%';

#查看数据库当前连接数，并发数
show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 27    |
| Threads_created   | 48    |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

#Threads_cached 代表当前此时此刻线程缓存中有多少空闲线程。
#Threads_connected 代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
#Threads_created 代表从最近一次服务启动，已创建线程的数量。
#Threads_running 代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。