Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to manage MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to manage MySQL.

For content-driven websites, the key to good design is relational databases. In this tutorial, we have used the MySQL relational database management system (RDBMS) to build our database. MySQL is a popular choice for website developers, not only because it is free for non-commercial applications on any platform, but also because it is easy to build and use. As we saw in the first chapter, with the correct guidance, a new user can set up a MySQL service and run it in no more than 30 minutes (even 10 minutes for an experienced user!) .

If all you want to do is set up a MySQL service environment to do some examples and exercises, then the initialization settings we used in the first chapter of the installation are sufficient for you. However, if you want to build a real database for Web sites-- which may be important to your company-- then you also need to learn some knowledge about MySQL.

Data backup is important for business transactions that are part of an Internet-based enterprise. Unfortunately, because backup work is often of little interest to an administrator, people always fail to recognize its importance, so it is often not "good enough" for an application. If you still don't understand "do we need to back up our database", or if you think "the database will be backed up with other things", then you should take a good look at this chapter. We will explain why a common file backup scheme is not enough for many MySQL services, and then we will introduce the "right way" to back up and restore an MySQL database.

In the first chapter, we set up a MySQL service and connect to the database through a 'root' with password. MySQL's' root' user (not to be confused with Unix's' root' user, by the way) has read / write access to all libraries and tables. In many cases, we need to establish other users who can only access certain databases and tables, and we also need to restrict this access (for example, direct read-only access to specified tables). In this chapter, we will learn how to do this using two new MySQL commands: GRANT and REVOKE.

In some cases, for example, due to power problems, the MySQL database may be corrupted. Such damage does not always mean that a backup must be used to recover. We will learn how to use the inspection and repair capabilities of the MySQL database to solve simple database corruption.

Why standard backups are not enough

Like Web servers, the vast majority of MySQL servers must be online continuously. This makes the backup of MySQL database 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 files may not be exactly the same as those of the current database. The standard backup program only includes the copy of the system and data files, this kind of backup of MySQL data files can not fully meet our needs, because they can not guarantee that the copied files can be used normally in the event of a system crash.

In addition, because many databases have to accept information all day, standard backups can only provide an "instant" image 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, for example, for an e-commerce site's database that handles user orders, such a loss is intolerable.

The tools in MySQL can back up the data in real time without affecting 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 developed. However, like any good backup system, when you actually use it, you will find that the trouble is worth it.

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 have to be changed.

Using mysqldump for database backup

In addition to mysqld, the MySQL server and mysql (the client of MySQL) also produce a lot of useful programs during installation. For example, the mysqladmin we saw earlier is the program responsible for controlling and collecting information about the running MySQL service.

Mysqldump is another such program. When it runs, it connects to a MySQL service (just as mysql programs and the PHP language do) 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, and you can build 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 a database as a backup. The following command connects to a MySQL service running on myhost with a root user with a password of mypass and stores a backup of the database named dbname in a dbname_backup.sql file:

Mysqldump-h myhost-u root-pmypass dbname > dbname_backup.sql

To restore such a database, simply run the following command:

% mysqladmin-h myhost-u root-pmypass create dbname

% mysql-h myhost-u root-pmypass dbname

< dbname_backup.sql 第一个命令使用mysqladmin程序建立一个数据库。第二个命令连接到MySQL服务并使用通常的mysql程序，并将刚才得到的备份文件作为其中执行的命令。 通过这种方法，我们可以使用mysqldump建立我们数据库的备份。因为mysqldump通过与MySQL服务的连接产生这个备份，这肯定要比直接访问MySQL数据目录下的数据库文件来得更为安全，因为这样的备份可以确保是数据库的一个有效的拷贝，而不仅仅是数据库文件的拷贝。 剩下来的问题就是如何解决这个"镜像"与一个不断更新的数据库之间的同步。要做到这一点，你需要命令服务保持一个变更日志。 利用变更日志进行增量备份 正如我们前面提到的，在很多情况下，我们使用的MySQL数据库会造成数据的丢失--甚至有的时候会丢失很重要的数据。在这样的情况下，我们必须找到一种方法保持我们使用上面介绍的方法用mysqldump制作的备份与当前数据库之间的同步。而解决方案就是让MySQL服务维持一个更新日志。一个更新日志是一个关于所有数据库接受到的可能改变数据库内容的查询的记录。这将包括INSERT、UPDATE和CREATE TABLE语句，但是不包括SELECT语句。 通常的想法是维持一个变更日志，这样当数据库崩溃时，你的恢复过程应该是这样的：首先使用备份(使用mysqldump命令产生)，然后使用备份之后的变量日志。 你也可以使用变更日志撤消错误操作。例如，如果一个合作者告诉你他错误地使用了一个DROP TABLE命令，你可以对变更日志进行编辑以删除这个命令，然后使用备份和修改过的变更日志进行恢复。通过这种方法，你甚至可以保持这次意外事故之后其它表的变化。作为预防措施，你也许还要收回你的合作者的 DROP权限(在下一部分你将看到该怎么做)。 告诉MySQL服务器维持一个变更日志是非常简单的，你只需要在服务的命令行中增加一个选项： % safe-mysqld --log-update=update 上面的命令启动MySQL服务，并告诉它在服务器的数据目录下(如果你依照第一章中指导配置你的服务器的话，这个目录将是/usr/local/mysql/var)建立名为update.001、update.002……的文件。一个新的这样的文件将在服务器每一次刷新它的日志文件时被建立(通常，这是指服务每一次重启动时)。如果你想将你的变更日志存储到另一个地方(通常这是一个好主意--如果包含你的数据目录的磁盘出了问题，你肯定不能指望它还能好好保存你的备份！)，你可以指定变更日志的路径。 但是，如果你的MySQL服务器是不间断地工作的，在启动MySQL服务时你也许还需要一些系统配置。在这种情况下，增加一个命令行选择可能变得很困难。建立变更日志的另一个简单的方法是在MySQL配置文件中增加相应的选项。 如果你还不清楚"什么是MySQL配置文件"，不要担心。事实上，在此之前，我们一直没用到过这样的配置文件。要建立这个文件，以我们在第一章中建立的MySQL用户(如果你是完全根据指导做的，这应该是mysqlusr)登录到Linux。使用你习惯的文本编辑器，在你的MySQL数据目录下(除非你选择了其它地方安装MySQL，这应该是指/usr/local/mysql/var)建立一个名为my.cnf的文件。在这个文件中，输入下面一行： [mysqld]log-update=/usr/backups/mysql/update 当然，你可以自由地指定你的日志文件所写入的位置。保存这个文件并重启你的MySQL服务。从现在开始，MySQL服务运行的情况将和你在命令行中使用了--log-update选项一样。 很明显，对于一个服务来说，变更日志可能占用大量的空间。因为这个原因以及MySQL不能自动地在建立新的日志文件删除旧的日志文件，你需要自己管理你的变更日志文件。例如，下面的Unix shell脚本，会删除所有一星期以前更改的变更日志文件，然后通知MySQL刷新它的日志文件。 #! /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 如果当前的日志文件被删除，最后一步(刷新日志文件)将建立一个新的变更日志，这意味着MySQL服务一直在线，而且在过去的一周中，没有收到任何改变数据库内容的查询。 如果你是一个有经验的用户，使用"时钟守护程序"设置一个脚本来定期(比方说，每周一次)执行数据库的备份并删除旧的变更日志应该是相当简单的。如果你还需要一点帮助，请教你当地的Unix权威。'MySQL' by Paul DuBois中的MySQL管理一章中对设置这样的一个系统也有详细的指南。 假定你已经有了一个备份以及在此之后的变更日志的一个拷贝，恢复你的数据库将是非常简单的。在建立一个空数据库后应用我们在上一节中讨论的方法导入备份，然后使用带--one-database命令行选项的mysql命令导入变更日志。这会指示服务器仅仅运行变更日志中与我们想要恢复的数据库(在这个例子中是指dbname)相关的查询： % mysql -u root -ppassword --one-database dbname < update.100 % mysql -u root -ppassword --one-database dbname < update.102 ... MySQL访问控制 在这个教程的早些时候，我们曾经提到一个叫做mysql的数据库，在每一个MySQL服务中都包含这个数据库，它是用来保存用户的相关信息、他们的口令以及他们的权限的。但是，在此之前，我们一直使用root用户登录到MySQL服务，这个用户可以访问所有的数据库和数据表。 如果你的MySQL服务仅仅被通过PHP访问，而用你对于将root用户的口令告诉什么人很小心，那么root帐号可能已经足够了。但是，如果一个MySQL服务是被许多人共享的，(例如，一个Web主机希望对它的每一个用户提供同一个MySQL服务)，为不同的用户设置相应的访问权限就显得很重要了。 在MySQL参考手册的第六章中详细介绍了MySQL的访问控制系统。从原理上来说，用户的访问是由mysql数据库中的五个数据表来管理的：user、db、host、tables_priv和columns_priv。如果你想直接使用INSERT、UPDATE和DELETE语句来编辑这些表，我建议你先阅读一下MySQL指南中的相关章节。而从3.22.11版本开始，MySQL提供了简单的方法来管理用户的访问。使用MySQL提供的非标准的命令GRANT和REVOKE，你可以建立用户并赋予其相应的权限，而不必关心它在前面提到的五个表中的存储形式。 使用GRANT GRANT命令用来建立新用户，指定用户口令并增加用户权限。其格式如下： mysql>

GRANT

ON

-> TO [IDENTIFIED BY ""]

-> [WITH GRANT OPTION]

As you can see, there is a lot to fill in in this command. Let's introduce them one by one and finally give you some examples to give you an idea of how they work together.

Is a comma-separated list of permissions you want to grant. 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: deletes the record of the table.

DROP: delete a data table or database.

INDEX: create or delete an index.

INSERT: add the record of the table.

SELECT: show / search 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 service threads that belong to other users.

RELOAD: reload access control tables, refresh logs, etc.

SHUTDOWN: turn off the MySQL service.

Special permissions:

ALL: allow to do anything (like root).

USAGE: only login is allowed-nothing else is allowed.

These permissions involve the characteristics of MySQL, some of which we haven't seen yet, and most of them are familiar to you.

Defines the area where these permissions work. *. * means that permissions are valid for all databases and tables. DbName.* means that it is valid for all tables in the database named dbName. DbName.tblName means that it is only valid for the data table named tblName in the name dbName. You can even specify that permissions are valid only for those columns by using a list of data columns in parentheses after the granted permissions (we'll see an example of this later).

Specify the users to whom these permissions can be applied. In MySQL, a user is specified by the user name it logs in to and the hostname / IP address of the computer the user uses. Both values can use% wildcards (for example, kevin@% will allow you to log in from any machine with the user name kevin to have the permissions you specify).

Specifies the password that the user uses to connect to the MySQL service. It is enclosed in square brackets to indicate that IDENTIFIED BY "" is optional in the GRANT command. The password specified here replaces the user's original password. If a new user is not assigned a password, he does not need a password when he connects.

The optional WITH GRANT OPTION section of this command specifies that a user can use the GRANT/REVOKE command to grant his permissions to other users. Please use this feature carefully-although the problem may not be so obvious! For example, two users who both have this feature may share their permissions with each other, which may not be what you wanted to see.

Let's look at two examples. Set up a user named dbmanager who can connect to MySQL from server.host.net using the password managedb and can only access the entire contents of the database named db (and can grant this permission to other users), using the following GRANT command:

Mysql > GRANT ALL ON db.*

-> TO dbmanager@server.host.net

-> IDENTIFIED BY "managedb"

-> WITH GRANT OPTION

Now change the 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 grant any additional permissions (the USAGE permissions only allow users to log in), but the permissions that users already have will not be changed.

Now let's create a new user named jessica who can connect to MySQL from any machine in the host. net domain. He can update the names and email addresses of users in the database, but he does not need to consult the information of other databases. That is, he has read-only access to the db database (for example, SELECT), but he can UPDATE the name and email columns of the Users table. The command 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

Notice in the first command that we used the% (wildcard) symbol to specify the hostname that Jessica can use to connect. In addition, we didn't give him the ability to pass his permissions to other users because we didn't bring WITH GRANT OPTION at the end of the command. The second command demonstrates how to grant permissions to specific data columns through a comma-separated list of columns in parentheses after the granted permissions.

Use REVOKE

As you might expect, the REVOKE command is used to remove permissions previously granted to a user. The syntax of the command is as follows:

Mysql > REVOKE [()]

-> ONFROM

The functions of each part of this command are the same as in the GRANT command above. To remove DROP permissions from a partner of Jessica (for example, if he often incorrectly deletes databases and tables), you can use the following command:

Mysql > REVOKE DROP ON *. * FROM idiot@%.host.net

Removing a user's login privileges 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 delete a user completely, you need to delete the corresponding record in the user table:

Mysql > DELETE FROM user

-> WHERE User= "idiot" AND Host= ".host.net"

Access control skills

Due to the influence of the way the access control system works in MySQL, you must know two characteristics before creating your users.

When established users can only log in to the MySQL service from the computer on which the MySQL service is running (that is, you need them to telnet to the server and run MySQL client programs there, or to communicate using a server-side scripting language like PHP), you will probably ask yourself what should be filled in the GRANT command. If the service is running on www.host.net. Should you set the user to username@www.host.net or username@localhost?

The answer is that you can't rely on any of them to handle any connection. In theory, if the user specifies a hostname when connecting (whether using the mysql client or PHP's 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 a hostname (in fact, mysql client users may not specify a hostname at all), you'd better use the following work environment.

For cases where users need to be able to connect to MySQL from the machine on which the MySQL service is running, establish two user records 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 permissions for the two users.

Another common problem for MySQL managers is that a user record in which the hostname uses wildcards (for example, jessica@%.host.net mentioned earlier) does not work. This is usually due to the priority recorded in the MySQL access control system. Specifically, the more specific the hostname, the higher the 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 (it allows connections to be made using any user name on the current host-- these records support connections from the localhost connection and the host name implemented from the server, respectively), and two root user directories. The situation we discussed above occurs because anonymous user directories have a higher priority than our new record because their hostnames are more specific.

Let's take a look at the contents of the user table on www.host.net, and we assume that a record for Jessica has been added. The data rows are arranged according to the priority of the MySQL service when confirming the connection:

As you can see, because the hostname of Jessica's record is the least specific, 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 maybe Jessica entered his password, MySQL will reject the connection. Even if Jessica does not enter a password, he may only be given the (very limited) privileges of an anonymous user, not the permissions he was originally given.

The solution to this problem is either to delete the records of anonymous users (DELETE FROM user WHERE User= ""), or to specify two more records for all users who may connect from localhost (for example, relative to localhost and the actual hostname relative to the server):

Because it can be troublesome to maintain three user records (and corresponding three sets of permissions) for each user, we recommend that you delete anonymous users unless you need to use them to complete some special application:

Locked out?

Just like losing your keys in your car, it can be troublesome to forget your password after spending an hour installing and debugging a new MySQL server. Fortunately, if you have root access to the computer where MySQL is running, or if you can log in using the user running the MySQL service (if you follow the instructions in Chapter 1, this means mysqlusr), then there will be no problem. Follow the steps below to gain control of the service.

First, you must shut down the MySQL service. Because the usual mysqladmin requires a password you forget, you can only do this by killing the process of the service. Use the ps command or look at the service's PID file (under the MySQL data directory), determine the ID of the process of the MySQL service, and then terminate it with the following command:

% kill

Here is the ID of the process of the MySQL service. This will allow the service to be terminated. Do not use kill-9 unless absolutely necessary, as it may damage your table file. If you are forced to do so, the following will show you how to check and repair those files.

After shutting down the service, you can restart it by running safe-mysqld with the-skip-grant-tables command line option (using mysqld or mysqld-nt under Windows). This will instruct the MySQL service to allow free access, and obviously, 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 reload the authorization table to receive the new password:

% mysqladmin flush-privileges

Everything's all right now-no one will even know what you've done. Just like you left your keys in the car, and you were in the car yourself.

Check and repair MySQL data files

Due to a temporary power outage, using kill-9 to abort the MySQL service process, or Jessica's friend idiot@%.host.net made another mistake, all of which may destroy MySQL's data files. If the service is changing the file when it is interfered, the file may leave an incorrect or inconsistent state. Because such damage is sometimes not easy to detect, it may be a long time later when you find this mistake. So, when you find this problem, maybe all backups have the same error.

Chapter 15 of the MySQL reference manual describes the functions of MySQL's native myisamchk and how to use it to check and repair your MySQL data files. Although this chapter is recommended for anyone who wants to build a strong MySQL service, it is necessary to discuss the main points here.

Before we proceed, you must be aware that the myisamchk program should have unique access to the MySQL data files used to check and modify. If the MySQL service is using a file and modifies the file that myisamchk is checking, myisamchk will mistakenly think that an error has occurred and will try to fix it-- which will cause the MySQL service to crash! In this way, to prevent this from happening, we usually need to shut down the MySQL service at work. Alternatively, you can temporarily shut down the service to make a copy of the file, and then work on that copy. When you are done, turn off the service again and replace the original file with the new file (you may also need to use the change log during the period).

The MySQL data catalog is not too difficult to understand. Each database corresponds to a subdirectory, and each subdirectory contains files corresponding to the data tables in the database. Each data table corresponds to three files with the same name as the table, but with a different extension. The tblName.frm file is the definition of the table, which holds the contents and types of 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 may contain the lookup table to help improve the query on the primary key column of the table).

