MariaDB

来自 Arch Linux 中文维基

MariaDB 是一个可靠的、高性能的、功能全面的数据库,旨在为用户提供长期免费、向下兼容能直接替代MySQL的数据库服务。自2013年起,MariaDB就被Arch Linux当作官方默认的MySQL实现[1]

安装[编辑 | 编辑源代码]

Archlinux 选择的 MySQL 默认实现 被称为 MariaDB

安装 mariadbmariadb-libs

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

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

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

然后 enable 或者 start mariadb.service

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

配置[编辑 | 编辑源代码]

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

  1. 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[编辑 | 编辑源代码]

警告: Before changing the character set be sure to create a backup first.
注意:
  • The mariadb package already uses utf8mb4 as charset and utf8mb4_unicode_ci as collation. Users using the default (character) settings may want to skip this section.
  • UTF8MB4 is recommended over UTF-8 since it allows full Unicode support [2] [3].

Append the following values to the main configuration file located at /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

Restart mariadb.service to apply the changes. Changing the character set does not change existing table formats, only newly created tables, and the protocol interaction that fetches data.

See #Maintenance to optimize and check the database health.

使用内存作为临时文件存放点[编辑 | 编辑源代码]

MySQL 存储临时文件的目录名是 tmpdir

创建一个临时目录:

# mkdir -pv /var/lib/mysqltmp
# chown mysql:mysql /var/lib/mysqltmp

通过命令找出 mysql 的id和gid:

$ id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

添加 tmpfs/etc/fstab 中。

 tmpfs   /var/lib/mysqltmp   tmpfs   rw,gid=27,uid=27,size=100M,mode=0750,noatime   0 0

将以下配置添加到 /etc/mysql/my.cnfmysqld 组下:

 tmpdir      = /var/lib/mysqltmp

Stop mariadb.service, mount /var/lib/mysqltmp/启动 mariadb.service.

Time zone tables[编辑 | 编辑源代码]

Although time zone tables are created during the installation, they are not automatically populated. They need to be populated if you are planning on using CONVERT_TZ() in SQL queries.

To populate the time zone tables with all the time zones:

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

Optionally, you may populate the table with specific time zone files:

$ mariadb-tzinfo-to-sql timezone_file timezone_name | mariadb -u root -p mysql

Security[编辑 | 编辑源代码]

Improve initial security[编辑 | 编辑源代码]

The mariadb-secure-installation command will interactively guide you through a number of recommended security measures, such as removing anonymous accounts and removing the test database:

# mariadb-secure-installation
警告: After running this, please note that TCP port 3306 will still be open, but refusing connections with an error message. To prevent MySQL from listening on an external interface, see the #Listen only on the loopback address and #Enable access locally only via Unix sockets sections.

Listen only on the loopback address[编辑 | 编辑源代码]

By default, MariaDB will listen on the 0.0.0.0 address, which includes all network interfaces. In order to restrict MariaDB to listen only to the loopback address, add the following line in /etc/my.cnf.d/server.cnf:

[mariadb]
bind-address = localhost

This will bind to both 127.0.0.1 and ::1, and enable MariaDB to receive connections both in IPv4 and IPv6.

Enable access locally only via Unix sockets[编辑 | 编辑源代码]

By default, MariaDB is accessible via both Unix sockets and the network. If MariaDB is only needed for the localhost, you can improve security by not listening on TCP port 3306, and only listening on Unix sockets instead. To do this, add the following line in /etc/my.cnf.d/server.cnf:

[mariadb]
skip-networking

You will still be able to log in locally as before, but only using Unix sockets.

授权远程访问[编辑 | 编辑源代码]

警告: This is not considered as best practice and may cause security issues. Consider using Secure Shell, VNC or VPN, if you want to maintain the MariaDB server from another host inside/outside your network.

To allow remote access to the MariaDB server, ensure that MariaDB has networking enabled and is listening on the appropriate interface.

Grant any MariaDB user remote access (example for root):

