Mysqli 简明教程

MySQLi - Administration

Running and Shutting down MySQL Server

首先检查你的 MySQL 服务器是否正在运行。你可以使用以下命令检查它 −

First check if your MySQL server is running or not. You can use the following command to check it −

ps -ef | grep mysqld

如果您的 MySQL 正在运行，那么您将在结果中看到 mysqld 进程。如果服务器没有运行，那么您可以使用以下命令启动服务器：

If your MySql is running, then you will see mysqld process listed out in your result. If server is not running, then you can start it by using the following command −

root@host# cd /usr/bin
./safe_mysqld &

现在，如果您想关闭已经运行的 MySQL 服务器，那么您可以使用以下命令执行此操作：

Now, if you want to shut down an already running MySQL server, then you can do it by using the following command −

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Setting Up a MySQL User Account

若要向 MySQL 添加新用户，您只需在数据库 mysql 中的 user 表中添加一个新条目。

For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.

以下程序是使用密码 guest123; 为新用户 guest 添加 SELECT、INSERT 和 UPDATE 权限的示例，SQL 查询如下：

The following program is an example of adding a new user guest with SELECT, INSERT and UPDATE privileges with the password guest123; the SQL query is −

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user
   (host, user, password,
   select_priv, insert_priv, update_priv)
   VALUES ('localhost', 'guest',
   PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
|    host   |   user  |     password     |
+-----------+---------+------------------+
| localhost |  guest  | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

添加新用户时，请记住使用 MySQL 提供的 PASSWORD() 函数对新密码进行加密。正如您在上面的示例中看到的，密码 mypass 被加密为 6f8c114b58f2ce9e。

When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL. As you can see in the above example, the password mypass is encrypted to 6f8c114b58f2ce9e.

注意 FLUSH PRIVILEGES 语句。这会告诉服务器重新加载授予表。如果您不使用它，那么至少在服务器重新启动之前，您将无法使用新用户帐户连接到 MySQL。

Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don’t use it, then you won’t be able to connect to MySQL using the new user account at least until the server is rebooted.

您还可以通过在执行 INSERT 查询时将用户表中以下列的值设置为“Y”来为新用户指定其他权限，或者稍后使用 UPDATE 查询更新它们。

You can also specify other privileges to a new user by setting the values of following columns in user table to 'Y' when executing the INSERT query or you can update them later using UPDATE query.

  1. Select_priv

  2. Insert_priv

  3. Update_priv

  4. Delete_priv

  5. Create_priv

  6. Drop_priv

  7. Reload_priv

  8. Shutdown_priv

  9. Process_priv

  10. File_priv

  11. Grant_priv

  12. References_priv

  13. Index_priv

  14. Alter_priv

添加用户帐户的另一种方法是使用 GRANT SQL 命令。以下示例将为名为 TUTORIALS 的特定数据库添加带有密码 zara123 的用户 zara 。

Another way of adding user account is by using GRANT SQL command. The following example will add user zara with password zara123 for a particular database, which is named as TUTORIALS.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   → ON TUTORIALS.*
   → TO 'zara'@'localhost'
   → IDENTIFIED BY 'zara123';

这还将在名为 user 的 MySQL 数据库表中创建一个条目。

This will also create an entry in the MySQL database table called as user.

NOTE − 在 SQL 命令的末尾加上分号 (;) 之前，MySQL 不会终止一个命令。

NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command.

The /etc/my.cnf File Configuration

在大部分情况下，您不应该触碰此文件。默认情况下，它将具有以下条目：

In most of the cases, you should not touch this file. By default, it will have the following entries −

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

在这里，您可以为错误日志指定一个不同的目录，否则您不应该更改此表中的任何条目。

Here, you can specify a different directory for the error log, otherwise you should not change any entry in this table.

Administrative MySQL Command

以下是一些重要的 MySQL 命令的列表，您将随时使用它们来处理 MySQL 数据库：

Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database −

  1. USE Databasename − This will be used to select a database in the MySQL workarea.

  2. SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS.

  3. SHOW TABLES − Shows the tables in the database once a database has been selected with the use command.

  4. SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.

  5. SHOW INDEX FROM tablename − Presents the details of all indexes on the table, including the PRIMARY KEY.

  6. SHOW TABLE STATUS LIKE tablename\G − Reports details of the MySQL DBMS performance and statistics.