Being able to import and export a MySQL or MaríaDB database using the console / terminal is a skill we must have. We can create backups in case of an emergency, we can use them to migrate data to a new server or to a development environment, and we can restore large databases that would be impossible to restore using browser access due to the limits of loading and processing time.
Working with database dumps in MySQL and MariaDB is simple. To start, all we need is access to a Linux server with MySQL / MariaDB installed and running, the name of the database we want to import/export and the access credentials (username and password).
How to export de database
In order to export the db to a text file we will use the mysqldump </ em> command. These files can be transferred simply by copying them, and can be edited with any text editor on any platform. We will only need the name of the database that we want to export, as well as the user and password to access an account with sufficient privileges that allow at least a full read access to the database.
We will export the database using the following command:
$ mysqldump -u username -p database > export-file.sql
- -u: indicates that the following word will be the username
- username: it is the username to access the database content
- -p: indicates that the system needs to ask us for a password
- database: it is the name of the database we want to export
- export-file.sql it is the file where we will save the database exported content
The command will not produce any visible output to the user in the console, but when it finishes its execution, the export-file.sql file will have been created with the database’s content inside in the same directory where the user is at that moment. You only need to type ls </ em> to see it.
How to import the database
To import an existing copy of a database in MySQL or MariaDB, we must copy our file-import.sql to a folder of our Linux system and position ourselves in it using the console. Once there, we will use the following command:
$ mysql -u username -p database < file-import.sql
- -u: indicates that the following word will be the username
- username: it is the username to access the database contents
- -p: indicates that the system needs to ask us for a password
- database: it is the name of the database we want to export
- file-import.sql it is the file that stores the content we want to import into the database
If the command is executed successfully it will not generate any results, if errors occur during the process they will be printed on the screen. If everything has been correct, we will have the contents of the file loaded in the database.
Conclusion
Now we know how to create export files with the contents of our database and how to import them again. The mysqldump command has additional functionalities that can be used to modify how the export file is generated. You can learn more in the official mysqldump web documentation page.