Source Viewer



1	#!/usr/bin/perl
2	#
3	# This script finds a list of all the databases that are currently
4	# in the database, and pulls full backups of those databases after
5	# flushing the bin-logs which contain the incremental updates
6	#
7	# Copyright 2008, Ian White, ian.o.white@gmail.com
8	#
9
10	use strict;
11
12	# Determine whether to perform a full or incremental backup. If the input is incorrect, display
13	# a usage information for the user.
14	my $action;
15
16	if(scalar(@ARGV) == 1 && $ARGV[0] eq "full") {
17	$action = \&full_backup;
18	}
19	elsif(scalar(@ARGV) == 1 && $ARGV[0] eq "incremental") {
20	$action = \&incremental_backup;
21	}
22	else {
23	die("Usage: backup_database.pl [full\|incremental]\n");
24	}
25
26	# Customize these variables to suit your situation
27	my $username = ""; # mysql root username
28	my $password = ""; # mysql root password
29	my $database_match = "'[a-zA-Z0-9.]*lc\$'"; # regex to match mysql databases to be backed up
30	my $mysql_database_location = "/var/lib/mysql"; # location of mysql databases in filesystem
31	my $mysql_log_location = "/var/log/mysql";
32	my $log_directory = "/var/log/mysql_backup"; # where to keep the error log
33	my $backup_directory = "/var/backups/mysqld"; # where to store the backups
34
35	my $datestamp = time_stamp();
36	my $errors = 0;
37
38	# Mark the beginning of this backup in the stdout
39	print "Beginning $ARGV[0] mysqld backup ($datestamp)...\n";
40
41	# Mark the beginning of this backup in the error log
42	`echo "$datestamp beginning backup..." >> ${log_directory}/mysql_backup_error.log`;
43
44	# Get a list of the databases to be backed up.
45	my @database_list = `ls ${mysql_database_location} -1 \| grep $database_match 2>> ${log_directory}/mysql_backup_error.log`;
46
47	# for each database found, create a full backup
48	foreach my $current_database (@database_list) {
49
50	chomp($current_database);
51
52	# Perform the update on the current database, record the number or errors encountered
53	$errors += $action->($current_database);
54
55	}
56
57	# Mark the end of the backup in the stdout
58	print "Backup Complete. There were $errors errors.\n\n";
59
60	# Mark the end of the backup in the error log
61	`echo "\Backup complete\n\n" >> ${log_directory}/mysql_backup_error.log`;
62
63
64	sub full_backup($) {
65
66	my $current_database = shift(@_);
67	my $errors = 0;
68
69	# Make sure that the backup directory for this lab account exists
70	mkdir("${backup_directory}/${current_database}", 0755);
71
72	# If this command complains, it's because the folder is empty and we don't care.
73	# This grabs all of the files that currently reside in the directory for deletion
74	# after the backup is successfully created. Must create sed string from $backup_directory first
75	my $sed_string = $backup_directory . "/" . $current_database . "/";
76
77	# This uses a regex to apply the proper espcaping to the sed string
78	$sed_string =~ s/\//\\\//g;
79
80	my @old_files = `ls ${backup_directory}/${current_database}/ -1 2> /dev/null \| sed 's/\$.*\$/${sed_string}\\1/'`;
81	chomp(@old_files);
82
83	# dump the $current_database into a file in the lab's backup dir
84	# called [lablc]-full-mysqld-backup-[datestamp].sql
85	print "\tCreating full backup of database: $current_database... ";
86	`mysqldump --databases --single-transaction --user=${username} --password=${password} --master-data=2 ${current_database} > "${backup_directory}/${current_database}/${current_database}-full-mysqld-backup-${datestamp}.sql" 2>> ${log_directory}/mysql_backup_error.log`;
87
88	# Check to make sure the mysqldump worked
89	if(($? & 255) != 0) {
90	print "FAILED (mysqldump error code " . ($? & 255) . ")\n";
91	# Remove the failed backup
92	unlink ("${backup_directory}/${current_database}/${current_database}-full-mysqld-backup-${datestamp}.sql");
93	print "\tNone of your existing backup files have been touched\n";
94	$errors++;
95	}
96	else {
97	print "SUCCESSFUL\n";
98	# Remove all of the old backups, now that we have a fresh, up-to-date backup.
99	unlink(@old_files);
100	print "\tOld backups have been purged from ${backup_directory}/$current_database.\n";
101
102	}
103
104	return $errors;
105	}
106
107
108	sub incremental_backup($) {
109
110	my $current_database = shift(@_);
111	my $errors = 0;
112
113	print "\tDatabase: $current_database\n";
114	print "\t\tFinding the current full backup of $current_database... ";
115
116	# Find the full backup file that references the start position in the trx logs
117	# This lists the files in lab backup folder with the newest files listed first.
118	# It then greps for only the full backups, and ends up taking only the first one.
119	my ($full_backup) = `ls ${backup_directory}/${current_database} -1r 2>> ${log_directory}/mysql_backup_error.log \| grep "${current_database}-full-mysqld-backup-[0-9]*\.sql\$" 2>> ${log_directory}/mysql_backup_error.log`;
120	my $ret_ls = $?;
121
122	chomp($full_backup);
123
124	# Open the full backup
125	my $ret_open = open(FULL_BACKUP, "${backup_directory}/${current_database}/${full_backup}");
126
127	# Find the date of the file we're using as the last full backup.
128	# If we could not find or open the full backup file, we cannot proceed. If the database is new
129	# and no full backup has yet been performed, perform a full backup and continue.
130	if(($ret_ls & 255) \|\| !$ret_open) {
131	print "FAILED ";
132	if($ret_ls & 255 \|\| $full_backup eq "") {
133	print "(Could not find the last full backup)\n\t";
134
135	# Try to create a full backup...
136	my $create_backup_errors = full_backup($current_database);
137
138	# If this fails too, give up. Otherwise, keep going as usual
139	if($create_backup_errors) {
140	$errors += $create_backup_errors;
141	return $errors;
142	}
143	else {
144	return 0;
145	}
146
147	}
148	else {
149	print "(Could not open file: ${backup_directory}/${current_database}/$full_backup)\n";
150	print "\t\tAborting backup of database: $current_database\n";
151	return ++$errors;
152	}
153	}
154	else {
155	print "SUCCESSFUL ($full_backup)\n";
156	}
157
158
159	my $master_log_file = -1;
160	my $master_log_position = -1;
161
162	print "\t\tFinding the last recorded transaction... ";
163
164	# Search through the full backup until you find the reference to the log file
165	# whose trxs start when this backup was created.
166	while(my $line = <FULL_BACKUP>) {
167
168	if($line =~ m/MASTER_LOG_FILE='(mysql-bin.\d)', MASTER_LOG_POS=(\d);/) {
169	$master_log_file = $1;
170	$master_log_position = $2;
171	last;
172	}
173	}
174
175	# If it cannot find the proper trx log file or position, we cannot continue
176	if($master_log_file == -1 \|\| $master_log_position == -1) {
177	print "FAILED\n\t\tAborting backup of database: $current_database\n";
178	return ++$errors;
179	}
180	else {
181	print "SUCCESSFUL\n";
182	}
183
184	print "\t\tCreating the incremental backup for $current_database... ";
185
186	# Output the trxs from only the current database that occurred from the time of
187	# the last full backup until present to a file [lablc]-incremental-mysqld-backup-[datestamp].sql
188	`mysqlbinlog --database=${current_database} --start-position=${master_log_position} --to-last-log --result-file=${backup_directory}/${current_database}/${current_database}-incremental-mysqld-backup-${datestamp}.sql ${mysql_log_location}/${master_log_file} 2>>${log_directory}/mysql_backup_error.log`;
189
190	# Check to make sure that the mysqlbinlog worked correctly
191	if(($? & 255) != 0) {
192	print "FAILED (mysqlbinlog error code: " . ($? & 255) . ")\n";
193	$errors++;
194	}
195	else {
196	print "SUCCESSFUL\n";
197	}
198
199	return $errors;
200	}
201
202
203	sub time_stamp {
204	my ($d);
205	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
206	$year += 1900;
207	$mon++;
208	$d = sprintf("%4d%2.2d%2.2d%2.2d%2.2d%2.2d",$year,$mon,$mday,$hour,$min,$sec);
209	return($d);
210	}
211
212