运行和关闭MySQL服务器

首先检查MySQL服务器正在运行与否。在Linux系统下可以使用下面的命令来检查:

$ ps -ef | grep mysqld

如果MySQL正在运行,那么会看到列出来的 mysqld 进程。如果服务器没有运行,那么可以使用下面的命令来启动它:

$ /etc/init.d/mysql start

现在,如果想关闭一个已经运行的 MySQL 服务器,那么可以使用下面的命令:

$ /etc/init.d/mysql stop

设置MySQL用户帐户

要添加一个新用户到MySQL,只需要在数据库中的新记录添加到用户表:mysql 下面是添加新用户:xyz,密码:xyz123; SQL 语句是:

[root@linuxxyz ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.38 Source distribution

Copyright (c) 2000, 2017, 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> use mysql #进入mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE USER 'xyz'@'localhost' IDENTIFIED BY 'xyz123'; #创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; #加载授权表
Query OK, 0 rows affected (0.00 sec)

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

给普通用户指定库的权限

下面是添加库:xyz_db,并指定用户xyz,有SELECT, INSERT and UPDATE权限

[root@linuxxyz ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.38 Source distribution

Copyright (c) 2000, 2017, 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> create database xyz_db;  #创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> use mysql; #进入mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant select,insert,update on xyz_db.* to 'xyz'@'%' identified by 'xyz123'; #为用户赋权
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; #加载授权表
Query OK, 0 rows affected (0.00 sec)

管理 mysql 命令

这里是重要的MySQL命令,经常在MySQL数据库的管理或工作中使用:

  • USE Databasename : 用于选择在MySQL工作区指定的数据库。
  • SHOW DATABASES: 列出了MySQL数据库管理系统中的所有可访问的数据库。
  • SHOW TABLES: 显示已经选择数据库中的表的命令。
  • SHOW COLUMNS FROM tablename: 显示属性,属性类型,关键信息,NULL是否被允许,默认值和其它的表信息。
  • SHOW INDEX FROM tablename: 提供所有指标的详细信息表,其中包括PRIMARY KEY.
  • SHOW TABLE STATUS LIKE tablename\G: 报告MySQL的数据库管理系统的性能和统计数据的详细信息。