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.

mysql_secure_installation

MySQL configuration file

/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

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

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

Repare & optimize databases

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

Check the MySQL memory

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

Optimize MySQL with MySQLTuner

curl -O http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./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

Comments