# MySQL Backup Tricks And Tips **URL:** \ **Category:** Tutorials & Methods\ **Tags:** tips-tricks, data-recovery, backup\ **Created:** [August 21, 2019, 10:09am UTC](https://onehack.st/t/mysql-backup-tricks-and-tips/54380 "2019-08-21T10:09:50Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![Aina](https://onehack.st/user_avatar/onehack.st/aina/32/167469_2.png) [@Aina](https://onehack.st/u/Aina)\ **Post date:** [August 21, 2019, 10:09am UTC](https://onehack.st/t/mysql-backup-tricks-and-tips/54380/1 "2019-08-21T10:09:51Z")
## **1- All Databases** **You need SSH access and possible Super User Privileges to run this commands.** ` mysqldump --all-databases | gzip > /path/backups/backup_$(date "+%b-%d-%Y-%H-%M-%S").sql.gz` `mysqldump` : utility to dump mysql database `-u ` specifies the user with mysql privileges with DB privileges.. In this tutorial, I am using root user `-p` specifies the password flag needed by the user. `-h` flag specifies the host you are connecting to.. You can simply ignore this if its localhost but if it’s a remote IP, you need to specify the IP address.. `gzip` : Utility to compress the .sql file after dump file is created. `date` : $(date “+%b-%d-%Y-%H-%M-%S”) by adding this you are giving a date and time to your backups… **Single Databases"** If you’re just trying to backup a simple database you can run this command ` mysqldump -h localhost -u root -ppassword dbname | gzip > /path/backups/backup_$(date "+%b-%d-%Y-%H-%M-%S").sql.gz` ## **2 - To export the data to a remote host. I advice using rsync for this.** Eg. command to transfer the backup to a backup server.. Code: ```auto rsync -av --progress /your/local/backup/directory/backup_$(date "+%b-%d-%Y-%H-%M-%S") .sql.gz root@remote.ip.address.here:/home/backups ``` **`rsync -av --progress /your/local/backup/directory/backup_$(date "+%b-%d-%Y-%H-%M-%S") .sql.gz`** **rsync** – tool to transfer files **-v** flag needed to view the output of rsync **–progress** flag to see the progress of the transfer **/your/local/backup/directory** path to your backup directory **backup\_$(date “+%b-%d-%Y-%H-%M-%S”) .sql.gz** this will be your backup file if you used the backup eg.in my first post. **`root@remote.ip.address.here:/home/backups`** **root** the user that has access to the remote server receiving the backup, in my case it;s root, you can change this accordingly. **@remote.ip.address.here** the remote location or IP address eg. @45.36.324.23 **:** add this if the remote is using default SSH ports.. **/home/backups** this is the drectory you want the backup to be stored into ## 3 - **Fast data import trick** `$ mysqldump -h localhost -u root -p --extended-insert --quick --no-create-info mydb mytable | gzip > mytable.sql.gz ` **A bit more about this line:** - `--extended-insert:` it makes sure that it is not one INSERT per line, meaning a single statement can have dozens of rows. - `--quick:` useful when dumping large tables, by default MySQL reads the whole table in memory then dumps into a file, that way the data is streamed without consuming much memory. - `--no-create-info` : this means only the data is being exported, no CREATE TABLE statements will be added **To do the import do this:** **Disable foreign key checks when importing batch data in MySQL** `SET foreign_key_checks = 0;` /\* do you stuff REALLY CAREFULLY \*/ `SET foreign_key_checks = 1;` **Add SET FOREIGN\_KEY\_CHECKS=0; to the beginning of your sql file** **or** 1. `cat <(echo "SET FOREIGN_KEY_CHECKS=0;") data.sql | mysql` **or** 1. `mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;"` **You don’t need to run SET FOREIGN\_KEY\_CHECKS=1 after as it is reset automatically after the session ends** **Checkout Google if you are interested in backing up to their Cloud Service** > **[Configure Cloud SQL and the external server for replication  | ...](https://docs.cloud.google.com/sql/docs/mysql/replication/configure-replication-from-external)** ## ENJOY & HAPPY LEARNING! 😊