目录
总纲目录
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
,则需更新数据源列表,可参考总纲的 更新数据源列表
相关配置信息文件以及对应路径
- Mysql配置文件
mysqld.cnf
,路径/etc/mysql/mysql.conf.d
常用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的初始配置进行任何修改,就用该工具去访问时,即会出现如下现象
步骤一 修改配置文件
正常情况下,安装上的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账号远程就访问不了了