How to import/export a MySQL or MariaDB db using the terminal

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 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 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.

No Comments

Post a Comment

He leído y acepto la política de privacidad *

Tell us about your project and we will make it happen

Uso de cookies

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.

ACEPTAR
Aviso de cookies