MySQL is an open-source relational database management system that is developed, distributed, and supported by Oracle Corporation. There are several web-based tools available for managing the MySQL server, but best practice is to use the native MySQL command-line client for performing day-to-day MySQL tasks. It does take some time to get familiar with a command-line interface, particularly if you don’t regularly work in a CLI environment.
In this tutorial, we will show you some MySQL command-line tips and tricks that help you to perform day-to-day tasks.
Prerequisites
- A fresh Ubuntu 18.04 VPS with MySQL installed on the Atlantic.Net Cloud Platform.
- A root password configured on your server.
Create Atlantic.Net Cloud Server
First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 18.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.
Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.
apt-get update -y
1 – Automate the MySQL Login
In order to connect the MySQL server, you will be required to authenticate by specifying a username and password.
If this is your routine process, then you can save your lots of time by specifying your MySQL username and password in ~/.my.cnf file to automate the login process.
To do so, create a ~/.my.cnf file in your home directory:
nano ~/.my.cnf
Add the following information containing your MySQL host, username, and password:
[client] host = localhost user = root password = your-password
Save and close the file then change the ownership of the file to mysql:
chown mysql:mysql ~/.my.cnf
Now, you can connect your MySQL server without specifying a username and password as shown below:
mysql
You should get the following output:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2 – Check Running Processes
To check all the running processes of the MySQL service, run the following command:
mysqladmin -u root -pnew-password processlist
You should see the following output:
mysqladmin: [Warning] Using a password on the command line interface can be insecure. +----+------+-----------+----+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+----------+------------------+ | 6 | root | localhost | | Query | 0 | starting | show processlist | +----+------+-----------+----+---------+------+----------+------------------+
3 – Shutdown MySQL Service
To shut down the MySQL server, run the following command:
mysqladmin -u root -pnew-password shutdown
4 – Create and Delete Database
To create a new database, run the following command:
mysqladmin -u root -pnew-password create dbname
To remove a database, run the following command:
mysqladmin -u root -pnew-password drop dbname
5 – Check the Status of MySQL
To check whether the MySQL server is running or not, use the following command:
mysqladmin -u root -pnew-password ping
You should get the following output:
mysqld is alive
To verify the current status of the MySQL server, run the following command:
mysqladmin -u root -pnew-password status
You should see the status of uptime with running threads and queries in the following output:
Uptime: 2607 Threads: 1 Questions: 30 Slow queries: 0 Opens: 112 Flush tables: 2 Open tables: 0 Queries per second avg: 0.011
If you want to check the status of remote MySQL server, run the following command:
mysqladmin -h remote-server-ip -u root -pnew-password status
6 – Check MySQL Version
To check the installed version of the MySQL server, run the following command:
mysqladmin -u root -pnew-password version
You should get the following output:
mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqladmin Ver 8.42 Distrib 5.7.29, for Linux on x86_64 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.29-0ubuntu0.18.04.1 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 44 min 44 sec Threads: 1 Questions: 35 Slow queries: 0 Opens: 113 Flush tables: 2 Open tables: 1 Queries per second avg: 0.013
7 – Store MySQL Debug Information
If you want to tell MySQL server to write debug information in MySQL log file, run the following command:
mysqldump -u root -pnew-password debug
8 – Backup and Restore MySQL Database
To back up a single database, run the following command:
mysqldump -u root -pnew-password databasename > database-backup.sql
To back up multiple databases in a single file, run the following command:
mysqldump -u root -pnew-password database1 database2 database3 > database-backup.sql
To back up all databases in a single file, run the following command:
mysqldump -u root -pnew-password --all-databases --single-transaction --quick --lock-tables=false > full-database.sql
To restore a database from the backup file, run the following command:
mysqldump -u root -pnew-password databasename < database-backup.sql
Conclusion
In the above guide, you learned some useful MySQL commands with examples. We hope this will help you to save lots of time when using MySQL on your VPS from Atlantic.Net.
