Ubuntu操作系统下之-MYSQL的常用操作

叫我Aso

已于 2024-04-20 15:48:04 修改

阅读量2.5k

点赞数 2

分类专栏： # 开发环境搭建文章标签： mysql linux centos

于 2020-03-04 12:48:55 首次发布

本文链接：https://blog.csdn.net/T_double/article/details/104577867

版权

开发环境搭建专栏收录该内容

11 篇文章 0 订阅

订阅专栏

总纲目录

Ubuntu操作系统服务器搭建

Mysql服务指令

以下均基于root用户下操作，普通用户在指令前加 sudo

Mysql环境检测

检测是否安装了Mysql服务 dpkg -l | grep mysql ，如果出现以下信息，表示已经安装

//已安装
root@itaso:~# dpkg -l | grep mysql
ii  libmysqlclient20:amd64                 5.7.32-0ubuntu0.18.04.1                         amd64        MySQL database client library
ii  mysql-client-5.7                       5.7.33-0ubuntu0.18.04.1                         amd64        MySQL database client binaries
ii  mysql-client-core-5.7                  5.7.33-0ubuntu0.18.04.1                         amd64        MySQL database core client binaries
ii  mysql-common                           5.8+1.0.4                                       all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                           5.7.33-0ubuntu0.18.04.1                         all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                       5.7.33-0ubuntu0.18.04.1                         amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7                  5.7.33-0ubuntu0.18.04.1                         amd64        MySQL database server binaries
root@itaso:~# 

//未安装
root@itaso:~# dpkg -l | grep mysql
ii  libmysqlclient20:amd64                 5.7.32-0ubuntu0.18.04.1                         amd64        MySQL database client library
ii  mysql-common                           5.8+1.0.4                                       all          MySQL database common files, e.g. /etc/mysql/my.cnf
root@itaso:~#

当然有安装了Mysql才会有帮助指令，所以也可以使用 mysql --help 且同时还能看到Mysql的版本信息

//已安装
root@itaso:~# mysql --help
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2021, 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.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
以下内容省略......

//未安装
root@itaso:~# mysql --help

Command 'mysql' not found, but can be installed with:

apt install mysql-client-core-5.7   
apt install mariadb-client-core-10.1

root@itaso:~#

Mysql的安装

apt-get install mysql-server

root@itaso:~# apt-get install mysql-server
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl
  liblwp-mediatypes-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
以下内容省略......

如遇数据源列表找不到mysql-server，则需更新数据源列表，可参考总纲的 更新数据源列表

常用LINUX操作指令

查看Mysql运行状态

service mysql status

root@instance-8scsy8qh:~# service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2020-04-09 12:55:37 CST; 11min ago
  Process: 13961 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid (code=exited, status=0/SUCCESS)
  Process: 13951 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 13963 (mysqld)
    Tasks: 29 (limit: 2327)
   CGroup: /system.slice/mysql.service
           └─13963 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Apr 09 12:55:36 instance-8scsy8qh systemd[1]: Stopped MySQL Community Server.
Apr 09 12:55:36 instance-8scsy8qh systemd[1]: Starting MySQL Community Server...
Apr 09 12:55:37 instance-8scsy8qh systemd[1]: Started MySQL Community Server.

------------------------------------------------
root@itaso:~# service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Sat 2021-04-10 10:16:18 CST; 8min ago
 Main PID: 23149 (mysqld)
    Tasks: 27 (limit: 2240)
   CGroup: /system.slice/mysql.service
           └─23149 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Apr 10 10:16:18 itaso systemd[1]: Starting MySQL Community Server...
Apr 10 10:16:18 itaso systemd[1]: Started MySQL Community Server.
root@itaso:~#

查看Mysql进程

ps aux|grep mysql

root@itaso:~# ps aux|grep mysql
mysql    23149  0.0  9.1 1162048 178308 ?      Sl   10:16   0:00 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root     27806  0.0  0.0  14436  1096 pts/0    S+   10:22   0:00 grep --color=auto mysql
root@itaso:~#

查看端口是否被占用

netstat -an|grep 3306

表示端口被以下进程占用了
root@itaso:~# netstat -an|grep 3006
tcp        0      0 0.0.0.0:3006            0.0.0.0:*               LISTEN     
tcp        0      0 172.16.16.4:3006        183.251.18.120:58258    ESTABLISHED
tcp        0      0 172.16.16.4:3006        183.251.18.120:58257    ESTABLISHED

表示端口并没有进程在使用
root@itaso:~# netstat -an|grep 3007
root@itaso:~#

操作Mysql服务

service mysql stop|start|restart

防火墙

关闭防火墙 service iptables stop

常用Mysql操作指令

进入Mysql服务

mysql -u root -p

root@itaso:~# mysql -u root -p
Enter password: 输入数据库密码

查看当前服务使用的端口号

show global variables like 'port';

mysql> show global variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

修改当前服务的端口号

修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf中的port的端口号后重启Mysql服务器即可

port = 3306

修改默认用户密码

1、进入mysql目录 /etc/mysql/ 进行查看debian.cnf文件

