Managing MySQL servers from the command line

Posted: May 14th, 2018

There are many ways to manage a MySQL server. There are countless frontends, such a PHPMyAdmin and MySQL Workbench. However, clearly the most powerful and efficient way to manage a MySQL server is directly through command line. Here is how to first access the MySQL command line interface, how to use many useful commands, how to use the select command to query the database, and how to do a database backup.

First Step : Accessing the MySQL server through command line.

You will first need to access the server through either SSH in Linux, or through Remote Desktop in Windows. You can also access it by doing a direct connection to the MySQL database, but this is more advanced and will not be covered here.

Once you're connected to the server, you need to enter the following command (please first open a command line window if you're connected to a Windows server):

mysql -u root -p

This command lets you access the database as root, which means you will have administrator privileges. Once the command is entered, you will be prompted for a password. The MySQL root password is normally set when installing MySQL. If installed manually, it's generally the same as the root password. If you're using cPanel, it can be found in the /root/.my.cnf file. It can also be reset through other means depending on your operating system. This will, however, not be covered in this article.

Once you're logged into the mysql server, you should see an interface similar to this :

From here, we can start querying the MySQL server.

Second Step : Using commands to manage the server.

Here is a list of useful MySQL commands that will help manage your server. Please note that all commands must end with the « ; » character:

SHOW DATABASES;

List all databases on the server.

USE databasename;

Chooses the database you wish to modify

CREATE DATABASE databasename;

Creates a database with the specified name

Creates a table inside the selected database with the appropriate parameters. For example, if someone wants to create a table to list his customers :

CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));

CREATE TABLE customers (name VARCHAR(35), country VARCHAR(20), registered DATE);

*You can find a list of data types for MySQL here : http://help.scibit.com/mascon/masconMySQL_Field_Types.html

SHOW TABLES;

Show the tables available in the selected database

INSERT INTO table_name VALUES ('VAR1', 'VAR2', 'VAR3');

Insert values into a table. VAR1 VAR2 and VAR3 represent the content that will go in each columns defined when creating the table. For example, if we use the previous customer table, here's a sample entry :

INSERT INTO customers VALUES ('Bob', 'Canada', '2012-07-31')

SHOW PROCESSLIST;

Gives you a rundown of all processes using the MySQL database and to which user they belong. Useful to find out which user is using most ressources when MySQL is using a lot of server ressources.

Third step: Querying the databases using the SELECT command

The SELECT command is arguably one of the most complex and useful commands to use in MySQL command line. It is used to query the database for specific values.

When querying the database for all values inside a table, you should use the following syntax:

SELECT * FROM table_name;

For example : SELECT * FROM customers;

Now, if you wanted to get the values for a specific row, you will need to specify the field and the value in the row you want. Here's the syntax as well as an example to understand this better:

SELECT * FROM table_name WHERE field_name = searchedvalue;

For example, if you wanted to find all the customers from canada in the customers table:

SELECT * FROM customers WHERE country = “canada”;

You can also specify many conditions for the query. Here's an example:

SELECT * FROM customers WHERE name = "Bob" AND country = “canada”;

Fourth Step: Doing database backups in MySQL from the command line.

Doing backups is easy. You need to log out from the MySQL interface first using the exit; command. Then enter the following command:

mysqldump -u root -p --database databasename > pathtofile.sql

For example, if you wanted to backup the "customers" database in the file "customers_31-07-2012.sql" located in "/home/admin/" folder:

mysqldump -u root -p –database customers > /home/admin/customers_31-07-2012.sql

And now, if you want to restore a database:

mysql -u root -p databasename < pathtofile.sql