MySQL/MariaDB Administration¶

See Overview for how this fits alongside Galera.

Users and Grants¶

Add / remove users¶

CREATE USER 'user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;

-- Same, but allow connecting from anywhere rather than just localhost
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

DROP USER 'user'@'%';
-- On older versions without DROP USER support:
DELETE FROM mysql.user WHERE User = 'user';
DELETE FROM mysql.db WHERE User = 'user';
FLUSH PRIVILEGES;

Grant privileges on a specific database¶

GRANT ALL ON your_database.* TO 'user'@'localhost' IDENTIFIED BY 'your_password';

Show / list users and their grants¶

SELECT User, Host FROM mysql.user;
SELECT User FROM mysql.user WHERE User = 'user_name';
SHOW GRANTS FOR 'user'@'localhost';

Revoke privileges¶

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'%';

Copy a user's password hash to another account¶

Useful when consolidating or renaming accounts without knowing the plaintext password:

SELECT @pass := Password FROM mysql.user WHERE User = 'source_user';
UPDATE mysql.user SET Password = @pass WHERE User = 'target_user';
FLUSH PRIVILEGES;

Change a user's password¶

-- Modern syntax
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

-- Older syntax (pre-5.7 style, still seen in older installs)
UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'user';
FLUSH PRIVILEGES;

From the shell instead:

mysqladmin -u root -p password 'new_password'

Rename a user¶

UPDATE mysql.user SET user = 'new_name' WHERE user = 'old_name';
FLUSH PRIVILEGES;

Useful for scripting/cron, at the cost of storing a plaintext password on disk — restrict file permissions accordingly.

Global, for all users on the host:

# /etc/mysql/my.cnf
[mysql]
user=root
password=your_password

[mysqladmin]
user=root
password=your_password

chmod 0700 /etc/mysql/my.cnf

Per-user, scoped to just that account:

# ~/.my.cnf
[client]
user=user_name
password=your_password

chmod 700 ~/.my.cnf

Connecting, Basic Queries, and Meta-Commands¶

mysql database_name

-- Order results
SELECT * FROM your_table ORDER BY some_column;
SELECT * FROM your_table ORDER BY some_column DESC;

-- Count matches
SELECT COUNT(*) FROM your_table WHERE some_field = 'value';

-- Wildcards: LIKE and RLIKE
SELECT id, name FROM your_table WHERE name LIKE 'A__a%';
SELECT id, name FROM your_table WHERE name RLIKE 'Pattern1|Pattern2';

-- Limit / paginate
SELECT * FROM your_table LIMIT 0, 30;

-- Verify a table is being actively updated
SELECT MAX(timestamp_column) FROM your_table;
SELECT NOW();
-- If the two are close, the table is being written to currently.

# Run a one-off query from the shell against a specific database
mysql your_database -e "SELECT * FROM your_table ORDER BY id;" > output.txt

Enter the shell without confirmation prompts on destructive commands¶

mysql --i-am-a-dummy

Blocks UPDATE/DELETE without a WHERE clause for the session — a cheap safety net worth using on production boxes.

Show table structure¶

DESC your_table;
-- or, more detail:
SHOW CREATE TABLE your_table;

Locking and Bulk Updates¶

-- Clear a column across every row in a table
UPDATE your_table SET some_column = NULL;

-- Drop a table despite foreign key constraints referencing it
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE your_table;
SET FOREIGN_KEY_CHECKS=1;

Table Maintenance (MyISAM)¶

See Overview: MyISAM vs. InnoDB — these apply to MyISAM tables specifically.

CHECK TABLE your_table;
REPAIR TABLE your_table;

# Check/repair every table across every database
mysqlcheck -A
mysqlcheck -Ar          # -r: repair
mysqlcheck -A --auto-repair

# Scope to one database or table
mysqlcheck your_database
mysqlcheck -a your_database   # -a: analyze
mysqlcheck -o your_database   # -o: optimize

Add a column¶

ALTER TABLE your_table ADD COLUMN new_column DOUBLE NOT NULL DEFAULT 0;

Rename a table¶

RENAME TABLE old_table_name TO new_table_name;

Monitoring Live Activity¶

SHOW FULL PROCESSLIST;
-- or, vertical output for wide queries:
SHOW FULL PROCESSLIST\G

# Poll the process list once a second
watch -n 1 'echo "SHOW FULL PROCESSLIST;" | mysql your_database'

Kill a runaway query¶

-- First, find the process ID from SHOW FULL PROCESSLIST\G, then:
KILL QUERY <process_id>;

Security Hardening¶

Standard secure-installation wizard¶

mysql_secure_installation

The same steps done manually¶

-- Set the root password (older syntax; use ALTER USER on modern versions)
UPDATE mysql.user SET Password = PASSWORD('your_password') WHERE User = 'root';

-- Remove anonymous accounts
DELETE FROM mysql.user WHERE User = '';

-- Disable remote root login
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- Remove the test database
DROP DATABASE test;

FLUSH PRIVILEGES;

Resetting a lost root password (CentOS/RHEL, systemd)¶

systemctl stop mysqld
systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
systemctl start mysqld

UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';
-- or, modern syntax:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

systemctl stop mysqld
systemctl unset-environment MYSQLD_OPTS
systemctl start mysqld

Reset via an init file¶

mysqld_safe --init-file=/var/lib/mysql/reset-root.sql &

The init file must live under the MySQL data directory (e.g. /var/lib/mysql/) — mysqld_safe won't find it elsewhere.

If you can already get a root shell without a password¶

Some distro packages default to unix_socket/auth_socket authentication for the local root account, meaning sudo mysql works with no password even though a password reset via UPDATE mysql.user doesn't visibly change anything. In that case, grant your own OS-matching account full privileges instead of fighting the plugin:

CREATE USER 'your_os_username'@'localhost' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'your_os_username'@'localhost';

Check which auth plugin an account is actually using if this comes up:

SELECT User, Host, plugin FROM mysql.user;

Backup and Restore — the backup-specific grant set is documented there rather than duplicated here
Troubleshooting — access-denied and connection errors referencing the accounts/grants above