如何在Linux中搭建mysql服务（Ubuntu）

安装MySQL服务（配置编码格式）

• 安装mysql服务

xiaodong@Xiaodong-VMSrv:~$ sudo apt install mysql-server
[sudo] password for xiaodong: 
Reading package lists... Done
......
Do you want to continue? [Y/n] y
......
Preconfiguring packages ...
......
Setting up libhtml-tagset-perl (3.20-2) 
......
Setting up mysql-server (5.7.17-0ubuntu0.16.04.2) ...
Processing triggers for libc-bin (2.23-0ubuntu7) ...
Processing triggers for systemd (229-4ubuntu16) ...
Processing triggers for ureadahead (0.100.0-19) ...

• 查看MySQL的状态

xiaodong@Xiaodong-VMSrv:~$ sudo service mysql status
[sudo] password for xiaodong: 
    ● mysql.service - MySQL Community Server
       Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
       Active: active (running) since Wed 2017-04-19 21:01:19 CST; 28min ago
     Main PID: 4151 (mysqld)
        Tasks: 28
       Memory: 110.6M
          CPU: 3.911s
       CGroup: /system.slice/mysql.service
               └─4151 /usr/sbin/mysqld
    
    Apr 19 21:01:17 Xiaodong-VMSrv systemd[1]: Starting MySQL Community Server...
    Apr 19 21:01:19 Xiaodong-VMSrv systemd[1]: Started MySQL Community Server.```
    
* 登录

```shell
xiaodong@Xiaodong-VMSrv:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-0ubuntu0.16.04.2 (Ubuntu)
    
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.
    
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
mysql> show databases;#查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)```
    
* 显示编码格式

```shell 
mysql> show VARIABLES LIKE '%char%'
    -> ;
+--------------------------+----------------------------+
    | Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.06 sec)
    
mysql> SHOW VARIABLES LIKE '%colla%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.07 sec)

• 退出

mysql> exit
Bye

• 逐级进入配置文件

xiaodong@Xiaodong-VMSrv:~$ cd /etc/mysql
xiaodong@Xiaodong-VMSrv:/etc/mysql$ ls
conf.d  debian.cnf  debian-start  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d
xiaodong@Xiaodong-VMSrv:/etc/mysql$ 

• 修改配置文件（编码格式）

xiaodong@Xiaodong-VMSrv:/etc/mysql$ sudo nano my.cnf
[sudo] password for xiaodong: 
#修改配置文件（修改数据库编码格式）
[client]
default-character-set = utf8
    
[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
#保存退出

vim----命令（先按esc键-->输入:wq保存修改并退出，输入:q!不保存修改并退出编辑器）

• 重启mysql服务

xiaodong@Xiaodong-VMSrv:/etc/mysql$ sudo service mysql restart
xiaodong@Xiaodong-VMSrv:/etc/mysql$ mysql -uroot -p
Enter password: 
......
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

• 查看编码格式是否修改正确

mysql> SHOW VARIABLES LIKE '%CHAR%'
    -> ;
+--------------------------+----------------------------+
| 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.01 sec)
#下面是查看mysql的排序规则
mysql> SHOW VARIABLES LIKE '%colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.10 sec)

• 回到用户目录

xiaodong@Xiaodong-VMSrv:/etc/mysql$ cd```
* 查看IP地址

```shell
xiaodong@Xiaodong-VMSrv:~$ ifconfig
enp0s3    Link encap:Ethernet  HWaddr 08:00:27:69:ec:7a  
       inet addr:192.168.1.138  Bcast:192.168.1.255  Mask:255.255.255.0
       inet6 addr: fd7a:3e42:5e2f:0:a00:27ff:fe69:ec7a/64 Scope:Global
       inet6 addr: fe80::a00:27ff:fe69:ec7a/64 Scope:Link
       UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
       RX packets:17403 errors:0 dropped:0 overruns:0 frame:0
       TX packets:7494 errors:0 dropped:0 overruns:0 carrier:0
       collisions:0 txqueuelen:1000 
       RX bytes:21652498 (21.6 MB)  TX bytes:625769 (625.7 KB)
    
lo        Link encap:Local Loopback  
       inet addr:127.0.0.1  Mask:255.0.0.0
       inet6 addr: ::1/128 Scope:Host
       UP LOOPBACK RUNNING  MTU:65536  Metric:1
       RX packets:160 errors:0 dropped:0 overruns:0 frame:0
       TX packets:160 errors:0 dropped:0 overruns:0 carrier:0
       collisions:0 txqueuelen:1 
       RX bytes:11840 (11.8 KB)  TX bytes:11840 (11.8 KB)
ip地址----192.168.1.138```
* 创建一个用户，使得通过这个用户的所有外界主机能够连接到服务器中的mysql(允许所有IP都能登录的用户)

```shell
mysql> create user 'xiaodong'@'%' IDENTIFIED BY 'xiaoxiao'
    -> ;
Query OK, 0 rows affected (0.00 sec)
    
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#为该用户授权，便于后面的数据库操作
mysql> grant all on *.* to 'xiaodong'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> 

• 修改监听地址
  • 默认情况下MySQL只允许本机localhost(127.0.0.1)访问，有时为了开发需求，我们需要让MySQL监听外来机器的请求。这样也便于后期项目的部署。

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
    
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
#修改后的配置文件
[client]
default-character-set = utf8

[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
bind-address = 0.0.0.0#添加监听ip

• 查看监听详情

xiaodong@Xiaodong-VMSrv:~$ sudo netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      2254/mysqld 
    
0  *:mysql-----指的所有外界主机ip都可连接