本文记录微信公众号后台数据库在生产环境的部署过程。

目录:

MySQL

部署环境

  • 阿里云 ECS 服务器
  • Ubuntu 16.04.2 LTS
  • MySQL 5.7.19

Install

  • sudo apt-get update
  • sudo apt-get install mysql-server 安装过程中会提示为数据库 root 账户设置密码;
  • dpkg -l | grep mysql | grep ii 列出从 MySQL APT 安装的所有包;

UnInstall

  • sudo apt-get remove mysql-server
  • sudo apt-get autoremove

MySQL 配置文件

apt-get 方式安装之后默认配置文件是 /etc/mysql/my.cnf,文件内容可参考 mysql-5.7.19 default config file

References

设置默认字符集

选择合适的字符集(Character-set)

由于目前数据库存储的字符种类越来越多(如汉字、各种符号、Emoji 等),为了满足存储需求,一般会将字符集设置为 utf8mb4,排序字符集设置为 utf8mb4_unicode_ci

  • utf8mb4 是 utf8 的扩展集,最大可存储四字节的字符,因而可表示的字符也大大增加;
  • utf8mb4_unicode_ci 基于标准的 Unicode 来排序和比较,能够在各种语言之间精确排序,相比 utf8mb4_general_ci 增加的性能消耗基本可以忽略。

更具体的介绍及其配置可参考本节 References 模块。

修改数据库配置

/etc/mysql/my.cnf 文件中添加下面的内容即可完成修改,修改之后的文件可见 mysql-5.7.19 customized config file,gist 评论 包含了修改前后的字符集对比;

/etc/mysql/my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
# customized config
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-client-handshake = FALSE

修改配置之后需要重启数据库才能生效,具体可用下面的命令查看修改前后的数据库字符集:

  • mysql> SHOW VARIABLES LIKE 'char%'; SHOW VARIABLES LIKE 'collation%';

更多配置选项可参考官方文档 5.1.4 Server Command Options

修改 MySQL 启动地址

可通过命令 netstat -noa | grep 3306 查看 MySQL 当前的启动地址,默认是 127.0.0.1,只支持本地访问;为了支持远程访问,需要将其修改为 0.0.0.0

具体需要修改 /etc/mysql/mysql.conf.d/mysqld.cnf 中的内容:

/etc/mysql/mysql.conf.d/mysqld.cnf
1
2
3
4
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1
bind-address = 0.0.0.0

References

用户管理

创建用户的目的是实现权限控制,保障数据库安全;对于外部访问,还需要设置防火墙参数。

创建用户

  • mysql > CREATE USER 'username1'@'localhost' IDENTIFIED BY 'password1'; 该用户只能在本地访问数据库;
  • mysql > CREATE USER 'username2'@'%' IDENTIFIED BY 'password2'; 该用户可在所有机器上访问数据库;
  • mysql > DROP USER 'username2'@'%'; 删除用户 username2'%' 机器上的访问权限;

分配权限

  • mysql > GRANT ALL PRIVILEGES ON dbname.* TO 'username1'@'localhost' WITH GRANT OPTION; 分配给该用户对数据库 dbname 的所有操作权限,并包含授权权限;
  • mysql > GRANT SELECT ON dbname.* TO 'username2'@'%'; 只分配给该用户对数据库 dbname 的查询权限;
  • mysql > FLUSH PRIVILEGES; 刷新缓存,及时生效权限设置;
  • mysql > SHOW GRANTS FOR 'username1'@'localhost'; 查看该用户的权限;
  • mysql > REVOKE DELETE ON dbname.* FROM 'username1'@'localhost'; 收回该用户的删除权限;
  • mysql > REVOKE GRANT OPTION ON dbname.* FROM 'username1'@'localhost'; 收回该用户的授权权限;

注:

  • MySQL 中的权限分为五个级别,分别是 Global PrivilegesDatabase PrivilegesTable PrivilegesColumn PrivilegesStored Routine Privileges
  • 全局权限(Global Privileges)存储在 mysql.user 表中,如通过 ... ON *.* TO ... 命令分配,不限定数据库和数据表;
  • 数据库权限(Database Privileges)存储在 mysql.db 表中,如通过 ... ON dbname.* TO ... 命令分配,其中指定了只能操作 dbname 数据库;
  • mysql > SELECT * FROM mysql.db \G; 查询 mysql.db 表中的数据,末尾的 \G 可让查询结果以键值对的形式展示,可读性更好;

远程访问

通过远程访问数据库,既方便又安全,还能借助于图形化工具操作数据库;

访问方式

远程连接数据库一般有下面两种方式:

  • 远程直接访问数据库,需要满足下面的条件:
    • 登录用户需要分配远程访问权限,上文中创建的用户 username2 就支持远程访问;
    • MySQL 需要启动在 0.0.0.0 地址下;
    • 修改 iptables,打开防火墙限制;
  • 通过 SSH 隧道访问,直接连接到数据库所在机器,此时相当于本地访问数据库;

打开防火墙

通过 iptables -L -n 可查看当前防火墙配置,可通过 iptables 对其修改,具体参考 Linux下iptables防火墙配置详解;

在我的阿里云服务器上修改 iptables 的做法一直不起作用 …

我在这里折腾了很久都不能远程访问,问了谷歌无数遍,一直没找到原因,最后无奈之下在阿里云提了一个售后支持的工单,几个小时就帮我搞定了,阿里云的机器需要在控制台设置安全组(打开 MySQL 端口的访问限制),具体可参考 添加安全组规则,总之,对于 ECS 服务器:

  • 不需要修改本机 iptables;
  • 直接在控制台设置安全组规则即可;
  • 所有对外暴漏的端口都需要通过安全组设置;

References

MySQL-基本操作命令

管理 MySQL 服务

  • mysql -u root -p [db_name] 登录 MySQL 数据库;
  • sudo service mysql status
  • sudo service mysql start
  • sudo service mysql stop

MySQL 命令行交互

  • mysql > STATUS;
  • mysql > SHOW databases; 查看当前数据库进程中的数据库实例;
  • mysql > SELECT database(); 查看当前所在的数据库;
  • mysql > USE databasename; 进入 databasename 数据库;
  • mysql > SHOW tables; 查看当前数据库中的表;
  • mysql > SELECT version(); 查看 MySQL 版本号;
  • mysql > SHOW CHAR SET WHERE Charset LIKE "%utf8%"; 查看数据库中所有包含 utf8 的字符集;

数据库管理

  • mysql > CREATE DATABASE test;
  • mysql > CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • mysql > SELECT @@character_set_database, @@collation_database; 查看当前数据库的字符集;

数据表管理

  • mysql > CREATE TABLE test_table (c CHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci); 创建数据表 test_table;
  • select * from information_schema.TABLES where TABLE_NAME = 'free' \G; 查看数据表信息
    • TABLE_SCHEMA:数据库名
    • TABLE_NAME:表名
    • ENGINE:所使用的存储引擎
    • TABLES_ROWS:记录数
    • DATA_LENGTH:数据大小
    • INDEX_LENGTH:索引大小
    • DATA_FREE:数据库碎片大小
  • 表占用空间的大小 ≈ 数据大小 + 索引大小

References