What's new

Welcome to GloTorrents Community

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

Ask question

Ask Questions and Get Answers from Our Community

Answer

Answer Questions and Become an Expert on Your Topic

Contact Staff

Our Experts are Ready to Answer your Questions

MySQL Backup Tricks and Tips

_.:=iTake=:._

Administrator
Staff member
ZeuS
Super Moderator
+Lifetime VIP+
Registered
Joined
Oct 20, 2018
Messages
1,370
Reaction score
1,431
Points
113
Credits
14,854
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
 

_.:=iTake=:._

Administrator
Staff member
ZeuS
Super Moderator
+Lifetime VIP+
Registered
Joined
Oct 20, 2018
Messages
1,370
Reaction score
1,431
Points
113
Credits
14,854
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:
rsync -av --progress /your/local/backup/directory/backup_$(date "+%b-%d-%Y-%H-%M-%S") .sql.gz [email protected]:/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.

[email protected]:/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
 

_.:=iTake=:._

Administrator
Staff member
ZeuS
Super Moderator
+Lifetime VIP+
Registered
Joined
Oct 20, 2018
Messages
1,370
Reaction score
1,431
Points
113
Credits
14,854
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
 
shape1
shape2
shape3
shape4
shape7
shape8
Top