The process of backing up is an essential task with the purpose to recover data after its loss or recover data from an earlier time. There would be many potential disasters in the process of working with data and there also exists plenty of recovery plans, backup is considered as a popular and complete solution. Put it in simple way, backup keeps one copy of all intrinsic data.
However, the procedure is kinda complicated and required lots of steps undertaken, such as data selection, extraction and manipulation. Therefore, various techniques and tools have been developed to make it easier and more convenient for both end-users and developers as well. Different approaches and methods have its own pros and cons but all include dry runs to validate the possible effect of failure, which can intentionally come up. Basically, the process involves jobs with open files and live data sources. Compression, encryption and deduplication need entailing.
Among many available methods, such as using phpMyAdmin, vn.net, mysql workbench, etc., we would like to provide you detailed guideline to undertake the mission using mysqldump. It is the most popular and simple tool to backup MySQL database. The operation mechanism of mysqldump is *.sql file creation with sql-statements of the source database to reproduce the original schema objects, table data, etc. Mysqldump can backup local database in the meantime restoring it on a remote one.
Hereafter are detailed guideline for even dummies to backup and restore data using mysqldump.
Using the following command to create a dump file named all_databases.sql.
mysqldump –all-databases –single-transaction –user=root –password > all_databases.sql
You will see the command –password after tapping enter. Insert your password to proceed. In case you are using Global Transaction Identifiers, –set-gtid-purged=OFF may in use. The following code can be sent out:
mysqldump –all-databases –single-transaction –set-gtid-purged=OFF –user=root –password > all_databases.sql
For those who want to execute partial backup, you may need clearly define which parts of database that you want to back up to put it in the database table. You can refer to below example:
After that, use the command to run from a prompt:
mysqldump –databases comicbookdb coupons scripts watchdb –single-transaction –set-gtid-purged=OFF –add-drop-database –triggers –routines –events –user=root –password > partial_database_backup.sql
Similarly, enter your password when it is asked. When exporting data, if you find out that your data might be corrupt, you can separate specific rows by using –skip-extended-insert. Size of backup file will be larger in this case, when you import data.
When you export your data, mysqldump will create INSERT INTO statements to import the data into the tables. However, the default is for the INSERT INTO statements to contain multiple-row INSERT syntax that includes several VALUES lists. This allows for a quicker import of the data. But, if you think that your data might be corrupt, and you want to be able to isolate a given row of data – or if you simply want to have one INSERT INTOstatement per row of data, then you can use the –skip-extended-insert option. If you use the –skip-extended-insert option, importing the data will take much longer to complete, and the backup file size will be larger. The following command is for data import:
mysql -uroot -p < partial_database_backup.sql
Last step is using mysql_bak.config file to specify which database that you want to backup.
#!/usr/bin/perl
# Perform a mysqldump on all the databases specified in the dbbackup.config file
use warnings;
use File::Basename;
# set the directory where you will keep the backup files
$backup_folder = ‘/Users/tonydarnell/mysqlbak’;
# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . “/mysql_bak.config”;
# example config file
# You may use a comment to bypass any database that you don’t want to backup
# # Unwanted_DB (commented – will not be backed up)
# twtr
# cbgc
# retrieve a list of the databases from the config file
my @databases = removeComments(getFileContents($config_file));
# change to the directory of the backup files.
chdir($backup_folder) or die(“Cannot go to folder ‘$backup_folder'”);
# grab the local time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$year += 1900;
$mon++;
#Zero padding
$mday = ‘0’.$mday if ($mday<10);
$mon = ‘0’.$mon if ($mon<10);
$hour = “0$hour” if $hour < 10;
$min = “0$min” if $min $folder/$file.Z`;
print “Donen”;
}
print “Donenn”;
# this subroutine simply creates an array of the list of the databases
sub getFileContents {
my $file = shift;
open (FILE,$file) || die(“Can’t open ‘$file’: $!”);
my @lines=;
close(FILE);
return @lines;
}
# remove any commented tables from the @lines array
sub removeComments {
my @lines = @_;
@cleaned = grep(!/^s*#/, @lines); #Remove Comments
@cleaned = grep(!/^s*$/, @cleaned); #Remove Empty lines
return @cleaned;
}
If you want to save time and effort on backup jobs, VPSTrust also provide web hosting management services. Our technical guru team is here to help so that you can totally focus on your business. Find more information about VPSTrust Web Security Service here.