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
- A
SET GLOBALchange is lost at the next restart. Write it in the configuration file to keep it. - The general log records every statement. Enable it only for short diagnostics.
- Avoid
ALL PRIVILEGES ON *.* ... WITH GRANT OPTIONfor application and monitoring accounts. Grant only the privileges the task needs, as in the read-only example above.