root@instance-8scsy8qh:~# cd /etc/mysql/
root@instance-8scsy8qh:/etc/mysql# cat debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = klGn4HxPadFXxzQU
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = klGn4HxPadFXxzQU
socket   = /var/run/mysqld/mysqld.sock

2、使用user为debian-sys-maint的用户以及password为klGn4HxPadFXxzQU进行数据库登录，当然，只要能登录mysql，自然就能操作数据库的很多事情了，比如密码的修改，数据库的查看等操作

root@instance-8scsy8qh:/etc/mysql# mysql -udebian-sys-maint -pklGn4HxPadFXxzQU
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| PLEASE_READ_ME_VVV |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql>

查看具体用户的权限

show grants for root;

mysql> show grants for root;
+-------------------------------------------+
| Grants for root@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)

新增数据库登录账号

新增数据库登录账号itsdf07，并且对所以客户端开放，密码为123456

CREATE USER 'itsdf07'@'%' IDENTIFIED BY "123456";

新增数据库登录账号itsdf07，并且只对客户端127.0.0.1开放，密码为123456

CREATE USER 'itsdf07'@'127.0.0.1' IDENTIFIED BY "123456";

账号权限设置

grant 权限1，权限2，...权限n on 数据库名称.表名称 to 用户名@用户地址 identified by '连接密码';

mysql> grant all on *.* to root@'%' identified by 'root123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>

权限可以多个，共14种，分别为：select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等，其中赋予全部权限时使用all privileges
数据名称.表名称 如果用*.*表示，说明赋予用户服务器上所有数据库所有表的权限。
用户名 即用于登录数据库的名称，如root
用户地址 可以是localhost，可以是ip地址、机器名字、域名等等，也可以用%表示任何连接地址。
连接密码 不能为空，否则会赋予权限失败。

Mysql设置刷新

flush privileges;

使用过程中遇到的问题

Mysql的无法远程访问

作为服务器上的Mysql服务，其开发者主要还是关心如何操作其数据库，所以只需要关心第三方的远程访问数据库工具能否访问即可，而不巧的是关于Ubuntu系统下的Mysql服务，在安装完成时默认只允许本机访问，并不允许其他第三方工具远程访问，比如上边所提供的Navicat for MySQL工具，当还没对Mysql的初始配置进行任何修改，就用该工具去访问时，即会出现如下现象
Navicat for MySQL远程访问数据库时被拒现象
步骤一修改配置文件
正常情况下，安装上的Mysql默认端口号为3306 通常情况下，我们也没有必要去修改它，那么远程连接会被拒绝呢？优先考虑两个方向 防火墙 以及端口占用
service iptables stop 关闭防火墙
netstat -an|grep 3306 检测端口情况

root@instance-8scsy8qh:~# netstat -an|grep 3306
tcp        0      0 127.0.0.1:3306            0.0.0.0:*               LISTEN
......

以上信息是表示3306端口监听在127.0.0.1，即只有本机客户端可以访问，其他服务器无法访问，那么怎么让其他服务器可以正常连接到该服务区的端口呢？需要修改配Mysql的置文件mysql.cnf，其路径为/etc/mysql/mysql.conf.d/
方法一、把3306端口监听在0.0.0.0上，表示没有访问地址的限制，那么其他服务器则可以连接该服务器的该端口
把bind-address = 127.0.0.1 改成bind-address = 0.0.0.0 即可

root@instance-8scsy8qh:~# netstat -an|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
......

方法二、既然这边绑定的地址是本机，那么是否可以追加访问者的地址呢？当然可以，只要将bind-address后面增加远程访问IP地址或者禁掉这句话就可以让远程机登陆访问了（允许多个IP可访问mysql服务器，空格隔开）

bind-address=127.0.0.1 183.251.17.221

反观方法二，对于开发者来说，所用网络的IP基本都是动态的，所用用这方法的人比较少，不过对于类似局域网的云服务器来说，是不是就可以设定固定服务器来访问，增加数据库的安全性？
步骤二更新用户权限
进入mysql之后操作如下指令

root@instance-8scsy8qh:~# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4154
Server version: 5.7.28-0ubuntu0.18.04.4 (Ubuntu)

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> grant all on *.* to root@'%' identified by 'root123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> quit;
Bye
root@instance-8scsy8qh:~# service mysql restart
root@instance-8scsy8qh:~#

以上意思为 允许任何用户地址以root的用户名和root123的连接密码进行访问，并赋予了所有数据库的操作权限，其中这边的密码root123只是设置远程访问数据库时需要的密码，与服务端本地进行root账号访问是独立的，比如服务端root没有设置密码，远程连接设置了root123

限制指定用户远程访问数据库

数据库的用户是存储在mysql数据库中的user表中
进入数据库，并且切换至mysql数据库进行sql语句操作

mysql> use mysql;		//切换到mysql数据库，因为要该数据库中的user表进行数据操作
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> 
mysql> select host,user from user where user="root";	//查询当然root账号的相关数据
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| 127.0.0.1 | root |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)
//% 表示允许远程访问，所以只需要把该数据删掉即可
mysql>
mysql> delete from user where user="root" and host!="localhost";
Query OK, 2 rows affected (0.00 sec)

mysql> select host,user from user where user="root";
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

mysql> flush privileges;	//刷新完配置之后，root账号远程就访问不了了