[ Pobierz całość w formacie PDF ]
.&' Store a third copy in a different physical location to prepare for theremote chance that the building burns down.If you store the secondbackup copy on a computer at another physical location, you don t needthis third copy.If you don t have access to a computer offsite on which you can back upyour database, you can copy your backup to a portable medium, such as aCD or DVD, and store it offsite.Certain companies will store your computermedia at their location for a fee, or you can just put the media in your pocketand take it home.If you use MySQL on someone else s computer, such as the computer of aWeb hosting company, the people who provide your access are responsiblefor backups.They should have automated procedures in place that makebackups of your database.When evaluating a Web hosting company, askabout their backup procedures.You want to know how often backup copiesare made and where they re stored.If you re not confident that your data issafe, you can discuss changes or additions to the backup procedures.AdministeringMySQL 17_167779 bk03ch02.qxp 12/17/07 8:32 PM Page 286286 Backing Up Your DatabaseIf you re the MySQL administrator, you re responsible for making backups.Even if you re using MySQL on someone else s computer, you might want tomake your own backup copy, just to be safe.Make backups at certain times at least once per day.If your databasechanges frequently, you might want to back up more often.For example, youmight want to back up to the backup directory hourly but back up toanother computer once a day.You can back up your MySQL database by using a utility program calledmysqldump, provided by MySQL, or by using features offered byphpMyAdmin.Backing up a database with mysqldumpMySQL provides a program called mysqldumpthat you can use to make backupcopies.The mysqldumpprogram creates a text file that contains all the SQLstatements you need to re-create your entire database.The file contains theCREATEstatements for each table and INSERTstatements for each row ofdata in the tables.You can restore your database, either to its current locationor on another computer, by executing this set of MySQL statements.Follow these steps to make a backup copy of your database in Linux, in Unix,or on a Mac:1.Change to the binsubdirectory in the directory in which MySQL isinstalled.For instance, type cd /usr/local/mysql/bin.2.Type the following:mysqldump --user=accountname --password=passworddatabasename >path/backupfilenameIn the preceding code, make the following substitutions:" accountname: Replace with the name of the MySQL account thatyou re using to back up the database." password: Use the password for the account." databasename: Use the name of the database that you want toback up." path/backupfilename: Replace pathwith the directory in whichyou want to store the backups and backupfilenamewith the nameof the file in which you want to store the SQL output.The account that you use needs to have SELECTprivilege.If theaccount doesn t require a password, you can leave out the entirepassword option. 17_167779 bk03ch02.qxp 12/17/07 8:32 PM Page 287287Backing Up Your DatabaseYou can type the command on one line without pressing Enter.Or youcan type a backslash (\), press Enter, and continue the command onanother line.For example, to back up the PetCatalogdatabase, you might use thecommandmysqldump --user=root --password=secret PetCatalog \>/usr/local/mysql/backups/PetCatalogBackupNote: With Linux or Unix, the account that you re logged into must haveprivilege to write a file into the backup directory.To make a backup copy of your database in Windows, follow these steps:1.Open a command prompt window.For instance, choose Startª'All Programsª'Accessoriesª'Command Prompt.2.Change to the binsubdirectory in the directory where MySQL isinstalled.For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bininto the command prompt.Book III3.Type the following:Chapter 2mysqldump --user=accountname --password=passworddatabasename >path\backupfilenameIn the preceding code, make the following substitutions:" accountname: Enter the name of the MySQL account that you reusing to back up the database.The account that you use needs to have SELECTprivilege.If theaccount doesn t require a password, you can leave out the entirepassword option." password: Use the password for the account." databasename: Replace with the name of the database that youwant to back up." path\backupfilename: Replace pathwith the directory in whichyou want to store the backups and use the name of the file inwhich you want to store the SQL output in place of backupfilename.You must type the mysqldumpcommand on one line without pressingEnter
[ Pobierz całość w formacie PDF ]