KNOWNHOST KNOWLEDGE BASE

Hosting Question? Find the Solution - Browse our Guides, Articles, and How-To's

Creating MySQL Backups and Importing them via Command Line

Category: Getting Started
Tags: # # #

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 MySQL backups is easier and it’s very unlikely that you’ll run into any errors that phpMyAdmin may.

Dumping a MySQL database, explained

The 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 Backups

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 backups 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:

With gunzip:

  gunzip < my_database_backup.sql.gz | mysql mydatabase

With zcat:

  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 backups dump files 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 backups 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

Related Articles: