MySQL/MariaDB

mysql
数据库
-
- 字
2024-08-27

序※

MySQL是目前最主流的强大的关系型数据库。

MariaDB是MySQl的社区开发分支，可以完全兼容MySQL。Archlinux自2013年3月25日默认的MySQL实现已经切换到Mariadb[1]。

安装※

对于Arch linux※

对于Archlinux，可直接使用pacman安装mariadb。

之前的mysql已经下降到AUR：mysql包。

$sudo pacman -Sy mariadb mariadb-libs

提示：

  • 如果数据库 (位于 /var/lib/mysql) 运行在 Btrfs 分区之上, 你应当在创建数据库之前禁用 Copy-on-Write 特性。
  • 如果数据库运行在 ZFS 分区之上, 你应该在创建数据库之前参阅 ZFS#Databases 。

 

在 enable 或者 start mariadb.service之前，务必执行初始化。

对于其它系统※

你可能需要使用yum或者apt安装

源码安装※

检查依赖及开发工具包（

gcc
gcc-c++ 
cmake
...

创建用户和用户组

# groupadd -r -g 303 mysql
# useradd -r -g 303 -u 303 -s /bin/false mysql
# id mysql
uid=303(mysql) gid=303(mysql) groups=303(mysql)

下载软件包：从镜像源下载需要版本的源码包 

eg.
$ wget -c  mysql-boost-5.7.37.tar.gz

解压软件包

tar xf mysql-boost-5.7.37.tar.gz -C /usr/local/src #  -C 指定解压路径

进入目录

make
make install

初始化※

Arch linux Mariadb※

安装mariadb软件包之后，你必须在启动 mariadb.service 之前运行下面这条命令：

# mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

提示：如果数据目录使用的不是 /var/lib/mysql，需要在 /etc/my.cnf.d/server.cnf 文件的 [mysqld] 部分设置 datadir=<数据目录>

注意：mysql文件在Btrfs和zfs等具有CoW（写时复制）功能的分区/子卷下需要禁用文件Cow功能，否则可能会导致数据丢失！

Btrfs可以参考禁用Cow章节

配置※

启动 mysql / mariadb 服务器，并添加 root 维护帐号后，可以登录服务器进行进一步配置：

# mariadb -u root -p

 注意： 默认密码为空，直接敲回车键登录

添加新用户※

以下是创建一个密码为'some_pass'的'monty'用户的示例,并赋予 mydb 完全操作权限：

$ mariadb -u root -p
MariaDB> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'monty'@'localhost';
MariaDB> quit

配置文件※

MariaDB 会按照以下顺序读取配置文件 (根据 mysqld --help --verbose | tail -20 的输出):

/etc/my.cnf /etc/my.cnf.d/ ~/.my.cnf

启用自动补全※

注意： 启用这项功能会增加客户端启动时间。

MySQL 默认禁用客户端自动补全功能。要在整个系统中启用它，编辑 /etc/my.cnf.d/mysql-clients.cnf，在mysql下 添加 auto-rehash。注意：不要将auto-rehash写在mysqld下，下次客户端启动时就会启用自动补全。

使用UTF8MB4※

警告：在更改字符集之前，请务必先创建备份。

注意：mariadb包已经使用utf8mb4作为字符集，utf8mb4_unicode_ci作为校对规则。使用默认（字符）设置的用户可能希望跳过本节。

推荐使用UTF8MB4而不是UTF-8，因为它允许完整的Unicode支持。

将以下数值追加到位于/etc/my.cnf.d/my.cnf的主配置文件中：

[client]
default-character-set = utf8mb4
[mariadb]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4
[mariadb-client]
default-character-set = utf8mb4

重新启动mariadb.service以应用更改。更改字符集不会改变现有表格格式，只会影响新创建的表格以及检索数据的协议交互。

使用内存作为临时文件存放点※

Time zone tables※

尽管在安装过程中创建了时区表，但它们并不会自动填充。如果您计划在SQL查询中使用CONVERT_TZ()，则需要进行填充。

要使用所有时区填充时区表：

