Advertisment

Backing up Your MySQL Database

author-image
PCQ Bureau
New Update

The need for backing up a database is very obvious. In case of MySQL, an easy to use, command line utility called mysqldump does the requisite. 

Advertisment

Suppose there is a MySQL database named ‘pcquest’, then backing up all its data is as simple as issuing the following command:





mysqldump —u root 


pcquest > pcquest.back -p







syntax : mysqldump —u > -p

When prompted enter the password for the MySQL ‘root’ user. In place of root user you can specify any user, who can access the particular database, and the corresponding password. Refer to Access Privileges in MySQL, on page 102, PCQuest November 2001. 

Advertisment

The above command results in a text file, not a binary file as in case of other databases, named pcquest.back. Open pcquest.back in a text editor and you will see the file flooded with SQL ‘CREATE TABLE’ and ‘INSERT’ statements. mysqldump has simply generated ‘CREATE TABLE’ statements for all the tables in the database and ‘INSERT’ statement for all the data in each table. 

Suppose the password corresponding to the ‘root’ user is ‘pcq’. Then instead of typing in the password when prompted, we can type the password in the command itself as:

mysqldump —u root 



pcquest > pcquest.back —ppcq





syntax : mysqldump —u > -p

Advertisment

Note that there is no space between —p and the . This facility comes handy when we schedule the backup process through scripts. 

Backing up on a remote machine



It does not make much sense to keep a backup of the database on the same machine. Hence we create the backup (pcquest.back) file on a remote machine–Windows or Linux/Unix. Suppose the IP address of the Linux machine running MySQL is 192.168.1.2 and the IP address of the remote machine (Windows/Linux) is 192.168.1.1. The remote machine has a directory named ‘backup’ to which we copy the database backup, namely pcquest.back. Preferably the ‘backup’ directory should be on a different partition. This comes handy if the OS on the remote machine crashes and you need to reinstall it. Since the ‘backup’ folder lies on a different partition, the OS can be reinstalled without formatting this partition. 

Backing up on Windows



Set up the ‘backup’ folder as a Windows share with read and write permissions. Once done, we access this share from Linux using a Samba utility called smbmount. Samba is a package, which contains various utilities to access Windows shares in Linux and vice versa. You need to install the RPMs samba-client-2.0.8-1.7.1.i386.rpm and

samba-common-2.0.8-1.7.1.i386.rpm found in RedHat/RPMS directory of PCQLinux 7.1 CD1 given out in July 2001. 



Next, we mount the Windows share on a local Linux directory named /mnt/backup_share. The following are the commands involved:





mkdir /mnt/backup_ share





mount -t smbfs -o username=shekhar,


password=secret //192.168.1.1/backup /mnt/backup_share







syntax : mount —t smbfs -o username=,password= //





Substitute ‘shekhar’ and ‘secret’ with the username and password required to access the share in your case. Now we create the backup on the mounted share as:





mysqldump —u root 


pcquest > /mnt/backup_share/pcquest.back -p









Advertisment

Backing up on Linux



For this we use NFS (Network File System). Create a directory /backup on the remote Linux machine (192.168.1.1). Install the nfs-utils-0.3.1-5.i386.rpm found in the directory RedHat/RPMS in PCQLinux 7.1 CD2. Open the file ‘exports’ in /etc directory and type in the following lines:

/backup 192.168.1.2( rw,no_root_squash) 






These lines specify to share /backup directory via NFS. It also allocates read/write permissions to the directory. By no_root_squash we specify that the root user connecting from 192.168.1.1 should be given root privileges on this directory. For an in depth explanation of these parameters refer to Share Directories between Linux machines in PCQuest, October 2000. You can also find the article in the PCQuest archives at
www.pcquest.com. Next, issue:






exportfs -a —r





/etc/rc.d/init.d/portmap start





/etc/rc.d/init.d/nfs start










Now on the Linux machine running MySQL i.e. 192.168.1.2, issue the following commands:





mkdir /mnt/backup_ share





/etc/rc.d/init.d/portmap start





mount —o soft 192.168.1.1:/backup /mnt/backup_share










The remote Linux directory is mounted at /mnt/backup_share. Subsequently, we create the database backup as:





mysqldump —u root 


pcquest > /mnt/backup_share/pcquest.back -p









Scheduling backups 



Since database backups should be taken regularly, it would be logical to automate the process. For scheduling we use the popular Cron daemon on Linux. Go through the above sections to setup Windows/Linux share. We put the required commands to take the backup in a Linux-shell script. Then, we set up Cron to run the script regularly. Type in the following in a Linux text editor:





if mount -o soft 192.168.1.1:/backup /mnt/backup_share


then


if mysqldump -u root pcquest > /mnt/backup_share/pcquest.back.new -pscream


then


rm /mnt/backup_sha re/pcquest.back -f


mv /mnt/backup_ share/pcquest.back.new /mnt/backup_share/pcquest.back


else


rm /mnt/backup_ share/pcquest.back.new


fi


umount /mnt/backup_share


fi











Advertisment

If you are taking the backup on a Windows share then replace the mount command (after if) in the first line with the mount command in the section ‘Backing up on Windows’. In the script we have used commands as conditions for ‘if’ statements. The script proceeds only if the command specified with ‘if’ is executed successfully. Note the mysqldump command with the second ‘if’ statement. We first take the backup in a file named pcquest.back.new. If this command succeeds, only then we rename this file to pcquest.back after removing the older backup. This is a precautionary measure to preserve the old backup, in case some error creeps when creating the new backup. 

Save the file as backupdb.sh. You can also find this script in cdrom/Dev Lab/Source directory on this month’s CD. Give executable permission to this script as:






chmod +x backupdb.sh





Now, we need to run this script at least once a day. In PCQLinux 7.1, it’s quite easy to do so. Just copy backupdb.sh to /etc/crond.daily directory. If the Cron daemon is not running start it as:





/etc/rc.d/init.d/crond restart





Henceforth, the script backupdb.sh will run automatically at 4:02 am each day. If you know how to work with Cron, feel free to schedule the execution of the script as per your case.






Recovering from backup



Assuming that you have completely lost the ‘pcquest’ database due to a system crash. First create the database again as:






mysqladmin -u root create pcquest -p





Enter the password for the ‘root’ MySQL user when prompted. Now copy the file pcquest.back from the backup machine to the Linux machine running MySQL. Execute the following command:





mysql -u root pcquest —p < pcquest.back




Shekhar Govindarajan

Advertisment