Import and Export Databases in MySQL and MariaDB on Linux Cloud Servers
In situations where a MySQL or MariaDB requires to be restored or when a backup of the database is required to be taken, having the proper knowledge to import and export MariaDB and MySQL databases becomes useful. This guide will help to understand the steps to export the database as well as import it from a dump file in MySQL and MariaDB.
The below steps can be followed to the process of export & import for MySQL & MariaDB respectively.
Exporting the Database
The mysqldump command is used to export databases to SQL text files. This simple SQL backup file can be used for further restoration of the database.
The below command shows how to take the backup dump of a database:
# mysqldump -u username -p dbname > dbdump.sqlIn the above command,
• mysqldump: The command used for dumping databases in SQL format.
• username: The name of the database user associated with the database (replace with the name of the username).
• dbname: The name of the database that needs to be exported (replace with the name of the database).
• dbdump.sql: The file to which the output will be saved to. This file will be saved to the same directory from which the command is executed.
When the above command is executed, you will be prompted to enter the password of the database user. Once the password has been typed in, all the contents of the database will be dumped/exported into the file
dbdump.sql.An example command for taking a database backup using the mysqldump command is provided below:
In the above command,
lsdbuserdenotes the database username,layerstackdbis the database that is to be backed up andlayerstackdb.sqlis the file to which the database backup will be written, once the password for the database user is entered.
Importing the Database
A database backup file can be imported either to a new database or an existing database. The below steps depict how to create a blank database and restore the backup file to that database.
Access MySQL/MariaDB server.
Log in to the database server as a root user or another user that have the privileges to create new databases:
# mysql -u root -pWhen the above command is executed, you will be prompted to enter the database server root password. Once entered, the MySQL/MariaDB shell prompt will be accessible.
Create Database and A User.
a) Executing the below command will create a new database,
newlayerstackdb.# CREATE DATABASE newlayerstackdb;The below output confirms that the new database was created. Query OK, 1 row affected (0.00 sec).
b) Create a user that will be associated with the new database by executing the below command.
# CREATE USER 'newdbuser' IDENTIFIED BY 'newusrpasswd';The new database user
newdbuserwill be created with the passwordnewusrpasswd.c) Grant privileges for the newly created database user to allow access to the new database.
# GRANT ALL privileges ON `newlayerstackdb`.* TO 'newdbuser'@localhost IDENTIFIED BY 'newusrpasswd';d) Then exit the MySQL shell by typing
quitfollowed by a semi-colon.# quit;The below screenshot shows the entire operation.
Restore Database.
Using the below command, the database backup file can be restored to the new database that has been created. The command needs to be run from the directory where the dump file is saved, else the exact path of the database dump file needs to be specified in the command.
# mysql -u newdbuser -p newlayerstackdb < dbdump.sql• mysql: The command used for restoring the database.
• newdbuser: The name of the database user associated with the database (replace with the name of the username).
• newlayerstackdb: The name of the database to which the backup will be restored (replace with the name of the database).
• dbdump.sql: The mysql backup dump file.
When the above command is executed, you will be prompted to enter the password of the database user. Once the password has been typed in, all the contents from the database dump file will be restored to the database
newlayerstackdb.An example command for restoring a database using a database dump file is provided below:
In the above command,
newdbuserdenotes the database username andlayerstackdb.sqlis the database backup file from which the contents are restored to the databasenewlayerstackdb.
Related Tutorials
