For content-driven Web sites, the key to the quality of the design is the relational database. In this tutorial, we have built our database using the MySQL relational database management system (RDBMS). MySQL is a popular choice for Web site developers, not only because it is free for non-commercial applications on any platform, but also because it is very simple to build and use. As we have seen in the first chapter, a new user can set up a MySQL service in no more than 30 minutes and run it (even 10 minutes for an experienced user), according to the right guidance. )。
If all you want to do is set up a MySQL service environment to use to do some examples and exercises, then the initialization settings that we use in the first chapter of the installation are sufficient for you. But if you're trying to build a real database for your Web site--perhaps the site is important for your company--then you need to learn a little bit about MySQL.
Backup of data is important for business transactions that are part of an internet-based enterprise. Unfortunately, because the work of backup is often less interesting to an administrator, people are always unable to recognize its importance, so this work is often not "good enough" for an application. If until now you don't understand whether "we need to back up our database", or if you think "the database will be backed up with something else," then you need to take a good look at the chapter. We will explain why a common file backup scheme is far from enough for many MySQL services, and then we'll introduce the "right way" to back up and restore a MySQL database.
In the first chapter, we set up a MySQL service and connected to the database through a password ' root '. MySQL's ' root ' user (by the way, not to be confused with Unix's ' root ' users) has read/write permissions for all libraries and tables. In many cases, we need to establish other users who can only access certain databases and datasheets, and we need to restrict such access (for example, only direct read-only access to a specified table). In this chapter, we will learn to use two new MySQL commands: Grant and revoke to do the work.
In some cases, for example, due to a power problem, the MySQL database may be corrupted. Such damage does not always mean that a backup must be used to recover. We will learn to use the MySQL database's check and repair capabilities to solve simple database corruption.
Why standard backups are not enough
As with Web servers, the vast majority of MySQL servers must be online without interruption. This makes the MySQL database backup seem very important. Because the MySQL service uses cache and buffers to improve the efficiency of updating database files stored on disk, the contents of the file may not be exactly the same as the contents of the current database. While standard backup programs only include copies of systems and data files, this backup of MySQL data files does not fully meet our needs because they do not guarantee that the copied files can be used properly when the system crashes.
In addition, since many databases must receive information all day long, standard backups can only provide "instantaneous" images of database data. If the MySQL database file is corrupted or becomes unavailable, the information added after the last backup will be lost. In many cases, such as for a database that handles user orders for an E-commerce Web site, such a loss is intolerable.
The tools in MySQL can back up the data in real time, while the backup does not affect the efficiency of the service. Unfortunately, this requires you to configure a special backup system for your MySQL data. It has nothing to do with other data backup scenarios that you have developed. However, as with any good backup system, when you really use it, you will find that the current problem is worth it.
In this chapter, we provide guidance for a computer running Linux or other unix-based operating systems. If you are using Windows, the method is basically the same, but some of these commands must be changed.
Using Mysqldump for database backup
In addition to mysqld, MySQL server and MySQL (MySQL client), the installation will also produce a lot of useful programs. For example, the mysqladmin we've seen earlier, is the program that controls and collects information about the running MySQL service.
Mysqldump is another such program. When it runs, it connects to a MySQL service (as it does with the MySQL program and the PHP language) and downloads the entire contents of the specified database. It then outputs a series of SQL CREATE TABLE commands and insert commands that run these commands in an empty MySQL database to create a MySQL database that is exactly the same as the original database.
By redirecting the output of mysqldump to a file, you can store a "mirror" of the database as a backup. The following command connects a password-mypass root user to a MySQL service running on Myhost and stores a backup of the database named dbname to the Dbname_backup.sql file:
% mysqldump-h myhost-u Root-pmypass dbname > Dbname_backup.sql
To recover such a database, you only need to run the following command:
% mysqladmin-h myhost-u Root-pmypass Create dbname
% mysql-h myhost-u Root-pmypass dbname < Dbname_backup.sql
The first command uses the Mysqladmin program to establish a database. The second command connects to the MySQL service and uses the usual MySQL program, and takes the backup file that you just received as a command to execute.
In this way, we can use mysqldump to establish a backup of our database. Because mysqldump generates this backup through a connection to the MySQL service, this is certainly more secure than accessing the database files directly under the MySQL data directory, as such backups ensure a valid copy of the database, not just a copy of the database file.
The remaining question is how to resolve the synchronization between this "mirror" and a constantly updated database. To do this, you need to command the service to keep a change log.
Incremental backups using the change log
As we mentioned earlier, in many cases, the MySQL database we use can cause data loss--and sometimes very important data loss. In such cases, we must find a way to keep the synchronization between the backup made with mysqldump and the current database using the method described above. The solution is to have the MySQL service maintain an update log. An update log is a record of all database-accepted queries that may alter the contents of the database. This will include the INSERT, update, and CREATE TABLE statements, but not the SELECT statement.
The usual idea is to maintain a change log so that when the database crashes, your recovery process should be this: first using the backup (generated using the mysqldump command), and then using the variable log after the backup.
You can also undo the error action using the change log. For example, if a partner tells you that he mistakenly used a drop table command, you can edit the change log to remove the command, and then use the Backup and modified change log to recover. In this way, you can even maintain the changes in the other tables after the accident. As a precaution, you may also want to reclaim your partner's drop privileges (you will see what to do in the next section).
Telling the MySQL server to maintain a change log is very simple, and you only need to add an option to the service's command line:
% Safe-mysqld--log-update=update
The above command starts the MySQL service and tells it to be in the server's data directory (if you configure your server according to the first chapter, this directory will be/usr/local/mysql/var) to create a update.001, update.002 ... of the file. A new such file is created every time the server refreshes its log file (typically, this refers to the service every time it restarts). If you want to store your change log somewhere else (which is usually a good idea-if there is a problem with the disk that contains your data directory, you can't expect it to be able to keep your backup properly!) , you can specify the path to the change log.
However, if your MySQL server is working uninterrupted, you may need some system configuration when you start the MySQL service. In this case, adding a command line selection can become difficult. Another simple way to create a change log is to add the appropriate options to the MySQL configuration file.
If you're not sure what a MySQL profile is, don't worry. In fact, we have not been used to this kind of configuration file until now. To create this file with the MySQL user we created in the first chapter (if you are completely guided by this, this should be mysqlusr) log in to Linux. Use your custom text editor, in your MySQL data directory (unless you choose to install MySQL elsewhere, this should mean/usr/local/mysql/var) to create a file called MY.CNF. In this file, enter the following line:
[Mysqld]log-update=/usr/backups/mysql/update
Of course, you are free to specify where your log files are written. Save this file and restart your MySQL service. From now on, the MySQL service will run in the same way that you used the--log-update option on the command line.
Obviously, change logs can take up a lot of space for a service. For this reason as well as MySQL cannot automatically delete old log files in creating new log files, you need to manage your change log files yourself. For example, the following Unix shell script deletes all change log files that were changed a week ago, and then notifies MySQL to refresh its log files.
#! /bin/sh
find/usr/backups/mysql/-name "Update. [0-9]*]
-type F-mtime +6 | Xargs rm-f
/usr/local/mysql/bin/mysqladmin-u Root
-ppassword Flush-logs
If the current log file is deleted, the final step (refreshing the log file) will create a new change log, which means that the MySQL service has been online, and that in the past week no queries have been received to change the contents of the database.
If you are an experienced user, it should be fairly straightforward to use the clock daemon to set a script to perform a backup of the database regularly (say, once a week) and delete the old change log. If you need a little help, ask your local UNIX authority. There is also a detailed guide to setting up such a system in the MySQL management chapter of ' MySQL ' by Paul Dubois.
Assuming you have a copy of the backup and the change log after that, it will be very easy to restore your database. After setting up an empty database, apply the method we discussed in the previous section to import the backup, and then import the change log using the MySQL command with the--one-database command-line option. This instructs the server to only run queries related to the database that we want to restore (in this case, dbname) in the change log only:
% mysql-u Root-ppassword--one-database dbname < update.100
% mysql-u Root-ppassword--one-database dbname < update.102
...
MySQL access control
Earlier in the tutorial, we mentioned a database called MySQL, which contains the database in every MySQL service that holds information about users, their passwords, and their permissions. However, prior to this, we have been using the root user to log on to the MySQL service, which allows access to all databases and datasheets.
If your MySQL service is only accessed through PHP and you are careful to tell someone what the root password is, the root account may be sufficient. However, if a MySQL service is shared by many people (for example, a web host wants to provide the same MySQL service to each of its users), it is important to set the appropriate access rights for different users.
MySQL's access control system is described in detail in chapter sixth of the MySQL reference manual. In principle, user access is managed by five data tables in the MySQL database: User, DB, host, Tables_priv, and Columns_priv. If you want to edit these tables directly using the INSERT, UPDATE, and DELETE statements, I suggest you read the relevant chapters in the MySQL guide first. and starting with version 3.22.11, MySQL provides a simple way to manage user access. Using the non-standard commands grant and revoke provided by MySQL, you can create and give the user the right to do so without having to worry about the form of storage in the five tables mentioned earlier.
Using Grant
The grant command is used to create a new user, specify a user password, and increase user permissions. The format is as follows:
Mysql> GRANT <privileges> on <what>
-> to <user> [identified by <password>]
-> [with GRANT OPTION];
As you can see, there are a lot of things to fill out in this command. Let's take a look at each of them and finally give you some examples to get an idea of how they work together.
<privileges> is a comma-delimited list of the permissions you want to give. The permissions you can specify can be grouped into three categories:
Database/data Table/Data column permissions:
Alter: Modify existing data tables (for example, add/Remove Columns) and indexes.
Create: Create a new database or datasheet.
Delete: Deletes a table record.
Drop: Deletes a datasheet or database.
Index: Create or delete indexes.
INSERT: Adds a record of the table.
SELECT: Displays/searches the records of the table.
UPDATE: Modifies records that already exist in the table.
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills a service thread belonging to another user.
RELOAD: Overload access Control table, refresh log, etc.
SHUTDOWN: Turn off MySQL service.
Special permissions:
All: Allow to do anything (like root).
USAGE: Only allow login-nothing else allowed.
These permissions are involved in the characteristics of MySQL, some of which we haven't seen yet, and most of them are familiar to you.
<what> defines the areas in which these permissions are scoped. *.* means that permissions are valid for all databases and datasheets. Dbname.* means that all data tables in a database named dbname are valid. Dbname.tblname means that only data tables named Tblname in the name dbname are valid. You can even use the list of data columns in parentheses after giving permissions to specify that permissions are valid only for those columns (as we'll see in the following example).
<user> Specify the users who can apply these permissions. In MySQL, a user is specified through the user name that it logs on and the host name/IP address of the computer to which the user is using. Both values can use the% wildcard character (for example, kevin@% will allow user name Kevin to log on from any machine to enjoy the permissions you specify).
<password> Specifies the password used by the user to connect to the MySQL service. It is enclosed in square brackets, stating that identified by "<password>" is optional in the grant command. The password specified here replaces the user's original password. If a password is not specified for a new user, the password is not required when the connection is made.
The optional with GRANT option section of this command specifies that the user can use the Grant/revoke command to give other users the permissions he has. Use this feature carefully-though it may not be so obvious! For example, two users who have this feature may share their permissions with each other, which may not be what you wanted to see.
Let's take a look at two examples. Create a user named Dbmanager who can use the password managedb to connect to MySQL from Server.host.net and only access the entire contents of the database named db (and give this permission to other users), which can be used by the following Grant command:
Mysql> GRANT all on db.*
-> to Dbmanager@server.host.net
-> identified by "Managedb"
-> with GRANT OPTION;
Now change this user's password to funkychicken, the command format is as follows:
Mysql> GRANT USAGE on *.*
-> to Dbmanager@server.host.net
-> identified by "Funkychicken";
Please note that we do not give any additional permissions (the usage permission can only allow users to log on), but the user's existing permissions are not changed.
Now let's build a new user named Jessica, who can connect to MySQL from any machine in the host.net domain. He can update the user's name and email address in the database, but does not need to consult other database information. That is, he has read-only access to the DB database (for example, SELECT), but he can perform an update operation on the name column and the email column of the users table. The order is as follows:
Mysql> GRANT SELECT on db.*
-> to Jessica@%.host.net
-> identified by "Jessrules";
Mysql> GRANT UPDATE (name,email) on DB. Users
-> to Jessica@%.host.net;
Note that in the first command we used the% (wildcard) symbol when specifying the host name that Jessica can use to connect. In addition, we did not give him the ability to pass his permissions on to other users because we did not bring with GRANT OPTION at the end of the command. The second command demonstrates how to grant permissions to a particular data column by using a comma-delimited list of columns after the given permission.
Using revoke
As you would expect, the revoke command is used to remove a user's previously assigned permissions. The syntax for the command is as follows:
Mysql> REVOKE <privileges> [(<columns>)]
-> on <what> from <user>;
The functions of each part of this command are the same as those in the grant command above. To remove the drop permission for a Jessica partner (for example, if he often mistakenly deletes the database and table), you can use the following command:
Mysql> REVOKE DROP on *.* from Idiot@%.host.net;
Removing a user's logon rights is probably the only one that cannot use revoke. REVOKE all on *.* removes all permissions from the user, but he can also log in to completely delete a user, and you need to delete the corresponding record in the user table:
Mysql> DELETE from user
-> WHERE user= "idiot" and host= "%.host.net";
Access Control Tips
Because of the impact of the way the access control system works in MySQL, you must know two features before you build your users.
When the established user can only log on to the MySQL service from the computer running the MySQL service (that is, you need them to telnet to the server and run the MySQL client program there, or to communicate using a server-side scripting language like PHP), You will probably ask yourself what the <user> part of the grant command should be. If the service is running in Www.host.net. Should you set the user to Username@www.host.net or username@localhost?
The answer is that you cannot rely on any of these to handle any connection. Theoretically, if a user specifies a host name at the time of the connection (whether using a MySQL client or using the PHP mysql_connect function), the hostname must match the record in the access control system. But because you may not want to force your users to specify the hostname (in fact, users of the MySQL client may not specify the hostname at all), you'd better use the work environment below.
For users who need to be able to connect to MySQL from a machine running on the MySQL service, two user records are created in the MySQL access control system: one using the actual hostname (for example, username@www.host.net) and the other using localhost ( For example, Username@localhost), of course, you need to grant/revoke all the permissions for two users respectively.
Another common problem that MySQL managers face is a user record in which the host name uses a wildcard (for example, the previous reference to jessica@%.host.net) does not work. This is typically due to a problem with the priority of the MySQL access control system. Specifically, the more specific the host name priority (for example, Www.host.net is the most specific,%.host.net is more specific, and% is the least specific).
After a new installation, the MySQL access control system contains two anonymous user records (which allow you to connect with any user name on the current host-the two records support the connection from the localhost connection and the host name from the server's implementation), and the two root user directory. The situation we discussed above occurs because the anonymous user directory has a higher priority than our new record because their host name is more specific.
Let's take a look at the contents of the user table on Www.host.net and assume that we have added Jessica Records. Data rows are arranged according to the precedence of the MySQL service when confirming the connection:
As you can see, because the Jessica record has the least specific hostname, it has the lowest priority. When Jessica tries to connect from Www.host.net, the MySQL service matches his connection to an anonymous user record (the blank user value matches anyone). Because these anonymous records do not require a password, and perhaps Jessica entered his password, MySQL will reject the connection. Even if Jessica does not enter a password, he may have only been given the privilege (very limited) of the anonymous user, rather than the permissions he had previously given.
The solution to this problem is either you delete the anonymous user's record (delete from user WHERE user= ""), or you can specify two records for all users who may be connected from localhost (for example, Relative to localhost and the actual host name relative to the server:
Because it can be cumbersome to maintain three user records (and the corresponding three sets of permissions) for each user, we recommend that you remove anonymous users unless you need to use them to accomplish any particular application:
Locked out?
Like losing a key in a car, forgetting a password after spending one hours installing and debugging a new MySQL server is a real hassle. Luckily, if you have root access to the computer running MySQL, or you can log in with the user running the MySQL service (which means mysqlusr if you follow the guide in Chapter One), then nothing will go wrong. Follow the steps below to gain control of your service.
First, you must shut down the MySQL service. Because the commonly used mysqladmin need to use your forgotten password, you can only do it by killing the service process. Use the PS command or look at the service's PID file (in the MySQL data directory), determine the ID of the MySQL service's process, and then terminate it using the following command:
% Kill <pid>
Here <pid> is the ID of the MySQL service process. This will allow the service to be terminated. Do not use kill-9 unless absolutely necessary, as this can damage your table files. If you are forced to do so, the following will show you how to check and fix those files.
After the service is turned off, you can restart it by running the safe-mysqld (mysqld or MYSQLD-NT) command with the--skip-grant-tables command line option. This will instruct the MySQL service to allow free access, and obviously we should use this mode to run the service as soon as possible to avoid inherent security risks.
After the connection is successful, change your root password:
mysql> use MySQL;
mysql> UPDATE user SET Password=password ("NewPassword")
-> WHERE user= "root";
Finally, disconnect and instruct the MySQL service to overload the authorization table to receive the new password:
% mysqladmin flush-privileges
It's all right now-no one even knows what you've done. It's like you've left your keys in the car and you're in the car yourself.
Review and repair MySQL data files
Due to temporary power outages, the use of kill-9 to stop the MySQL service process, or Jessica friend Idiot@%.host.net made a mistake, all of which could destroy MySQL data files. If the service is changing the file while it is being disturbed, the file may leave a wrong or inconsistent state. Because this kind of destruction is sometimes not easy to find, when you find this error may be a long time later. So, when you find this problem, maybe all the backups have the same error.
The 15th chapter of the MySQL reference manual describes the features of MySQL's myisamchk, and how to use it to check and repair your MySQL data files. While this chapter is recommended for everyone who wants to build a strong MySQL service, it is important to discuss the main points here.
Before we proceed, you must be aware that the MYISAMCHK program's access to MySQL data files for inspection and modification should be unique. If the MySQL service is using a file and modifies the file that Myisamchk is checking, Myisamchk will mistakenly assume that an error has occurred and will attempt to fix it-which will cause the MySQL service to crash! In this way, to avoid this, we usually need to turn off the MySQL service while we work. As a choice, you can also temporarily shut down the service to make a copy of the file and then work on that copy. When you're done, shut down the service and replace the original file with the new file (you may also need to use the change log for the period).
MySQL Data directory is not too difficult to understand. Each database corresponds to a subdirectory, and each subdirectory contains a file corresponding to the data table in the database. Each data table corresponds to three files, which are the same as the table names, but have different extensions. The Tblname.frm file is a table definition that holds the contents and types of the data columns contained in the table. The Tblname.myd file contains the data in the table. The Tblname.myi file contains the index of the table (for example, it might contain a lookup table to help increase queries against the table's primary key columns).
To check for errors in a table, you only need to run Myisamchk (in the MySQL bin directory) and provide the location and table name of the file, or the index file name of the table:
% Myisamchk/usr/local/mysql/var/dbname/tblname
% Myisamchk/usr/local/mysql/var/dbname/tblname.myi
All two of the above commands can perform a check on the specified table. To check all tables in the database, you can use wildcard characters:
% myisamchk/usr/local/mysql/var/dbname/*. Myi
To check all tables in all databases, you can use two wildcard characters:
% myisamchk/usr/local/mysql/var/*/*. Myi
If you do not have any options, MYISAMCHK will perform a normal check on the table file. If you have doubts about a table, but the normal check does not detect any errors, you can perform a more thorough check (but also slower!). ), which requires the use of the--extend-check option:
% Myisamchk--extend-check/path/to/tblname
Checking for errors is not destructive, which means you don't have to worry that performing a check on your data files will make the existing problems worse. On the other hand, the fix option, while usually safe, does not undo changes to your data file. For this reason, we strongly recommend that you try to repair a corrupted table file first by making a backup and make sure that your MySQL service is off before making this backup.
When you try to fix a problem with a damaged table, there are three types of fixes. If you get an error message stating that a temporary file cannot be created, delete the file indicated by the information and try again-this is usually the legacy of the last repair operation.
The three repair methods are as follows:
The first is the fastest, to fix the most common problems, and the last one is the slowest to fix problems that some other methods cannot fix.
Review and repair MySQL data files
If the above method cannot fix a damaged table, you can try these two techniques before you give up:
If you suspect that the index file of the table (*. Myi An unrecoverable error occurred, or even lost the file, you can use the data file (*. MYD) and the data format file (*.FRM) to regenerate it. First, make a copy of the data file (TBLNAME.MYD). To restart your MySQL service and connect to this service, delete the contents of the table using the following command:
Mysql> DELETE from Tblname;
When you delete the contents of a table, a new index file is created. Quit logging on and shut down the service and overwrite the new (empty) data file with the data file (tblname.myd) you just saved. Finally, use MYISAMCHK to perform a standard fix (the second method above) to regenerate index data based on the contents of the table's data and the table's format file.
If your table's format file (TBLNAME.FRM) is missing or an unrecoverable error occurs, but you know how to regenerate the table using the corresponding CREATE TABLE statement, you can regenerate a new. frm file and and your data files and index files ( If there is a problem with the index file, use the method above to reconstruct a new one. First make a copy of the data and index file, and then delete the original file (delete all the records in the data directory about the table).
Start the MySQL service and create a new table using the original CREATE table file. The new. frm file should work, but the best thing to do is to perform the standard fix (the second method above).
Conclusion
OK, indeed, there is not a lot of code in this chapter that we usually get used to doing a real job. But all this work-backing up and restoring databases, managing MySQL access control systems, checking and repairing data tables-will help us build a MySQL database server that can withstand the test of time.
In the penultimate chapter of this tutorial--the Nineth chapter, we'll take a few more complex SQL techniques to make our relational database server complete some of the work you might have never thought of before.
