学习笔记｜MySQL

本文最后更新于：5 years ago

1.数据库基本概念

**数据库（DateBase）：**用于储存和管理数据的仓库

1.1特点

  • 持久化储存数据，数据库是一个文件系统
  • 方便储存和管理数据
  • 使用了统一的方式操作数据库——SQL

2.MySQL 数据库软件

2.1安装

CentOS 需要安装 mariadb。分别为 mariadb-server 和 mariadb-client。

1
yum install mariadb-server

2.2卸载

1
yum remove mariadb-server

2.3启动

1
systemctl start mariadb

2.4开机自启

1
systemctl enable mariadb

2.5配置

执行以下语句，开始进入配置步骤。

1
mysql_secure_installation   # mysql 安全安装

根据提示，完成以下步骤：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

# 修改密码
Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!

# 删除匿名用户
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

# 关闭 root 管理员的远程登陆
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

# 删除用于测试的数据库 “test”
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

# 重新加载权限表
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

# 配置成功
Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

2.7配置字符集

1
vim /etc/mysql/my.cnf

在[mysqld]中添加 default-character-set=utf8 。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in https://fedoraproject.org/wiki/Systemd
character-set-server=utf8

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

PS：Client 的字符集在 /etc/my.cnf.d/client.cnf 中的 [client] 和/etc/my.cnf.d/mysql-clients.cnf 中的 [mysql] 添加 default-character-set=utf8 。

2.8创建用户

1
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
参数说明
username将要创建的用户名
host指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

2.9其他命令

2.9.1登陆

1
2
3
4
5
# 显示密码登陆
mysql -uroot -pYourPassword

# 不显示密码登陆
mysql -uroot -p

2.9.2远程登陆

利用 -h 加 ip。

1
2
3
mysql -h127.0.0.1 -uroot -p
# 或者
mysql --host=127.0.0.1 --user=root --password

报错排查

  • ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.47.128’ (111)
    • 设置 bind-address 监听IP
      1
      vim /etc/mysql/mariadb.conf.d/50-server.cnf
  • ERROR 1698 (28000): Access denied for user ‘root’@‘192.168.47.1’
    • 设置root 用户权限
      1
      2
      UPDATE mysql.user SET host='%' WHERE user='root' AND host='localhost';
      FLUSH PRIVILEGES;
    • 查看服务器日志 /var/log/mysql/error.log
    • 设置权限
      1
      GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
      • GRANT ALL PRIVILEGES：授予用户所有权限（包括选择、插入、更新、删除、创建数据库、修改权限）。
      • ALL PRIVILEGES：允许该用户在 MySQL 中执行任何操作。
      • ON *.*：指定了权限的作用范围。（第一个 * 表示所有数据库，第二个 * 表示所有数据库中的所有表）。
      • TO 'root'@'%'：指定用户的用户名和允许连接的主机。'root' 是用户名，表示这是对 root 用户授予权限。'%' 表示允许该用户从任何 IP 地址进行访问。如果想限制某个 IP，可以将 '%’ 改成具体的 IP 地址，如 '192.168.1.100'。
      • IDENTIFIED BY 'your_password'：用于设置用户 root 的密码。'your_password' 是密码，可以替换成你希望为 root 用户设置的实际密码。如果已经为 root 用户设置了密码，则可以省略此部分。
      • WITH GRANT OPTION：表示授予 root 用户 转授权限 的能力。即，root 用户可以将自己拥有的权限授权给其他用户。

2.9.3退出

1
2
3
exit
# 或者
quit

3.目录结构

3.1路径

  • usr/bin/mysql 是指：mysql的运行路径
  • var/lib/mysql 是指：mysql数据库文件的存放路径
  • usr/lib/mysql 是指：mysql的安装路径（有可能是 usr/lib64/mysql）

3.2基本概念

3.2.1数据库

目录

  • information_schema：视图（描述mysql 下的库和文件）

  • mysql：mysql的核心数据库，存放mysql服务的核心数据

  • proformance_schema：对性能提升的操作的表

3.2.2表

文件

表：文件（.frm）

3.2.3数据

数据：表中的记录

4.SQL

4.1基本概念

Structured Query Language（结构化查询语言）

SQL：定义了所有操作关系型数据库的规则。

4.2SQL 语法规则

  1. SQL语句可以单行/多行书写，以分号结尾。

  2. 用空格和缩进增强语句可读性。

  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  4. 注释

    • 单行注释：
    1
    2
    3
    -- 两个横杠一个空格后面加注释。

    # 号后直接写注释（MySQL特有的）。
    • 多行注释
    1
    2
    3
    4
    /*
    这是
    注释 
    */

4.3SQL分类

分类备注
DDL
Data Definition Language，数据定义语言		操作数据库和表
DML
Data Manipulation Language，数据操纵语言		增删改表中的数据
DQL
Data Query Language，数据查询语言		查询表中的数据
DCL
Data Controlling Language，数据控制语言		定义数据库访问权限和安全级别

5.DDL

5.1操作数据库：CRUD

5.1.1Create：创建

  1. 创建数据库 db1
1
2
3
4
5
create database db1;

-- result
[MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.01 sec)
  1. 若数据库 db1 不存在，则创建db1
1
2
3
4
5
create database if not exists db1;

-- result
[MariaDB [(none)]> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
  1. 创建数据库 db1 时，指定字符集为 utf-8
1
2
3
4
5
create database db1 character set utf8;  -- 注意是 utf8，不是 utf-8。

-- result
[MariaDB [(none)]> create database db1 character set utf8;
Query OK, 1 row affected (0.00 sec)
  1. 创建数据库 db1 时，检查是否存在，若不存在指定其字符集为 utf-8
1
2
3
4
5
create database if not exists db1 character set utf8;

-- result
[MariaDB [(none)]> create database if not exists db5 character set utf8;
Query OK, 1 row affected (0.00 sec)

5.1.2Retrieve：查询

  1. 查询所有数据库的名称
1
2
3
4
5
6
7
8
9
10
11
12
13
14
show databases;

-- result
[MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
  1. 查询数据库 db1 的字符集
1
2
3
4
5
6
7
8
9
10
show create database db1;

-- result
[MariaDB [(none)]> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

5.1.3Updata：修改

  1. 修改数据库的字符集
1
2
3
4
5
alter database db1 character set gbk;

-- result
[MariaDB [(none)]> alter database db1 character set gbk;
Query OK, 1 row affected (0.00 sec)

5.1.4Delete：删除

  1. 删除数据库
1
2
3
4
5
drop database db2;

-- result 
MariaDB [(none)]> drop database db2;
Query OK, 0 rows affected (0.00 sec)
  1. 删除之前判断是否存在
1
2
3
4
5
drop database if exists db2;

-- result
MariaDB [(none)]> drop database if exists db2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

5.1.5使用数据库

1.进入数据库（使用数据库）

1
2
3
4
5
use db1;

-- result
[MariaDB [(none)]> use db1
Database changed
  1. 查询正在使用的数据库
1
2
3
4
5
6
7
8
9
10
select database();

-- result 
[MariaDB [db1]> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

5.2操作表：CRUD

5.2.1Create：创建

  1. 创建表

5.2.2Retrieve：查询

  1. 查询数据库中所有表的名称

  2. 查询表结构

5.2.3Updata：修改

5.2.4Delete：删除

docker 运行 mysql

X.1 主要流程

  1. pull image

    最新版

1
docker pull mysql
  1. 设置配置文件
1
2
mkdir -p /data/mysql/config
vim /data/mysql/config/my.cnf

在my.cnf中添加参数

1
2
3
4
[mysqld]
user=mysql
lower_case_table_names=1  # 在处理表名时是否区分大小写，无论操作系统的默认行为如何，表名都会转换为小写并且始终以小写进行比较。
event_scheduler=ON # 启用事件调度器，MySQL 会按照定义的计划执行事件。
  1. 启动 mysql
1
2
3
4
5
6
7
docker run -d mysql:latest\   # -d 表示后台运行最新版本的 mysql
    -p 3306:3306\             # -p 设置端口映射
    -e MYSQL_ROOT_PASSWORD=root\  # 初始化用户密码
     -e TZ=Asia/Shanghai\    # 设置时区
    --name container_mysql\ # 设置容器名称 
    -v /data/mysql/config/my.cnf:/etc/mysql/my.cnf\    # 将本地的配置文件，挂载到容器中
    -v /data/mysql/db:/var/lib/mysql\                  # 将本地的 db 目录，挂载在容器中，当作数据库数据存放的位置，以达到持久化的目的
  1. 进入容器，使用 mysql
1
docker exec -it mysql-container bash

X.2 遇到的问题

/data/mysql/config/my.cnf 配置错了一个参数，导致 docker run 之后，容器退出。

导致无法进入容器，报 container is not running

1
2
➜  config docker exec -it container_mysql bash
Error response from daemon: Container 0e05eabc064db236faf34f788cb738db1018040f65669ee7ace15a2f32b1ffc7 is not running
  1. 现象

docker ps看不到应用，docker ps -a 可以看到应用，但是显示 states 为 exited。

1
2
3
➜  config docker ps -a
CONTAINER ID   IMAGE          COMMAND                  CREATED         STATUS                     PORTS     NAMES
c6251bcd7309   mysql:latest   "docker-entrypoint.s…"   3 seconds ago   Exited (1) 2 seconds ago             container_mysql
  1. 解决：查看 docker log
1
docker logs -f container_id
1
2
3
4
5
6
7
8
➜  / docker logs -f container_mysql
2023-01-16 23:51:09+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.31-1.el8 started.
2023-01-16 23:51:09+08:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config
    command was: mysqld --verbose --help --log-bin-index=/tmp/tmp.PlBV02NSRG
    2023-01-16T15:51:09.759899Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2023-01-16T15:51:09.759939Z 0 [ERROR] [MY-000077] [Server] /usr/sbin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2023-01-16T15:51:09.763322Z 0 [ERROR] [MY-010119] [Server] Aborting
➜  / cd /data/mysql/

删除错误的配置，docker run 就可以。

X.3 数据库连接排查步骤

1.确认服务运行状态

  • 确认容器是否正确启动并运行 MySQL，确保容器的端口正确映射到宿主机：

    1
    docker ps

    你应该看到类似以下的输出：

    1
    2
    CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                   NAMES
    578dd9b4f9a6        mysql:latest        "docker-entrypoint.s…"   2 hours ago         Up 2 hours          0.0.0.0:3306->3306/tcp  mysql-container

2.检查网络联通性

在远程机器上尝试连接 MySQL。

1
2
3
4
telnet 172.16.103.134 3306

# 作为对比，可以 ping
ping 172.16.103.134

3.检查端口监听

确保宿主机的 3306 端口正在监听。

1
sudo netstat -tuln | grep 3306

  • 正在监听。

    1
    tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

4.检查 MySQL 配置

检查 MySQL 配置文件（例如 /etc/mysql/my.cnf 或 /etc/my.cnf）：

1
cat /etc/mysql/my.cnf | grep bind-address

  • 如果 bind-address 设置为 127.0.0.1，说明 MySQL 仅允许本地连接，远程连接被禁止。修改配置允许远程访问： 将 bind-address = 127.0.0.1 改为 bind-address = 0.0.0.0，然后重启 MySQL。

  • 如果在 MySQL 配置文件 /etc/my.cnf 中没有显式地配置 bind-address，默认情况下，MySQL 通常会绑定到 0.0.0.0（即允许来自任何 IP 的连接）

    • 在 MySQL 中运行以下 SQL 查询来查看 MySQL 实际上绑定的 IP 地址：

      1
      SHOW VARIABLES LIKE 'bind_address';
      1
      2
      3
      4
      5
      6
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | bind_address  | *     |
      +---------------+-------+
      1 row in set (0.01 sec)

5.检查端口映射

检查 Docker 在 bridge 模式下端口映射规则，以下命令检查 NAT 表规则：

1
sudo iptables -t nat -L -n

  • DOCKER 链是否包含以下规则：

    1
    2
    DNAT       tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:3306 to:172.17.0.2:3306
    MASQUERADE  tcp  --  172.17.0.2           172.17.0.2           tcp dpt:3306

6.检查宿主机防火墙配置

  • 确保宿主机的防火墙允许外部流量访问 3306 端口：

    1
    sudo iptables -L -n -v

  • 如果 FORWARD 链的默认策略是 DROP，执行以下命令允许流量通过：

    1
    sudo iptables -P FORWARD ACCEPT

7.检查 MySQL 配置

检查 MySQL 配置文件（例如 /etc/mysql/my.cnf 或 /etc/my.cnf）：

1
cat /etc/mysql/my.cnf | grep bind-address

  • 如果 bind-address 设置为 127.0.0.1，说明 MySQL 仅允许本地连接，远程连接被禁止。修改配置允许远程访问： 将 bind-address = 127.0.0.1 改为 bind-address = 0.0.0.0，然后重启 MySQL。

  • 如果在 MySQL 配置文件 /etc/my.cnf 中没有显式地配置 bind-address，默认情况下，MySQL 通常会绑定到 0.0.0.0（即允许来自任何 IP 的连接）

    • 在 MySQL 中运行以下 SQL 查询来查看 MySQL 实际上绑定的 IP 地址：

      1
      SHOW VARIABLES LIKE 'bind_address';
      1
      2
      3
      4
      5
      6
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | bind_address  | *     |
      +---------------+-------+
      1 row in set (0.01 sec)

8.检查用户权限

查看用户的主机（host）字段

1
SELECT user, host, plugin FROM mysql.user;
1
2
3
4
5
6
7
8
9
10
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

  • 主机（host）字段的含义：

    • localhost: 仅允许本地连接。
    • %: 允许从任意远程主机连接。
    • 具体 IP 或域名：仅允许从指定主机连接。

  • 检查 root 的主机字段

    • 确保存在 root@'%'，如果没有，则添加：
    1
    2
    3
    CREATE USER 'root'@'%' IDENTIFIED BY 'your_password';
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

  • 允许远程的常见情况：

    • 如果存在类似 root@'%' 的记录，说明允许远程使用 root 登录。

    • 如果插件为 auth_socket，表示使用了 Unix socket 认证，而非密码认证。

      • 修改为 mysql_native_password 或 caching_sha2_password：
      1
      2
      ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
      FLUSH PRIVILEGES;

    • 如果 plugin 是 mysql_native_password 或 caching_sha2_password，说明允许使用密码认证。

9.检查最大连接数

查询 MySQL 配置的最大连接数。

1
SHOW VARIABLES LIKE '%max_connections%'

查询 MySQL 当前线程的相关状态信息。

1
2
3
4
5
6
7
8
9
SHOW STATUS LIKE 'Threads%';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Threads_connected | 5     |
| Threads_running   | 2     |
| Threads_created   | 120   |
+------------------+-------+
  • Threads_connected 显示当前有 5 个连接。表示 MySQL 使用线程来处理客户端请求。过多的连接（Threads_connected）或者过多的正在执行的线程（Threads_running）可能导致系统资源耗尽，进而影响性能。
  • Threads_running 显示当前有 2 个线程正在执行查询。如果 Threads_running 很高，可能表明存在查询性能瓶颈，或者某些查询操作需要优化。
  • Threads_created 显示自 MySQL 启动以来创建了 120 个线程。如果数量过多，可能意味着每次请求都需要创建新线程，增大了开销。你可以考虑调整 thread_cache_size 来优化线程复用，避免频繁地创建和销毁线程。

10.检查抓包数据

检查流量是没有到主机，还是没有到 docker。

  • 监听 ens33 网络接口上的 3306 端口流量。

    1
    sudo tcpdump -i ens33 port 3306

  • 监听 Docker 网桥上的流量，确认是否有流量到达容器。

    1
    sudo tcpdump -i docker0 port 3306

11.终极手段

重启 Docker 服务

有时候，Docker 网络可能会遇到配置问题，重启 Docker 服务可以帮助重新加载网络配置：

1
sudo systemctl restart docker
Mark
#MySQL
学习笔记｜MySQL
https://www.aimtao.net/mysql/
Posted on
2019-07-14
Licensed under