# mariadb -u root -p

Check current users with remote access privileged:

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

Now grant remote access for your user (here root):

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'my_optional_remote_password' WITH GRANT OPTION;

You can change the '%' wildcard to a specific host if you like. The password can be different from user's main password.

配置主目录访问[编辑 | 编辑源代码]

注意: 出于安全考虑,systemd 的 .service 文件通过 ProtectHome=true 禁止 MariaDB 访问 /home/root/run/user 目录内的文件。datadir 必须要放在以上文件夹之外,并且由 mysql 用户和用户组 所有。 如果要改变这个设置,可以根据以下链接创建一个替代的 service 文件:[4]

维护[编辑 | 编辑源代码]

升级[编辑 | 编辑源代码]

mariadb大版本升级的时候(例如 mariadb-10.3.10-1 到 mariadb-10.9.4-1),最好更新一下数据库:

# mariadb-upgrade -u root -p

要从 10.3.x 更新到 10.9.x:

  • 停止 10.3.x 服务器
  • 更新软件包
  • 启动新服务并用执行新软件包的 mariadb_upgrade

如果新服务未启动,请参考 MariaDB 未启动,无法执行 mariadb_upgrade。

备份[编辑 | 编辑源代码]

Backup[编辑 | 编辑源代码]

There are various tools and strategies to back up your databases.

If you are using the default InnoDB storage engine, a suggested way of backing up all your bases online while provisioning for point-in-time recovery (also known as "roll-forward", when you need to restore an old backup and replay the changes that happened since that backup) is to execute the following command:

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

This will prompt for MariaDB's root user's password, which was defined during database #Configuration.

Specifying the password on the command line is strongly discouraged, as it exposes it to discovery by other users through the use of ps aux or other techniques. Instead, the aforementioned command will prompt for the specified user's password, concealing it away.

Compression[编辑 | 编辑源代码]

As SQL tables can get pretty large, it is recommended to pipe the output of the aforementioned command in a compression utility like gzip:

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

Decompressing the backup thus created and reloading it in the server is achieved by doing:

$ zcat all_databases.sql.gz | mariadb -u root -p

This will recreate and repopulate all the databases previously backed up (see this or this).

Non-interactive[编辑 | 编辑源代码]

If you want to setup non-interactive backup script for use in cron jobs or systemd timers, see option files and this illustration for mariadb-dump.

Basically you should add the following section to the relevant configuration file:

[mariadb-dump]
user=mysqluser
password=secret

Mentioning a user here is optional, but doing so will free you from having to mention it on the command line. If you want to set this for all tools, including mariadb-client, use the [client] group.

Example script[编辑 | 编辑源代码]

The database can be dumped to a file for easy backup. The following shell script will do this for you, creating a db_backup.gz file in the same directory as the script, containing your database dump:

#!/bin/sh

THISDIR=$(dirname $(readlink -f "$0"))

mariadb-dump --single-transaction --flush-logs --events --routines --master-data=2 --all-databases \
 | gzip > $THISDIR/db_backup.gz
echo 'purge master logs before date_sub(now(), interval 7 day);' | mariadb

See also the official mariadb-dump page in the MariaDB manuals.

Holland Backup[编辑 | 编辑源代码]

A python-based software package named Holland Backup is available in AUR to automate all of the backup work. It supports direct mysqldump, LVM snapshots to tar files (mysqllvm), LVM snapshots with mysqldump (mysqldump-lvm), and xtrabackup methods to extract the data. The Holland framework supports a multitude of options and is highly configurable to address almost any backup situation.

