MariaDB and MySQL Cheatsheet

Quick recipes for routine MariaDB administration. Each section is self-contained, so jump to the task you need. Commands use the mariadb, mariadb-dump, and mariadb-check client names. On older servers or MySQL, the mysql, mysqldump, and mysqlcheck names take the same options.

Create a Database and User

Create an application database with a full Unicode character set and a user that can access only that database.

CREATE DATABASE <DB_NAME> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER '<DB_USER>'@'localhost' IDENTIFIED BY '<DB_PASSWORD>';
GRANT ALL PRIVILEGES ON <DB_NAME>.* TO '<DB_USER>'@'localhost';

CREATE USER and GRANT take effect immediately, so FLUSH PRIVILEGES isn't needed. Leave off WITH GRANT OPTION unless the user really needs to grant privileges to others.

Allow a User to Set Definers

Imported dumps often contain views, triggers, or routines with a DEFINER clause naming another account. In MariaDB 10.5.2 and later, the SET USER privilege lets a non-root user create those objects.

GRANT SET USER ON *.* TO '<DB_USER>'@'localhost';

If you'd rather change the definer in the dump itself, see Shell One-Liners.

Create an Administrative User for Development

Frappe's bench new-site and similar tools need an account that can create databases and users. On a development machine, create a dedicated admin account rather than reusing root.

CREATE USER '<ADMIN_USER>'@'localhost' IDENTIFIED BY '<DB_PASSWORD>';
GRANT ALL PRIVILEGES ON *.* TO '<ADMIN_USER>'@'localhost' WITH GRANT OPTION;

Change How Root Authenticates

To let the system root user log in through the Unix socket with no password (the Debian and Ubuntu default), run:

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket;

To allow either socket login or a password:

ALTER USER 'root'@'localhost' IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('<DB_PASSWORD>');

To remove the root password entirely, for example on a throwaway local development container:

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

Check and Repair All Tables

Check every table in every database, and repair the ones whose storage engine supports REPAIR TABLE:

mariadb-check --check --auto-repair --all-databases

Add -u root -p if you aren't using socket authentication.

Optimize All Tables

Rebuild tables to reclaim space after large deletes:

mariadb-check --optimize --all-databases

For InnoDB, MariaDB reports Table does not support optimize, doing recreate + analyze instead. That's expected: the table is rebuilt with ALTER TABLE ... FORCE. Each rebuild needs free disk space roughly equal to the table's size and can take a long time on big tables, so run it during a quiet period.

Dump and Restore a Single Table

Dump one table (quote names that contain spaces) and restore it into the same or another database:

mariadb-dump --single-transaction <DB_NAME> 'tabScheduled Job Log' > table.sql
mariadb <DB_NAME> < table.sql

The dump includes DROP TABLE IF EXISTS, so restoring replaces the existing table.

Extract One Table from a Full Dump

Pull a single table out of a large mariadb-dump file without restoring everything. Each table's section runs from its DROP TABLE line to the matching UNLOCK TABLES:

sed -n '/^DROP TABLE IF EXISTS `<TABLE_NAME>`/,/^UNLOCK TABLES/p' backup.sql > table.sql
mariadb <DB_NAME> < table.sql

List a Table's Columns

Query information_schema for column names and types in their defined order:

SELECT COLUMN_NAME, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '<DB_NAME>'
  AND TABLE_NAME = '<TABLE_NAME>'
ORDER BY ORDINAL_POSITION;

For a quick interactive look, SHOW COLUMNS FROM <TABLE_NAME>; gives the same information.

Capitalize Every Word

MariaDB has no built-in title-case function. This stored function capitalizes each word, leaving short joining words lowercase unless they come first:

DELIMITER //

CREATE OR REPLACE FUNCTION CAP_FIRST(input VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE word_count INT;
  DECLARE word VARCHAR(255);
  DECLARE output VARCHAR(255) DEFAULT '';

  IF input IS NULL THEN
    RETURN NULL;
  END IF;

  SET input = TRIM(LOWER(input));
  SET word_count = CHAR_LENGTH(input) - CHAR_LENGTH(REPLACE(input, ' ', '')) + 1;

  WHILE i <= word_count DO
    SET word = SUBSTRING_INDEX(SUBSTRING_INDEX(input, ' ', i), ' ', -1);
    IF i = 1 OR word NOT IN ('a', 'an', 'and', 'for', 'of', 'the', 'with') THEN
      SET word = CONCAT(UPPER(LEFT(word, 1)), SUBSTRING(word, 2));
    END IF;
    SET output = CONCAT_WS(' ', NULLIF(output, ''), word);
    SET i = i + 1;
  END WHILE;

  RETURN output;
END //

DELIMITER ;

SELECT CAP_FIRST('the QUICK brown fox and the lazy dog');

The Quick Brown Fox and the Lazy Dog

Edit the NOT IN list to change which words stay lowercase.