Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 58 10 Older Entries

Displaying posts with tag: mysqldump (reset)

An argument for using mysqldump
+3 Vote Up -0Vote Down

I fully agree with Morgan’s An argument for not using mysqldump. This post does not come to contradict it, but rather shed a positive light on mysqldump.

I usually prefer an LVM snapshot based backup, or using XtraBackup. And, with databases as large as dozens of GB and above, I consider mysqldump to be a poor alternative. Poor in runtime, poor in overhead while taking the backup.

However once in a while I get to be reminded that mysqldump just works.

As a recent example, I had a …

  [Read more...]
An argument for not using mysqldump
+2 Vote Up -0Vote Down

I have a 5G mysqldump which takes 30 minutes to restore from backup.  That means that when the database reaches 50G, it should take 30x10=5 hours to restore.  Right?  Wrong.

Mysqldump recovery time is not linear.  Bigger tables, or tables with more indexes will always take more time to restore.

If I restore from a raw backup (LVM snapshot, xtrabackup, innodb hot backup), it is very easy to model how much longer recovery time will take:

Backup is 80G
Copy is at 70MB/s.
10G is already complete.
= ((80-10) * 1024)/70/60 = ~17 minutes …


  [Read more...]
Setting up slave, stripping indexes and changing engines, on the fly
+4 Vote Up -0Vote Down

Warning, the following is quite ugly, but does the job :)

A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.

mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl …

  [Read more...]
Easy MySQL: how to backup databases to a remote machine
+3 Vote Up -1Vote Down

Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

Method one – this writes a remote file.
mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

Method two – this writes directly into a remote mysql server
mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

mysqldump each object separately
+3 Vote Up -1Vote Down

As a continuation to a previous blog post last week and inspired by Kedar I have created a small script to export tables, stored procedures, functions and views into their respective file. It works for multiple databases where you can specify a list of databases too and although things like events, triggers and such are still missing they are easily added.

It is especially …

  [Read more...]
Dumping DDL – mysqldump tables, stored procedures, events, triggers (separately)
+4 Vote Up -0Vote Down

If you like to keep your ddl backed up in some source management tool like svn or cvs and want to do it individually for stored procedures, events, triggers, tables and such rather than having a single file you can easily do so using the below. You could even include the –skip-dump-date or –skip-comments and use the below to compare ddl daily checking for alterations thus making sure you are aware of any ddl changes done on the database.

user=backup_user
password=`cat ~/.backup_password`
hostname=127.0.0.1
port=3306
dbname=test_db
path=/home/mysql/ddl






  [Read more...]
Simple Backup Server
+1 Vote Up -1Vote Down

I have not written an article in a while, I partially blame it on the World Cup and my day job. The time has come to share some of my recent experiences with a neat project to provide several teams internally with current MySQL backups.

When faced with these types of challenges is my first step is to look into OSS packages and how can they be combined into an actual solution. It helps me understand the underlying technologies and challenges.

ZRM BackupI have reviewed Zmanda's Recovery Manager for MySQL Community Edition in the Fall 2008 issue of …



  [Read more...]
Backup large databases with mysqldump by splitting up the backup files
+0 Vote Up -0Vote Down

The primary responsibility of MySQL professionals is to establish and run proper backup and recovery plans. The most used method to backup a MySQL database is the mysqldump utility. This mysqldump utility creates a backup file for one or more MySQL databases that consists of DDL/DML statements needed to recreate the databases with their data. To [...]

MySQLDump from A to B with pipes
+0 Vote Up -0Vote Down

Moving your data and tables around comes in many different flavours. The use of mysqldump is common practice to dump your data and schema out to a file. It is also possible to pipe your mysqldump into a 2nd server. Try the code below (adapting the users and passwords!) in a test environment;
$ mysqldump -u UserA -p p455w0rd --single-transaction --all-databases --host=Server1 | mysql -u UserA -p p455w0rd --host=Server2
As you can see from the command we are taking all the databases in a single transaction into Server2 from Server1. If you're not using transactional tables substitute the --single-transaction for --lock-all-tables to ensure you get a …

  [Read more...]
Simple Backup Restore Trick
+2 Vote Up -1Vote Down

I don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: How do I restore my production backup to a different schema? It looks obvious, but I haven't seen many people thinking about it.

Most of the time backups using mysqldump will include the following line:

USE `schema`;
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?

The actual trick
Using …







  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 58 10 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.