安裝MySQL服務(wù)（配置編碼格式）

• 安裝mysql服務(wù)

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的狀態(tài)

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;#查看數(shù)據(jù)庫(kù)
+--------------------+
| 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

• 逐級(jí)進(jìn)入配置文件

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: 
#修改配置文件（修改數(shù)據(jù)庫(kù)編碼格式）
[client]
default-character-set = utf8
    
[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
#保存退出

vim----命令（先按esc鍵-->輸入:wq保存修改并退出誉碴，輸入:q!不保存修改并退出編輯器）

• 重啟mysql服務(wù)

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的排序規(guī)則
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```
* 創(chuàng)建一個(gè)用戶属提，使得通過(guò)這個(gè)用戶的所有外界主機(jī)能夠連接到服務(wù)器中的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)
#為該用戶授權(quán)溉卓，便于后面的數(shù)據(jù)庫(kù)操作
mysql> grant all on *.* to 'xiaodong'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> 

• 修改監(jiān)聽(tīng)地址
  • 默認(rèn)情況下MySQL只允許本機(jī)localhost(127.0.0.1)訪問(wèn)枣宫，有時(shí)為了開(kāi)發(fā)需求哮翘，我們需要讓MySQL監(jiān)聽(tīng)外來(lái)機(jī)器的請(qǐng)求锣杂。這樣也便于后期項(xiàng)目的部署。

#
# * 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#添加監(jiān)聽(tīng)ip

• 查看監(jiān)聽(tīng)詳情

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