Ubantu 14.04安装MySQL 5.7过程

本次安装环境使用阿里云ECS服务器，使用Ubantu 14.04操作系统。

1. 检查ECS mysql镜像

在ECS中Ubantu系统一般存有已备好的mysql安装包。首先，检查Ubantu中mysql版本。

Shell>apt-get install mysql

不使用回车，而是点击两次Tab键，显示全部与mysql相关的安装包。

Shell>mysql-client           mysql-mmm-monitor      mysql-source-5.6 
mysql-client-5.5       mysql-mmm-tools        mysqltcl
mysql-client-5.6       mysql-proxy            mysql-testsuite
mysql-client-core-5.5  mysql-server           mysql-testsuite-5.5
mysql-client-core-5.6  mysql-server-5.5       mysql-testsuite-5.6
mysql-common           mysql-server-5.6       mysqltuner
mysql-common-5.6       mysql-server-core-5.5  mysql-utilities
mysql-mmm-agent        mysql-server-core-5.6  mysql-workbench
mysql-mmm-common       mysql-source-5.5       mysql-workbench-data

1.1 如果存在5.7版本的mysql，可以直接安装：

Shell>apt-get install mysql-server-5.7

 安装过程需要输入两次密码，这是mysql数据库root用户的登录密码。
默认安装client，但如果未安装，则主动执行

Shell>apt-get install mysql-client-5.7

1.2 如果不存在5.7版本的mysql(比如本次安装，在ECS镜像中只备份了5.6和5.5的版本)，需要在MySQL官网中下载mysql-5.7安装包。

2. MySQL 5.7 下载及安装

 访问MySQL官网下载5.7.25版本安装包，下载配置Select Version：5.7.25，Select Operating System：Ubantu Linux，Select OS Version：Ubantu Linux 14.04(x86,64-bit)，选中下载DEB Bundle 5.7.25 (mysql-server_5.7.25-1 ubantu14.04_amd64.deb-bundle.tar)压缩包。
 使用WinSCP工具将tar压缩包远程上传到ECS UbantuOS内，将mysql-server_5.7.25-1 ubantu14.04_amd64.deb-bundle.tar文件解压至自定义文件夹内。解压命令：

Shell>cd /usr/~
Shell>mkdir mysql
Shell>sudo tar -xvf ./mysql-server_5.7.25-1ubuntu14.04_amd64.deb-bundle.tar -C ./mysql/
Shell>ll ./mysql/

 进入~/mysql/文件夹内可以查看解压后文件

mysql-client_5.7.25-1ubuntu14.04_amd64.deb
mysql-community-server_5.7.25-1ubuntu14.04_amd64.deb
libmysqlclient20_5.7.25-1ubuntu14.04_amd64.deb
mysql-common_5.7.25-1ubuntu14.04_amd64.deb
mysql-community-test_5.7.25-1ubuntu14.04_amd64.deb
mysql-community-client_5.7.25-1ubuntu14.04_amd64.deb
libmysqld-dev_5.7.25-1ubuntu14.04_amd64.deb
mysql-server_5.7.25-1ubuntu14.04_amd64.deb
libmysqlclient-dev_5.7.25-1ubuntu14.04_amd64.deb
mysql-community-source_5.7.25-1ubuntu14.04_amd64.deb
mysql-testsuite_5.7.25-1ubuntu14.04_amd64.deb

 解压后得到Debian类型文件(.deb)，在~/mysql/文件夹下使用dpkg命令安装以上11个文件。在安装前，先安装libaio1，一般Ubantu系统应该已经安装过，重复安装不影响后续步骤。

Shell>sudo apt-get install libaio1
libaio1 is already the newest version.
libaio1 set to manually installed.
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

 安装命令如下：

Shell>sudo dpkg -i mysql-common_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i libmysqllibmysqlclient20_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i libmysqlclient-dev_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i libmysqld-dev_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i mysql-community-client_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i mysql-client_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i mysql-community-server_5.7.25-1ubuntu14.04_amd64.deb
Error:Package libmecab2 is not installed.安装过程异常，提示未安装libmecab2
Shell>sudo dpkg -i mysql-server_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i mysql-community-source_5.7.25-1ubuntu14.04_amd64.deb
Shell>sudo dpkg -i mysql-community-test_5.7.25-1ubuntu14.04_amd64.deb
Error:Package libjson-perl is not installed.安装过程异常，提示未安装libjson-perl
Shell>sudo dpkg -i mysql-testsuite_5.7.25-1ubuntu14.04_amd64.deb

 在安装过程曾经出现两次异常，概因为缺少依赖安装包导致。需要在报错后使用同样的方法自动修补依赖缺失项。

Shell>sudo apt-get -f install
 注意命令中-f和install关键字的位置关系。

 其中在安装mysql-community-server_5.7.25-1ubuntu14.04_amd64.deb时，会弹出界面提示“Configuring mysql-community-server”，回车选择OK即可。然后，再次弹出两次界面，提示设置MySQL数据库root用户的登录密码。

 当最后安装完mysql-testsuite_5.7.25-1ubuntu14.04_amd64.deb后，安装结束，并已启动mysql-5.7.25 service。

3. 查看操作service mysql

查看MySQL节点状态

Shell>sudo netstat -tap | grep mysql

tcp        0      0 localhost:mysql         *:*                     LISTEN      30943/mysqld

打开MySQL服务

Shell>sudo service mysql start

..
 * MySQL Community Server 5.7.25 is started

关闭MySQL服务

Shell>sudo service mysql stop

...
 * MySQL Community Server 5.7.25 is stopped

重启MySQL服务

Shell>sudo service mysql restart

* Stopping MySQL Community Server 5.7.25
..
 * MySQL Community Server 5.7.25 is stopped
 * Re-starting MySQL Community Server 5.7.25
..
 * MySQL Community Server 5.7.25 is started

4. 操作MySQL数据库

进入数据库

Shell>mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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>

退出数据库

mysql>exit;
Bye

修改数据库字符集

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.00 sec)

数据库内character_set_server的字符编码是latin1，在以后使用中文时，会出现乱码问题，需要先退出mysql shell，修改mysqld.cnf配置文件，在[mysqld]下添加一行 character_set_server = utf8。

Shell>sudo vim /etc/mysql/mysql.conf.d/mysql.cnf
配置文件固定保存在etc文件夹下

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
character_set_server=utf8
# By default we only accept connections from localhost
bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

保存文件退出vim编辑器，重新启动service mysql后，进入mysql数据库操作界面，再次查询数据库的字符集信息，可查看character_set_server的字符编码是utf8。

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.00 sec)

5. 下载安装MySQL连接驱动程序

 数据库安装成功后，经常需要在程序中通过JDBC访问数据库，所以需要安装MySQL的JDBC驱动文件。从MySQL官网下载驱动Jar，这里不选择Connector/J 8.0.15版本的Jar包，而是选择使用Connector/J 5.1.47版本的Jar包，所以点击“Looking for previous GA versions?”链接，跳转至以前的版本。选择下载项：Platform Independent(Architecture Independent),Compressed TAR Archive 5.1.47(mysql-connector-java-5.1.47.tar.gz)。

 通过WinSCP工具将mysql-connector-java-5.1.47.tar.gz安装包上传至ECS UbantuOS内自定义文件夹。将文件解压缩至/usr/local文件夹下，然后修改解压缩文件夹权限，至此，顺利完成MySQL数据库的安装。命令：

Shell>sudo tar -zxvf /usr/~/mysql-connector-java-5.1.47.tar.gz -C /usr/local
Shell>sudo chown -R root:root ./mysql-connector-java-5.1.47/

 安装好后，需要指定防火墙和端口情况。查看防火墙

Shell>sudo ufw status

如果想要保持防火墙打开状态，就要将端口3306保持不阻止状态。另外，阿里云ECS上的端口开放，需要在安全组内添加入网方向的端口规则。

6. 远程连接service mysql

 进入MySQL数据库的Shell界面。然后使用命令切换状态，使数据库远程可登录。再次，授权登录账号，刷新权限。最后退出数据库。

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> grant all privileges on *.* to 'root'@'%' identified by 'password' with grant option;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> exit;
Bye

 操作完数据库后，进入mysqld.cnf文件，注释行bind-address = 127.0.0.1，保存文件退出vim编辑器，最后重启service mysql。

Shell>sudo vim /etc/mysql/mysql.conf.d/mysql.cnf

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
character_set_server=utf8
# By default we only accept connections from localhost
# bind-address    = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

 使用界面管理工具Navicat for MySQL远程登录，创建新数据连接，连接信息如下:

********************
*General Information
********************
连接名: remote_ubantu_mysql
主机名或 IP 地址: ECS 公网IP地址
端口: 3306
用户名: root
保存密码: True

 测试连接成功，其他连接内操作同本地数据库相同。在Navicat工具内，想通过.sql文件导入数据，可用鼠标点住.sql文件，拖曳鼠标到希望插入的数据库内，便可自动实现数据导入。