Automated or Regular Backup of MySQL Data

Automated or regular backup of mysql data

CSV and SELECT INTO OUTFILE

http://dev.mysql.com/doc/refman/5.7/en/select-into.html

SELECT ... INTO OUTFILE writes the selected rows to a file. Column and
line terminators can be specified to produce a specific output format.

Here is a complete example:

SELECT * FROM my_table INTO OUTFILE '/tmp/my_table.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

The file is saved on the server and the path chosen needs to be writable. Though this query can be executed through PHP and a web request, it is best executed through the mysql console.

The data that's exported in this manner can be imported into another database using LOAD DATA INFILE

While this method is superior iterating through a result set and saving to a file row by row, it's not as good as using....

mysqldump

mysqldump is superior to SELECT INTO OUTFILE in many ways, producing CSV is just one of the many things that this command can do.

The mysqldump client utility performs logical backups, producing a set
of SQL statements that can be executed to reproduce the original
database object definitions and table data. It dumps one or more MySQL
databases for backup or transfer to another SQL server. The mysqldump
command can also generate output in CSV, other delimited text, or XML
format.

Ideally mysqldump should be invoked from your shell. It is possible to use exec in php to run it but since producing the dump might take a long time depending on the amount of data, and php scripts usually run only for 30 seconds, you would need to run it as a background process.

mysqldump isn't without it's fair share of problems.

It is not intended as a fast or scalable solution for backing up
substantial amounts of data. With large data sizes, even if the backup
step takes a reasonable time, restoring the data can be very slow
because replaying the SQL statements involves disk I/O for insertion,
index creation, and so on.

A classic example see this question: Server crash on MySQL backup using python where one mysqldump seems to start before the earlier one has finished and rendered the website completely unresponsive.

Mysql replication

Replication enables data from one MySQL database server (the master)
to be copied to one or more MySQL database servers (the slaves).
Replication is asynchronous by default; slaves do not need to be
connected permanently to receive updates from the master. Depending on
the configuration, you can replicate all databases, selected
databases, or even selected tables within a database.

Thus replication operates differently from SELECT INTO OUTFILE or msyqldump It's ideal keeping the data in the local copy almost upto date (Would have said perfectly in sync but there is something called slave lag) On the other hand if you use a scheduled task to run mysqldump once every 24 hours. Imagine what can happen if the server crashes after 23 hours?

Each time you run mysqldump you are producing a large amount of data, keep doing it regularly and you will find your hard disk filled up or your file storage bills are hitting the roof. With replication only the changes are passed on to the server (by using the so called binlog)

XtraBackup

An alternative to replication is to use Percona XtraBackup.

Percona XtraBackup is an open-source hot backup utility for MySQL -
based servers that doesn’t lock your database during the backup.

Though by Percona, it's compatible with Mysql and Mariadb. It has the ability to do incremental backups lack of which is the biggest limitation of mysqldump.

Automatic backup of MySQL data in linux

I guess you can throw the command you provided yourself in a script, and add that to cron. See http://www.scrounge.org/linux/cron.html for instructions on how to do that.

This'll overwrite /file/private/billing_backup.sql every time; you can use timestamped filenames to avoid that

Automatic MySQL backup using batch File

For future reference and help, I am updating my answer! Just update the below batch file according to your configuration:

echo off 
start "" "C:\wamp\bin\mysql\mysql5.6.12\bin\mysqldump.exe(your mysqldump address)" --user root --password=(provide here) databaseNameHere --result-file="D:\where you want path with SqlFileName.sql" --database databaseNameHere

you can use

.%date:~10,4%-%date:~7,2%-%date:~4,2%
Blockquote

in your backup file name it will also store the date in file name.

Then run this batch file regularly using Windows Task Schedule.

How to backup MySQL with MySQL workbench automatically

The correct answer is There is no backup scheduling/automating option in MySQL Workbench as of 6.2. You can do manual backup in version 6.x by clicking on DataExport under Management. See below:

MySQL WorkBench Data Export Import

How to do automatic mysql db backup using mysql workbench

If you have a webserver with PHP I'd suggest MySqlDumper

It supports:

• Automatic backups
• Emails backups
• Compresses backups
• Rotates backups
• etc.

Automatic MySQL Database backup and email through cPanel cron

AutoMySQLBackup gets great reviews. I have not used it but it seems to do exactly what you are looking for. Also, here is another link with different ways to backup, including emailing.

---

Related Topics