Over the years of being a admin I have seen many ideas on how to backup a mysql database server. Many people are satisfied with with one large dump of all databases into one flat text file. Others think copy the actual db files to other locations are the way to back things up. I have found dealing with customer and internal databases you need incremental backups. With all that said I developed a perl script that will log in grab all existing database names and do a mysqldump of each database. It will also keep a specified amount of backups and you also can choose to have some logs gzip’ed or left uncompressed. Below is the link to the code. You will have update login credentials and the ip address of the mysql server.
Explanation of the script:
The script will login to your mysql server and issue a “show databases;”. Once it the database names are retrieved it will run a mysqldump on each database and store the output in the specified directory. The name scheme of the out will be database_name-YYYYMMDD.sql. Doing this it will allow you to sort based on date. Once all mysqldumps have completed it will go through each database directory and audit what should be saved, deleted, gzipped or leave alone. You will be able to chose what the numbers of day you want to retain and the how many days you want to leave as a uncompressed file.