Protecting Your Data

our data is essential to your Web database application. Storing and/or presenting data are major activities of your Web database application.
You have spent valuable time developing your database, and it contains important information entered by you or by your users. You need to protect it. In this chapter, I show you how.

Controlling Access to Your Data

You need to control access to the information in your database. You need to decide who can see the data and who can change it. Imagine what would happen if your competitors could change the information in your online product catalog or copy your list of customers — you’d be out of business
in no time flat. Clearly, you need to guard your data.

MySQL provides a security system for protecting your data. No one can access the data in your database without an account. Each MySQL account has the following attributes:

A name

A hostname — the machine from which the account can access the
MySQL server

A password

A set of permissions

To access your data, someone must use a valid account name and know the password associated with that account. In addition, that person must be con­ necting from a computer that is permitted to connect to your database via that specific account.

After the user is granted access to the database, what he or she can do to the data depends on what permissions have been set for the account. Each account is either allowed or not allowed to perform an operation in your data­ base, such as SELECT, DELETE, INSERT, CREATE, DROP, and so on. The settings that specify what an account can do are privileges or permissions. You can set up an account with all permissions, no permissions, or anything in between. For instance, for an online product catalog, you want the customer to be able to see the information in the catalog but not be able to change it.

When a user attempts to connect to MySQL and execute a query, MySQL con­
trols access to the data in two stages:

1. Connection verification: MySQL checks the validity of the account name and password and checks whether the connection is coming from a host that is allowed to connect to the MySQL server by using the specified account. If everything checks out, MySQL accepts the connection.

2. Request verification: After MySQL accepts the connection, it checks whether the account has the necessary permissions to execute the spec­ ified query. If it does, MySQL executes the query.

Any query that you send to MySQL can fail either because the connection is rejected in the first step or because the query is not permitted in the second step. An error message is returned to help you identify the source of the problem.

In the following few sections, I describe accounts and permissions in more detail.

Understanding account
names and hostnames

Together, the account name and hostname (the name of the computer that is authorized to connect to the database) identify a unique account. Two accounts with the same name but different hostnames can exist and can have different passwords and permissions. However, you cannot have two
accounts with the same name and the same hostname.

The MySQL server will accept connections from a MySQL account only when it is connecting from hostname. When you build the GRANT or REVOKE query (which I describe later in this chapter), you identify the MySQL account by using both the account name and the hostname in the following format: accountname@hostname (for instance, root@localhost).

The MySQL account name is completely unrelated in any way to the Unix/Linux or Windows user name (also sometimes called the login name). If you’re using an administrative MySQL account named root, it is not related to the Unix/Linux root login name. Changing the MySQL login name does not in any way affect the Unix/Linux or Windows login name — and vice versa.

MySQL account names and hostnames are defined as follows:

An account name can be up to 16 characters long. You can use special characters in account names, such as a space or hyphen (-). However, you cannot use wildcards in the account name.

An account name can be blank. If an account exists in MySQL with
a blank account name, any account name will be valid for that account. A user could use any account name to connect to your database, given that the user is connecting from a hostname that is allowed to connect to the blank account name and uses the correct password, if required. You can use an account with a blank name to allow anonymous users to connect to your database.

The hostname can be a name or an IP address. For example, it can be a name such as thor.mycompany.com or an IP (Internet protocol) address such as 192.163.2.33. The machine on which the MySQL server is installed is localhost.

Wildcards can be used in the hostname. You can use a percent sign (%) as a wildcard; % matches any hostname. If you add an account for george@%, someone using the account named george can connect to the MySQL server from any computer.

The hostname can be blank. A blank hostname is the same as using %
for the hostname.

An account with a blank account name and a blank hostname is possible. Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. An account with a blank name and a percent sign (%) for the hostname is the same thing. It is very unlikely that you would want such an account. Such an account is sometimes installed when MySQL is installed, but it’s given no privileges, so it can’t do anything.

When MySQL is installed, it automatically installs an account with all privi­ leges: root@localhost. This account is installed without a password. Anyone who is logged in to the computer on which MySQL is installed can access MySQL and do anything to it by using the account named root. (Of course, root is a well-known account name, so this account is not very secure. If you’re the MySQL administrator, you should add a password to this account immediately.)

On some operating systems, additional accounts besides root@localhost are automatically installed. For instance, on Windows, an account called root@% might be installed with no password protection. This root account with all privileges can be used by anyone from any machine. You should remove this account immediately or, at the very least, give it a password.

Finding out about passwords

A password is set up for every account. If no password is provided for the account, the password is blank, which means that no password is required. MySQL doesn’t have any limit for the length of a password, but sometimes other software on your system limits the length to eight characters. If so, any characters after eight are dropped.

For extra security, MySQL encrypts passwords before it stores them. That means passwords are not stored in the recognizable characters that you entered. This security measure ensures that no one can look at the stored passwords and see what they are.

Unfortunately, some bad people out there might try to access your data by guessing your password. They use software that tries to connect rapidly in succession using different passwords — a practice called cracking. The fol­ lowing are some recommendations for choosing a password that is as diffi­ cult to crack as possible:

Use six to eight characters.

Include one or more of each of the following — uppercase letter, lower­
case letter, number, and punctuation mark.

Do not use your account name or any variation of your account name.

Do not include any word that’s in a dictionary.

Do not include a name.

Do not use a phone number or a date.

A good password is hard to guess, does not include any word in any dictio­ nary (including foreign language dictionaries), and is easy to remember. If it’s too hard to remember, you might need to write it down, which defeats the

purpose of having a password. One way to create a good password is to use the first characters of a favorite phrase. For instance, you could use the phrase “All for one! One for all!” to make this password:

Afo!Ofa!

This password doesn’t include any numbers, but you can fix that by using the numeral 4 instead of the letter f. Then your password is

A4o!O4a!

Or you could use the number 1 instead of the letter o to represent one. Then the password is

A41!14a!

This password is definitely hard to guess. Other ways to incorporate num­ bers into your passwords include substituting 1 (one) for l (ell) or substitut­ ing 0 (zero) for the letter o.

Taking a look at account permissions

Account permissions are used by MySQL to specify who can do what. Anyone using a valid account can connect to the MySQL server, but he or she can
only do those things that are allowed by the permissions for the account. For example, an account might be set up so that users can select data but cannot insert data nor update data.

Permissions can be granted for particular databases, tables, or columns. For instance, an account can be set up that allows the user to select data from all the tables in the database but insert data into only one table and update on only a single column in a specific table.

Permissions are added by using the GRANT query and removed by using the REVOKE query. The GRANT or REVOKE query must be sent using an account that has permission to execute GRANT or REVOKE statements in the database. If you attempt to send a GRANT query or a REVOKE query by using an account without grant permission, you get an error message. For instance, if you try to grant permission to use a select command, and you send the query using an account that does not have permission to grant permissions, you might see the following error message:

grant command denied

Permissions can be granted or removed individually or all at once. Table 5-1 lists some permissions that you might want to assign or remove.

Table 5-1 MySQL Account Permissions

Permission Description

ALL All permissions

ALTER Can alter the structure of tables CREATE Can create new databases or tables DELETE Can delete rows in tables
DROP Can drop databases or tables

FILE Can read and write files on the server

GRANT Can change the permissions on a MySQL account

INSERT Can insert new rows into tables SELECT Can read data from tables SHUTDOWN Can shut down the MySQL server UPDATE Can change data in a table
USAGE No permissions at all

Granting ALL is not a good idea because it includes permissions for adminis­ trative operations, such as shutting down the MySQL server. You are unlikely to want anyone other than yourself to have such sweeping privileges.

Setting Up MySQL Accounts

When creating a new account, you specify the password, the name(s) of the computer(s) allowed to access the database using this account, and the per­ missions; however, you can change these at any time. All the account infor­ mation is stored in a database named mysql that is automatically created when MySQL is installed. To add a new account or change any account infor­ mation, you must use an account that has the proper permissions on the mysql database.

You need at least one account in order to access the MySQL server. When MySQL is installed, it automatically sets up some accounts, including an account called root that has all permissions. If you have MySQL access through a company Web site or a Web hosting company, the MySQL adminis­ trator for the company should give you the account; the account is probably not named root, and it might or might not have all permissions.

Chapter 5: Protecting Your Data 99

The MySQL security database

When MySQL is installed, it automatically cre­ ates a database called mysql. All the informa­ tion used to protect your data is stored in this database, including account names, host- names, passwords, and permissions.

Permissions are stored in columns. The format of each column name is permission_priv where permission is one of the permissions shown in Table 5-1. For instance, the column containing ALTER permissions is named alter_priv. The value in each permission column is Y or N, meaning yes or no. So, for instance, in the user table (described in the fol­ lowing list), there would be a row for an account and a column for alter_priv. If the account field for alter_priv contains Y, the account can be used to execute an ALTER query. If alter_priv contains N, the account doesn’t have permission to execute an ALTER query.

The mysql database contains the following five tables that store permissions:

user table: This table stores permissions that apply to all the databases and tables. It contains a row for each valid account with user name, hostname, and password. The MySQL server will reject a connection for an account that does not exist in this table.

db table: This table stores permissions that apply to a particular database. It contains a row for the database, which gives permis­ sions to an account name and hostname. The account must exist in the user table for the permissions to be granted. Permissions that are given in the user table overrule permis­ sions in this table. For instance, if the user table has a row for the account designer that gives INSERT privileges, designer can insert

into all the databases. If a row in the db table shows N for INSERT for the designer account in the PetCatalog database, the user table overrules it, and designer can insert in the PetCatalog database.

host table: This table controls access to a database depending on the host. The host table works with the db table. If a row in the db table has an empty field for the host, MySQL checks the host table to see whether the db has a row there. In this way, you can allow access to a db from some hosts but not from others. For instance, say you have two databases: db1 and db2. The db1 database has information that is very sensitive, so you only want certain people to see it. The db2 database has information that you want everyone to see. If you have a row in the db table for db1 with a blank host field, you can have two rows for db1 in the host table. One row can give all per­ missions to users connecting from a spe­ cific host, whereas another row can deny privileges to users connecting from any other host.

tables_priv table: This table stores per­
missions that apply to specific tables.

columns_priv table: This table stores permissions that apply to specific columns.

You can see and change the tables in mysql directly if you’re using an account that has the necessary permissions. You can use SQL queries such as SELECT, INSERT, UPDATE, and others. If you’re accessing MySQL through your employer, a client, or a Web hosting com­ pany, it is unlikely that you will be given an account that has the necessary permissions.

In the rest of this section, I describe how to add and delete accounts and change passwords and permissions for accounts. If you have an account that you received from your company IT department or from a Web hosting com­ pany, you might receive an error when you try to send any or some of the GRANT or REVOKE queries described. If your account is restricted from per­ forming any of the necessary queries, you need to request an account with more permissions or ask the MySQL administrator to add a new account or make the changes that you need.

Identifying what accounts currently exist

To see what accounts currently exist for your database, you need an account that has the necessary permissions. Try to execute the following query on a database named mysql:

SELECT * FROM user

You should get a list of all the accounts. However, if you’re accessing MySQL through your company or a Web hosting company, you probably don’t have the necessary permissions. In that case, you might get an error message
like this:

No Database Selected

This message means that your account is not allowed to select the mysql database. Or you might get an error message saying that you don’t have SELECT permission. Even though this message is annoying, it’s good in the sense that it’s a sign the company has good security measures in place. However, it’s bad in the sense that you can’t see what privileges your account has. You must ask your MySQL administrator or try to figure it out yourself
by trying queries and seeing whether you’re allowed to execute them.

Adding new accounts and changing permissions

The preferred way to access MySQL from PHP is to set up an account specifi­ cally for this purpose with only the permissions that are needed. In this sec­ tion, I describe how to add new accounts and change permissions. If you’re using an account given to you by a company IT department or a Web hosting company, it might or might not have all the permissions needed to create a new account. If it doesn’t, you won’t be able to successfully execute the GRANT query to add an account, and you’ll have to request a second account to use with PHP.

If you need to request a second account, get an account with restricted per­ mission (if at all possible) because your Web database application will be more secure if the account used in your PHP programs doesn’t have more privileges than are necessary.

You use the same GRANT query to set up a new account or to change the password or add permissions for an existing account. If the account already exists, the GRANT query changes the password or adds permissions. If the account doesn’t yet exist, the GRANT query adds a new account.

Here is the general format for a GRANT query:

GRANT permission (columns) ON tablename
TO accountname@hostname IDENTIFIED BY ‘password’

You can use this GRANT query to create a new account or change an existing account. You need to fill in the following information:

permission (columns): You must list at least one permission. You can limit each permission to one or more columns by listing the column name in parentheses following the permission. If no column name is listed, the permission is granted on all columns in the table(s). You can list as many permission/columns as needed, separated by commas. The possible permissions are listed in Table 5-1. For instance, a GRANT query might start with this:

GRANT select (firstName,lastName), update, insert (birthdate) ...

tablename: This indicates which tables the permission is granted on. At least one table is required. You can list several tables, separated by commas. The possible values for tablename are

• tablename: The entire table named tablename in the current data­ base. You can use an asterisk (*) to mean all tables in the current database. If you use an asterisk and no current database is
selected, the privilege will be granted to all tables on all databases.

• databasename.tablename: The entire table named tablename in databasename. You can use an asterisk (*) for either the database name or the table name to mean all. Using *.* grants the permis­ sion on all tables in all databases.

accountname@hostname: If the account already exists, it is given the indicated permissions. If the account doesn’t exist, it’s added. The account is identified by the accountname and hostname as a pair. If an account exists with the specified account name but a different host- name, the existing account is not changed; a new one is created.

password: This is the password that you’re adding or changing. A pass­ word is not required. If you don’t want to add or change a password for this account, leave out the entire phrase IDENTIFIED BY ‘password’.

The GRANT query to add a new account for use in the PHP programs for the
PetCatalog database might be

GRANT select ON PetCatalog.* TO phpuser@localhost
IDENTIFIED BY ‘A41!14a!’

Adding and changing passwords

You can add or change passwords by using the GRANT query. You can include a password requirement when you add a new account, as I describe in the preceding section. If an account already exists, you can change its password by using the following GRANT query:

GRANT permission ON * TO accountname@hostname
IDENTIFIED BY ‘password’

You need to fill in the following information:

permission: You must list at least one permission in a GRANT query. If the permission has already been granted, it is not changed.

accountname@hostname: It the account already exists, it’s given the indi­ cated permission and password. If the account doesn’t exist, the account is added. The account is identified by the accountname and hostname as a pair. If an account exists with the specified account name but a different hostname, the account is not changed; a new one is created.

password: The password in the GRANT query replaces the existing pass­
word. If you supply an empty password using

IDENTIFIED BY ‘’

the existing password is replaced with a blank, leaving the account with no password. For tips on choosing a good password, check out the “Finding out about passwords” section, earlier in the chapter.

Removing permissions

To remove permissions, use the REVOKE query. The general format is

REVOKE permission (columns) ON tablename
FROM accountname@hostname

You need to fill in the following information:

permission (columns): You must list at least one permission. You can remove each permission from one or more columns by listing the column name in parentheses following the permission. If no column name is listed, the permission is removed from all columns in the table(s). You can list as many permission/columns as needed, separated by commas. The possible permissions are listed in Table 5-1. For instance, a REVOKE query might start like this:

REVOKE select (firstName,lastName), update, insert (birthdate) ...

tablename: Indicate which tables the permission is being removed from.
At least one table is required. You can list several tables, separated by commas. The possible values for tablename are

• tablename: The entire table named tablename in the current data­ base. You can use an asterisk (*) to mean all tables. If you use an asterisk when no current database is selected, the privilege will be revoked on all tables on all databases.

• databasename.tablename: The entire table named tablename in databasename. You can use an asterisk (*) for either the database name or the table name to mean all. Using *.* revokes the permis­ sion on all tables in all databases.

accountname@hostname: The account is identified by the accountname and hostname as a pair. If an account exists with the specified account name but a different hostname, the REVOKE query will fail, and you will receive an error message.

Removing accounts

Removing an account is usually not necessary. If you created an account with permissions that you don’t want, just change the permissions. If you don’t want to use an account, remove all the permissions so that the account can’t do anything. To remove all the permissions for an account, use a REVOKE query with the following syntax:

REVOKE all ON *.* FROM accountname@hostname

To actually be able to remove an account, you need an account with the nec­ essary permissions on the mysql database. You need to use a DELETE query on the user table in the mysql database. For more information on the struc­ ture of the mysql security database, see “The MySQL security database” side­ bar, elsewhere in this chapter. Be careful using a DELETE query because with incorrect format, it can remove the wrong account or even all the accounts. See the discussion of the DELETE command at the end of Chapter 4.

