MySQL commands for sysadmins

I usually forget basic MySQL commands like checking the status and where is the configuration file. So, I collect the most useful and most used commands for MySQL in this post for a quick reference. Note that I use Ubuntu, so the the commands are Ubuntu-specific.

These commands are extremely useful, especially when you want to manage your own VPS or backup databases. Here we go:

Setup

Secure MySQL installation

This command is usually used after installing LAMP/LEMP on the server to setup the password for the root user. It's highly recommended to run this command on a production environment. Otherwise, you'll have an insecure root user (which has all privileges) without a password.

mysql_secure_installation

MySQL configuration files

/etc/mysql/ - Main MySQL server configuration directory
/etc/mysql/my.cnf - The MySQL database server configuration file

The my.cnf simly loads config from these folders:

/etc/mysql/conf.d/
/etc/mysql/mariadb.conf.d/

In most cases, you might need to edit this file:

/etc/mysql/mariadb.conf.d/50-server.cnf

Status

Check MySQL status

Checking if MySQL is active or not:

systemctl is-active mysql

For more advanced details, use:

systemctl status mysql

Start, stop, restart MySQL

service mysql start
service mysql stop
service mysql restart

Check the MySQL memory

ps aux | grep 'mysql' | awk '{print $6}'

Working with databases

Create a new database & user

CREATE DATABASE dbname;
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';

Backup & restore a database

Backup a database into a .sql file:

mysqldump -u root -p dbname > file.sql

Restore a database from a .sql file:

mysql -u root -p dbname < file.sql

Optimize a table

optimize table table_name
wp db optimize

Repair & optimize all databases

mysqlcheck --repair --all-databases -u root -p
mysqlcheck --optimize --all-databases -u root -p

Get tables' sizes

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "mel"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Optimize MySQL with MySQLTuner

curl -O http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl