@cdmonkey 2016-04-22T06:20:10.000000Z 字数 8605 阅读 1367

MySQL基础操作（一）

`数据库`

以下操作适合MySQL5.1与5.5的环境。

一、启动与关闭MySQL

1、单实例MySQL服务的启动与关闭

方法一：常规的服务启停方式（推荐）

1.1、常规方法启动数据库

[root@MySQL-B ~]# /etc/init.d/mysqld start
Starting MySQL...                                    [  OK  ]
//服务启动成功。
//我们查看一下端口：
[root@MySQL-B ~]# ss -lnt|grep 3306
LISTEN     0      50                        *:3306                     *:*
//接下来查看一下服务进程：
[root@MySQL-B ~]# ps -ef|grep mysql|grep -v grep
root      5167  5001  0 12:12 pts/1    00:00:00 mysql
root      5320     1  0 14:43 pts/0    00:00:00 /bin/sh /application/mysql//bin/mysqld_safe --datadir=/application/mysql/data/ --pid-file=/application/mysql/data//MySQL-B.pid
mysql     5563  5320  2 14:43 pts/0    00:00:07 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/application/mysql/data/ --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/application/mysql/data//MySQL-B.err --pid-file=/application/mysql/data//MySQL-B.pid --socket=/tmp/mysql.sock --port=3306
//注意观察上面出现的进程，其中包括mysqld_safe和mysqld进程。

MySQL启动的基本原理：
/etc/init.d/mysqld是一个shell启动脚本，其最终会调用mysqld_safe脚本，最后调用mysqld服务启动MySQL，下面是/etc/init.d/mysqld脚本中调用mysqld_safe的语句：

[root@MySQL-B ~]# vim /etc/init.d/mysqld
bindir=/usr/local/mysql/bin
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

初始化数据库时，MySQL系统提示的启动方法：

mysqld_safe  --user=mysql &

注意：

当找回root密码时，会经常使用mysqld_safe --user=mysql &带参数启动；

我们自己开发脚本启动数据库时可能会用到这个启动方式；

其实使用/etc/init.d/mysqld或者mysqld_safe --user=mysql &来启动，其实质是一样的。

1.2、常规方法关闭数据库

关闭MySQL的指令：

[root@MySQL-B ~]# /etc/init.d/mysqld stop    
Shutting down MySQL...                                       [  OK  ]
//提示：使用编译及二进制安装的MySQL，其启动脚本mysqld是由源码包目录中的“support-files/mysql.server”文件复制而来的。

MySQL启动的基本原理：

kill $mysqld_pid
//这仍旧是mysqld脚本中的语句，可以看出基本上服务停止的实质就是杀掉进程。

除此之外，我们还可以强制关闭MySQL服务：

killall mysqld
killall -9 mysqld
pkill mysqld

提示：

使用killall来关闭进程时，要直到出现mysqld: no process killed才表示进程被彻底的杀掉；

方法二的启动与关闭命令一般在生产环境中不会使用，特别是关闭命令；

强调：尽量不要这样粗暴的强制杀掉数据库服务进程，在生产中的高并发环境下容易丢失数据。
由此导致的故障案例请参阅老师的博文：
http://oldboy.blog.51cto.com/2561410/1431161
http://oldboy.blog.51cto.com/2561410/1431172
http://www.cnblogs.com/peida/archive/2012/12/20/2825837.html

优雅（安全）关闭数据库的方法：

数据库的停止和关闭操作非常重要，其重要性与其他服务（例如Web服务）有很大的区别，如果数据库没有按照正确的方式停止，那么很可能会导致无法再次启动服务。

//方法一：使用mysqladmin指令。
[root@MySQL-B ~]# mysqladmin -u root -poldboy123 shutdown
//方法二：使用mysqld脚本。
[root@MySQL-B ~]# /etc/init.d/mysqld stop
//方法三：使用kill信号的方式（同样不推荐）。
[root@MySQL-B ~]# kill -USR2 `cat path/pid`

2、多实例MySQL服务的启动与关闭

//启动实例
[root@MySQL-B ~]# /data/3306/mysql start
[root@MySQL-B ~]# /dtat/3307/mysql strat
//关闭实例
[root@MySQL-B ~]# /data/3306/mysql stop
[root@MySQL-B ~]# /data/3307/mysql stop
//此方法为老师课程里的双实例所使用的方法，脚本为老师自行编写的。实际上就是指定不同配置文件my.cnf并启动服务。

