Import and export MySQL databases

Import and export MySQLdatabase allows users to transfer data between different database systems, as well as back up existing data for later restoration. The import feature allows you to load data into a MySQL database from files in various formats, such as SQL, CSV or XML. Conversely, export allows you to create a copy of the database in the desired format, making it easy to share or migrate data. Understanding how to effectively manage these operations is essential to maintaining data integrity and performance. MySQL provides powerful tools such as mysqldump for exporting and the ability to import directly from the MySQL shell, which allow you to efficiently handle large amounts of data. Proper use of these features not only simplifies data management, but also improves collaboration between teams. Mastering MySQL database import and export is critical for backup strategies and overall database management.

Backing up databases with mysqldump

The mysqldump utility is a powerful tool for backing up MySQL databases. This tool allows you to export data and database structure to a text file, which can be easily saved and restored when needed. Using mysqldump is especially useful for regular backups as well as for migrating data between different systems.

Export thе data from MySQL database

To back up the database using the mysqldump utility, use the following command in the terminal:
mysqldump -u username -p database > db_backup.sql

• username - MySQL username;
• database - name of the base you want to save.

When executing this command, the username password will be requested. And successful execution will create a db_backup.sql file containing all the necessary information.

It is important to note that the mysqldump utility allows you to backup not only a single database, but also several databases simultaneously, as well as individual tables, which makes it a universal tool for data management in MySQL.

To dump several databases at once, use the SQL command:
mysqldump -u username -p --databases database1 database2 > db_backup.sqlAnd to create a MySQL dump of a separate table:
mysqldump -u username -p database table > db_backup.sql

• table - is the name of a table in the database.

Sometimes you may need to back up all databases in a single file. The command will accomplish this task:
mysqldump -u username -p --all-databases > all_db_backup.sqlWhen creating a MySQL dump, we advise you to add the following parameters:

• --no-create-db - this option suppresses CREATE DATABASE statements that would otherwise be included in the dump if the --databases or --all-databases option is specified;
• --quick - this option is useful for dumping large tables. It forces mysqldump to fetch rows for a table from the server one row at a time, rather than fetching the entire set of rows and buffering it in memory before writing it;
• --single-transaction - option is useful for transactional InnoDB tables recording the sequential state of the database at the time BEGIN is issued without locking. This option together with --quick is recommended for large tables;
• --skip-lock-tables - disables table locking.

If there are no innoDB tables, a better solution is to use --lock-tables or even --lock-all-tables instead of --skip-lock-tables.

Importing data into MySQL database

Importing data into MySQL database is an important process that allows you to load data from various sources into MySQL database. There are several methods to successfully import data, such as using the command line, graphical interfaces, or specialized utilities. Let's consider importing a database from the console:
mysql -u username -p database < db_backup.sqlThis command will also ask for the username password. If the command completes successfully, the output will be empty. The command returns only errors during its execution.
The same command can be used to import not only the entire database, but also a specific table, if the dump contains only that table. Simply put, all SQL commands that are in the file are executed.

Importing data into MySQL database from source

In the case of a very large dump, sometimes it is faster to import data from the MySQL shell, to enter which we use the command:
mysql -u username -pAnd then we execute it inside the MySQL shell:
USE database;
SOURCE db_backup.sql;The db_backup.sql file in this case should be located in the root user's home directory.

We recommend increasing the buffer (net_buffer_length) and maximum packet size (max_allowed_packet) before importing. And also disable foreign key restrictions for InnoDB tables using foreign_key_checks option. But you should remember to enable this restriction after data import.

To summarize, the following commands should be executed:
SET global net_buffer_length = 1048576;
SET global max_allowed_packet = 1073741824;
SET foreign_key_checks = 0;
USE database;
SOURCE db_backup.sql;
SET foreign_key_checks = 1;
exit;

Checking the database structure

To be sure that the import was successful and all tables were created, log into the MySQL shell and see the list of tables. To do this, use the commands:
USE database;
SHOW TABLES;