How To Back Up MySQL Databases With mylvmbackup On Debian Squeeze
mylvmbackup is a Perl script for quickly creating MySQL backups. It uses LVM's snapshot feature to do so. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the tables again. This article shows how to use it on a Debian Squeeze server.
Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate
a mysqldump –ignore-database.
This mysqldump option doesn’t exist and
these three guys have given
us various helpful solutions.
But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :
--exclude-pattern "pattern" All databases or tables that match the pattern are not backed up. If --all-databases or --databases are specified, the exclude pattern applies to …[Read more]
Database backups are typically critical to organizations, and are an important part of an overall disaster recovery strategy.
MySQL Enterprise Backup performs online "Hot", non-blocking backups of your MySQL databases, and interfaces with media management software such as Symantec NetBackup, Oracle Secure Backup and IBM Tivoli Storage Manager to execute backup and restore operations.
Two new white papers are available to help you better understand:
Enjoy the white papers.
A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.
Usage is simple:
garfield ~ # ./extract.sh -f dump.sql -d redmine >
redmine.sql
garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows
> redmine-workflows.sql
You can grab the script from Downloads page.
extract.sh
#!/bin/bash
#
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
#
function help()
{
cat <<EOF
Usage: [-h] -f <dump file> -d <database …[Read more]
Ronald Bradford and Giuseppe Maxia (hey guys!) wrote about different ways to ignore a database when using mysqldump –all-databases over the past couple of days.
Whilst the solutions are interesting, I wondered why not attack it from the proper approach, and add the option to mysqldump itself? Honestly, the patch is trivial, and doing anything against INFORMATION_SCHEMA with lots of databases and tables … well let’s just say … group_concat_max_len is the least of your worries..
15 minutes later I had a working solution:
To my surprise, I also found Bug#3228, created a little over 8 years ago.. I’ve posted the patch …
[Read more]Using mysqldump is a quick way to do backups, although usually limited to only smaller databases – perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren’t even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.
Rather than editing out parts of the file that I don’t need, I wrote simple one-liners that do that for me. These are not perfect as for example they can’t deal with multiple tables by the same name existing in several different schemas, but that so far never turned out to be a problem. I encourage you to suggest improved ways of doing this, just let me know in the …
[Read more]A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.
As a workaround, he proposes:
mysqldump --databases `mysql --skip-column-names \
-e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
FROM information_schema.schemata WHERE schema_name \
NOT IN ('mysql','performance_schema','information_schema');" \
>` >/mysql/backup/rds2.sql
It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)
There are two alternative methods.
The all-shell methodThis method lets …
[Read more]This example uses a backup to rename a database in the MySQL instance.
Today I tried to make incremental backups of a MariaDB instance
in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there
is documentation about making incremental
backups.
MySQL sandbox makes it easy to run many different MySQL versions
on one machine. It does this by changing the port number, data
directory, UNIX socket location and a whole lot more.
So I first started with a full backup and after that I used that
backup as a base for the incremental backups. To do that I had to
specify the port number which is 5522 and the username and
password for the msandbox account. As MySQL uses a UNIX socket
instead …
The MySQL Enterprise Backup (MEB) Team is pleased to
announce the release of MEB 3.7.1, a maintenance release version
that includes bug fixes and enhancements to some of the existing
features.
The most important feature introduced in this release is
Automatic Incremental Backup. The new argument syntax for
the --incremental-base option is introduced which makes it
simpler to perform automatic incremental backups. When the
options --incremental &
--incremental-base=history:last_backup are combined, the
mysqlbackup command uses the metadata in the
mysql.backup_history table to determine the LSN to use as the
lower limit of the incremental backup. You no longer need to keep
track of the actual LSN (as in the option --start-lsn=LSN) or
even the location of the previous backup (as in the option …