多实例的启动与停止的实质：

服务启动：mysqld_safe

服务停止：mysqladmin shutdown

二、登录MySQL

1、单实例MySQL的登录方法

[root@MySQL-B ~]# mysql   //如果是刚刚安装完MySQL，因为没有密码，可以这样直接登录。
[root@MySQL-B ~]# mysql -uroot      //同上。
[root@MySQL-B ~]# mysql -uroot -p
//这是标准的登录指令，即便是没有密码，由于使用了-p选项，也需要在提示输入密码的情况下敲一下回车。
[root@MySQL-B ~]# mysql -uroot -poldboy123
//在非脚本的情况下一般不这样使用，因为它会暴露登录密码，很不安全。

登陆后的默认提示符为：mysql>，当然这个提示符就如同Linux命令行的提示符一样，可以进行修改。

对提示符进行修改，有助于我们区分正式环境与测试环境。

//修改提示符：
mysql> prompt \u@MySQL-B>
PROMPT set to '\u@MySQL-B>'
root@MySQL-B>    //注意：这样的修改是临时的，下次登录时就会变回原来的样子。
//如果想永久性的修改提示符，就要修改配置文件my.cnf。
[root@MySQL-B ~]# vim /etc/my.cnf
[mysql]
prompt=\\u@MySQL-B>
//将该配置项添加到“[mysql]”模块中去（注意，不是[mysqld]模块）。保存后，无需重启mysql，只需退场当前会话，重新登录即可。
//注意，在my.cnf配置文件中进行编辑时，可以使用“\\”来避免转意带来的问题。

另外，在当前用户的主目录下有一个文件：.mysql_history，该文件记录了所有登录MySQL服务后所进行的操作。

2、多实例MySQL的登录方法

2.1 本地多实例登录

[root@MySQL-B ~]# mysql -uroot -p -S /data/3306/mysql.sock
[root@MySQL-B ~]# mysql -uroot -p -S /data/3307/mysql.sock
//多实例的登录，通过使用-S选项指定不同的sock文件来登录到不同的实例中。

2.2 远程多实例登录

[root@MySQL-B ~]# mysql -uroot -p -h 172.16.1.4 -P3306
[root@MySQL-B ~]# mysql -uroot -p -h 172.16.1.4 -P3307
//对于远程连接，时不需要指定sock文件的，只要指定IP地址及端口号即可（有待验证）！

三、登录MySQL后

为了防止误操作，可以通过修改提示符来标记这里到底是测试环境还是生产环境，具体操作见上文。

1、查看登陆后的提示信息