Backing Up Your Data

You need to have at least one copy of your valuable database. Disasters
occur rarely, but they do occur. The computer where your database is stored can break down and lose your data, the computer file can become corrupted, the building can burn down, and so on. Backup copies of your database
guard against data loss from such disasters.

You should have at least one backup copy of your database, stored in a loca­ tion that is separate from the copy that is currently in use. More than one copy — perhaps as many as three — is usually a good idea.

Store one copy in a handy location, perhaps even on the same computer, to quickly replace a working database that has been damaged.

Store a second copy on another computer in case the computer breaks down, and the first backup copy isn’t available.

Store a third copy in a completely different physical location, for that remote chance that the building burns down. If the second backup copy is stored via network on a computer at another physical location, this third copy isn’t needed.

If you don’t have access to a computer offsite where you can back up your database, you can copy your backup to a portable medium, such as a tape or a CD, and store it offsite. Certain companies will store your
computer media at their location for a fee, or you can just put the media in your pocket and take it home.

If you use MySQL on someone else’s computer, such as the computer of your employer or a Web hosting company, the people who provide your access are responsible for backups. They should have automated procedures in place that make backups of your database. A good question to ask when evaluating a Web hosting company is what its backup procedures are. You want to know how often backup copies are made and where they are stored. If you aren’t confident that your data is safe, you can discuss changes or additions to the backup procedures.

If you are the MySQL administrator, you are responsible for making backups. MySQL provides a program called mysqldump that you can use to make backup copies; mysqldump creates a text file that contains all the SQL statements needed to re-create your entire database. The file contains the CREATE state­ ments for each table and INSERT statements for each row of data in the tables. You can restore your database by executing the set of MySQL statements. You can restore it in its current location, or you can restore it on another computer if necessary.

Follow these steps to make a backup copy of your database in
Linux/Unix/Mac:

1. Change to the bin subdirectory in the directory where MySQL is installed.

For instance, type cd /usr/local/mysql/bin.

2. Type the following:

mysqldump --user=accountname --password=password databasename >path/backupfilename

where

• accountname is the name of the MySQL account that you’re using to back up the database.

• password is the password for the account.

• databasename is the name of the database that you want to back up.

• path/backupfilename is the path to the directory where you want to store the backups and the name of the file that the SQL output will be stored in.

The account that you use needs to have select permission. If the account doesn’t require a password, you can leave out the entire pass­ word option.

You can type the command on one line, without pressing Enter. Or you can type a backslash (\), press Enter, and then continue the command on another line.

For example, to back up the PetCatalog database, the command might be

mysqldump --user=root --password=bigsecret PetCatalog \
>/usr/local/mysql/backups/PetCatalogBackup

Note: The Linux/Unix account that you are logged into must have per­
mission 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➪Programs➪MS-DOS prompt.

2. Change to the bin subdirectory in the directory where MySQL is installed.

For instance, type cd c:\mysql\bin.

3. Type the following:

mysqldump.exe --user=accountname --password=password databasename >path\backupfilename

where

• accountname is the name of the MySQL account that you’re using to back up the database.

• password is the password for the account.

• databasename is the name of the database that you want to back up.

• path\backupfilename is the path to the directory where you want to store the backups and the name of the file that the SQL output will be stored in.

The account that you use needs to have select permission. If the account does not require a password, you can leave out the entire pass­ word option.

You must type the mysqldump command on one line without pressing
Enter.

For example, to back up the PetCatalog database, the command might be

mysqldump.exe --user=root PetCatalog >PetCatalogBackup

Backups should be made at certain times — at least once per day. If your database changes frequently, you might want to back up more often. For example, you might want to back up to the backup directory hourly but back up to another computer once a day.

Restoring Your Data

At some point, one of your database tables might become damaged and unus­ able. It’s unusual, but it happens. For instance, a hardware problem or an unex­ pected shutdown of the computer can cause corrupted tables. Sometimes an anomaly in the data that confuses MySQL can cause corrupt tables. In some cases, a corrupt table can cause your MySQL server to shut down.

Here is a typical error message that signals a corrupted table:

Incorrect key file for table: ‘tablename’.

In some cases, you can repair the corrupted data table(s) by using a repair utility provided with MySQL. If the repair utility doesn’t restore the corrupted table(s) to working order, all is not lost — you can replace the corrupted table(s) with the data stored in a backup copy. In some cases, the database might be lost completely. For instance, if the computer where your database resides breaks down and can’t be fixed, your current database is lost, but
your data isn’t gone forever. You can replace the broken computer with a new computer and restore your database from a backup copy.

Repairing tables

Often a damaged database can be fixed. MySQL provides a utility called myisamchk that repairs tables. If you’re accessing MySQL on your employer’s or client’s computer or through a Web hosting company, you need to contact the MySQL administrator to run myisamchk for you.

If you are the MySQL administrator, you can run myisamchk yourself. To use
myisamchk on Linux/Unix/Mac, follow these steps:

1. Change to the bin subdirectory in the directory where MySQL is
installed.

For instance, type cd /usr/local/mysql/bin.

2. Stop the MySQL server by typing this query:

mysqladmin -u accountname -p shutdown

where -u accountname specifies the name of an account to be used to connect to MySQL.

The account must have shutdown permission. If the account does not require a password, leave out the -p. If you include -p, you will be asked for your password.

3. Type the following:

myisamchk -r path/databasename/tablename.MYI

Include the complete path to your data directory, followed by the data­ base name, the table name, and .MYI. You can use an asterisk (*) as a wildcard. For instance, to repair all the tables in the PetCatalog data­ base, you might type

