USING MYSQLDUMP

I’m diving headfirst into MySQL administration lately, and one of the first tools I’ve learned to use frequently is mysqldump, and is it powerful! Mysqldump makes copying, backing up, and restoring databases and/or tables easier than any available tool in SQL Server. I’m going to focus on some database uses for this post, and save the table operations for a later date.

BACKUPS & RESTORES
First, a simple backup of a database named ‘apptsol’ (from a linux command prompt):

mysqldump -uroot -pmypassword apptsol  > ./backups/apptsol_backup.sql

The apptsol_backup.sql file that’s created is a text file of actual MySQL statements! Drop table, create table, etc.

Now, a super-fast restore using the mysql command:

mysql -uroot -pmypassword apptsol < ./backups/apptsol_backup.sql

Checking the restored database reveals that I lost all of my stored procedures! To prevent this, use the –routines parameter in the mysqldump command to include the procs:

mysqldump -uroot -pmypassword –routines apptsol > ./backups/apptsol_backup.sql

COPYING DATABASES
I’m using MySQL replication on my servers, and periodically I want to manually reconfigure replication, as sometimes the servers get out of sync. I need to replace the slave’s copy of the apptsol database with the master’s copy. I could make a backup, tarball, gzip and scp it over to the slave, then restore it to the slave. Instead, I use a nice fast copy operation using mysqldump:

mysqldump –routines -umasterroot -pmasterpass apptsol | mysql -C -uslaveroot -pslavepass apptsol

SHAZZAM!  The database has been copied, with all stored procedures and triggers.

About these ads

Posted on January 2, 2010, in MySQL. Bookmark the permalink. 1 Comment.

  1. Wow! This sounds cool, even for the non-technical among us! Congrats on the new blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: