Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best practices for MySQL backup

Tags:

mysql

backup

I need to backup the MySQL database on my current system. I am using the mysqldump command in a cron job using a shell script.

Here is roughly what I do:

#!/bin/bash

fileName=$(date +%H-%M)
mysqldump -ubackup -hserver1.local.com -A database1 > /backup/$filename.sql

This take about 1 hour to complete so my question is this:

I need to compress the data so i would like to know if I should first back up the file as pure sql then compress it or should I compress it right away from the mysqldump command?

like image 453
Weston Carlson Avatar asked Jan 31 '12 00:01

Weston Carlson


People also ask

What is the best backup strategy in SQL Server?

A full backup strategy is best for SQL Servers that are relatively small because the entire database is backed up each time. This strategy is the appropriate strategy for system databases such as master, model, and msdb because of their typical small size. Each backup takes longer to run.

What are the types of backups in MySQL?

There are two backup types: physical and logical. Physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup), and also you can use cp or rsync command lines to copy the datadir as long as mysql is down/stopped.


1 Answers

To reduce intermediate disk space usage, you can compress on-the-fly:

mysqldump (options) | bzip2 -c > /backup/$filename.sql.bz2

This means you won't have to write out the entire uncompressed SQL data to a file, and then read back over it to compress it.

like image 163
Justin ᚅᚔᚈᚄᚒᚔ Avatar answered Oct 10 '22 15:10

Justin ᚅᚔᚈᚄᚒᚔ