mariadb-tzinfo-to-sql /usr/share/zoneinfo | mariadb -u root -p mysql

或者，您可以选择使用特定的时区文件填充表：


mariadb-tzinfo-to-sql 时区文件 时区名称 | mariadb -u root -p mysql

使用※

连接数据库※

使用mysql命令行工具或者通过编程语言提供的MySQL连接库进行连接。

$ mysql -u -p

参数简述：

  • -u, --user=name: 指定用户名进行登录。
  • -p, --password[=name]: 使用密码进行连接。如果未提供密码，系统会提示您输入密码。
  • -h, --host=name: 指定要连接的MySQL服务器主机名。
  • -P, --port=#: 指定要连接的端口号。
  • -D, --database=name: 指定要使用的数据库。
  • -e, --execute=name: 执行指定的SQL命令并退出。
  • --ssl: 启用SSL连接。
  • --compress: 在服务器/客户端协议中使用压缩。
  • --auto-rehash: 启用自动重新哈希。无需使用'rehash'即可获得表和字段完成，但启动和重新连接可能需要更长的时间。
  • --batch: 禁用交互式行为，不使用历史文件。
  • --tee=name: 将所有内容追加到输出文件中。
  • --xml: 生成XML输出。
  • --html: 生成HTML输出。

退出：exit

基本操作：※

连接数据库：

mysql -u -p

参数简述

  • -u, --user=name: 指定用户名进行登录。
  • -p, --password[=name]: 使用密码进行连接。如果未提供密码，系统会提示您输入密码。
  • -h, --host=name: 指定要连接的MySQL服务器主机名。
  • -P, --port=#: 指定要连接的端口号。
  • -D, --database=name: 指定要使用的数据库。
  • -e, --execute=name: 执行指定的SQL命令并退出。
  • --ssl: 启用SSL连接。
  • --compress: 在服务器/客户端协议中使用压缩。
  • --auto-rehash: 启用自动重新哈希。无需使用'rehash'即可获得表和字段完成，但启动和重新连接可能需要更长的时间。
  • --batch: 禁用交互式行为，不使用历史文件。
  • --tee=name: 将所有内容追加到输出文件中。
  • --xml: 生成XML输出。
  • --html: 生成HTML输出。

退出：exit

数据库※

  • 创建数据库：使用CREATE DATABASE database_name;语句来创建新的数据库。
  • 删除数据库：使用DROP DATABASE database_name;语句来删除数据库。
  • 查询数据库：使用SHOW DATABASES；查看所有数据库
  • 切换数据库：使用USE database_name;

表※

  • 创建表：使用CREATE TABLE table_name (column1 datatype, column2 datatype, ...);语句来创建新的表格。
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY, # 键名 类型 自增 主键
    department_name VARCHAR(50) NOT NULL # 键名 类型 非空
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY, # 键名 类型 自增 主键
    first_name VARCHAR(50) NOT NULL, # 键名 类型 非空
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
    # 外键限制 KEY(键名) 来源 表名(键名)
);
  • 显示表：使用SHOW TABLES;
  • 修改表：使用RENAME TABLE old_table_name TO new_table_name;可以修改表名，如果使用MySQL版本高于8.0.13 可以使用ALTER TABLE old_table_name RENAME TO new_table_name;来修改
  • 删除表：使用DROP TABLE table_name;语句来删除表格。

列※

添加新列：

ALTER TABLE table_name
ADD column_name data_type constraints;

修改列类型：

ALTER TABLE table_name
MODIFY column_name new_data_type;

修改列名：

ALTER TABLE table_name
CHANGE old_column_name new_column_name data_type constraints;

删除列：

ALTER TABLE table_name
DROP column_name;

数据※

  • 插入数据：使用INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);语句来向表格中插入新的数据。
  • 查询数据：使用SELECT column1, column2, ... FROM table_name WHERE condition;语句来从表格中检索数据。
  • 更新数据：使用UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;语句来更新表格中的数据。
  • 删除数据：使用DELETE FROM table_name WHERE condition;语句来删除表格中的数据。

约束和索引※

 添加主键※

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

