Category Archives: MySQL

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.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: