IT范儿

程序员改变世界

0%

Ubuntu下安装及配置MySql数据库

发表于 更新于
Ubuntu下安装Mysql数据库及一些必要设置

安装过程

测试系统为 Ubuntu 16.04 LTS

更新Ubuntu软件安装源
1
$ echo "deb http://cn.archive.ubuntu.com/ubuntu/ xenial main restricted universe multiverse" >> /etc/apt/sources.list
安装mysql-server

执行如下命令安装mysql:

1
2
$ sudo apt-get update
$ sudo apt-get install mysql-server

安装过程会弹出提示框，输入root用户的密码，这里设置密码为 mysql 。

安装完成后，通过命令 mysql -V 查看mysql版本信息：

1
2
# mysql -V
mysql  Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using  EditLine wrapper

解决mysql连接错误 ERROR 2002 (HY000)

在使用 root 账户连接mysql时，报了如下的 2002 错误：

1
2
3
# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

查看 mysql 服务是否启动，执行如下命令查看：

1
2
3
4
# ps -aux |grep mysqld
root       984  0.0  0.0  11276   728 ?        S+   07:23   0:00 grep --color=auto mysqld
# ps -aux |grep mysql 
root       986  0.0  0.0  11276   728 ?        S+   07:23   0:00 grep --color=auto mysql

可见mysql的服务并没有启动，然后我们尝试启动服务：

1
2
3
4
$ sudo service mysql start

# service mysql start
 * Starting MySQL database server mysqld                                                                                                                       No directory, logging in with HOME=/

mysql的服务居然无法启动。

最终找到原因是当前用户对 /var/run/mysqld 目录没有操作权限导致的。

先查看 /var/run/ 下是否存在 mysqld目录，没有先创建。

执行如下命令：

1
$ sudo chown -R mysql:mysql /var/run/mysqld

然后再次尝试启动mysql服务：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ sudo service mysql start

# service mysql status
 * /usr/bin/mysqladmin  Ver 8.42 Distrib 5.7.15, for Linux on x86_64
Copyright (c) 2000, 2016, 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.

Server version		5.7.15-0ubuntu0.16.04.1
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/run/mysqld/mysqld.sock
Uptime:			7 sec

Threads: 1  Questions: 8  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 1.142

可以看到mysql服务启动成功了。

网上查找各种解决该问题的方法，也只有这一种方法是根本原因。

所以当再次遇到该问题的时候，先查看一下目录是否有操作权限：

1
$ ls -al /var/run/mysqld/

chown命令将指定文件的拥有者改为指定的用户或组

新增账户及权限设置

第一种方法
以管理员身份登陆mysql
1
$ mysql -uroot -p

输入之前设置的密码，登陆mysql命令模式。

选择 mysql 数据库
1
mysql> use mysql;
创建用户并设定密码

先查看默认存在哪些账户：

1
2
3
4
5
6
7
8
9
10
mysql> select host,user from user;

+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
3 rows in set (0.00 sec)

执行以下命令来创建新用户账户及密码：

1
mysql> create user 'testuser1'@'localhost' identified by 'testpassword';

将 testpassword 替换为你自己的密码。

执行如下命令使操作生效：

1
mysql> flush privileges;

示例操作如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create user 'testuser1'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | testuser1        |
+-----------+------------------+
4 rows in set (0.00 sec)
为新账户创建数据库
1
2
mysql> create database testdb;
Query OK, 1 row affected (0.03 sec)
为新账户赋予操作新建数据库 testdb 的权限
1
2
3
4
5
mysql> grant all privileges on testdb.* to 'testuser1'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • testdb.* 表示操作 testdb 这个数据库中所有的表
  • 'testuser1'@'localhost' 表示使用账户 testuser1 登陆到 localhost
  • '123456' 表示登陆密码
使用新账户登陆

我们在上面的步骤中创建的新用户为 testuse1 密码为 123456 管理的数据库为 testdb。

使用 exit 退出 root 账户的登陆，然后使用新账户登陆：

1
2
3
4
5
6
mysql> exit
Bye

$ mysql -u testuser1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

登陆成功后，查看数据库列表：

1
2
3
4
5
6
7
8
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
+--------------------+
2 rows in set (0.00 sec)
第二种方法 通过GRANT授权的方式新增用户
以root账户登录：
1
2
3
$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
新增数据库

为新账户创建数据库 testdb2

1
2
mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)
新增账户并设置密码

新增账户 testuser2 密码为 123456 管理 testdb2 数据库。

通过 grant all privileges on 语句来操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb2            |
+--------------------+
6 rows in set (0.00 sec)

mysql> grant all privileges on testdb2.* to 'testuser2'@'localhost' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

grant all 语句不需要使用 flush privilege; 刷新系统权限表，该操作立即生效。

使用新账户 testuser2 登陆
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> exit
Bye

$ mysql -utestuser2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb2            |
+--------------------+
2 rows in set (0.00 sec)

将默认编码改为utf8

默认情况下，MySQL的字符集是 latin1 ，因此在存储中文的时候，会出现乱码的情况，所以我们需要把字符集统一改成 UTF-8 。

mysql 默认编码

通过如下命令查看mysql默认编码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> show variables like "%character%";show variables like "%collation%";

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
更改配置文件

一般情况下，在 Ubuntu 14.04 系统中，mysql的配置文件目录为：

1
/etc/mysql/my.cnf

在 Ubuntu 16.04 系统下，mysql的配置文件目录为：

1
2
3
/etc/mysql/my.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

我当前的系统为 Ubuntu16.04 。其实在 ubuntu 16.04 系统中，mysql的配置文件路径也为 /etc/mysql/my.cnf ，只不过这个 my.cnf 是全局配置文件，在该文件内部可以看到如下配置：

1
2
3
# 
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

具体的配置文件是存放在上面两个目录下的。所以我们可以更改 /etc/mysql/my.cnf 这个文件，也可以更改 /etc/mysql/mysql.conf.d/mysqld.cnf 这个文件。或者也可以自己新增一个扩展名为 *.cnf 的配置文件放在上面包含的两个目录内。

从网上找到各种说法的修改编码为utf-8的方法，经测试后需要修改的配置如下：

1
2
3
4
5
6
7
8
9
10
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci

编辑 /etc/mysql/my.cnf 配置文件，依次添加上面的编码设置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci


!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

然后重启 mysql 服务：

1
$ service mysql restart

再次登录mysql命令模式查看默认编码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

可见，我们已经更改成功了。

让MySQL服务器被远程访问

默认情况下，root账户只能从 localhost 即本机下来访问mysql的服务。而在正式使用时，mysql数据库都是放在远程的数据库服务器上，这样也就需要我们通过远程的方式能够访问到mysql服务。

开启绑定端口

编辑配置文件 /etc/mysql/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf ，将绑定地址行注释掉或者修改为指定IP：

1
2
#注释bind-address
# bind-address                   = 127.0.0.1
  • 注释掉则允许所有ip都能够访问，也可以设置成 0.0.0.0
  • 修改为指定的IP地址，则只允许该IP网段可以访问

修改配置文件后，重启 mysql 服务生效：

1
$ service mysql restart

如果这时通过外网连接mysql，在连接时会出现错误 “’Host XXX is not allowed to connect to this MySQL server’ ” ，则还需要修改数据库中用户的访问权限。

修改数据库中账户访问权限

这里以 root 账户为例来设置远程访问。

查看root账户可访问权限

以 root 账户登录：

1
2
mysql -u root -p
Enter password: <enter password>

切换到 mysql 数据库，并查询 user 表中的账户设置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> use 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> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | testuser1        |
| localhost | testuser2        |
+-----------+------------------+
5 rows in set (0.00 sec)

可以看到，root账户默认下不允许从远程登陆，只能从 localhost 来访问，我们还要为 root 账户添加访问权限。

添加远程访问授权

这里有两种方法，一种是将上面的 mysql 数据库中的 user 表里的 host 项，将 localhost 改为 %，

1
2
mysql> use mysql;
mysql> update user set host ='%' where user ='root';

另外一种是为账号 root 添加一个新的远程访问授权。

这里我们采用第二种方法。

通过命令 GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'put-your-password' WITH GRANT OPTION; 来操作。

执行如下命令：

1
2
3
4
5
6
mysql -u root -p
Enter password: <enter password>

mysql> GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'mysql' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

再次查看：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | testuser1        |
| localhost | testuser2        |
+-----------+------------------+
6 rows in set (0.00 sec)

现在再尝试通过外网来连接 mysql 数据库，就能连接成功了。

本地和远程访问使用不同权限或密码

在上面的表中我们可以知道，root 账户有两个 host 配置项，一个本地的，一个远程的。其实我们可以将两项设置成不同的密码，以防止本地或远程的密码泄露问题。也可以在 grant 后跟详细的查询条件 select,delete 等，为本地或远程访问设置不同的访问权限。比如：

1
mysql> GRANT SELECT,UPDATE,INSERT,DELETE on *.* to root@'%' IDENTIFIED BY 'mysql' WITH GRANT OPTION;
添加特定远程访问权限

假设账户 myuser 密码 mypwd

  1. grant all privileges on *.* to 'myuser'@'localhost' identified by 'mypwd'
  2. grant all privileges on *.* to 'myuser'@'%' identified by 'mypwd'
  3. grant all privileges on *.* to 'myuser'@'10.22.255.18' identified by 'mypwd'

说明：

  1. 添加一个本地用户 myuser ,一般用于web服务器和数据库服务器在一起的情况
  2. 添加一个用户 myuser ,只要能连接数据库服务器的机器都可以使用，这个比较危险，一般不用
  3. 在数据库服务器上给 10.22.255.18 机器添加一个用户 myuser，一般用于web服务器和数据库服务器分离的情况

注意：真正使用的时候不会用 grant all PRIVILEGES on *.* ，而是根据实际需要设定相关的权限。

特定访问权限
  • 如果想让账户 myuser 使用 mypwd 从任何主机连接到 mysql 服务器，执行： 
    1
    GRANT ALL PRIVILEGES ON *.* to myuser@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
  • 如果想让账户 myuser 使用密码 123456 从 ip为 123.123.123.123 的主机连接到 mysql 服务器，执行：
    1
    GRANT ALL PRIVILEGES ON *.* to myuser@'123.123.123.123' IDENTIFIED BY '123456' WITH GRANT OPTION;

即 ‘%’ 表示任何主机。

WITH GRANT OPTION 是啥意思

WITH GRANT OPTION 表示具有授予权限的权利。比如 上面的： 

1
GRANT ALL PRIVILEGES ON *.* to myuser@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;

为 root 用户赋予了 ALL PRIVILEGES 的权限，那么 root 账户就可以为其他的账户比如 testuser1 设置不同的权限。

如有疑问或需要技术讨论，请留言或发邮件到 service@itfanr.cc