删除主键※

ALTER TABLE table_name
DROP PRIMARY KEY;

添加外键※

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES other_table(other_column);

删除外键※

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

添加索引※

CREATE INDEX index_name
ON table_name (column_name);

 删除索引※

DROP INDEX index_name
ON table_name;

触发器※

创建触发器

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器执行的操作
END;
  • BEFORE INSERT：在插入数据之前触发。
  • AFTER INSERT：在插入数据之后触发。
  • BEFORE UPDATE：在更新数据之前触发。
  • AFTER UPDATE：在更新数据之后触发。
  • BEFORE DELETE：在删除数据之前触发。
  • AFTER DELETE：在删除数据之后触发。

删除触发器

DROP TRIGGER IF EXISTS trigger_name;

视图※

创建视图：

要创建一个视图，可以使用如下语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

删除视图

DROP VIEW IF EXISTS view_name;

权限操作※

  • 授权：使用GRANT语句来授予用户特定的权限，如SELECT、INSERT、UPDATE、DELETE等
  • 撤销权限：使用REVOKE语句来撤销用户的特定权限。
  • 创建用户：使用CREATE USER语句来创建新的用户。
  • 删除用户：使用DROP USER语句来删除用户。
  • 更改密码：使用ALTER USER语句来更改用户的密码。
  • 刷新权限：使用FLUSH PRIVILEGES;语句来刷新权限，使更改立即生效。

其他关键字※

维护※

升级※

备份※

如果你正在使用默认的 InnoDB 存储引擎，建议的在线备份所有数据库并为时序恢复 （也称为“向前滚动”，当你需要恢复旧备份并重放自那个备份以来发生的更改时）的方法是执行以下命令：

$ mariadb-dump --single-transaction --flush-logs --events --routines --master-data=2 --all-databases -u root -p > all_databases.sql

这将提示输入 MariaDB 的 root 用户密码，该密码是在数据库 配置期间定义的。

强烈建议不在命令行中指定密码，因为这会使密码通过 ps aux 或其他技术暴露给其他用户发现。相反，上述命令会提示输入指定用户的密码，并将其隐藏起来。

还原※

安全※

重置root密码※

  • 停止 mariadb.service.
  • 用安全方式启动服务：
# mariadbd-safe --skip-grant-tables --skip-networking &
  • 连接服务器:
# mariadb -u root
  • 修改 root 密码:
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
MariaDB [mysql]> exit
  • 停掉 mariadbd 进程:
# kill $(cat /var/lib/mysql/$HOSTNAME.pid)
  • 启动 mariadb.service.

检查并修复所有数据表※

检查并自动修复所有数据库中的所有表

# mariadb-check -A --auto-repair -u root -p

优化所有数据表※

强制优化所有数据表，自动修复可能出现的数据表错误

# mariadb-check -A --auto-repair -f -o -u root -p

OS error 22 when running on ZFS※

如果您正在使用 ZFS 并且遇见了如下错误

InnoDB: Operating system error number 22 in a file operation.

那么就需要修改 /etc/mysql/my.cnf 中的设置来禁用 aio_writes

[mariadb]
...
innodb_use_native_aio = 0

另※

 常见mysql连接错误※

  • 1045 - Access denied for user 'username'@'host' (using password: YES)：这表示用户名或密码不正确，或者用户没有足够的权限连接到数据库。
  • 2003 - Can't connect to MySQL server on 'host' (10061)：这表示 MySQL 服务器未在指定的主机和端口上运行，或者由于防火墙设置而无法连接到 MySQL 服务器。
  • 1130 - Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server：这表示远程主机的连接被拒绝，可能是由于主机没有被授权连接到 MySQL 服务器。
  • 1040 - Too many connections：这表示 MySQL 达到了最大连接数，无法处理更多的连接请求。
  • 2013 - Lost connection to MySQL server during query：这表示在执行查询过程中与 MySQL 服务器的连接丢失，可能是由于网络问题或超时导致的。

更多error code 参见官方文档

“您的支持是我持续分享的动力”

微信收款码
微信
支付宝收款码
支付宝
目录