LLI Wiki — My Digital Knowledge Base

MySQL and MariaDB administration cheat sheet

Common commands to manage the service, configuration, logs, sizes, users and privileges of MySQL or MariaDB.

A short reference for inspecting and administering a MySQL or MariaDB server from the shell and from a SQL client. Paths and default file locations depend on the distribution and the version.

Service management

Platform Start Stop Restart
RHEL/CentOS 7 and later, MariaDB systemctl start mariadb systemctl stop mariadb systemctl restart mariadb
RHEL/CentOS 7 and later, MySQL systemctl start mysqld systemctl stop mysqld systemctl restart mysqld
RHEL/CentOS 4 to 6 service mysqld start service mysqld stop service mysqld restart
Debian/Ubuntu service mysql start service mysql stop service mysql restart

Version, identity and uptime

SHOW VARIABLES LIKE '%version%';
SELECT @@BASEDIR;
SELECT DATABASE();
SELECT CURRENT_USER();   -- the account you are authenticated as
SELECT USER();           -- the account you tried to authenticate as
SHOW GLOBAL STATUS LIKE 'Uptime';   -- seconds since the server started

Configuration

Show the option files that are read at startup:

mysql --help | grep -A 1 "Default options"

The usual locations are /etc/my.cnf, /etc/mysql/my.cnf, /usr/etc/my.cnf and ~/.my.cnf.

Save all global variables to a file:

sudo -u root mysql -e "SHOW GLOBAL VARIABLES" > variables.txt

Change a value until the next restart. A global change applies to new connections only:

SET GLOBAL sort_buffer_size = 1000000;
SET SESSION sort_buffer_size = 10000;

Make a change permanent in the configuration file:

[mysqld]
max_allowed_packet = 1G

The same option can be passed on the command line, for example mysqld --max_allowed_packet=1G.

Connections and processes

SHOW PROCESSLIST;
SELECT * FROM information_schema.PROCESSLIST;
SHOW STATUS LIKE 'Conn%';
ps -ef | grep mysql

Logs and the slow query log

SHOW VARIABLES LIKE '%log%';
SHOW VARIABLES LIKE '%slow%';

SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';
FLUSH LOGS;

The same settings in the configuration file:

[mysqld]
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
general_log = 1
general_log_file = /var/log/mysql/mysql.log

Restart the service after editing the file, using the name from the service table above.

Databases, tables and sizes

SHOW DATABASES;
SHOW TABLES;
DESCRIBE app_db.app_table;

SELECT SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'app_db';

SELECT table_name, table_rows
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'app_db';

SELECT table_schema AS db_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS db_size_mb
FROM information_schema.tables
GROUP BY table_schema;

Users and privileges

SELECT host, user FROM mysql.user;
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'app_user'@'%';

CREATE USER 'app_user'@'%' IDENTIFIED BY '<PASSWORD>';
GRANT SELECT ON app_db.* TO 'app_user'@'%';

REVOKE SELECT ON app_db.* FROM 'app_user'@'%';
DROP USER 'app_user'@'%';

CREATE USER and GRANT apply immediately. FLUSH PRIVILEGES is needed only after changing the grant tables directly.

Useful mysqladmin commands

mysqladmin -u dba -p ping
mysqladmin -u dba -p status
mysqladmin -u dba -p extended-status
mysqladmin -u dba -p variables
mysqladmin -u dba -p version

Notes

Mastodon LLIMastodon SocialFosstodon