OurSQL Episode 35: Advanced Logical Export Features (Backup Series #4)

This week, we discuss advanced mysqldump options, ear candy comparing NOW() vs. SYSDATE(), and the Manga Guide to Databases.

Previous podcasts in the series:
Backup Series #1: Backup Glossary
Backup Series #2: Backup Tools You Already Have (mysqlhotcopy and mysqldump)
Backup Series #3: Looking Through the Lenz (snapshots including LVM)

ERRATA:
Giuseppe Maxia points out via twitter "The mysqldump option to skip table creation is not '--no-create-table' but '--no-create-info' " -- he's right!

Feedback/News:
We had some great feedback on the snapshots podcast, and we explain how to get some swag from us.

mysqldump options discussed:
barewords
--databases or -B
--tables
--master-data (only gives log file and position, not host, username or password)
--dump-slave (only gives log file and position, not host, username or password) New in 5.5
--apply-slave-statements - New in 5.5
--no-autocommit
--order-by-primary
--routines or -R
--events or -E
--insert-ignore
--replace
--hex-blob
--complete-insert
--log-error-file
--set-charset
--single-transaction
--tab
--xml

To pipe into gzip:
mysqldump [options] | gzip -c > output.sql.gz
or
mysqldump [options] | gzip --std-out > output.sql.gz

To avoid saving the output file directly to the disk:
mysqldump | gzip --std-out | ssh username@host “cd /preferred_path/; cat > mysqldump_file.gz”

or even more fancy (though, if one step dies this can be a problem)

mysqldump | gzip --std-out | ssh username@host “ cat > mysql -u username -p password”

(Note: If you prefer, netcat can be used instead of ssh)

Estimating time to restore

How to export INFORMATION_SCHEMA in MySQL 5.5, and whether or not PERFORMANCE_SCHEMA can be exported.

Troubleshooting mysqldump - what to do when you get the error: Got a packet bigger than 'max_allowed_packet' bytes

Planet MySQL article on How to Syntax Check Your my.cnf File - note that this is not 100% safe, but it does usually work.

Also discussed: The Manga Guide to databases

Ear Candy:
NOW() vs. SYSDATE()

Where you can see us:
On Tuesday March 1st at 3 pm Eastern Standard Time (that’s 8 pm UTC) Sheeri will be doing a webinar for ODTUG about doing query reviews with mk-query-digest. A query review is like a code review -- the idea is to review current queries, and new queries making it into the next release. Register for this free webinar at http://bit.ly/mar1_qreview

On Monday March 7th at 7pm PT, the Seattle MySQL meetup will happen with Brian Aker talking about Drizzle 7 and the upcoming GA verision! http://www.meetup.com/seattlemysql/events/16489408/

Sheeri will be at Collaborate in Orlando, Florida from Sunday April 10th through Thursday April 14th. She is organizing the Community dinner, eastern US edition on Sunday April 10th at 5 pm at Maggiano's Little Italy near the Convention Center.

Sarah will be at the O'Reilly MySQL Conference in Santa Clara, California from Monday, April 11th through Thursday April 14th, including the community dinner, western US edition on Monday April 11th at 7 pm at Pedro's.

Sheeri will be speaking about monitoring MySQL efficiently with Nagios at the Professional IT Community Conference, otherwise known as PICC, in New Brunswick, New Jersey. The conference is Friday, April 29 – Saturday April 30, 2011 and registration is a mere $249.

Sheeri will be at OpenDBCamp, Fri May 6th - Sun May 8th in Sardinia, Italy.

Send us feedback via:
Facebook group
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1104