BackUp&Restore
------
Back Up and Restore MySQL Databases with Mysqldump
The mysqldump utility expressions take the following form:
mysqldump [options] > file.sql
Copy
options - The mysqldump options
file.sql - The dump (backup) file
To use the mysqldump command the MySQL server must be accessible and running.
The most common use case of the mysqldump tool is to backup a single database.
For example, to create a backup of the database named database_name using the user root and save it to a file named database_name.sql you would run the following command:
mysqldump -u root -p database_name > database_name.sql
Copy
You will be prompted to enter the root password. After successful authentication, the dump process will start. Depending on the database size, the process can take some time.
If you are logged in as the same user that you are using to perform the export and that user does not require a password, you can omit the -u and -p options:
mysqldump database_name > database_name.sql
Copy
To backup multiple MySQL databases with one command you need to use the --database option followed by the list of databases you want to backup. Each database name must be separated by space.
mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql
Copy
The command above will create a dump file containing both databases.
Use the --all-databases option to back up all the MySQL databases:
mysqldump -u root -p --all-databases > all_databases.sql
Copy
Same as with the previous example the command above will create a single dump file containing all the databases.
The mysqldump utility doesn’t provide an option to backup all databases to separate files but we easily achieve that with a simple bash FOR loop:
for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql";done
Copy
The command above will create a separate dump file for each database using the database name as the filename.
If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown below:
mysqldump database_name | gzip > database_name.sql.gz
Copy
If you want to keep more than one backup in the same location, then you can add the current date to the backup filename:
mysqldump database_name > database_name-$(date +%Y%m%d).sql
Copy
The command above will create a file with the following format database_name-20180617.sql
You can restore a MySQL dump using the mysql tool. The command general syntax is as follows:
mysqld database_name < file.sql
Copy
In most cases you’ll need to create a database to import into. If the database already exists, first you need to delete it.
In the following example the first command will create a database named database_name and then it will import the dump database_name.sql into it:
mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < database_name.sql
Copy
If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the --one-database option as shown below:
mysql --one-database database_name < all_databases.sql
Copy
Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner:
mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name
Copy
The command above will pipe the output to a mysql client on the remote host and it will import it into a database named remote_database_name. Before running the command, make sure the database already exists on the remote server.
Automating the process of backing up the databases is as simple as creating a cron job what will run the mysqldump command at specified time.
To set up automated backups of a MySQL database using cronjob, follow the steps below:
Create a file named .my.cnf in your user home directory:
sudo nano ~/.my.cnf
Copy
Copy and paste the following text into the .my.cnf file.
[client]user = dbuserpassword = dbpasswd
Copy
Do not forget to replace dbuser and dbpasswdwith the database user and user’s password.
Restrict permissions of the credentials file so that only your user has access to it:
chmod 600 ~/.my.cnf
Copy
Create a directory to store the backups:
mkdir ~/db_backups
Copy
Open your user crontab file:
crontab -e
Copy
Add the following cron job that will create a backup of a database name mydb every day at 3am:
0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql
Copy
Do not forget to replace username with your actual user name.
You can also create another cronjob to delete any backups older than 30 days:
find /path/to/backups -type f -name "*.sql" -mtime +30 -delete
Copy
-------