Creating MySQL Backups and Importing them via Command Line
Importing and dumping backups from command line is more efficient than using a GUI like phpMyAdmin. Command line queries for MySQL don’t follow any PHP limits, so importing or exporting a MySQL backup is easier and it’s very unlikely that you’ll run into any errors that phpMyAdmin may.
Dumping a MySQL database, explained
mysqldump command writes a plain text version of your database(s) including the CREATE DATABASE and USE commands, essentially printing a snapshot of your current database. Running just the command will print out the entire database with all the statements, but nothing else – so you’ll need to pipe the output to another command or crocodile it into a file.
Creating a MySQL Backup
We’re going to assume moving forward that you’re logged in as the root user – if you’re not, you need to make sure the command is followed by the username and password of the MySQL user which has privileges on the database, like this:
mysqldump -uaccount_testuser -pthisismypassword …
When creating a backup, the most common process is to compress it when dumped. Compressing a MySQL dump reduces the size of the payload – it’s also recommended when transferring (or downloading the backup) to compress it to maintain its integrity.
To do this, you can use the
gzip command, which should be installed on all of our servers:
mysqldump database | gzip > database.sql.gz
You can specify more than one database:
mysqldump –databases database1 database2 database3 | gzip > database123.sql.gz
You can even specify one or more tables of a database:
mysqldump database tablename tablename2 tablename3 | gzip > database_tables.sql.gz
If you want to dump multiple tables from different databases into a single backup, you can do that as well, but it requires multiple commands. Here’s an example:
mysqldump database1 table1 > database_tables.sql mysqldump database2 table2 >> database_tables.sql gzip -c database_tables.sql > database_tables.sql.gz
Lastly, if you just want to dump all databases, you can do that with the following command:
mysqldump --all-databases | gzip > all-databases.sql.gz
Importing a MySQL database
If possible and space allows, always backup the database you’re working with before importing over it, just in case!
Importing MySQL databases is just as easy as backing them up. If the database already contains data, it will be overwritten in the process! If the database is compressed, you can use one of the following commands to import it:
gunzip < my_database_backup.sql.gz | mysql mydatabase
zcat my_database_backup.sql.gz | mysql mydatabase
And if the database is not compressed, you can import it two different ways. The easiest way is:
mysql my_database < my_database_backup.sql
Sometimes MySQL dumps from other sources will dump specifically into a database name that is specified in the dump file. This can be confusing at first, so it’s a good idea to get into the habit of importing MySQL databases safely by
sourcing them via MySQL’s command line. Here’s how:
[root@cluster ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2856719 Server version: 10.3.27-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use knownhost_database; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [knownhost_database]> source /home/knownhost/my_database.sql Query OK, 19 rows affected (0.001 sec) Records: 19 Duplicates: 0 Warnings: 0 Query OK, 3 rows affected (0.001 sec) Records: 3 Duplicates: 0 Warnings: 0 Query OK, 43 rows affected (0.000 sec) Records: 43 Duplicates: 0 Warnings: 0 MariaDB [knownhost_database]> quit Bye [root@cluster ~]#
Using the above method requires that the database is uncompressed before you import it – make sure you uncompress it before attempting the import.
Importing a Specific Database from Large Dump
Sometimes you’ll only need to import a single database from a MySQL dump file backup you created. This is fairly simple to do:
mysql –one-database database < all_databases.sql
Other times, you may want to import a single table into a database from a full database backup. This still overwrites the table in question (if it exists), but will leave the rest of the database untouched. This requires multiple commands to first pull the table out of the dump file, and then to import the table:
sed -n -e '/CREATE TABLE.*`single_table`/,/Table structure for table/p' mysql_backup.sql > single_table.sql mysql database_to_import_into < single_table.sql
Importing all Databases from Dump
You can import all the databases in a dump file.
THIS WILL OVERWRITE ANY CURRENT INFORMATION THAT IS DIFFERENT FROM THE IMPORT. DO NOT DO THIS UNLESS YOU’RE SURE OF THE CHANGES!
mysql < all_databases.sql
If it is compressed as a
gzip file, you can also do:
gunzip all_database.sql.gz | mysql