MySQL 服务常用操作命令

2023-08-07 13:41:28

1、MySQL 服务设置

在使用 mysql.server 命令之前需要将路径 /usr/local/mysql/support-files 添加到系统环境变量中。
```
export PATH=$PATH:/usr/local/mysql/support-files
```
在使用 mysql 命令之前需要将路径 /usr/local/mysql/bin 添加到系统环境变量中。
```
export PATH=$PATH:/usr/local/mysql/bin
```
具体设置请参照《添加系统环境变量》章节。

2、MySQL 服务常用操作

2.1 MySQL 服务控制命令

MySQL 服务控制命令

# 启动 MySQL 服务

$ sudo mysql.server start

	Starting MySQL

	.. SUCCESS!

# 停止 MySQL 服务

$ sudo mysql.server stop

	Shutting down MySQL

	.. SUCCESS!

# 重启 MySQL 服务

$ sudo mysql.server restart

	Shutting down MySQL

    .. SUCCESS!

    Starting MySQL

    .. SUCCESS!

# 重新加载

$ sudo mysql.server reload

    SUCCESS! Reloading service MySQL

# 强制重新加载

$ sudo mysql.server force-reload

	SUCCESS! Reloading service MySQL

# 查看 MySQL 服务状态

$ mysql.server status

	ERROR! Multiple MySQL running but PID file could not be found (61998 62083 )

2.2 MySQL 服务连接命令

MySQL 服务连接命令

# 登录 MySQL 服务

# mysql -u 用户名 -p 密码

$ mysql -u root -p

	Enter password:

	Welcome to the MySQL monitor.  Commands end with ; or \g.

	Your MySQL connection id is 930

	Server version: 8.0.11 MySQL Community Server - GPL

    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its

    affiliates. Other names may be trademarks of their respective

    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>

# 登出 MySQL 服务

> exit

	Bye

3、MySQL 服务帮助操作

3.1 按照层次看帮助

如果不知道帮助能够提供什么，那么就可以直接用 “? contents” 命令来显示所有可供查询的分类，对于分类可以使用 “? 分类名称” 的方式针对用户感兴趣的内容做进一步的查看，通过这种 “? 类型别名” 的方式，就可以一层层的往下查找用户所关心的主题内容。

> ? contents

You asked for help about help category: "Contents"

For more information, type 'help <item>', where <item> is one of the following

categories:

   Account Management

   Administration

   Components

   Compound Statements

   Data Definition

   Data Manipulation

   Data Types

   Functions

   Functions and Modifiers for Use with GROUP BY

   Geographic Features

   Help Metadata

   Language Structure

   Plugins

   Storage Engines

   Table Maintenance

   Transactions

   User-Defined Functions

   Utility

3.2 快速查阅帮助

在实际应用中，如果需要快速查阅某项语法时，可以使用 “? 关键字” 方式进行快速查询。

> ? insert

Name: 'INSERT'

Description:

Syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [PARTITION (partition_name [, partition_name] ...)]

    [(col_name [, col_name] ...)]

    {VALUES | VALUE} (value_list) [, (value_list)] ...

    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [PARTITION (partition_name [, partition_name] ...)]

    SET assignment_list

    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    [PARTITION (partition_name [, partition_name] ...)]

    [(col_name [, col_name] ...)]

    SELECT ...

    [ON DUPLICATE KEY UPDATE assignment_list]

value:

    {expr | DEFAULT}

value_list:

    value [, value] ...

assignment:

    col_name = value

assignment_list:

    assignment [, assignment] ...

INSERT inserts new rows into an existing table. The INSERT ... VALUES

and INSERT ... SET forms of the statement insert rows based on

explicitly specified values. The INSERT ... SELECT form inserts rows

selected from another table or tables. INSERT with an ON DUPLICATE KEY

UPDATE clause enables existing rows to be updated if a row to be

inserted would cause a duplicate value in a UNIQUE index or PRIMARY

KEY.

For additional information about INSERT ... SELECT and INSERT ... ON

DUPLICATE KEY UPDATE, see [HELP INSERT SELECT], and

http://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html.

In MySQL 8.0, the DELAYED keyword is accepted but ignored by the

server. For the reasons for this, see [HELP INSERT DELAYED],

Inserting into a table requires the INSERT privilege for the table. If

the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes

an UPDATE to be performed instead, the statement requires the UPDATE

privilege for the columns to be updated. For columns that are read but

not modified you need only the SELECT privilege (such as for a column

referenced only on the right hand side of an col_name=expr assignment

in an ON DUPLICATE KEY UPDATE clause).

When inserting into a partitioned table, you can control which

partitions and subpartitions accept new rows. The PARTITION option

takes a list of the comma-separated names of one or more partitions or

subpartitions (or both) of the table. If any of the rows to be inserted

by a given INSERT statement do not match one of the partitions listed,

the INSERT statement fails with the error Found a row not matching the

given partition set. For more information and examples, see

http://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.

URL: http://dev.mysql.com/doc/refman/8.0/en/insert.html

3.3 常用的网络资源

MySQL 官网可以下载到各个版本的 MySQL 以及相关客户端开发工具等。
MySQL 在线手册提供了目前最权威的 MySQL 数据库及工具的在线手册。
MySQL bug 列表可以查看到 MySQL 已经发布的 bug 列表，或者向 MySQL 提交 bug 报告。
MySQL 的最新消息通常会发布各种关于 MySQL 的最新消息。

码农公寓