//登录后的信息如下所示：
[root@MySQL-B ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 298
Server version: 5.5.32 MySQL Community Server (GPL)
//这里显示的版本为5.5.32。
Copyright (c) 2000, 2013, 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>

2、查看帮助信息

MySQL中的help指令和Linux系统中的man是类似的，和Linux下查看内置指令的帮助信息所使用的help指令是同名的。要想看MySQL中的命令使用帮助，就需要使用help指令，指令后面需要接相关的命令或命令的组合即可。

注意：默认情况下，MySQL的SQL语句（或者说是指令关键字）是不分大小写的，只要拼写正确即可。但是，不区分大小写的只限于SQL关键字。在MySQL中，数据库、数据表以及字段的名称是区分大小写的。

//直接使用help指令：
mysql> help
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
...

//查看CREATE指令的帮助信息。
mysql> help create
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   ...
//例如，我们想要继续查询“CREATE DATABASE”的帮助信息。
mysql> help CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html
//我靠，帮助信息如此详细。

所以要善于使用帮助信息，指令的用法及示例在帮助信息中都写得非常清楚。

四、退出MySQL

mysql> quit    //或者是：
mysql> exit
//或者使用组合键Ctrl+D

五、设置及修改root用户的密码

1. MySQL数据库用户安全策略介绍

安装MySQL数据库后，默认的管理员“root”的密码为空，这当然是很不安全的了，因此就需要设置密码。其实在安装MySQL单实例后，我们已经做了一些基本的安全措施，例如：为root用户设置密码、清除数据库内无用的用户账号、清除默认的test库。

1.1 创建新的管理员

除了上面这些最基本的安全措施外，针对MySQL数据库的用户管理，我们还有更加严格的设置。
第一步：创建一个新的超级管理员。
新的超级管理员等价于默认的root用户，只是用户名不同而已。

mysql> grant all privileges on *.* to system@'localhost' identified by 'oldboy123' with grant option;
Query OK, 0 rows affected (0.01 sec)

第二步：删除所有的用户账户，包括root用户。

mysql> delete from mysql.user
//提示：root用户可以保留，然后修改为其他用户也可以。

1.2 为root用户设置密码

如果超级管理员用户没有密码，可以使用下面的操作来为其设置密码：

[root@MySQL-B ~]# mysqladmin -u root password 'oldboy123'
//如果是多实例环境下：

1.3 修改管理员密码

方法一：在命令行中直接进行修改

[root@MySQL-B ~]# mysqladmin -u root -poldboy123 password 'oldboy456'
//如果是多实例环境下：
[root@MySQL-B ~]# mysqladmin -u root -poldboy123 password 'oldboy456' -S /data/3306/mysql.sock

方法二：用SQL语句进行修改

//查询用户表的表结构，找到用户密码对应的字段名称。
mysql> desc mysql.user;
//通过查找，用户密码应该是对应着Password字段。
mysql> select user,host,password from mysql.user;
+--------+---------------+-------------------------------------------+
| user   | host          | password                                  |
+--------+---------------+-------------------------------------------+
| root   | localhost     | *7DB922C59F217871B8165D72BEC8ED731A0EFFA1 |
| root   | 127.0.0.1     |                                           |
| oldboy | 192.168.0.102 | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| oldboy | 192.168.0.103 | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| system | localhost     | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | 
+--------+---------------+-------------------------------------------+
//可以看到，密码字段的记录信息都是被加密的。

错误的修改方法：

mysql> update mysql.user set password='456' where user='root' and host='localhost';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select user,host,password from mysql.user;   //再次查看：
+--------+---------------+-------------------------------------------+
| user   | host          | password                                  |
+--------+---------------+-------------------------------------------+
| root   | localhost     | 456                                       |
| root   | 127.0.0.1     |                                           |
| oldboy | 192.168.0.102 | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| oldboy | 192.168.0.103 | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| system | localhost     | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 | 
+--------+---------------+-------------------------------------------+
//修改后的密码显示为明文，未被加密。但是，这样修改密码是不行的，用户无法使用经该方法修改的密码进行登录。

正确的修改方法：

mysql> update mysql.user set password=password('oldboy123') where user='root' and host='localhost';
mysql> flush privileges;
//注意：这里使用了密码加密函数“password()”。
//这种方法适合于密码丢失后通过“--skip-grant-tables”参数启动数据库后进行密码的修改。
//还需要注意：此方法在修改密码时，必须通过SQL语句指定修改条件。

方法三：使用数据库命令行的set指令
MySQL安装完成后，默认情况下root的密码为空，需要马上进行修改。

mysql> set password=password('oldboy123');
mysql> flush privileges;
//这种方式用于当前的登录用户修改自己的密码。
//注意，不论在什么情况下，只要修改了密码，都要进行刷新权限的操作。

2、找回丢失的root用户密码

2.1 单实例情况

首先，停止后台的服务进程：

[root@MySQL-B ~]# /etc/init.d/mysqld stop
Shutting down MySQL..                                      [  OK  ]

然后，使用--skip-grant-tables参数启动服务，忽略登录验证：

[root@MySQL-B ~]# mysqld_safe --skip-grant-tables --user=mysql  &
//然后直接登录即可。
[root@MySQL-B ~]# mysql
//然后，通过上面的方法，使用update语句修改密码以及刷新权限（过程略）。
//最后退出，并关闭服务进程，然后重新启动数据库服务。
[root@MySQL-B ~]# mysqladmin -u root -poldboy123 shutdown
[root@MySQL-B ~]# /etc/init.d/mysqld start
Starting MySQL...                                          [  OK  ]
//注意：当使用忽略授权表方式登录并修改完密码后，一定要重启MySQL服务。

2.2 多实例情况

首先也是停止服务。
然后在启动时也要加上--skip-grant-tables参数：

[root@MySQL-B ~]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table &
[root@MySQL-B ~]# mysql -u root -p -S /data/3306/mysql.sock
//登陆时空密码。

最后使用update语句进行修改（具体操作见上文，这里不再赘述）。