☰

在 MariaDB 10.6 中完全禁用 InnoDB

tag-icon • tag-icon mariadb innodb ubuntu-20.04
在 MariaDB 10.6 中完全禁用 InnoDB

不太确定是否应该在这里询问还是在 dba.stackexchange.com 上询问，但我想这里可能更合适，因为这是一个服务器配置问题。

我在 Ubuntu 20.04 上安装了全新的 MariaDB 10.6.5

我使用了 MariaDB 自己的存储库，可以在以下 URL 上找到 -https://mariadb.org/download/?t=repo-config&d=20.04+%22focal%22&v=10.6&r_m=one

按照上面链接的说明，一切都安装顺利，但之后事情开始变得混乱。

我有另一台装有 Ubuntu 20.04 和 MariaDB 10.3.32 的机器，并且一切运行正常，这要归功于安装后运行了这些查询：

ALTER TABLE mysql.innodb_index_stats CHANGE table_name table_name VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') FROM information_schema.tables WHERE table_schema='mysql' AND engine='InnoDB';

然后在[mysqld]部分中添加以下几行/etc/mysql/my.cnf：

skip-innodb
default-storage-engine=MyISAM

我尝试对 10.6 安装执行相同的操作（通过在部分中添加 2 行[mysqld]） ，但由于某种原因，在我于控制台中/etc/mysql/mariadb.conf.d/50-server.cnf发出查询后，InnoDB 仍然处于活动状态且为默认状态。SHOW ENGINES;

我注意到的另一件事是新sys数据库也使用 InnoDB，我找不到有关如何删除它的任何信息。删除数据库后，我遇到了一些问题，不得不重新安装所有内容。

我需要这个的原因主要是因为使用具有 1GB 或更少 RAM 的廉价机器，其中 MyISAM 仍然是王者。

//编辑：

以下是输出my_print_defaults --mysqld --defaults-file=/etc/mysql/mariadb.cnf

--socket=/run/mysqld/mysqld.sock
--skip-innodb
--default-storage-engine=MyISAM
--user=mysql
--pid-file=/run/mysqld/mysqld.pid
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--lc-messages=en_US
--skip-external-locking
--bind-address=127.0.0.1
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci

以下是systemctl status mariadb.service

mariadb.service - MariaDB 10.6.5 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since  Mon 2021-12-27 08:13:02; 3min 57s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 2884 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 2889 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 2899 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (c>
    Process: 2924 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 2926 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 2907 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 10 (limit: 9451)
     Memory: 63.1M
     CGroup: /system.slice/mariadb.service
             └─2907 /usr/sbin/mariadbd

Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysql' as: /usr/bin/mysql
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Version check failed. Got the following error when calling the 'mysql' command line client
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: FATAL ERROR: Upgrade failed
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2941]: Checking for insecure root accounts.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 5 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost debian-start[2944]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

这是journalctl -u mariadb.service -n 30

Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27  8:13:01 0 [Note] /usr/sbin/mariadbd (server 10.6.5-MariaDB-1:10.6.5+maria~focal) starting as process 2907 ...
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27  8:13:01 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27  8:13:01 0 [Note] InnoDB: Number of pools: 1
Dec 27 08:13:01 testhost mariadbd[2907]: 2021-12-27  8:13:01 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Using Linux native AIO
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Completed initialization of buffer pool
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: 128 rollback segments are active.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: 10.6.5 started; log sequence number 33062; transaction id 4
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] Plugin 'FEEDBACK' is disabled.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] InnoDB: Buffer pool(s) load completed at 211227  8:13:02
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] Server socket created on IP: '0.0.0.0'.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] Server socket created on IP: '::'.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 0 [Note] /usr/sbin/mariadbd: ready for connections.
Dec 27 08:13:02 testhost mariadbd[2907]: Version: '10.6.5-MariaDB-1:10.6.5+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
Dec 27 08:13:02 testhost systemd[1]: Started MariaDB 10.6.5 database server.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 3 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysql' as: /usr/bin/mysql
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: Version check failed. Got the following error when calling the 'mysql' command line client
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2931]: FATAL ERROR: Upgrade failed
Dec 27 08:13:02 testhost /etc/mysql/debian-start[2941]: Checking for insecure root accounts.
Dec 27 08:13:02 testhost mariadbd[2907]: 2021-12-27  8:13:02 5 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Dec 27 08:13:02 testhost debian-start[2944]: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

答案1

MariaDB Zulip 上的 Daniel Black 对此进行了测试，它在容器中运行良好：

$ podman run --rm --env MARIADB_RANDOM_ROOT_PASSWORD=1 mariadb:10.6 --skip-innodb --default-storage-engine=Aria
2021-12-23 05:54:16+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.5+maria~focal started.
2021-12-23 05:54:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2021-12-23 05:54:17+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.5+maria~focal started.
...
2021-12-23  5:54:27 0 [Note] Plugin 'InnoDB' is disabled.
2021-12-23  5:54:27 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-12-23  5:54:27 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2021-12-23  5:54:27 0 [Note] Server socket created on IP: '0.0.0.0'.
2021-12-23  5:54:27 0 [Note] Server socket created on IP: '::'.
2021-12-23  5:54:27 0 [Warning] 'proxies_priv' entry '@% root@9df410ee481f' ignored in --skip-name-resolve mode.
2021-12-23  5:54:27 0 [Note] mysqld: ready for connections.
Version: '10.6.5-MariaDB-1:10.6.5+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

显示引擎：

$ podman exec optimistic_kalam mariadb -p'4(V_6icy&6BPL4wt*cG!=vi"rh8gvW;Q' -e 'show engines'
Engine  Support Comment Transactions    XA  Savepoints
CSV                 YES Stores tables as CSV files  NO  NO  NO
MRG_MyISAM          YES Collection of identical MyISAM tables   NO  NO  NO
MEMORY              YES Hash based, stored in memory, useful for temporary tables   NO  NO  NO
Aria                DEFAULT Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables NO  NO  NO
MyISAM              YES Non-transactional engine with good performance and small data footprint NO  NO  NO
SEQUENCE            YES Generated tables filled with sequential values  YES NO  YES
InnoDB              NO  Supports transactions, row-level locking, foreign keys and encryption for tables    NULL    NULL    NULL
PERFORMANCE_SCHEMA  YES Performance Schema  NO  NO  NO

这mysql.transaction_registry使用它，因为它需要一个事务存储引擎。

相关内容