myisamchk -r ../data/PetCatalog/*.MYI

The -r option is the recover option. After you type the statement, you see output on the screen showing which tables are being checked.

4. Start the MySQL server by typing the following:

mysqladmin -u accountname -p start

To use myisamchk on Windows, follow these steps:

1. Open a command prompt window.
For instance, choose Start➪Programs➪MS-DOS prompt.

2. Change to the bin subdirectory in the directory where MySQL is
installed.

For instance, type cd c:\mysql\bin.

3. Stop the MySQL server by typing

mysqladmin -u accountname -p shutdown

where accountname is the name of an account with shutdown permis­ sion. If the account does not require a password, leave out the -p. If you include -p, you will be prompted for your password.

4. Type the following:

myisamchk -r path/databasename/tablename.MYI

Include the complete path to your data directory, followed by the data­ base name, the table name, and .MYI. You can use an asterisk (*) as a wildcard. The -r option is the recover option. For instance, to repair all the tables in the PetCatalog database, you might type

myisamchk -r ..\data\PetCatalog\*.MYI

After you enter this statement, you see output on the screen showing which tables are being checked. Wait for myisamchk to finish running.

5. Start your MySQL server by typing the following;

mysqladmin -u accountname -p start

If your table still isn’t working after you run this command, try running the myisamchk utility again by using the -o option instead of the -r option. The -o option is an older repair process that is much slower than the -r option, but it handles some cases that the -r option can’t handle.

Restoring from a backup copy

If repairing your data doesn’t return the database to working condition or if your database is completely unavailable, such as in the case of a computer fail­ ure, you can replace your current database table(s) with the database stored
in a backup copy. The backup copy contains a snapshot of the data as it was when the copy was made. Any changes to the database since the backup copy was made are not included; you have to re-create those changes manually.

Again, if you access MySQL through an IT department or through a Web hosting company, you need to ask the MySQL administrator to restore your database from a backup. If you’re the MySQL administrator, you can restore it yourself.

As I describe in Chapter 4, you build a database by creating the database and then adding tables to the database. The backup that you create with the mysqldump utility is a file that contains all the SQL statements necessary to rebuild all the tables, but it does not contain the statements needed to create the database.

Your database might not exist at all, or it could exist with one or more cor­ rupted tables. You can restore the entire database or any single table. Follow these steps to restore a single table:

1. If the table currently exists, delete the table with the following SQL
query:

DROP TABLE tablename

where tablename is the table that you want to delete.

2. Point your browser at mysql_send.php.

For a description of mysql_send.php, see Chapter 4.

3. Copy the CREATE query for the table from the backup file into the
form in the browser window.
For instance, choose Edit➪Copy and Edit➪Paste.

4. Type the name of the database in which you are restoring the table.

The form shows where to type the database name.

5. Click Submit.

A new Web page shows the results of the query.

6. Click New Query.

7. Copy an INSERT query for the table from the backup file into the form in the browser window.
For instance, choose Edit➪Copy and Edit➪Paste.

8. Type the name of the database in which you are restoring the table.

The form shows where to type the database name.

9. Click Submit.

A new Web page shows the results of the query.

10. Click New Query.

11. Repeat Steps 7–10 until all the INSERT queries from the backup file
have been sent.

If you have so many INSERT queries for the table that sending them one by one would take forever — or if there are just a lot of tables — you can send all the queries in the backup file at once by following these steps:

1. If any of the tables in the backup file currently exist, delete them with the following SQL query:

DROP TABLE tablename

where tablename is the table that you want to delete.

2. Change to the bin subdirectory in the directory where MySQL is
installed.

On Linux/Unix/Mac:

Type a cd command to change to the correct directory (for instance, type cd /usr/local/mysql/bin).

On Windows:

a. Open a command prompt window.
For instance, choose Start➪Programs➪Accessories➪Command
Prompt.

b. Type a cd command to change to the correct directory (for instance, type cd c:\mysql\bin).

3. Type the command that sends the SQL queries in the backup file.

On Linux/Unix/Mac:

Type

mysql -u accountname -p databasename < path/backupfilename

where accountname is an account that has create permission. If the account doesn’t require a password, leave out the -p. If you use the -p, you will be asked for the password. databasename is
the existing database in which you want to build all the tables. Use the entire path and filename for the backup file. For instance, a command to restore the PetCatalog database might be

mysql -u root -p PetCatalog < /usr/backupfiles/PetCatalog.bak

On Windows:

Type

mysql -u accountname -p databasename < path\backupname

where accountname is an account that has create permission. If the account doesn’t require a password, leave out the -p. If you use the -p, you will be asked for the password. databasename is

the existing database in which you want to build all the tables. Use the entire path and filename for the backup file. For instance, a command to restore the PetCatalog database might be

mysql -u root -p PetCatalog < c:\mysql\bak\PetCatalog.bak

The tables might take a short time to restore. Wait for the command to finish. If a problem occurs, an error message is displayed. If no problems occur, you see no output. When the command is finished, the prompt appears.

To restore only selected tables from the backup file, make a file that contains only the queries for the selected tables that you want to restore. Then follow Steps 1–3 in the preceding list. In Step 3, type the path name or filename for the file with the subset of queries that you want instead of the full backup file.

If the database is not there at all, you need to create it before you can use the queries in the backup file to rebuild all the tables. To restore the database when nothing exists, use the following steps:

1. Add the following two lines to the top of the backup file:

CREATE DATABASE databasename;
use databasename;

where databasename is the name of the database that you want to restore. For instance, the commands for the PetCatalog database are

CREATE DATABASE PetCatalog;
use PetCatalog;

Note: Make sure that you add a semicolon (;) at the end of each line.

2. Change to the bin subdirectory in the directory where MySQL is installed.

On Linux/Unix/Mac:

Type a cd command to change to the correct directory (for instance, type cd /usr/local/mysql/bin).

On Windows:

a. Open a command prompt window.
For instance, choose Start➪Programs➪Accessories➪Command
Prompt.

b. Type a cd command to change to the correct directory (for instance, type cd c:\mysql\bin).

3. Type the command that sends the SQL queries in the backup file.

On Linux/Unix/Mac:

Type this:

mysql -u accountname -p < path/backupfilename

where accountname is an account that has create permission. If the account doesn’t require a password, leave out the -p. If you use the -p, you will be asked for the password. Use the entire path and filename for the backup file. For instance, a command to restore the database might be

mysql -u root -p < /usr/backupfiles/PetCatalog.bak

On Windows:

Type this:

mysql -u accountname -p < path\backupfilename

where accountname is an account that has create permission. If the account doesn’t require a password, leave out the -p. If you use the -p, you will be asked for the password. Use the entire path and filename for the backup file. For instance, a command to restore the PetCatalog database might be

mysql -u root -p < c:\mysql\bak\PetCatalog.bak

The tables might take a short time to restore. Wait for the command to finish. If a problem occurs, an error message is displayed. If no problems occur, you see no output. When the command is finished, the prompt appears.

Your database is now restored with all the data that was in it at the time the copy was made. If the data has changed since the copy was made, the changes are lost. For instance, if more data was added after the backup copy was made, the new data is not restored. If you know the changes that were made, you can make them manually in the restored database.