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
- Open up the terminal or command prompt
- 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.
- 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.
Login to the MySQL server as the root user using the command
mysql -u root -p
Enter the password
This gives you the mysql> prompt.
To create the new database, run
CREATEDATABASE newdatabase_name;
Logout from the MySQL shell using the exit command
mysql>exit
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;
- Login to the MySQL server using the commandmysql -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
- 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
- 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.
Stop the MySQL process using the command
Sudo /etc/init.d/mysql stop
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
Set the new MySQL database password
Run the command
updateusersetpassword=PASSWORD("newprd") whereUser='root';Reload everything by running the command.
FLUSHPRIVILEGES;
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
- Click this link and all your queries to best hosting will end.
