User Tools

Site Tools


developmental:create-or-restore-mysql-mariadb-backups-ssh-command-line

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

Dumping a MySQL database, explained

The command "mysqldump" 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

You can pipe the *gzip" command with the initial dump, and then crocodile it into a compressed format, like so:

mysqldump database | gzip > database_backup.sql.gz

This dumps the database and compresses it with one line of commands, and is the most common way to backup a MySQL database. Further, you can specify more than one database, like so:

mysqldump –databases database1 database2 database3 | gzip > database123_backup.sql.gz

You can also specify specific tables that you'd like to dump and backup, with a command like the one below:

mysqldump database tablename [tablename2, tablename3, etc] | gzip > database_table.sql.gz

If you want to dump multiple tables from different databases, you'll need to use an and_if command to continue writing to the backup, like so:

mysqldump database1 table1 > database_tables.sql && mysqldump database2 table2 » database_tables.sql gzip -c database_tables.sql > database_tables.sql.gz rm database_tables.sql [y]

Lastly, if you need to make a backup of all of your databases, you can use the following command:

mysqldump –all-databases | gzip > all_databases.sql.gz

Importing a MySQL database

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

If the database is not compressed, you can use the following command:

mysql database < my_database_backup.sql

If you want to restore a single database from a backup of all databases (or multiple databases), use the following command:

mysql –one-database database < all_databases.sql

You can import any table backups into any database, however, you cannot change the name of the table being imported with the *mysql* command. You can change the name of the table to be imported by modifying the dump/backup file, however, using *sed*. This is for EXPERIENCED USERS ONLY.

1. Uncompress the backup, if it's compressed with the ".gz" suffix:

gunzip my_database_backup.sql.gz

2. Use the *sed* command to find, and replace all instances of "`table_name`", and redirect i/o to another file, to save original file from changes:

sed 's/`old_table_name`/`new_table_name`/g' my_database_backup.sql > database_with_changed_table.sql

3. Import the table to your desired database:

mysql desired_database < database_with_changed_table.sql

Importing multiple databases at once

You can import multiple databases at once by not specifying a database to import into.

THIS WILL OVERWRITE ANY CURRENT INFORMATION THAT IS DIFFERENT FROM THE IMPORT. DO NOT DO THIS UNLESS YOU'RE SURE OF THE CHANGES!

gunzip < all_databases.sql.gz | mysql

developmental/create-or-restore-mysql-mariadb-backups-ssh-command-line.txt · Last modified: 2018/03/20 20:02 by Daniel P.