Bash Script to dump MySQL Databases Over Network to Other Server.
Posted on 23 March 2010 by Jason Grimme
At some point in time I need to clone many of our databases over to another MySQL server. This will require locking all users out of our severs (Apache) so that none of the databases can be updated during the clone. Because this must be preformed in a very timely manner at a very untimely time to reduce downtime, I decided to write a bash script that will be performed in the wee hours of the morning.
This bash script loops through an array of databases. For each database it performs a mysqldump and pipes the SQL directly over the network via SSH to the other MySQL server. This avoids creating SQL files, using SCP to transfer them, and then importing them.
It’s not a very complex script but I figured I would share it anyways.
databases[] : Contains all the databases you would like to clone Master_User : Master MySQL username Master_Pass : Master MySQL password Slave_SSH_User : A SSH account that will have appropriate access Slave_IP : IP addresss / hostname of your slave server Slave_User : Slave MySQL username Slave_Pass : Slave MySQL password
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | #!/bin/bash # Pauses the httpd so that we can create an exact replica of the database. Resumes after # Array containing each database to backup databases[1]="database_name_1" databases[2]="database_name_2" databases[3]="database_name_3" databases[4]="database_name_4" databases[5]="database_name_5" # Prompt the user for confirmation to continue while true; do read -p "Are you sure you want to STOP Apache and MySQL to clone the databases? Y/N: " response case $response in [Yy]* ) break;; [Nn]* ) echo "Quitting."; exit;; * ) echo "Please answer y/n.";; esac done # Stop Apache service httpd stop # Loop through each database and pipe dump to the slave server to import for db in ${databases[@]} do echo -n "Starting clone of $db ... " # !!! There should NOT be a space before your master password. Ex (-pMyPassword) mysqldump -u Master_User -pMaster_Pass $db | ssh Slave_SSH_User@Slave_IP mysql -u Slave_User -Slave_Pass $db echo "Complete!" $'\n' done echo "All Databases Cloned" # Start Apache service httpd start |