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
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.