How to Manage MySQL Databases Using the Command Line

This article will show you how to use the command line to export, import, or delete MySQL databases as well as reset the MySQL root password.

The command line is a powerful, fast and flexible server management tool that enables administrators to perform a wide range of functions using simple commands. Generally, in remote applications, the command line works over SSH, hence ensuring the security of the communication channel and all information that passes through. The SSH allows administrators to manage file, databases and other web applications.

Although the command line requires memorizing some of the commands and may appear difficult to people used to the GUI, it has benefits such as these:

  • Better control over operating and file systems
  • Faster performance
  • Computer to issue commands requires fewer system resources than to a GUI
  • Most commands remain almost the same, unlike a GUI interface which may change over time due to design or functionalities upgrades.
  • Does not have size limitations and can work with very large files and databases.

In order to use the command line, you need to connect to the remote server using an SSH client. This may vary according to your operating system. Please read How to Connect to a Server by Using SSH on Linux and Mac

Using the Command Line to Manage MySQL Databases

Once you access the remote server, you can perform a wide range of function using the command line. Although you can perform other functions, this article will we will only discuss how you can use the command line to manage MySQL databases.

How to Export a MySQL database Using the Command Line

  1. Open up the terminal or command prompt
  2. Use the mysqldump utility to create the backup  or export file

Syntax:  mysqldump-u[user name] -p[password] [database name] > [backupfile]

For example to backup sampledb database to a file by the name sampled-backup, run the command;

$ mysqldump -u root -p sampledb > sampledb_backup.sql

The system will prompt you to enter the password.

  1. Type the password and press Enter. The exporting process begins and you will see the command prompt again once complete.

If you want to dump the database in a specific folder, include its path in the command.

mysqldump-u [username] -p [database-to-dump] > [destination path and filename]

For example to dump the file in the folder /home/userfolder/ use the command;

mysqldump -u root -p mydatabase > /home/userfolder/sampledb-dump.sql

Export a single table

If you want to export a single table

mysqldump -p --user=username database_name tableName > tableName.sql

Export specific tables

$ mysqldump -u root -p sampledb table1 table2 > sampledb_tables_backup.sql

Multiple databases

To backup up multiple databases with one command, use the syntax

$ mysqldump -u root -p --databases sampledb2 sampledb4 sampledb5 > sampledb245_backup.sql

To back up all the databases

$ mysqldump -u root -p --all-databases > alldb_backup.sql

Export and compress the backup file

Export and create a gzip version of the SQL file

mysqldump -u [user] -p [database_name] | gzip > [file_name].sql.gz

How to Import or Restore the Database Data from a MySQL Dump File

First, you need to create a blank target database which will be the destination for the data you will import. You can use a command line or cPanel to create the new database with a similar name or a different name from that of the dump database data file.

  1. Login to the MySQL server as the root user using the command

mysql -u root -p
  1.  Enter the password

This gives you the mysql> prompt.

  1.  To create the new database, run

CREATEDATABASE newdatabase_name;

  1. Logout from the MySQL shell using the exit command

mysql>exit

  1. Once done, you will now use the mysql command to restore the data from the dump file to the new database file.

mysql  -u [username] -p[password] [newdatabase] < [databasebackupfile.sql]=

Ensure that the MySQL for the source and destination are the same version to avoid compatibility issues

How to Delete a Database in MySQL via the Command Line

To delete the database from the server;

  1. Login to the MySQL server using the command
mysql -u yourusername -p

For the root user, the command will be

mysql -u root -p

The –p tells the server to prompt for a password

  1. Type in your password and press Enter. This takes you to the MySQL prompt – mysql> where you can now use the drop command to delete the database
  1. Type DROP DATABASE sample_database and press Enter. The command will be like

Mysql> DROP DATABASE sample-database;

This will remove the sample_database from your server and you must be very careful since you cannot undo the process. If there is no file with such a filename on your server, it will give an error 1008 ‘ERROR 1008 (HY000): Can't drop database 'tutorial_database'; database doesn't exist’ .

If you don’t want to see this error such, add the ‘if exists’  condition.

DROPDATABASEIFEXISTSsample-database;

How to Reset MySQL Database Root Password

To reset the root password, you need to stop the MySQL services, access the MySQL in safe mode, set up the new password and finally restart the services.

  1. Stop the MySQL process using the command

Sudo  /etc/init.d/mysql stop
  1. Start the MySQL in safe mode.

sudo mysqld_safe --skip-grant-tables --skip-networking &

This starts the server without loading the grant tables as well as networking. The safe  mode allows you to access and make changes without the MySQL root password.

After starting in safe mode, start the MySQL shell by running the command

mysql -u root mysql

Type in the standard root password and press Enter

  1. Set the new MySQL database password

Run the command

updateusersetpassword=PASSWORD("newprd") whereUser='root';

Reload everything by running the command.

   FLUSHPRIVILEGES;
  1. Restart the database server normally

Stop and re-launch the mysqld process

  sudo /etc/init.d/mysql stop
 sudo /etc/init.d/mysql start

Conclusion

The command line provides a fast and powerful means of managing MySQL databases as well as performing other server functions both locally and remotely. Unlike the GUI based tools that may differ significantly as versions change, the commands remain much or less the same over time.

To effectively use the command line, you need to understand and remember each of the relevant commands. However, you can also use the inbuilt help to get the correct syntax for most of the commands.

Check out these top 3 Linux hosting services

Was this article helpful?