The main hollandAUR and holland-commonAUR packages provide the core framework; one of the sub-packages (holland-mysqldumpAUR, holland-mysqllvmAUR and/or holland-xtrabackupAUR must be installed for full operation. Example configurations for each method are in the /usr/share/doc/holland/examples/ directory and can be copied to /etc/holland/backupsets/, as well as using the holland mk-config command to generate a base configuration for a named provider.

故障排除[编辑 | 编辑源代码]

执行 mysql_upgrade 后 MySQL 不能启动[编辑 | 编辑源代码]

试试安全模式下运行的 MySQL:

# mariadbd-safe --datadir=/var/lib/mysql/

然后再运行:

# mariadb-upgrade -u root -p

重置 root 密码[编辑 | 编辑源代码]

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

无法通过命令行登录, 但是 phpmyadmin 正常工作[编辑 | 编辑源代码]

当使用了超长 (>70-75) 的密码后,这个问题有可能发生。 mariadb 的命令行不能在 readline 模式中处理那么多的字符。 所以如果打算使用推荐的密码输入方式:

$ mariadb -u user -p
Password:

不妨考虑更换一个长度短一点的密码。

注意: 您依然可以通过在命令行参数中指定密码来登录
$ mysql -u <user> -p"some-very-strong-password"
警告: 但这样做很危险,因为您的密码很可能会泄漏到某个地方,例如,日志。只有当遇到紧急情况才能考虑这么做,并且事后不要忘记更改密码。

MySQL 日志文件占用太多空间[编辑 | 编辑源代码]

默认情况下, mysqld 会在 /var/lib/mysql 下创建二进制日志文件。这在某些场景下是很有用的。但是这些日志文件也可能耗光您的硬盘空间。如果需要,您可以在 /etc/mysql/my.cnf 中注释掉以下两行来禁用日志:

#log-bin=mysql-bin
#binlog_format=mixed

或者限制 logfile 的大小:

OpenRC fails to start MariaDB[编辑 | 编辑源代码]

To use MariaDB with OpenRC you need to add the following lines to the [mariadb] section in the MySQL configuration file, located at /etc/my.cnf.d/my.cnf.

user = mysql
basedir = /usr
datadir = /var/lib/mysql
pid-file = /run/mysqld/mysql.pid

You should now be able to start MariaDB using:

# rc-service mysql start

Specified key was too long[编辑 | 编辑源代码]

See #Increase character limit.

Changed limits warning on max_open_files/table_open_cache[编辑 | 编辑源代码]

Increase the number of file descriptors by creating a systemd drop-in, e.g.:

/etc/systemd/system/mariadb.service.d/limit_nofile.conf
[Service]
LimitNOFILE=8192

10.4 to 10.5 upgrade crash: "InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.4.x"[编辑 | 编辑源代码]

Before MariaDB 10.5, redo log was unnecessarily split into multiple files.[5]

Do NOT ever remove the old binary logs /var/lib/mysql/ib_logfile* out of the way.

To resolve this, install MariaDB 10.4. Start it and let it undergo a clean shutdown. After that happens you can upgrade to 10.5 again. Same applies if another version of MariaDB was specified.

Table 'mysql.xxx' does not exist in engine[编辑 | 编辑源代码]

Symptom: When running mariadb-upgrade or mariadb-check, it return one or more error like these:

Table 'mysql.xxx' does not exist in engine

Where "xxx" usually is the system table inside the mysql database.

Steps to fix this,

  1. Create backup directory outside of MariaDB ${DATADIR}</nowiki>, for example in $HOME/mariadb_backup.
  2. Copy the offending files from ${DATADIR}/mysql/xxx.{frm,ibd}</nowiki> to backup directory. The xxx.ibd may not exist.
  3. Drop the tables with DROP TABLE mysql.xxx on the mariadb prompt.
  4. Run the mariadb-check. On success, the file xxx.frm and xxx.ibd should be created again.
  5. Re-run mariadb-upgrade if necessary. You may need the --force option.
expire_logs_days = 10
max_binlog_size  = 100M

另外,您也可以执行以下命令来清除 /var/lib/mysql 里的一些日志文件来释放硬盘空间:

#mysql -u root -p"PASSWORD" -e "PURGE BINARY LOGS TO 'mysql-bin.0000xx';"

更多资源[编辑 | 编辑源代码]