MariaDB 数据库管理

原创已于 2025-12-16 10:52:36 修改 · 669 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mariadb #android

于 2025-11-29 07:33:26 首次发布

部署 MariaDB

MariaDB是一个开源的关系型数据库管理系统，由MySQL的原始开发者创建；MariaDB是MySQL的一个分支；

服务器安装mariadb-server并启动：

[root@server ~]# yum install mariadb-server | tail -n 1
Complete!
[root@server ~]# systemctl enable mariadb --now
# 配置防火墙
[root@server ~]# firewall-cmd --permanent --add-service=mysql
[root@server ~]# firewall-cmd --reload

mysql_secure_installation是mariadb的安全配置命令；该命令可以打开安全配置交互界面，进行设置root密码、移除匿名用户、禁止远程root登录等操作；

[root@server ~]# mysql_secure_installation

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 `设置root用户密码`
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!

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] Y `禁止root远程登录`
 ... Success!

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] Y `删除测试数据库`
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

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!

接下来运行mysql -u root -p可以进行测试连接；mysql 命令是由于管理mariadb数据库的命令；
语法：mysql [选项] [数据库名]

mysql连接选项	作用
-h, --host=	连接的主机名，默认localhost
-P, --port=	连接端口，默认3306
-u, --user=	用户名
-p, --password	密码
-D, --database=	指定连接的数据库

登录后进入交互页面：

[root@server ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

以 root 用户身份登录 MariaDB 后，可以在本地创建用户，本地或远程的客户端可以使用该用户进行登录；

创建命令：CREATE USER ‘[用户名]’@‘%’ IDENTIFIED BY ‘[密码]’;
%表示该用户允许从任意主机连接；可替换为实际的IP，或localhost表示只能从本地登录；

MariaDB [(none)]> create user 'gc'@'10.1.8.10' identified by 'gc';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; # 刷新
Query OK, 0 rows affected (0.00 sec)

由于 MariaDB 默认只监听本地连接 127.0.0.1；因此要允许远程连接，需要修改其配置文件；

MariaDB 的主配置文件为/etc/my.cnf，另外/etc/my.cnf.d/目录下的文件也会生效；

[mysqld]
# Basic
port=3306
bind-address=0.0.0.0 # 监听所有网络接口
server_id=1 # 主从复制配置

# Connections
max_connections=500
wait_timeout=300
interactive_timeout=300

# Memory & Buffers
innodb_buffer_pool_size=4G # InnoDB性能关键参数
innodb_log_buffer_size=32M
tmp_table_size=64M
max_heap_table_size=64M

# Logging
log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=1

# InnoDB
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

# Charset
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

/etc/my.cnf.d/server.cnf是数据库服务主要配置；其中 [mysqld] 标签下的部分是服务器配置的主作用域；系统更新时，主配置文件 /etc/my.cnf 可能被覆盖，因此配置 /etc/my.cnf.d/server.cnf 是更好的选择。

注意不同发行版的配置文件命名不同，上述以CentOS发行版为例；

bind-address：监听的网络接口；
server_id：唯一标识一个 MySQL 服务器实例，配置主从复制时必需；
max_connections：允许的最大并发客户端连接数；
wait_timeout：关闭非交互式连接之前等待活动的秒数；
interactive_timeout：关闭交互式连接之前等待活动的秒数；
innodb_buffer_pool_size：设置 InnoDB 存储引擎的缓冲池大小；
innodb_log_buffer_size：设置 InnoDB 用于写入重做日志 (redo log) 的缓冲区大小；
tmp_table_size / max_heap_table_size：控制内存中临时表的最大大小；如果临时表超过此大小或包含 TEXT/BLOB 列，MySQL 会将其转换为磁盘上的 MyISAM 表；两个参数通常设置为相同的值；
log_error：错误日志文件的路径；
slow_query_log：启用或禁用慢查询日志记录；
slow_query_log_file：慢查询日志文件；
long_query_time：定义“慢查询”的阈值（单位：秒）；执行时间超过此值的查询会被记录到慢查询日志；
log_queries_not_using_indexes：启用时即使查询执行时间没超过 long_query_time，只要它没有使用索引，也会被记录到慢查询日志；
innodb_file_per_table：启用时，每个 InnoDB 表使用独立的表空间文件(.ibd)；关闭时，所有表都存储在系统表空间 (ibdata)；
innodb_flush_log_at_trx_commit：控制重做日志 (redo log) 的刷新策略到磁盘；
innodb_flush_method：设置 InnoDB 用于读写数据文件的文件 I/O 方法；
character_set_server：设置服务器的默认字符集为完整的 Unicode 字符；
collation_server：设置服务器默认的排序规则；
skip-networking：启用时，禁用 TCP/IP 网络连接；

此外，数据目录为/var/lib/mysql/；日志文件存储于/var/log/mysql/；

MariaDB的主从复制是一种数据同步机制，允许将主数据库（Master）的数据实时复制到一个或多个从数据库（Slave）。
主从复制的原理是主库（Master） 记录所有数据变更到二进制日志，再通过线程将日志事件发送给从库；从库（Slave） 通过I/O线程接收主库的二进制日志，再通过SQL线程重放日志事件，实现数据同步。

启用主从复制首先要修改配置文件/etc/my.cnf；

# 主库配置
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW

# 从库配置
[mysqld]
server-id = 2
relay_log = /var/lib/mysql/mysql-relay-bin

log_bin：是否开启二进制日志；使用复制或需要 PITR 时必须开启二进制日志；
binlog_format：指定二进制日志中记录数据库变更事件的具体格式；推荐设为ROW；
relay_log：在 MySQL 从库（Slave）服务器上存在的一种日志文件；

配置完成后，从库需执行CHANGE MASTER 命令：

MariaDB [(none)]> change master to
  MASTER_HOST='10.1.8.11',
  MASTER_USER='gc',
  MASTER_PASSWORD='gc',
  MASTER_LOG_FILE='mariadb-bin.000003',
  MASTER_LOG_POS=154;
MariaDB [(none)]> start slave;

MASTER_HOST：主数据库服务器的IP地址或域名；
MASTER_PORT：主数据库的监听端口；
MASTER_USER：主库上创建的用于主从复制的用户名；
MASTER_PASSWORD：主从复制用户的密码；
MASTER_LOG_FILE：主库当前的Binlog文件名，通过主库执行 SHOW MASTER STATUS; 获取；
MASTER_LOG_POS：主库当前的Binlog位置，通过主库执行 SHOW MASTER STATUS; 获取；

使用 STOP SLAVE; 或 RESET SLAVE; 可重置同步状态。

客户端想要使用已创建的账号进行登录，需要先在客户端安装mariadb；
安装完成后就可以验证登录了。

[root@client ~]# yum install mariadb | tail -n 1
Complete!
[root@client ~]# mysql -h 10.1.8.11 -P 3306 -u gc -p

SQL 语句及元命令

SQL(Structured Query Language)即结构化查询语言，用于管理和操作关系型数据库；

SQL语句可以分类为以下几类：

DDL：数据定义语言；如CREATE、ALTER、DROP；
DML：数据操作语言；如SELECT、INSERT、UPDATE、DELETE；
DCL：数据控制语言；如GRANT、REVOKE
TCL：事务控制语言；如COMMIT、ROLLBACK

MySQL命令行界面有一些专用的命令，称为客户端命令或元命令；这些命令不是标准的SQL语句，而是MySQL特有的；

MariaDB [(none)]> show databases; # 显示所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql：系统数据库，保存数据库用户及其访问权限等信息；
INFORMATION_SCHEMA：保存关于数据库或者数据表的元数据信息；
PERFORMANCE_SCHEMA：保存数据库服务器性能信息；

CREATE DATABASE 命令用于创建数据库；DROP DATABASE 命令用于删除数据库；
使用USE 命令可以指定数据库作为当前所在位置；

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use db1;
Database changed

一个数据库可以包含多个表（Table）；

SHOW TABLES 命令可以查看当前数据库下所有表；一个表包含若干个列，列代表了该表中数据所拥有的属性（如user表前三列为主机地址、用户名、密码），表中每条数据都拥有分别对应所有列的值；一个表中必然有一个不重复列作为主列，用于数据查询；

DESCRIBE 命令可以显示表结构，即表拥有的列；

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [mysql]> describe user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11)                           | NO   |     | 0       |       |
| plugin                 | char(64)                          | NO   |     |         |       |
| authentication_string  | text                              | NO   |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

