PHP, MySQL, JavaScript & HTML5 All-in-One For Dummies (2013)

Book V: MySQL

Chapter 2: Administering MySQL

In This Chapter

Administering MySQL

Establishing and controlling access to data

Creating and managing accounts

Backing up and restoring databases

Getting the newest version of MySQL

As discussed previously, MySQL is database management software. It manages databases that contain the information you need for the dynamic website that you are building. Your goal is to store data in a database or retrieve data from the database. You can store and retrieve data directly (see Chapters 3 and 4 of this minibook) or store and retrieve data from PHP scripts (see Chapter 5 of this minibook). In addition, a MySQL administrator is required to ensure that MySQL performs its work correctly and efficiently.

We describe MySQL administration in this chapter. In the first few sections of this chapter, we give you the preliminary information you need to know about MySQL administration and how you can control access to your data with account names, hostnames, and passwords. Later, we give you specific information on how to add accounts and change passwords and privileges. Backing up and restoring the database are also important administrative tasks, and we tell you how to do that in this chapter as well. Finally, as a MySQL administrator, you’ll also need to make sure that you’re using the latest version of MySQL, and we discuss that in the final section of this chapter.

Understanding the Administrator Responsibilities

Administering MySQL encompasses the tasks required to ensure that MySQL can perform its data management duties in an efficient and secure manner.

You might be responsible for some or all of the administrative tasks, depending on how you access MySQL. If you’re using MySQL on a web hosting company’s computer, the hosting company performs most or all of the administrative tasks. However, if you’re using MySQL on your local computer, you’re the administrator, entirely responsible for the administration of MySQL.

The duties of the administrator include the following:

Install MySQL. Described in Book I, Chapter 4. If MySQL is running on a web hosting computer, you’re not responsible for installation.

Start and shut down the MySQL server. Described in Book I, Chapter 4. If MySQL is running on a web hosting computer, you don’t start or stop the server.

Create and maintain MySQL user accounts. No one can access the data in your database without an account. Accounts need to be installed and removed, passwords added or removed, and privileges assigned to or removed from accounts. We describe administering user accounts in the section Setting Up MySQL Accounts, later in this chapter.

If you’re using MySQL at a web hosting company, you might or might not be allowed to create or alter MySQL accounts. You might be limited to one account with defined privileges.

Back up data. You need to keep backup copies of your data in case the data is lost or damaged. If you’re using MySQL at a web hosting company, you need to check with that company regarding its backup procedures. You might still want to keep your own backup, just in case the web hosting company’s backup procedures fail. You can read about backup databases in the section Backing Up Your Database, later in this chapter.

Update MySQL. Install new MySQL releases when needed. If MySQL is running on a web hosting computer, you’re not responsible for updates. We talk about upgrading MySQL in the section Upgrading MySQL, later in this chapter.

Default Access to Your Data

When MySQL is installed, a default MySQL account named root is installed. Sometimes, this account is installed without a password. If you configured MySQL on Windows with the Configuration Wizard (as described in Book I, Chapter 4), you set a password during the configuration procedure. In addition, you might have set up an anonymous account with no account name and no password. If you're accessing MySQL through a web hosting company, the company provides you with the account name and password to use.

warning_bomb.eps In general, you shouldn't use the account root without a password. If your installation set up a root account without a password, add a password right away. The root account is set up with all privileges. You use this account for the administration of your MySQL databases. You don't need an account with all privileges to access your MySQL databases, or to add and retrieve data. Therefore, in most cases, you want to create an account with fewer privileges that you use to access the data from your PHP scripts, and we tell you how to do that in this chapter.

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.

Fortunately, 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:

An account name

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

A password

A set of privileges

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 connecting from a computer that’s 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 privileges have been set for the account. Each account is either allowed or not allowed to perform an operation in your database, such as SELECT, DELETE, INSERT, CREATE, or DROP. (Table 2-1, later in this chapter, explains those privileges.) The settings that specify what an account can do are privileges. You can set up an account with all privileges, no privileges, 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 change that information.

When a user attempts to connect to MySQL and execute a statement, MySQL controls 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’s 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 privileges to execute the specified statement. If it does, MySQL executes the statement.

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

In the following sections, we describe accounts and privileges in detail.

Account names and hostnames

