For content-driven websites, the key to good or bad design is relationship databases. In this tutorial, we have built our database using MySQL Relational Database Management System (RDBMS). MySQL is a popular choice for website developers, not only because it is free for non-commercial applications on any platform, but also because its setup and use are very simple. As we saw in Chapter 1, according to the correct guidance, a new user can set up a MySQL service in less than 30 minutes and run it (for an experienced user even only 10 minutes!).
If all you want to do is to set up a MySQL service environment for some examples and exercises, the initialization settings we used in Chapter 1 installation are enough for you. But if you are trying to build a real database for your web site – maybe this site is important to your company – then you also need to learn something about MySQL.
Data backup is important for commercial transactions as part of an Internet-based enterprise. Unfortunately, because backup work is often not very interested in an administrator, people always cannot recognize its importance, so this work often cannot be "good enough" for an application. If you still don't understand "whether we need to back up our database" until now, or if you think "the database will be backed up with other things", then you have to take a good look at the content of this chapter. We will explain why a normal file backup scheme is far from enough for many MySQL services, and then we will introduce the "right way" to back up and restore a MySQL database.
In Chapter 1, we set up a MySQL service and connect to the database through a password ‘root’. MySQL's 'root' user (not to be confused with Unix's 'root' user, by the way) has read/write permissions for all libraries and tables. In many cases, we need to create other users who can only access certain databases and data tables, and we also need to restrict this access (for example, only direct read-only access to specified tables). In this chapter, we will learn if we use two new MySQL commands: GRANT and REVOKE to do these tasks.
In some cases, for example, due to power problems, the MySQL database may be corrupted. Such corruption does not always mean that backups must be used to restore. We will learn to solve simple database corruption by leveraging the check and repair functions of MySQL database.
Why is standard backup not enough
Like web servers, most MySQL servers must be online continuously. This makes backups of MySQL databases very important. Because MySQL service uses cache and buffers to improve efficiency of updating database files stored on disk, the content of the file may not be exactly the same as the content of the current database. Standard backup programs only include copying of the system and data files. This backup of MySQL data files does not fully meet our needs because they cannot guarantee that the copied files can be used normally when the system crashes.
Furthermore, because many databases have to receive information all day long, standard backups can only provide a "momental" image of the 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 loss is not tolerated, for example, for an e-commerce website that processes user orders.
Tools in MySQL can backup data in real time, and when the backup is in progress, it will 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 plans you have already formulated. However, like any good backup system, when you really use it, you will find that the trouble is worth it now.
In this chapter, the guidance we provide is for a computer running Linux or other Unix-based operating system. If you are using Windows, the method is basically the same, except that some of the commands must be changed.
Use mysqldump for database backup
In addition to mysqld, MySQL server and mysql (MySQL client) will also produce many useful programs during installation. For example, the mysqladmin we have seen before is the program responsible for controlling and collecting information about running MySQL services.
mysqldump is another such program. When it runs, it connects to a MySQL service (just as mysql programs do with PHP languages) and downloads the entire contents of the specified database. Then it will output a series of SQL CREATE TABLE commands and INSERT commands. Run these commands in an empty MySQL database to create a MySQL database that is exactly the same as the original database content.
By redirecting the output of mysqldump to a file, you can store a "mirror" of a database as a backup. The following command is to connect to a MySQL service running on myhost using a root user with mypass password, and store the backup of the database named dbname into the dbname_backup.sql file:
% mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql
To restore 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 create a database. The second command connects to the MySQL service and uses the usual mysql program and takes the backup file you just got as the command executed in it.
In this way, we can use mysqldump to build a backup of our database. Because mysqldump generates this backup through a connection to the MySQL service, it is definitely safer than directly accessing the database files in the MySQL data directory, because such a backup can ensure that it is a valid copy of the database, not just a copy of the database files.
The remaining problem is how to solve the synchronization between this "mirror" and a constantly updated database. To do this, you need to command the service to keep a change log.
Use the change log for incremental backup
As we mentioned earlier, in many cases, the MySQL database we use can cause data loss - sometimes even very important data. In this case, we must find a way to keep the backups we made with mysqldump using the method described above and the current database. The solution is to keep MySQL service an update log. An update log is a record of queries received by all databases that may change the content 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 like this: first use a backup (generated using the mysqldump command), and then use the variable log after the backup.
You can also use the change log to undo the error. For example, if a collaborator tells you that he used a DROP TABLE command incorrectly, you can edit the change log to delete the command and then restore it using the backup and modified change log. In this way, you can even keep other tables changing after this accident. As a precaution, you may also revoke your collaborator’s DROP permissions (you will see what to do in the next section).
Telling MySQL server to maintain a change log is very simple, 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 create a file named update.001, update.002... in the server's data directory (if you follow the instructions in Chapter 1, this directory will be /usr/local/mysql/var). A new such file will be created every time the server refreshes its log file (usually, this refers to every time the service is restarted). If you want to store your changelog to another place (usually it's a good idea - if something goes wrong with the disk containing your data directory, you can't expect it to save your backups well!), you can specify the path to the changelog.
However, if your MySQL server works continuously, you may also need some system configuration when starting the MySQL service. In this case, adding a command line selection can become difficult. Another easy way to create a change log is to add corresponding options to the MySQL configuration file.
If you are not clear about "what is a MySQL configuration file", don't worry. In fact, we have never used such a configuration file before this. To create this file, log in to Linux with the MySQL user we created in Chapter 1 (if you do it entirely according to the guidance, this should be mysqlusr). Use the text editor you are used to and create a file named in your MySQL data directory (unless you choose to install MySQL elsewhere, which should mean /usr/local/mysql/var). 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 the same way you used the --log-update option on the command line.
Obviously, for a service, the change log can take up a lot of space. Because of this reason and MySQL cannot automatically delete old log files when creating new log files, you need to manage your change log files yourself. For example, the following Unix shell script will delete all change log files that have been changed a week ago and then notify 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 last step (refresh the log file) will create a new change log, which means that the MySQL service has been online and has not received any queries to change the content of the database over the past week.
If you are an experienced user, it should be fairly simple to set up a script using the "Clock Daemon" to perform a backup of the database periodically (let's say once a week) and delete the old changelog. If you need a little help, please ask your local Unix authority. 'MySQL' by Paul The MySQL Management chapter in DuBois also has detailed guides on setting up such a system.
Assuming you have already had a backup and a copy of the change log after that, restoring your database will be very simple. After creating an empty database, use the method we discussed in the previous section to import the backup, and then use the mysql command with the --one-database command line option to import the change log. This instructs the server to run only queries in the change log related to the database we want to recover (in this example, dbname):
% mysql -u root -ppassword --one-database dbname < update.100
% mysql -u root -ppassword --one-database dbname < update.102
...
MySQL Access Control
Earlier in this tutorial, we mentioned a database called mysql, which contains this database in every MySQL service. It is used to store user related information, their passwords, and their permissions. However, before that, we had been logging in to the MySQL service using the root user, which could access all databases and data tables.
If your MySQL service is only accessed through PHP and you are careful about telling people who are root users' passwords, then the root account may be enough. 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 corresponding access rights for different users.
The MySQL access control system is described in detail in Chapter 6 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 INSERT, UPDATE, and DELETE statements, I suggest you read the relevant chapters in the MySQL guide first. Since version 3.22.11, MySQL provides an easy way to manage user access. Using the non-standard commands GRANT and REVOKE provided by MySQL, you can create a user and give it corresponding permissions without having to care about its storage form 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 to be filled in in this command. Let's introduce them one by one and finally give some examples to give you an idea of how they work together.
<privileges> is a comma-separated list of permissions you want to give. The permissions you can specify can be divided into three types:
Database/data table/data column permissions:
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: Create a new database or data table.
DELETE: Delete the record of the table.
DROP: Delete data tables or databases.
INDEX: Create or delete index.
INSERT: Add records in the table.
SELECT: Display/search records in the table.
UPDATE: Modify the records that already exist in the table.
Global management permissions:
FILE: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload access control tables, refresh logs, etc.
SHUTDOWN: Close MySQL service.
Special permissions:
ALL: Allows to do anything (as with root).
USAGE: Only login is allowed--nothing else is allowed.
Some of the MySQL features involved in these permissions have not been seen yet, and most of them are familiar to you.
<what> Defines the area where these permissions act. *.* means that permissions are valid for all databases and data tables. dbName.* means that it is valid for all data tables in a database named dbName. Meaning only valid for data tables named tblName in dbName. You can even specify that permissions are only valid for these columns by using a list of data columns in parentheses after the permissions you are given (we will see an example like this later).
<user>Specifies the user who can apply these permissions. In MySQL, a user is specified by the user name it logs in and the hostname/IP address of the computer the user is using. Both values can be % wildcarded (for example, kevin@% will allow logging in from any machine using the username kevin to enjoy the permissions you specified).
<password> specifies the password used by the user to connect to the MySQL service. It is enclosed in square brackets, indicating that IDENTIFIED BY "<password>" is optional in the GRANT command. The password specified here will replace the user's original password. If a new user is not assigned a password, it does not need a password when it connects.
The optional WITH GRANT OPTION part in this command specifies that the user can use the GRANT/REVOKE command to assign the permissions he has to other users. Please be careful with this feature - although this problem may not be that obvious! For example, two users who both have this feature may share their permissions with each other, which may not be what you originally wanted to see.
Let's look at two examples. Create a user named dbmanager. He can use the password managedb to connect from MySQL and can only access the entire contents of the database named db (and can assign this permission to other users). This can use the following GRANT command:
mysql> GRANT ALL ON db.*
-> TO dbmanager@
-> IDENTIFIED BY "managedb"
-> WITH GRANT OPTION;
Now change the password of this user to funkychicken, the command format is as follows:
mysql> GRANT USAGE ON *.*
-> TO dbmanager@
-> IDENTIFIED BY "funkychicken";
Please note that we do not grant any additional permissions (the USAGE permissions can only allow users to log in), but the permissions that already exist in the user will not be changed.
Now let's create a new user named jessica that can connect to MySQL from any machine in the domain. It can update the user's name and email address in the database, but it does not need to check the information of other databases. That is to say, it has read-only permissions (for example, SELECT), but it can perform UPDATE operations on the name column and email column of the Users table. The command is as follows:
mysql> GRANT SELECT ON db.*
-> TO jessica@%.
-> IDENTIFIED BY "jessrules";
mysql> GRANT UPDATE (name,email) ON
-> TO jessica@%.;
Note that in the first command we use the % (wildcard) symbol when specifying the hostname that Jessica can use to connect to. In addition, we did not have the ability to pass his permissions 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 specific data column through a list of commas separated columns in parentheses after the granted permissions.
Use REVOKE
As you expected, the REVOKE command is used to remove permissions that a user has previously been granted. The syntax of 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 DROP permissions from Jessica's collaborator (for example, if he often mistakenly deletes databases and tables), you can use the following command:
mysql> REVOKE DROP ON *.* FROM idiot@%.;
Removing a user's login permission is probably the only way to use REVOKE. REVOKE ALL ON *.* will remove all permissions of the user, but he can also log in. To completely delete a user, you need to delete the corresponding record in the user table:
mysql> DELETE FROM user
-> WHERE User="idiot" AND Host="%.";
Access control skills
Due to the influence of the approach to access control systems in MySQL, you must know two characteristics before building your users.
When the established user can only log in 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 communicate using a server-side scripting language like PHP), you will probably ask yourself what should be filled in the <user> part of the GRANT command. If the service is running in. Should you set the user to username@ or username@localhost?
The answer is, you can't rely on any of these to handle any connection. In theory, if the user specifies the hostname when connecting (whether using the mysql client or PHP's mysql_connect function), this hostname must match the record in the access control system. But because you may not want to force your user to specify a hostname (in fact, the user of the mysql client may not specify a hostname at all), you'd better use the following working environment.
For situations where users need to be able to connect MySQL from the machine on which the MySQL service is running, two user records are established in the MySQL access control system: one uses the actual host name (e.g. username@), and the other uses localhost (e.g. username@localhost). Of course, you need to grant/revoke all permissions for the two users separately.
Another common problem that MySQL administrators have to face is that a user record in which the hostname uses wildcard characters (for example, the aforementioned jessica@%.) does not work. This happens generally due to the priority of records in the MySQL access control system. Specifically, the more specific the hostname has a higher priority (for example, it is the most specific, %. is relatively specific, while % is the least specific).
After a new installation, the MySQL access control system contains two anonymous user records (which allows connections with any username on the current host - these two records support connections from localhost and from the server's implementation's host name, respectively), and two root user directories. The situation we discussed above occurs when the anonymous user directory has higher priority than our new records because their hostnames are more specific.
Let's look at the contents of the user table above, we assume that Jessica's record has been added. Data rows are arranged according to the priority of the MySQL service when confirming the connection:
As you can see, because Jessica's record has the least specific hostname, it has the lowest priority. When Jessica tries to connect from, the MySQL service matches his connection to an anonymous user record (blank User value matches anyone). Because these anonymous records do not require a password, and perhaps Jessica entered his password, MySQL will reject this connection. Even if Jessica did not enter the password, he might have been given only the anonymous user's permissions (very limited) instead of the permissions he was originally granted.
The solution to this problem is either you delete the anonymous user's records (DELETE FROM user WHERE User=""), or you specify two records for all users who may connect from localhost (for example, relative to localhost and relative to the actual host name of the server):
Because it will be troublesome to maintain three user records (and the corresponding three sets of permissions) for each user, we recommend you delete anonymous users unless you need to use them to complete some special application:
Locked outside?
Just like losing the key in the car, it is indeed a hassle to forget the password after spending an hour installing and debugging a new MySQL server. Fortunately, if you have root access to the computer running MySQL, or if you are able to log in with a user running MySQL service (if you follow the instructions in Chapter 1, which refers to mysqlusr), nothing goes wrong. Follow the steps below and you can gain control of the service.
First, you have to shut down the MySQL service. Because the usual mysqladmin needs to use the password you forgot, you can only do this by killing the service process. Use the ps command or look at the service's PID file (under the MySQL data directory), determine the ID of the MySQL service's process, and then terminate it with the following command:
% kill <pid>
Here <pid> is the ID of the process of the MySQL service. This will terminate the service. Don't use kill -9 unless absolutely necessary, as this may damage your table files. If you are forced to do nothing but do this, the following will tell you how to check and repair those files.
After closing the service, you can restart it by running the safe-mysqld (using mysqld or mysqld-nt under Windows) command with the --skip-grant-tables command line option. This will indicate that the MySQL service allows free access, and it is obvious that we should use this mode to run the service as short 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
Everything is fine now--no one even knows what you did. Just like you throw the key in the car, and you are in the car yourself.
Check and repair MySQL data files
Due to temporary power outage, using kill-9 to abort the MySQL service process, or Jessica's friend idiot@%. Another mistake was made, all of which could destroy MySQL's data files. If the service is changing the file while it is being interfered, the file may leave an incorrect or inconsistent state. Because such destruction is sometimes not easy to detect, it may take a long time to find this error. So, when you find this problem, maybe all backups have the same error.
Chapter 15 of the MySQL Reference Manual describes the functionality of MySQL's own myisamchk, and how to use it to check and repair your MySQL data files. Although this chapter is recommended for everyone who wants to build a strong MySQL service, it is still necessary to discuss the key points here.
Before we proceed, you must realize that the access of the MySQL data file that the myisamchk program should be unique to check and modify. If the MySQL service is using a file and modifying the file myisamchk is checking, myisamchk will mistakenly think that an error has occurred and will try to fix it - this will cause the MySQL service to crash! In this way, to avoid this, we usually need to shut down MySQL service while working. Alternatively, you can also temporarily close the service to make a copy of the file and then work on this copy. When you're done, close the service again and replace the original file with the new file (maybe you need to use the change log for the period).
The MySQL data directory is not too difficult to understand. Each database corresponds to a subdirectory, and each subdirectory contains files corresponding to the data table in this database. Each data table corresponds to three files, which are the same as the table name, but have different extensions. A file is a table definition that saves the content and type of the data column contained in the table. The file contains the data in the table. The file contains the index of the table (for example, it might contain a lookup table to help improve querying of the primary key column of the table).
To check for errors in a table, just run myisamchk (in MySQL's bin directory) and provide the file location and table name, or the table index file name:
% myisamchk /usr/local/mysql/var/dbName/tblName
% myisamchk /usr/local/mysql/var/dbName/
Both of the above commands can perform checks 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 wildcards:
% myisamchk /usr/local/mysql/var/*/*.MYI
If there are no options, myisamchk will perform normal checks on the table file. If you have doubts about a table, but normal checks cannot find any errors, you can perform a more thorough check (but also slower!), which requires the --extend-check option:
% myisamchk --extend-check /path/to/tblName
Checking for errors is not destructive, which means you don't have to worry about performing a check on your data file making the existing problems worse. On the other hand, the fix option, although usually safe, is irrevocable to its changes to your data files. For this reason, we highly recommend that you first make a backup when trying to fix a corrupted table file and make sure your MySQL service is closed before making this backup.
When you are trying to fix an issue with a corrupted table, there are three types of fixes. If you get an error message indicating that a temporary file cannot be created, delete the file indicated by the message and try again - this is usually left over from the last repair operation.
These three fixes are as follows:
The first one is the fastest, used to fix the most common problems; the last one is the slowest, used to fix some problems that other methods cannot fix.
Check and repair MySQL data files
If the above method cannot repair a damaged table, you can also try these two tips before you give up:
If you suspect an irrepairable error occurred in the index file of the table (*.MYI), or even lost the file, you can regenerate it using the data file (*.MYD) and the data format file (*.frm). First make a copy of the data file (). Restart your MySQL service and connect to this service, and use the following command to delete the content of the table:
mysql> DELETE FROM tblName;
While deleting the contents of the table, a new index file will be created. Log out and close the service again, and overwrite the new (empty) data file with the data file you just saved. Finally, use myisamchk to perform the standard repair (the second method above), regenerating the index data based on the content of the table's data and the format file of the table.
If your table format file() is missing or an irrepairable error occurs, but you know how to regenerate this table using the corresponding CREATE TABLE statement, you can regenerate a new .frm file and use it with your data file and index file (if there is a problem with the index file, use the above method to rebuild a new one). First make a copy of the data and index file, and then delete the original file (delete all records about this table in the data directory).
Start the MySQL service and use the original CREATE TABLE file to create a new table. The new .frm file should work properly, but it is best to do a standard fix (the second method above).
Conclusion
OK, indeed, there is not much code we are usually used to in this chapter that can specifically execute a certain job. But all this work -- backing up and restoring databases, managing MySQL's access control system, checking and repairing data tables -- will help us build a MySQL database server that can stand the test of time.
In the penultimate chapter of this tutorial, we will learn more complex SQL techniques to enable our relational database server to do some work you might not have thought of before.
If all you want to do is to set up a MySQL service environment for some examples and exercises, the initialization settings we used in Chapter 1 installation are enough for you. But if you are trying to build a real database for your web site – maybe this site is important to your company – then you also need to learn something about MySQL.
Data backup is important for commercial transactions as part of an Internet-based enterprise. Unfortunately, because backup work is often not very interested in an administrator, people always cannot recognize its importance, so this work often cannot be "good enough" for an application. If you still don't understand "whether we need to back up our database" until now, or if you think "the database will be backed up with other things", then you have to take a good look at the content of this chapter. We will explain why a normal file backup scheme is far from enough for many MySQL services, and then we will introduce the "right way" to back up and restore a MySQL database.
In Chapter 1, we set up a MySQL service and connect to the database through a password ‘root’. MySQL's 'root' user (not to be confused with Unix's 'root' user, by the way) has read/write permissions for all libraries and tables. In many cases, we need to create other users who can only access certain databases and data tables, and we also need to restrict this access (for example, only direct read-only access to specified tables). In this chapter, we will learn if we use two new MySQL commands: GRANT and REVOKE to do these tasks.
In some cases, for example, due to power problems, the MySQL database may be corrupted. Such corruption does not always mean that backups must be used to restore. We will learn to solve simple database corruption by leveraging the check and repair functions of MySQL database.
Why is standard backup not enough
Like web servers, most MySQL servers must be online continuously. This makes backups of MySQL databases very important. Because MySQL service uses cache and buffers to improve efficiency of updating database files stored on disk, the content of the file may not be exactly the same as the content of the current database. Standard backup programs only include copying of the system and data files. This backup of MySQL data files does not fully meet our needs because they cannot guarantee that the copied files can be used normally when the system crashes.
Furthermore, because many databases have to receive information all day long, standard backups can only provide a "momental" image of the 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 loss is not tolerated, for example, for an e-commerce website that processes user orders.
Tools in MySQL can backup data in real time, and when the backup is in progress, it will 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 plans you have already formulated. However, like any good backup system, when you really use it, you will find that the trouble is worth it now.
In this chapter, the guidance we provide is for a computer running Linux or other Unix-based operating system. If you are using Windows, the method is basically the same, except that some of the commands must be changed.
Use mysqldump for database backup
In addition to mysqld, MySQL server and mysql (MySQL client) will also produce many useful programs during installation. For example, the mysqladmin we have seen before is the program responsible for controlling and collecting information about running MySQL services.
mysqldump is another such program. When it runs, it connects to a MySQL service (just as mysql programs do with PHP languages) and downloads the entire contents of the specified database. Then it will output a series of SQL CREATE TABLE commands and INSERT commands. Run these commands in an empty MySQL database to create a MySQL database that is exactly the same as the original database content.
By redirecting the output of mysqldump to a file, you can store a "mirror" of a database as a backup. The following command is to connect to a MySQL service running on myhost using a root user with mypass password, and store the backup of the database named dbname into the dbname_backup.sql file:
% mysqldump -h myhost -u root -pmypass dbname > dbname_backup.sql
To restore 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 create a database. The second command connects to the MySQL service and uses the usual mysql program and takes the backup file you just got as the command executed in it.
In this way, we can use mysqldump to build a backup of our database. Because mysqldump generates this backup through a connection to the MySQL service, it is definitely safer than directly accessing the database files in the MySQL data directory, because such a backup can ensure that it is a valid copy of the database, not just a copy of the database files.
The remaining problem is how to solve the synchronization between this "mirror" and a constantly updated database. To do this, you need to command the service to keep a change log.
Use the change log for incremental backup
As we mentioned earlier, in many cases, the MySQL database we use can cause data loss - sometimes even very important data. In this case, we must find a way to keep the backups we made with mysqldump using the method described above and the current database. The solution is to keep MySQL service an update log. An update log is a record of queries received by all databases that may change the content 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 like this: first use a backup (generated using the mysqldump command), and then use the variable log after the backup.
You can also use the change log to undo the error. For example, if a collaborator tells you that he used a DROP TABLE command incorrectly, you can edit the change log to delete the command and then restore it using the backup and modified change log. In this way, you can even keep other tables changing after this accident. As a precaution, you may also revoke your collaborator’s DROP permissions (you will see what to do in the next section).
Telling MySQL server to maintain a change log is very simple, 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 create a file named update.001, update.002... in the server's data directory (if you follow the instructions in Chapter 1, this directory will be /usr/local/mysql/var). A new such file will be created every time the server refreshes its log file (usually, this refers to every time the service is restarted). If you want to store your changelog to another place (usually it's a good idea - if something goes wrong with the disk containing your data directory, you can't expect it to save your backups well!), you can specify the path to the changelog.
However, if your MySQL server works continuously, you may also need some system configuration when starting the MySQL service. In this case, adding a command line selection can become difficult. Another easy way to create a change log is to add corresponding options to the MySQL configuration file.
If you are not clear about "what is a MySQL configuration file", don't worry. In fact, we have never used such a configuration file before this. To create this file, log in to Linux with the MySQL user we created in Chapter 1 (if you do it entirely according to the guidance, this should be mysqlusr). Use the text editor you are used to and create a file named in your MySQL data directory (unless you choose to install MySQL elsewhere, which should mean /usr/local/mysql/var). 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 the same way you used the --log-update option on the command line.
Obviously, for a service, the change log can take up a lot of space. Because of this reason and MySQL cannot automatically delete old log files when creating new log files, you need to manage your change log files yourself. For example, the following Unix shell script will delete all change log files that have been changed a week ago and then notify 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 last step (refresh the log file) will create a new change log, which means that the MySQL service has been online and has not received any queries to change the content of the database over the past week.
If you are an experienced user, it should be fairly simple to set up a script using the "Clock Daemon" to perform a backup of the database periodically (let's say once a week) and delete the old changelog. If you need a little help, please ask your local Unix authority. 'MySQL' by Paul The MySQL Management chapter in DuBois also has detailed guides on setting up such a system.
Assuming you have already had a backup and a copy of the change log after that, restoring your database will be very simple. After creating an empty database, use the method we discussed in the previous section to import the backup, and then use the mysql command with the --one-database command line option to import the change log. This instructs the server to run only queries in the change log related to the database we want to recover (in this example, dbname):
% mysql -u root -ppassword --one-database dbname < update.100
% mysql -u root -ppassword --one-database dbname < update.102
...
MySQL Access Control
Earlier in this tutorial, we mentioned a database called mysql, which contains this database in every MySQL service. It is used to store user related information, their passwords, and their permissions. However, before that, we had been logging in to the MySQL service using the root user, which could access all databases and data tables.
If your MySQL service is only accessed through PHP and you are careful about telling people who are root users' passwords, then the root account may be enough. 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 corresponding access rights for different users.
The MySQL access control system is described in detail in Chapter 6 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 INSERT, UPDATE, and DELETE statements, I suggest you read the relevant chapters in the MySQL guide first. Since version 3.22.11, MySQL provides an easy way to manage user access. Using the non-standard commands GRANT and REVOKE provided by MySQL, you can create a user and give it corresponding permissions without having to care about its storage form 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 to be filled in in this command. Let's introduce them one by one and finally give some examples to give you an idea of how they work together.
<privileges> is a comma-separated list of permissions you want to give. The permissions you can specify can be divided into three types:
Database/data table/data column permissions:
ALTER: Modify existing data tables (such as adding/deleting columns) and indexes.
CREATE: Create a new database or data table.
DELETE: Delete the record of the table.
DROP: Delete data tables or databases.
INDEX: Create or delete index.
INSERT: Add records in the table.
SELECT: Display/search records in the table.
UPDATE: Modify the records that already exist in the table.
Global management permissions:
FILE: Read and write files on the MySQL server.
PROCESS: Display or kill service threads belonging to other users.
RELOAD: Reload access control tables, refresh logs, etc.
SHUTDOWN: Close MySQL service.
Special permissions:
ALL: Allows to do anything (as with root).
USAGE: Only login is allowed--nothing else is allowed.
Some of the MySQL features involved in these permissions have not been seen yet, and most of them are familiar to you.
<what> Defines the area where these permissions act. *.* means that permissions are valid for all databases and data tables. dbName.* means that it is valid for all data tables in a database named dbName. Meaning only valid for data tables named tblName in dbName. You can even specify that permissions are only valid for these columns by using a list of data columns in parentheses after the permissions you are given (we will see an example like this later).
<user>Specifies the user who can apply these permissions. In MySQL, a user is specified by the user name it logs in and the hostname/IP address of the computer the user is using. Both values can be % wildcarded (for example, kevin@% will allow logging in from any machine using the username kevin to enjoy the permissions you specified).
<password> specifies the password used by the user to connect to the MySQL service. It is enclosed in square brackets, indicating that IDENTIFIED BY "<password>" is optional in the GRANT command. The password specified here will replace the user's original password. If a new user is not assigned a password, it does not need a password when it connects.
The optional WITH GRANT OPTION part in this command specifies that the user can use the GRANT/REVOKE command to assign the permissions he has to other users. Please be careful with this feature - although this problem may not be that obvious! For example, two users who both have this feature may share their permissions with each other, which may not be what you originally wanted to see.
Let's look at two examples. Create a user named dbmanager. He can use the password managedb to connect from MySQL and can only access the entire contents of the database named db (and can assign this permission to other users). This can use the following GRANT command:
mysql> GRANT ALL ON db.*
-> TO dbmanager@
-> IDENTIFIED BY "managedb"
-> WITH GRANT OPTION;
Now change the password of this user to funkychicken, the command format is as follows:
mysql> GRANT USAGE ON *.*
-> TO dbmanager@
-> IDENTIFIED BY "funkychicken";
Please note that we do not grant any additional permissions (the USAGE permissions can only allow users to log in), but the permissions that already exist in the user will not be changed.
Now let's create a new user named jessica that can connect to MySQL from any machine in the domain. It can update the user's name and email address in the database, but it does not need to check the information of other databases. That is to say, it has read-only permissions (for example, SELECT), but it can perform UPDATE operations on the name column and email column of the Users table. The command is as follows:
mysql> GRANT SELECT ON db.*
-> TO jessica@%.
-> IDENTIFIED BY "jessrules";
mysql> GRANT UPDATE (name,email) ON
-> TO jessica@%.;
Note that in the first command we use the % (wildcard) symbol when specifying the hostname that Jessica can use to connect to. In addition, we did not have the ability to pass his permissions 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 specific data column through a list of commas separated columns in parentheses after the granted permissions.
Use REVOKE
As you expected, the REVOKE command is used to remove permissions that a user has previously been granted. The syntax of 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 DROP permissions from Jessica's collaborator (for example, if he often mistakenly deletes databases and tables), you can use the following command:
mysql> REVOKE DROP ON *.* FROM idiot@%.;
Removing a user's login permission is probably the only way to use REVOKE. REVOKE ALL ON *.* will remove all permissions of the user, but he can also log in. To completely delete a user, you need to delete the corresponding record in the user table:
mysql> DELETE FROM user
-> WHERE User="idiot" AND Host="%.";
Access control skills
Due to the influence of the approach to access control systems in MySQL, you must know two characteristics before building your users.
When the established user can only log in 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 communicate using a server-side scripting language like PHP), you will probably ask yourself what should be filled in the <user> part of the GRANT command. If the service is running in. Should you set the user to username@ or username@localhost?
The answer is, you can't rely on any of these to handle any connection. In theory, if the user specifies the hostname when connecting (whether using the mysql client or PHP's mysql_connect function), this hostname must match the record in the access control system. But because you may not want to force your user to specify a hostname (in fact, the user of the mysql client may not specify a hostname at all), you'd better use the following working environment.
For situations where users need to be able to connect MySQL from the machine on which the MySQL service is running, two user records are established in the MySQL access control system: one uses the actual host name (e.g. username@), and the other uses localhost (e.g. username@localhost). Of course, you need to grant/revoke all permissions for the two users separately.
Another common problem that MySQL administrators have to face is that a user record in which the hostname uses wildcard characters (for example, the aforementioned jessica@%.) does not work. This happens generally due to the priority of records in the MySQL access control system. Specifically, the more specific the hostname has a higher priority (for example, it is the most specific, %. is relatively specific, while % is the least specific).
After a new installation, the MySQL access control system contains two anonymous user records (which allows connections with any username on the current host - these two records support connections from localhost and from the server's implementation's host name, respectively), and two root user directories. The situation we discussed above occurs when the anonymous user directory has higher priority than our new records because their hostnames are more specific.
Let's look at the contents of the user table above, we assume that Jessica's record has been added. Data rows are arranged according to the priority of the MySQL service when confirming the connection:
As you can see, because Jessica's record has the least specific hostname, it has the lowest priority. When Jessica tries to connect from, the MySQL service matches his connection to an anonymous user record (blank User value matches anyone). Because these anonymous records do not require a password, and perhaps Jessica entered his password, MySQL will reject this connection. Even if Jessica did not enter the password, he might have been given only the anonymous user's permissions (very limited) instead of the permissions he was originally granted.
The solution to this problem is either you delete the anonymous user's records (DELETE FROM user WHERE User=""), or you specify two records for all users who may connect from localhost (for example, relative to localhost and relative to the actual host name of the server):
Because it will be troublesome to maintain three user records (and the corresponding three sets of permissions) for each user, we recommend you delete anonymous users unless you need to use them to complete some special application:
Locked outside?
Just like losing the key in the car, it is indeed a hassle to forget the password after spending an hour installing and debugging a new MySQL server. Fortunately, if you have root access to the computer running MySQL, or if you are able to log in with a user running MySQL service (if you follow the instructions in Chapter 1, which refers to mysqlusr), nothing goes wrong. Follow the steps below and you can gain control of the service.
First, you have to shut down the MySQL service. Because the usual mysqladmin needs to use the password you forgot, you can only do this by killing the service process. Use the ps command or look at the service's PID file (under the MySQL data directory), determine the ID of the MySQL service's process, and then terminate it with the following command:
% kill <pid>
Here <pid> is the ID of the process of the MySQL service. This will terminate the service. Don't use kill -9 unless absolutely necessary, as this may damage your table files. If you are forced to do nothing but do this, the following will tell you how to check and repair those files.
After closing the service, you can restart it by running the safe-mysqld (using mysqld or mysqld-nt under Windows) command with the --skip-grant-tables command line option. This will indicate that the MySQL service allows free access, and it is obvious that we should use this mode to run the service as short 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
Everything is fine now--no one even knows what you did. Just like you throw the key in the car, and you are in the car yourself.
Check and repair MySQL data files
Due to temporary power outage, using kill-9 to abort the MySQL service process, or Jessica's friend idiot@%. Another mistake was made, all of which could destroy MySQL's data files. If the service is changing the file while it is being interfered, the file may leave an incorrect or inconsistent state. Because such destruction is sometimes not easy to detect, it may take a long time to find this error. So, when you find this problem, maybe all backups have the same error.
Chapter 15 of the MySQL Reference Manual describes the functionality of MySQL's own myisamchk, and how to use it to check and repair your MySQL data files. Although this chapter is recommended for everyone who wants to build a strong MySQL service, it is still necessary to discuss the key points here.
Before we proceed, you must realize that the access of the MySQL data file that the myisamchk program should be unique to check and modify. If the MySQL service is using a file and modifying the file myisamchk is checking, myisamchk will mistakenly think that an error has occurred and will try to fix it - this will cause the MySQL service to crash! In this way, to avoid this, we usually need to shut down MySQL service while working. Alternatively, you can also temporarily close the service to make a copy of the file and then work on this copy. When you're done, close the service again and replace the original file with the new file (maybe you need to use the change log for the period).
The MySQL data directory is not too difficult to understand. Each database corresponds to a subdirectory, and each subdirectory contains files corresponding to the data table in this database. Each data table corresponds to three files, which are the same as the table name, but have different extensions. A file is a table definition that saves the content and type of the data column contained in the table. The file contains the data in the table. The file contains the index of the table (for example, it might contain a lookup table to help improve querying of the primary key column of the table).
To check for errors in a table, just run myisamchk (in MySQL's bin directory) and provide the file location and table name, or the table index file name:
% myisamchk /usr/local/mysql/var/dbName/tblName
% myisamchk /usr/local/mysql/var/dbName/
Both of the above commands can perform checks 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 wildcards:
% myisamchk /usr/local/mysql/var/*/*.MYI
If there are no options, myisamchk will perform normal checks on the table file. If you have doubts about a table, but normal checks cannot find any errors, you can perform a more thorough check (but also slower!), which requires the --extend-check option:
% myisamchk --extend-check /path/to/tblName
Checking for errors is not destructive, which means you don't have to worry about performing a check on your data file making the existing problems worse. On the other hand, the fix option, although usually safe, is irrevocable to its changes to your data files. For this reason, we highly recommend that you first make a backup when trying to fix a corrupted table file and make sure your MySQL service is closed before making this backup.
When you are trying to fix an issue with a corrupted table, there are three types of fixes. If you get an error message indicating that a temporary file cannot be created, delete the file indicated by the message and try again - this is usually left over from the last repair operation.
These three fixes are as follows:
The first one is the fastest, used to fix the most common problems; the last one is the slowest, used to fix some problems that other methods cannot fix.
Check and repair MySQL data files
If the above method cannot repair a damaged table, you can also try these two tips before you give up:
If you suspect an irrepairable error occurred in the index file of the table (*.MYI), or even lost the file, you can regenerate it using the data file (*.MYD) and the data format file (*.frm). First make a copy of the data file (). Restart your MySQL service and connect to this service, and use the following command to delete the content of the table:
mysql> DELETE FROM tblName;
While deleting the contents of the table, a new index file will be created. Log out and close the service again, and overwrite the new (empty) data file with the data file you just saved. Finally, use myisamchk to perform the standard repair (the second method above), regenerating the index data based on the content of the table's data and the format file of the table.
If your table format file() is missing or an irrepairable error occurs, but you know how to regenerate this table using the corresponding CREATE TABLE statement, you can regenerate a new .frm file and use it with your data file and index file (if there is a problem with the index file, use the above method to rebuild a new one). First make a copy of the data and index file, and then delete the original file (delete all records about this table in the data directory).
Start the MySQL service and use the original CREATE TABLE file to create a new table. The new .frm file should work properly, but it is best to do a standard fix (the second method above).
Conclusion
OK, indeed, there is not much code we are usually used to in this chapter that can specifically execute a certain job. But all this work -- backing up and restoring databases, managing MySQL's access control system, checking and repairing data tables -- will help us build a MySQL database server that can stand the test of time.
In the penultimate chapter of this tutorial, we will learn more complex SQL techniques to enable our relational database server to do some work you might not have thought of before.