Mysql 简明教程

MySQL - Administration

MySQL Server 是在数据库系统中主要执行 SQL 查询的程序。因此，有必要优化此服务器的工作。通常的 MySQL 管理通常包括以下概念：

MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it becomes necessary to optimize the working of this server. The general MySQL administration usually includes concepts like:

  1. Starting and Stopping the Server

  2. User Security

  3. Database Maintenance

  4. Backup & Restore

Start MySQL Server

我们需要首先在设备上启动 MySQL 服务器才能使用它。一种方法是执行以下命令（以管理员身份运行）在命令提示符中：

We need to first start the MySQL server on the device in order to use it. One way to do so, is by executing the following command on the command prompt (run as an administrator) −

mysqld

我们还可以通过 Windows 提供的服务来启动服务器，并遵循以下步骤：

We can also start the server by going through the services provided by the Windows and follow the steps below −

  1. Open the 'Run' Window using the 'Windows+R' shortcut and run 'services.msc' through it.

  2. Then, select the "MySQL80" service click "start" to start the server.

Stop, Pause, Restart MySQL Server

现在，如果你想暂停、停止或重新启动已运行的 MySQL 服务器，则可以通过打开 Windows 服务并选择所需操作来执行：

Now, if you want to pause, stop or restart an already running MySQL server, then you can do it by opening the Windows Services and selecting the desired action −

要停止 MySQL 服务器，请选择“停止”选项，如下图所示：

To stop the MySQL Server, select the 'stop' option as shown in the image below −

要暂停 MySQL 服务器，请选择“暂停”选项，如下图所示：

To pause the MySQL Server, select the 'pause' option as shown in the image below −

我们还可以根据需要通过选择“重新启动”选项来重新启动 MySQL 服务器，如下图所示：

We can also restart the MySQL server as needed, by selecting the 'restart' option as shown below −

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.

在以下示例中，我们在“localhost”上使用密码 guest123 创建了一个新用户 guest 。我们还授予了执行 SQL 查询所需的所有权限：

In the following example, we are creating a new user guest with the password guest123 on the 'localhost'. We are also granting all privileges required to executing SQL queries −

CREATE USER 'guest'@'localhost' IDENTIFIED BY 'guest123';

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

Now, execute 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.

FLUSH PRIVILEGES;

最后，您需要向此新用户授予所有权限以执行 SQL 查询。

Finally, you need to grant all privileges to this new user to execute SQL queries.

GRANT ALL PRIVILEGES ON * . * TO 'sample'@'localhost';

您还可以通过使用 UPDATE 查询将“user”表中下列列的值设置为“Y”来向新用户指定其他权限。

You can also specify other privileges to a new user by setting the values of following columns in 'user' table to 'Y' using the 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

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 Commands

以下是一些重要的 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 database_name − This will be used to select a database in the MySQL.

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

  3. SHOW TABLES − Displays the list of the tables in the current database.

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

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

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