1. Log in to your MySQL server from the command line:
mysql -u username -p
2. Create a new database:
CREATE DATABASE new_database;
3. Select the new database:
USE new_database;
4. Import the dump file into the new database:
SOURCE path/to/dumpfile.sql;
5. Verify the import was successful:
SHOW TABLES;
If you want to move your site from one server to another. For this, first, you have to take a backup of your database. If your database size is 3 GB or more. till you cannot easily import and export the database. Then you have to create a mysql dump file for your database and with this dump file, you can easily restore your database.
So, in this tutorial, you will learn how to restore or backup up MySQL dump files from the command line in Linux Ubuntu.
How to Restore MySQL database from Dump File in Linux Ubuntu Command Line
By using the following steps, you can restore or backup up your MySQL database from a dump file using the command line or terminal in Linux Ubuntu:
- Step 1: Check your MySQL version
- Step 2: Create a new database
- Step 3: Move your dump file to your server
- Step 4: Restore the database from the dump file
- Step 5: Verify the restore
Step 1: Check your MySQL version
Firstly, open your terminal and execute the following command into it to check your MySQL version:
mysql -V
Step 2: Create a new database
If you don’t already have a database to restore your data to, you’ll need to create one. You can do this by executing the following command on the command line or terminal:
mysql -u root -p -e "create database yourdatabase;"
Replace ‘yourdatabase‘ with the name you want to give your database.
Step 3: Move your dump file to your server
Now, execute the following scp command on the command line or terminal to move your dump file to your server:
scp /path/to/dump.sql [email protected]:/path/on/server
In the above-given command, Replace ‘/path/to/dump.sql’ with the path to your dump file, ‘username’ with your username, ‘your.server.example.com’ with the IP address or domain name of your server, and ‘/path/on/server’ with the path where you want to store your dump file on your server.
Step 4: Restore the database from the dump file
Execute the following command on the terminal or command line to restore your database:
mysql -u root -p yourdatabase < /path/on/server/dump.sql
In the above-given command, replace ‘yourdatabase‘ with the name of the database you created in step 2, and ‘/path/on/server/dump.sql’ with the path to your dump file on your server.
If the database dump file is compressed, you can execute the following command on the terminal or command line to decompress it:
gunzip < /path/to/dumpfile.sql.gz | mysql -u root -p yourdatabase
The above-given command will decompress the dump file and restore it to the specified database.
Step 5: Verify the restore
Before you verify that your database was restored successfully, you need to login into your server. So execute the following command on the terminal or command line:
mysql -u root -p
Once you’re logged in, execute the following command on the terminal or command line to verify whether your database restored or not:
use yourdatabase;
In the above-given command, Replace ‘yourdatabase’ with the name of your database. Then, you can run any MySQL queries to verify that your data has been restored successfully.
Conclusion
Restoring MySQL databases from dump files is an essential skill for any web developer or system administrator. By following the steps outlined in this tutorial, you should be able to restore your database quickly and easily. Remember to always back up your databases regularly to avoid any data loss, and to test your backups regularly to ensure they are working correctly.
Recommended Tutorials
