avatar Deluxe Blog Tips About Projects

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:


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 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:


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



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 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

  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
  TABLE_SCHEMA = "mel"

Optimize MySQL with MySQLTuner

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

🔥 HOT: Interested in boosting your WordPress SEO? My Slim SEO plugin is a super lighweight and automated plugin that handles most the hard work for you: meta tags, sitemap, redirection, schema & link building.

👉 Have a look and you will love it: wpslimseo.com