How to upgrade MySQL?

New versions of MySQL are released periodically, and you can upgrade from one version of MySQL to a newer version. Upgrading information is provided in the MySQL manual at dev.mysql.com/doc/refman/5.0/en/upgrade. html. However, there are special considerations when upgrading. As a precaution, it is wise to back up your current databases, including the grant tables in the mysql database, before upgrading. MySQL recommends that you do not 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 checks your files, repairing them if needed, and upgrades the system tables if needed. Before MySQL version 5.0.19, the mysql_upgrade script does not 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 does not perform the complete table check and repair that mysql_upgrade performs.

How to Restore 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 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’.
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.
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 created by the mysqldump utility is a file that contains all the SQL statements necessary to rebuild the tables, but it does not contain the statements needed to create the database. Your database might not exist, or it could exist with one or more corrupted 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.
3. Copy the CREATE query for the table from the backup file into the form in the browser window. For instance, choose EditCopy and EditPaste.
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 EditCopy and EditPaste.
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. First, you may need to edit the backup file, as follows:

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 after the line located in Step 2: CREATE DATABASE IF NOT EXISTS databasename
4. After 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 do not want to rebuild a table, comment out the lines that rebuild the table by adding -- (two hyphens) in front of each line.
6. Check the INSERT lines for each table. If you do not 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 need to perform a few more steps.
On Linux, Unix, and Mac:

1. Change to the bin subdirectory in the directory where MySQL is installed. Type a cd command to change to the correct directory. For instance, type cd /usr/local/mysql/bin.
2. Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>

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

• a. Open a command prompt window. For instance, choose StartAll ProgramsAccessoriesCommand Prompt.
• b. Type a cd command to change to the correct directory.

For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin.

Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>

How to Back 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 location 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 different physical location, for that remote chance that the building burns down. If the second backup copy is stored via a 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 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 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. When evaluating a Web hosting company, ask about their backup procedures. 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. The mysqldump program creates a text file that contains all the SQL statements needed to re-create your entire database. The file contains the CREATE statements 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, in Unix, or on a 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 filename 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 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, the command might be mysqldump—user=root—password=secret PetCatalog \ >/usr/local/mysql/backups/PetCatalogBackup Note: With Linux or Unix, the account that you are logged into must have permission 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 StartAll ProgramsAccessoriesCommand 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.
3. 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 filename 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 password 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—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.

How to Remove accounts and permissions?

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

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

You must be using an account that has DELETE privileges on the mysql database to execute the DROP USER query.
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 it permissions on specific databases or tables. However, before MySQL 5.0.2, DROP USER drops only accounts with no privileges. Therefore, in older versions, you must remove all the privileges from an account, including database or table permissions, before you can drop it.
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 permissions/columns as needed, separated by commas. 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 database. 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 in 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 permission on all tables in all databases.
• accountname@hostname: 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 REVOKE query will fail, and you will receive an error message. You can remove all the permissions for an account with the following REVOKE query: REVOKE all ON . FROM accountname@hostname

How to change permissions?

You can see the current permissions for an account with the following query:

SHOW GRANTS ON accountname@hostname

The output is a GRANT query that would create the current account. It shows all the current permissions. If you do not include the ON clause, you see the current permissions for the account that issued the SHOW GRANTS query. You can change permissions for an account with the GRANT query, which has the following general format:

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

You can also create a new account or change a password with the GRANT query. 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 permissions and columns as needed, separated by commas. 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 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 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 permission 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 the hostname as a pair. If an account exists with the specified account name but a different hostname, the existing account is not changed; a new one is created.
  
• password: This is the password that you’re adding or changing. A password is not required. If you don’t want to add or change a password for this account, leave out the 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!’

How to add and change SQL passwords?

You can add or change a password for an existing account with the SET PASSWORD query, as follows:

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 do not need to specify the FOR clause. If you do not, the password is set for the account you are currently using.
You can remove a password by sending the SET PASSWORD query with an empty password, as follows:

SET PASSWORD FOR username@hostname = PASSWORD(‘’)

How to Add Accounts?

The preferred way to access MySQL from PHP is to set up an account specifically for this purpose with only the permissions that are needed. In this section, I describe 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 permissions needed to create an account. If it doesn’t, you won’t be able to successfully execute the 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 permission (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.
To create one or more users, you can use the CREATE USER query added to MySQL in version 5.0.2, as follows:

CREATE USER accountname@hostname IDENTIFIED BY ‘password’, accountname@hostname IDENTIFIED BY ‘password’,...

This query creates the specified new user account(s) with the specified password and no permissions. You do not 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. Adding passwords and permissions is discussed in the following sections. If you’re using a version of MySQL before 5.0.2, you must use a GRANT query to create an account. The GRANT query is described in the “Changing permissions” section.