To check for errors in a table, simply run myisamchk (under MySQL's 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

Both of the above commands perform a check on the specified table. To check all the tables in the database, you can use wildcards:

% 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 performs a normal check on the table file. If you have doubts about a watch, but a regular check cannot find any errors, you can perform a more thorough check (but also more slowly!). Which requires the-- extend-check option:

% myisamchk-- extend-check / path/to/tblName

Checking for errors is non-destructive, which means you don't have to worry that performing checks on your data files will make existing problems worse. On the other hand, the repair option, although usually safe, cannot be undone to your data file. For this reason, we strongly recommend that you first make a backup when trying to repair a corrupted table file and make sure that your MySQL service is turned off before making this backup.

When you try to fix a problem with a broken table, there are three types of repair. If you get an error message 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.

The three repair methods are as follows:

% myisamchk-- recover-- quick / path/to/tblName

% myisamchk-- recover / path/to/tblName

% myisamchk-- safe-recover / path/to/tblName

The first is the fastest to fix the most common problems, while the last is the slowest to fix problems that cannot be fixed by other methods.

Check and repair MySQL data files

If the above method cannot repair a damaged table, you can also try the following two techniques before you give up:

If you suspect that there is an irreparable error in the table's index file (* .MYI), or even lose 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). Restart your MySQL service and connect to it and delete the contents of the table using the following command:

Mysql > DELETE FROM tblName

When you delete the contents of the table, a new index file is created. Log out and shut down the service again, and then overwrite the new (empty) data file with the data file you just saved (tblName.MYD). Finally, use myisamchk to perform a standard fix (the second method above) to regenerate the index data based on the contents of the table's data and the table's format file.

If your table format file (tblName.frm) is missing or an irreparable error has occurred, but you know how to use the appropriate CREATE TABLE statement to regenerate the table, 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 the table in the data directory).

Start the MySQL service and create a new table using the original CREATE TABLE file. The new .frm file should work, but you'd better perform a standard fix (the second method above). At this point, I believe you have a deeper understanding of "how to manage MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.