MySQL I : Backing Up

1. Techniques described are the same on Linux/UNIX, Windows, and Mac OS X.
2. One way to find out where your MySQL data files are stored is to look at the MySQL variables. CD to your “bin” directory, and type: mysqladmin –u root –p variables

On Windows you can also launch “Winmysqladmin.exe” and click the “Variables” tab.
3. cd to your data directory. On Windows: cd c:\mysql\data or open Windows Explorer to look at this directory.
4. Each database has a directory, for instance you have a directory named: c:\mysql\data\accounting
5. cd into your “accounting”directory: cd c:\mysql\data\accounting. Type: dir
6. Notice that there are three files for each table that you have created. For example, for the clients table:
clients.frm, clients.myd, clients.myi
7. To backup your data, first stop MySQL (very important lest you corrupt your files)!
Windows: “NET STOP mysql”, stop from the Services Manager, or use Winmysqladmin.exe to stop the service.
Redhat Linux/UNIX: ./etc/rc.d/init.d/mysqld stop
8. Copy the “c:\mysql\data” directory to your archive. For example, on Windows, open Windows Explorer and copy the data directory. Paste it into your “My Documents\mysql_excercises\archives” folder, and rename it with the current date. Then, restart MySQL: NET START mysql
9. On Redhat Linux this procedure might be:
./etc/rc.d/init.d/mysqld stop
cd /usr/local/mysql
tar –cvf data.tar data
mv data.tar \home\username\archive\.

More fancy backups, with date incrementing, can be created using shell scripting like Perl or Python. Before you write one yourself, search the Internet, and you will probably find one you can modify. See the example in your scripts directory named: daily_backup_and_scp.pl.
10. Automate timed backups using Windows “Task Manager” or UNIX “Cron”.
11. Restart MySQL.
12. You can use mysql dump to back up your database structure, data, or both. Create a directory named “c:\temp” if it does not already exist. From the “c:\mysql\bin” directory, type:
mysqldump -u root -p -T c:\temp accounting

Then look in your “c:\temp” directory for the dump files. Table structures are saved with the “.sql” file extension. Data is stored with the “.txt” file extension. Open these with any text editor.
13. There are many more options for the MySQLDump utility. Check the manual!
14. You might want to run MySQLDump daily using Windows “Task Manager” or UNIX “Cron”.