升级MariaDB¶
为了完成在古老的CentOS7(aliOS 7.2)上部署 Django配置MySQL数据库 ，遇到了非常折磨的问题，无法完成 mysqlclient Python模块编译安装。这个问题我最初想通过 CentOS 7 安MySQL 来绕过，但是发现MySQL官方提供的 mysql-server 实际上已经无法在CentOS早期版本上运行(crash)。所以还是回到MariaDB安装，想通过社区提供的高版本MariaDB 10.11 来解决这个编译兼容问题。
由于已经安装过MariaDB 5.5，所以启动 MaraiDB 10.11 时候观察 systemctl status mariadb 可以看到提示需要升级系统表:
systemctl status mariadb 提示需要升级系统表¶● mariadb.service - MariaDB 10.11.5 database server
Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Wed 2023-09-20 17:01:58 CST; 15min ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 201912 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 8
Memory: 93.1M
CGroup: /system.slice/mariadb.service
└─201912 /usr/sbin/mariadbd
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [Note] Server socket created on IP: '0.0.0.0'.
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [Note] Server socket created on IP: '::'.
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type varchar(, found type char(77).
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALU
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysql.gtid_slave_pos' doesn't exist
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: 2023-09-20 17:01:58 0 [Note] /usr/sbin/mariadbd: ready for connections.
Sep 20 17:01:58 haiguangxdn033142047230.stl mariadbd[201912]: Version: '10.11.5-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Sep 20 17:01:58 haiguangxdn033142047230.stl systemd[1]: Started MariaDB 10.11.5 database server.
升级步骤似乎非常简单，主要就是使用 /bin/mysql_upgrade :
升级 mysql 数据库¶
mysql_upgrade -u root -p
输出显示:
mysql_upgrade 升级 mysql 数据库输出信息显示正常¶Enter password:
Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading from a version before MariaDB-10.1
Phase 2/8: Installing used storage engines
Checking for tables with unknown storage engine
Phase 3/8: Running 'mysql_fix_privilege_tables'
Phase 4/8: Fixing views
mysql.user
Warning : The user specified as a definer ('mariadb.sys'@'localhost') does not exist
status : OK
sys.host_summary OK
sys.host_summary_by_file_io OK
sys.host_summary_by_file_io_type OK
sys.host_summary_by_stages OK
sys.host_summary_by_statement_latency OK
sys.host_summary_by_statement_type OK
sys.innodb_buffer_stats_by_schema OK
sys.innodb_buffer_stats_by_table OK
sys.innodb_lock_waits OK
sys.io_by_thread_by_latency OK
sys.io_global_by_file_by_bytes OK
sys.io_global_by_file_by_latency OK
sys.io_global_by_wait_by_bytes OK
sys.io_global_by_wait_by_latency OK
sys.latest_file_io OK
sys.memory_by_host_by_current_bytes OK
sys.memory_by_thread_by_current_bytes OK
sys.memory_by_user_by_current_bytes OK
sys.memory_global_by_current_bytes OK
sys.memory_global_total OK
sys.metrics OK
sys.processlist OK
sys.ps_check_lost_instrumentation OK
sys.schema_auto_increment_columns OK
sys.schema_index_statistics OK
sys.schema_object_overview OK
sys.schema_redundant_indexes OK
sys.schema_table_lock_waits OK
sys.schema_table_statistics OK
sys.schema_table_statistics_with_buffer OK
sys.schema_tables_with_full_table_scans OK
sys.schema_unused_indexes OK
sys.session OK
sys.session_ssl_status OK
sys.statement_analysis OK
sys.statements_with_errors_or_warnings OK
sys.statements_with_full_table_scans OK
sys.statements_with_runtimes_in_95th_percentile OK
sys.statements_with_sorting OK
sys.statements_with_temp_tables OK
sys.user_summary OK
sys.user_summary_by_file_io OK
sys.user_summary_by_file_io_type OK
sys.user_summary_by_stages OK
sys.user_summary_by_statement_latency OK
sys.user_summary_by_statement_type OK
sys.version OK
sys.wait_classes_global_by_avg_latency OK
sys.wait_classes_global_by_latency OK
sys.waits_by_host_by_latency OK
sys.waits_by_user_by_latency OK
sys.waits_global_by_latency OK
sys.x$host_summary OK
sys.x$host_summary_by_file_io OK
sys.x$host_summary_by_file_io_type OK
sys.x$host_summary_by_stages OK
sys.x$host_summary_by_statement_latency OK
sys.x$host_summary_by_statement_type OK
sys.x$innodb_buffer_stats_by_schema OK
sys.x$innodb_buffer_stats_by_table OK
sys.x$innodb_lock_waits OK
sys.x$io_by_thread_by_latency OK
sys.x$io_global_by_file_by_bytes OK
sys.x$io_global_by_file_by_latency OK
sys.x$io_global_by_wait_by_bytes OK
sys.x$io_global_by_wait_by_latency OK
sys.x$latest_file_io OK
sys.x$memory_by_host_by_current_bytes OK
sys.x$memory_by_thread_by_current_bytes OK
sys.x$memory_by_user_by_current_bytes OK
sys.x$memory_global_by_current_bytes OK
sys.x$memory_global_total OK
sys.x$processlist OK
sys.x$ps_digest_95th_percentile_by_avg_us OK
sys.x$ps_digest_avg_latency_distribution OK
sys.x$ps_schema_table_statistics_io OK
sys.x$schema_flattened_keys OK
sys.x$schema_index_statistics OK
sys.x$schema_table_lock_waits OK
sys.x$schema_table_statistics OK
sys.x$schema_table_statistics_with_buffer OK
sys.x$schema_tables_with_full_table_scans OK
sys.x$session OK
sys.x$statement_analysis OK
sys.x$statements_with_errors_or_warnings OK
sys.x$statements_with_full_table_scans OK
sys.x$statements_with_runtimes_in_95th_percentile OK
sys.x$statements_with_sorting OK
sys.x$statements_with_temp_tables OK
sys.x$user_summary OK
sys.x$user_summary_by_file_io OK
sys.x$user_summary_by_file_io_type OK
sys.x$user_summary_by_stages OK
sys.x$user_summary_by_statement_latency OK
sys.x$user_summary_by_statement_type OK
sys.x$wait_classes_global_by_avg_latency OK
sys.x$wait_classes_global_by_latency OK
sys.x$waits_by_host_by_latency OK
sys.x$waits_by_user_by_latency OK
sys.x$waits_global_by_latency OK
Phase 5/8: Fixing table and database names
Phase 6/8: Checking and upgrading tables
Processing databases
information_schema
notifier
notifier.notifier_alert_statistics OK
onesredb
performance_schema
sys
sys.sys_config OK
Phase 7/8: uninstalling plugins
Phase 8/8: Running 'FLUSH PRIVILEGES'
OK
再次重启 MariaDB 观察状态，就可以看到所有输出信息正常:
完成
mysql_upgrade 之后再次检查 mariadb 状态¶● mariadb.service - MariaDB 10.11.5 database server
Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Wed 2023-09-20 17:25:22 CST; 5h 56min ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 257558 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 8
Memory: 77.8M
CGroup: /system.slice/mariadb.service
└─257558 /usr/sbin/mariadbd
Sep 20 17:25:22 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 17:25:22 0 [Note] Server socket created on IP: '0.0.0.0'.
Sep 20 17:25:22 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 17:25:22 0 [Note] Server socket created on IP: '::'.
Sep 20 17:25:22 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 17:25:22 0 [Note] /usr/sbin/mariadbd: ready for connections.
Sep 20 17:25:22 haiguangxdn033142047230.stl mariadbd[257558]: Version: '10.11.5-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Sep 20 17:25:22 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 17:25:22 0 [Note] InnoDB: Buffer pool(s) load completed at 230920 17:25:22
Sep 20 17:25:22 haiguangxdn033142047230.stl systemd[1]: Started MariaDB 10.11.5 database server.
Sep 20 22:02:59 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 22:02:59 3 [Warning] Access denied for user 'notifier'@'localhost' (using password: YES)
Sep 20 22:03:04 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 22:03:04 4 [Warning] Access denied for user 'notifier'@'localhost' (using password: YES)
Sep 20 22:03:19 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 22:03:19 5 [Warning] Access denied for user 'notifier'@'localhost' (using password: YES)
Sep 20 22:03:33 haiguangxdn033142047230.stl mariadbd[257558]: 2023-09-20 22:03:33 6 [Warning] Access denied for user 'notifier'@'localhost' (using password: YES)