输出显示，表格中有六列（属性）：

Field：列名；
Type：列格式；
Null：是否允许值为null；
Default：是否有默认值；
Key：是否为主键；主键是表中一行的唯一标识符，该属性的任何其他行都不能具有相同的值；
Extra：提供该列额外信息；

一般情况下，SQL语句中使用最频繁的是CRUD操作（create 添加数据，read读取数据，update 修改数据，delete删除数据）；

基本语法：
SELECT [列] FROM [表] WHERE [条件]; select * 代表显示所有列；
INSERT INTO [表] ([列]…) VALUES ([值…]) 可以有多个列，但值必须一一对应；
UPDATE [表] SET [列]=[值] WHERE [条件];
DELETE FROM [表] WHERE [条件];

MariaDB [mysql]> SELECT host,user,password FROM user WHERE user = 'gc';
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| 10.1.8.10 | gc   | *75562E0C956E92996DA66DC7803FF4888035599A |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql命令也包含命令行选项以实现不进入交互界面来处理SQL语句；

mysql命令行选项	作用
-e, --execute=[SQL语句]	执行SQL语句并退出
–init-command=	连接后执行的命令
-N, --skip-column-names	不显示列名
-s, --silent	静默模式
-B, --batch	批处理模式
-H, --html	生成HTML格式输出
-X, --xml	生成XML格式输出
-t, --table	表格格式输出（默认）
–vertical	垂直格式输出
–defaults-file=	指定配置文件

MariaDB 用户管理

默认情况下，新建用户被授予最小特权；在不授予其他特权的情况下，新用户只能访问最少的帐户信息；

GRANT 命令可以向用户授予特权；要授予GRANT特权，连接的用户必须具有GRANT OPTION且必须具有他们所授予的特定特权；
语法：GRANT [权限] ON [数据库].[表] TO ‘[用户名]’@‘[主机]’;

MariaDB [(none)]> grant select on mysql.user to gc@'10.1.8.10'; # 授予查询权限
Query OK, 0 rows affected (0.00 sec)

如果要回收权限，可以使用REVOKE 命令；语法与grant命令相同；

普通用户有权修改自己的密码，root用户可以修改所有用户的密码；
修改密码需要使用ALTER USER 命令（适用于 MySQL 5.7.6+ 和 MariaDB 10.2+）；

MariaDB [(none)]> ALTER USER 'gc'@'10.1.8.11' IDENTIFIED BY '123';

如果是较低版本，则推荐使用grant命令重置密码；

MariaDB [(none)]> GRANT USAGE ON *.* TO 'gc'@'10.1.8.11' IDENTIFIED BY '123';
MariaDB [(none)]> FLUSH PRIVILEGES;

当不再需要特定的用户帐户时，可以使用DROP USER 命令将其从数据库中删除；

MariaDB [(none)]> drop user gc@'10.1.8.10';
Query OK, 0 rows affected (0.00 sec)