Together, the account name and hostname (the name of the computer that’s 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 privileges. However, you can’t have two accounts with the same name and the same hostname.

The MySQL server accepts connections from a MySQL account only when that account is connecting from hostname. When you build the GRANT or REVOKE statement (which we describe in the section Changing privileges, 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).

remember.eps The MySQL account name is completely unrelated in any way to the Unix, Linux, or Windows username (also sometimes called the login name). If you're using an administrative MySQL account named root, that account is not related to the Unix or Linux root login name. Changing the MySQL account name doesn't affect the Unix, Linux, or Windows login name — and vice versa.

MySQL account names and hostnames have the following characteristics:

An account name can be up to 16 characters long. You can use special characters in account names, such as a space or a hyphen (-). However, you can't 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 is valid for that account. A user can use any account name to connect to your database if the user is connecting from a hostname that’s allowed to connect to the blank account name and uses the correct password (if a password is 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, the hostname 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.

The hostname can contain wildcards. You can use a percent sign (%) as a wildcard; % matches any hostname. If you add an account for george@%, someone who uses the account named george can connect to the MySQL server from any computer.

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

You can create an account with both a blank account name and a blank hostname (or a percent sign — % — for the hostname). Such an account would allow anyone to connect to the MySQL server by using any account name from any computer. But you probably don't want such an account. This kind of an account is sometimes installed when MySQL is installed, but it's given no privileges, so it can't do anything.

warning_bomb.eps When MySQL is installed, it automatically installs an account with all privileges: root@localhost. Depending on your operating system, this account might be installed without a password. Anyone who's 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 isn't secure. If you're the MySQL administrator, add a password to this account immediately.)

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 aren’t stored in the recognizable characters that you enter. This security measure ensures that no one can simply look at the stored passwords and understand 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 with different passwords — a practice called a brute force attack.

In any event, your MySQL server shouldn’t be exposed directly to the Internet, so an attacker would need to get access to the MySQL server first in order to try a brute force attack.

Account privileges

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

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

Table 2-1 lists some privileges that you might want to assign or remove. Other privileges are available, but they're less commonly used. You can find a complete list of privileges in the MySQL online manual at http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html.

Table 2-1 MySQL Account Privileges

*Privilege*	*Description*
ALL	All privileges
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 privileges 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 privileges

warning_bomb.eps You probably don't want to grant ALL because it includes privileges for administrative operations, such as shutting down the MySQL server — privileges that you don't want anyone other than yourself to have.

Setting Up MySQL Accounts

An account is identified by the account name and the name of the computer allowed to access MySQL from this account. When you create a new account, you specify it as accountname@hostname. You can specify a password when you create an account, or you can add a password later. You can also set up privileges when you create an account or add privileges later.

The MySQL security database

When MySQL is installed, it automatically creates a database called mysql. All the information used to protect your data is stored in this database, including account names, hostnames, passwords, and privileges.

Privileges are stored in columns. The format of each column name is privilege_priv, in which privilege is a specific account privilege. For instance, the column containing ALTER privileges is named alter_priv. The value in each privilege column is Y or N, meaning yes or no. So, for instance, in the user table (described in the following 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 statement. If alter_priv contains N, the account doesn't have privilege to execute an ALTER statement.

The mysql database contains the following tables that store privileges:

user table: This table stores privileges that apply to all the databases and tables. It contains a row for each valid account that includes the columns user name, hostname, and password. The MySQL server rejects a connection for an account that doesn't exist in this table.

db table: This table stores privileges that apply to a particular database. It contains a row for the database, which gives privileges to an account name and a hostname. The account must exist in the user table for the privileges to be granted. Privileges that are given in the user table overrule privileges 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, suppose you have two databases: db1 and db2. The db1 database has sensitive information, so you want only 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 privileges to users connecting from a specific host, whereas another row can deny privileges to users connecting from any other host.

tables_priv table: This table stores privileges that apply to specific tables.

columns_priv table: This table stores privileges 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 privileges. You can use SQL queries such as SELECT, INSERT, and UPDATE. If you're accessing MySQL through your employer, a client, or a web hosting company, you probably don't have an account with the necessary privileges.

All the account information is stored in a database named mysql that's automatically created when MySQL is installed. To add a new account or change any account information, you must use an account that has the proper privileges on the mysql database.

In the rest of this section, we describe how to add and delete accounts and change passwords and privileges for accounts — and how to refresh privileges so that MySQL sees the changes.

However, if you have an account that you received from your company IT department or from a web hosting company, you might receive an error when you try to add an account or change account privileges as described in this chapter. If your account is restricted from performing any of the necessary queries, you need to request an account with more privileges or ask the MySQL administrator to add a new account for you or make the changes you need.

Identifying what accounts currently exist

To see the account information, you can execute an SQL query, using the mysql client as described in Chapter 1 of this minibook. To see what accounts currently exist for your database, you need an account that has the necessary privileges.

All the account names are stored in a database named mysql in a table named user. To see the account information, you can 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 privileges. 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 the SELECT privilege. Even though this message is annoying, it's a sign that the company has good security measures in place. However, it also means 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 accounts

The preferred way to access MySQL from PHP is to set up an account specifically for this purpose with only the privileges that are needed, and we describe in this section how to add accounts.

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 privileges needed to create an account. If it doesn’t, you can’t successfully execute the statement to add an account, and you have to request a second account to use with PHP.

tip.eps If you need to request a second account, get an account with restricted privilege (if at all possible) because your web database application is more secure if the account your PHP programs use doesn’t have more privileges than are necessary.

To create one or more users when you have the necessary privileges, you can use the CREATE USER statement (added to MySQL in version 5.0.2), as follows:

CREATE USER accountname@hostname IDENTIFIED BY 'password',

accountname@hostname IDENTIFIED BY 'password',...

This statement creates the specified new user account(s) with the specified password for each account and no privileges. You don't need to specify a password. If you leave out IDENTIFIED BY 'password', the account is created with no password. You can add or change a password for the account at a later time. We discuss adding passwords and privileges in the sections Adding and changing passwords and Changing privileges, later in this chapter.

warning_bomb.eps If you're using a version of MySQL before 5.0.2, you must use a GRANT statement to create an account. We describe the GRANT statement in the Changing privileges section, later in this chapter.

Adding and changing passwords

Passwords aren’t set in stone. You can add or change a password for an existing account. Like any of the procedures in this section, you can add or change passwords with an SQL statement, like this:

SET PASSWORD FOR username@hostname = PASSWORD('password')

The account is set to password for the account username@hostname. If the account currently has a password, the password is changed. You don't need to specify the FOR clause. If you don't, the password is set for the account you're currently using.

You can remove a password by sending the SET PASSWORD statement with an empty password:

SET PASSWORD FOR username@hostname = PASSWORD('')

remember.eps When you make changes to passwords, you need to refresh the privileges so that MySQL sees the change. This is accomplished with the FLUSH PRIVILEGES statement:

FLUSH PRIVILEGES

Changing privileges

Each account has a set of privileges that specifies what the user of the account can and can’t do. You can set the privileges when you create an account, but you can also change the privileges of an account at any time. The most useful privileges that you can set for an account are shown earlier in the chapter, in Table 2-1.

You can see the current privileges for an account by sending the following statement:

SHOW GRANTS ON accountname@hostname

The output is a GRANT statement that would create the current account. The output shows all the current privileges. If you don't include the ON clause, you see the current privileges for the account that issued the SHOW GRANTS query.

You can change privileges for an account with the GRANT statement, which has the following general format:

GRANT privilege (columns) ON tablename

TO accountname@hostname IDENTIFIED BY 'password'

remember.eps Like other privilege-related changes, you need to refresh the privileges after making changes using FLUSH PRIVILEGES.

You can also create a new account or change a password with the GRANT statement. You need to fill in the following information:

privilege (columns): You must list at least one privilege. You can limit each privilege to one or more columns by listing the column name in parentheses following the privilege. If you don't list a column name, the privilege is granted on all columns in the table(s). You can list as many privileges and columns as needed, separated by commas. You can see the possible privileges listed in Table 2-1. For instance, a GRANT statement might start with this:

GRANT select (firstName,lastName), update,

insert (birthdate) ...

tablename: The name (or names) of the table(s) on which the privilege is granted. You need to include at least one table. You can list several tables, separated by commas. The possible values for tablename are

• tablename: The entire table named tablename in the current database. 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 is 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 databases or tables. Using *.* grants the privilege on all tables in all databases.

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

password: The password that you're adding or changing. A password isn't required. If you don't want to add or change a password for this account, leave out the phrase IDENTIFIED BY 'password'.

For example, the GRANT statement that adds a new account for use in the PHP scripts for an online catalog database named ProductCatalog might be

GRANT select ON ProductCatalog.* TO phpuser@localhost

IDENTIFIED BY 'A41!14a!'

To remove privileges, use the REVOKE statement. The general format is

REVOKE privilege (columns) ON tablename

FROM accountname@hostname

You need to fill in the appropriate information.

You can remove all the privileges for an account with the following REVOKE statement:

REVOKE all ON *.* FROM accountname@hostname

Removing accounts

You might want to remove an account. In most cases, having an account that no one uses doesn’t have any negative effects. However, if you think an account has been compromised, you might want to remove it for security reasons.

To remove an account, you can use the DROP USER statement (which was added in MySQL 4.1.1), as follows:

DROP USER accountname@hostname, accountname@hostname, ...

You must use an account that has DELETE privileges on the mysql database to execute the DROP USER statement.

warning_bomb.eps The behavior of DROP USER has changed through MySQL versions. As of MySQL 5.0.2, it removes the account and all records related to the account, including records that give the account privileges on specific databases or tables. However, in versions before MySQL 5.0.2,DROP USER drops only accounts that have no privileges. Therefore, in older versions, you must remove all the privileges from an account, including database or table privileges, before you can drop that account.

Backing Up Your Database

You need to have at least one backup 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.

remember.eps You should have at least one backup copy of your database stored in a location that’s separate from the copy you currently use. You should probably have more than one copy — perhaps as many as three.

Here’s how you can store your copies:

First copy: Store one copy in a handy location, perhaps even on the same computer on which you store your database, to quickly replace a working database that becomes damaged.

Second copy: Store a second copy on another computer in case the computer on which you have your database breaks down, making the first backup copy unavailable.

Third copy: Store a third copy in a different physical location to prepare for the remote chance that the building burns down. If you store the second backup copy on a computer at another physical location, you don’t need this third copy.

tip.eps If you don’t have access to a computer offsite on which you can back up your database, you can copy your backup to a portable medium, such as a CD or DVD, 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 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. When evaluating a web hosting company, ask about the backup procedures. You want to know how often backup copies are made and where they’re stored. If you’re not confident that your data is safe, you can discuss changes or additions to the backup procedures.

remember.eps If 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 to make your own backup copy, just to be safe.

Make backups 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.

You can back up your MySQL database by using a utility program called mysqldump, provided by MySQL. The mysqldump program creates a text file that contains all the SQL statements you need to re-create your entire database. The file contains the CREATE statements for each table andINSERT statements for each row of data in the tables. You can restore your database, either to its current location or on another computer, by executing this set of MySQL statements.

Backing up on Windows

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 bin subdirectory in the directory where MySQL is installed.

For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin into the command prompt.

3. Type the following:

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

Backing up on Linux, Unix, and Mac

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

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

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

2. Type the following:

mysqldump --user=accountname --password=password

databasename >path/backupfilename

In the preceding code, make the following substitutions:

• accountname: Replace with the name of the MySQL account that you're using to back up the database.

• password: Use the password for the account.

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

• path/backupfilename: Replace path with the directory in which you want to store the backups and backupfilename with the name of the file in which you want to store the SQL output.

tip.eps The account that you use needs to have SELECT privilege. If the account doesn't require a password, you can leave out the entire password option.

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

For example, to back up the PetCatalog database, you might use the command

mysqldump --user=root --password=secret PetCatalog \

>/usr/local/mysql/backups/PetCatalogBackup

Note: With Linux or Unix, the account that you’re logged in to must have privilege to write a file into the backup directory.

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

In the preceding code, make the following substitutions:

• accountname: Enter the name of the MySQL account that you're using to back up the database.

The account that you use needs to have SELECT privilege. If the account doesn't require a password, you can leave out the entire password option.

• password: Use the password for the account.

• databasename: Replace with the name of the database that you want to back up.

• path\backupfilename: Replace path with the directory in which you want to store the backups and use the name of the file in which you want to store the SQL output in place of backupfilename.

For example, to back up the ProductCatalog database, you might use the command

mysqldump --user=root ProductCatalog >ProdCatalogBackup

Restoring Your Data

At some point, one of your database tables might become damaged and unusable. It’s unusual, but it happens. For instance, a hardware problem or an unexpected computer shutdown 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’s a typical error message that signals a corrupted table:

Incorrect key file for table: 'tablename'.

You can replace the corrupted table(s) with the data stored in a backup copy.

However, in some cases, the database might be lost completely. For instance, if the computer on which 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.

You can replace your current database table(s) with the database you’ve stored in a backup copy. The backup copy contains a snapshot of the data as it was when the copy was made. Of course, you don’t get any of the changes to the database since the backup copy was made; 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 we describe in Chapter 1 of this minibook, you build a database by creating the database and then adding tables to the database. The backup created by the mysqldump utility, as described in the section Backing Up Your Database, earlier in this chapter, is a file that contains all the SQL statements necessary to rebuild the tables, but it doesn't contain the statements you need to create the database itself.

To restore the database from the backup file, you must first edit the backup file (which is a text file). Then, you use the mysql client to create the database from the SQL statements in the backup file.

First, you edit the backup file by following these steps:

1. Open the backup file in a text editor.

2. Locate the line that shows the Server Versions.

3. If you want to rebuild an entire database, add the following statement below the line that you locate in Step 2:

CREATE DATABASE IF NOT EXISTS databasename

4. Below the line in Step 3, add a line specifying which database to add the tables to:

USE databasename

5. Check the blocks of statements that rebuild the tables.

If you don't want to rebuild a table, add -- (two hyphens) at the beginning of each line that rebuilds the table. The hyphens mark the lines as comments.

6. Check the INSERT lines for each table.

If you don't want to add data to any tables, comment out the lines that INSERT the data.

7. Save the edited backup file.

After the backup file contains the statements that you want to use to rebuild your database or table(s), you can use the mysql client to execute the SQL statements in the backup file. Just follow these steps:

1. From a command line prompt, change to the bin subdirectory in the directory where MySQL is installed.

remember.eps In Windows, you open a command prompt window to use the mysql client, as described in Chapter 1 of this minibook.

Type a cd command to change to the correct directory. For instance, you might type cd /usr/local/mysql/bin or cd c:\Program Files\MySQL\MySQL Server 5.0\bin.

2. Type this command (which sends the SQL queries in the backup file):

mysql -u accountname -p < path/backupfilename

You replace accountname with an account that has CREATE privilege. If the account doesn't require a password, leave out the -p. If you use the -p, you're asked for the password. Use the entire path and filename for the backup file. For instance, you could use this command to restore theProductCatalog database:

mysql -u root -p < c:\Program Files\MySQL\MySQL Server 5.0\bin\bak\ProductCatalog.bak

The tables might take a short time to restore. Wait for the command to finish. If a problem occurs, an error message appears. 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, you lose those changes. For instance, if more data was added after the backup copy was made, the new data isn’t restored. If you know the changes that were made after creating the backup, you can make them manually in the restored database.

Upgrading MySQL

New versions of MySQL are released periodically, and you can upgrade from one version of MySQL to a newer version. You can find upgrading information in the MySQL manual at http://dev.mysql.com/doc/refman/5.5/en/upgrading.html.

However, there are special considerations when you upgrade. As a precaution, back up your current databases, including the GRANT tables in the mysql database, before upgrading.

tip.eps MySQL recommends that you don’t skip versions. If you want to upgrade from one version to a version more than one version newer, such as from MySQL 4.0 to MySQL 5.0, you should upgrade to the next version first. After that version is working correctly, you can upgrade to the next version, and so on. In other words, upgrade from 4.0 to 4.1, then from 4.1 to 5.0.

Occasionally, incompatible changes are introduced in new versions of MySQL. Some releases introduce changes to the structure of the GRANT tables. For instance, MySQL 4.1 changed the method of encrypting passwords, requiring a longer password field in the GRANT tables.

After upgrading to the newer version, you should run the mysql_upgrade script. It repairs your files and upgrades the system tables, if needed. In versions prior to MySQL version 5.0.19, the mysql_upgrade script doesn't run on Windows; it runs only on Unix. On Windows, you can run a script called mysql_fix_privileges_tables with MySQL versions prior to 5.0.19. The script upgrades the system tables but doesn't perform the complete table check and repair that mysql_upgrade performs.