MySQL环境 #

环境安装 #

# 查看Linux服务器上是否安装过MySQL
rpm -qa | grep -i mysql # 查询出所有mysql依赖包

# 1、拉取镜像
docker pull mysql:5.7

# 2、创建实例并启动
docker run -p 3306:3306 --name mysql \
-v /root/mysql/log:/var/log/mysql \
-v /root/mysql/data:/var/lib/mysql \
-v /root/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=qwer \
-d mysql:5.7

# conf
# 配置文件!!!

# 4、重启mysql容器
docker restart mysql

# 5、进入到mysql容器
docker exec -it mysql /bin/bash

# 6、查看修改的配置文件
cat /etc/mysql/my.conf

安装位置 #

Docker容器就是一个小型的Linux环境，进入到MySQL容器中。

docker exec -it mysql /bin/bash

Linux环境下MySQL的安装目录。

路径	解释
`/var/lib/mysql`	MySQL数据库文件存放位置
`/usr/share/mysql`	错误消息和字符集文件配置
`/usr/bin`	客户端程序和脚本
`/etc/init.d/mysql`	启停脚本相关

修改字符集 #

# determine which charset/collations are available
SHOW CHARSET;
SHOW COLLATION;

# check charset
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

# cnf)
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

# check database/table charset:检查数据库/表的创建信息，注意这里可以在下一步中的命令改，就是它不是最早创建时的信息，未来的命令可以改变这个输出结果。
SHOW CREATE DATABASE databasename;
SHOW CREATE TABLE tablename;

# change the database/table charset：改变数据库/表的字符
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

# set when create database/table：可以在创建数据库/表的时候指定字符
CREATE DATABASE new_db CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
CREATE TABLE new_table (id INT) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

#  so, if you have special character that can't save to mysql, maybe you should use utf8mb4 and utf8mb4_general_ci

MySQL5.7配置文件位置是/etc/my.cnf或者/etc/mysql/my.cnf，如果字符集不是utf-8直接进入配置文件修改即可。

[client]
#mysqlde utf8字符集默认为3位的，不支持emoji表情及部分不常见的汉字，故推荐使用utf8mb4
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
#设置client**连接**mysql时的字符集,防止乱码
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'

#数据库默认字符集
character-set-server=utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8mb4_general_ci

# 跳过mysql程序起动时的字符参数设置 ，使用服务器端字符集设置
skip-character-set-client-handshake

# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
skip-name-resolve

# 数据库错误日志文件
log-error = /var/log/mysql/error.log

注意：安装MySQL完毕之后，第一件事就是修改字符集编码。

配置文件 #

MySQL配置文件讲解：https://gist.github.com/zput/9609dbbbbe2aa1f2f516f62c699fd682

# 查询错误日志的位置
show variables like 'log_error';
show variables like 'general_log_file';
show variables like 'slow_query_log_file';

1、错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

# my,cnf
# 数据库错误日志文件
log-error = /var/log/mysql/error.log

2、查询日志log：默认关闭，记录查询的sql语句，如果开启会降低MySQL整体的性能，因为记录日志需要消耗系统资源。

# my,cnf
# 慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

3、二进制日志log-bin：主从复制。

# my,cnf
# 开启mysql binlog功能
log-bin=mysql-bin

4、数据文件。

frm文件：存放表结构。
myd文件：存放表数据。
myi文件：存放表索引。

# frm文件来存储表结构
# ibd文件来存储表索引和表数据
-rw-r-----  1 mysql mysql   8988 Jun 25 09:31 pms_category.frm
-rw-r-----  1 mysql mysql 245760 Jul 21 10:01 pms_category.ibd

MySQL5.7的Innodb存储引擎可将所有数据存放于ibdata*的共享表空间，也可将每张表存放于独立的.ibd文件的独立表空间。共享表空间以及独立表空间都是针对数据的存储方式而言的。

共享表空间: 某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。默认的文件名为:ibdata1 初始化为10M。
独立表空间: 每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中。在配置文件my.cnf中设置： innodb_file_per_table。

MySQL逻辑架构 #

MySQL逻辑架构

Connectors：指的是不同语言中与SQL的交互。
Connection Pool：管理缓冲用户连接，线程处理等需要缓存的需求。MySQL数据库的连接层。
Management Serveices & Utilities：系统管理和控制工具。备份、安全、复制、集群等等。。
SQL Interface：接受用户的SQL命令，并且返回用户需要查询的结果。
Parser：SQL语句解析器。
Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。For Example： select uid,name from user where gender = 1;这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。
Caches & Buffers：查询缓存。
Pluggable Storage Engines：存储引擎接口。MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎(注意：存储引擎是基于表的，而不是数据库)。
File System：数据落地到磁盘上，就是文件的存储。

MySQL数据库和其他数据库相比，MySQL有点与众不同，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。

逻辑架构分层

MySQL逻辑架构

连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层：MySQL的核心服务功能层，该层是MySQL的核心，包括查询缓存，解析器，解析树，预处理器，查询优化器。主要进行查询解析、分析、查询缓存、内置函数、存储过程、触发器、视图等，select操作会先检查是否命中查询缓存，命中则直接返回缓存数据，否则解析查询并创建对应的解析树。
引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

存储引擎 #

show engines;命令查看MySQL5.7支持的存储引擎。

mysql> show engines;

存储引擎

show variables like 'default_storage_engine%';查看当前数据库正在使用的存储引擎。

mysql> show variables like 'default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

InnoDB和MyISAM对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不适合高并发操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，対内存要求较高，而且内存大小対性能有决定性影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

主从复制 #

复制基本原理 #

主从复制

MySQL复制过程分为三步：

Master将改变记录到二进制日志(Binary Log)。这些记录过程叫做二进制日志事件，Binary Log Events；
Slave将Master的Binary Log Events拷贝到它的中继日志(Replay Log);
Slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。

复制基本原则 #

每个Slave只有一个Master。
每个Slave只能有一个唯一的服务器ID。
每个Master可以有多个Salve。

docker-compose #

github

# 拉起Master和Slave
$ docker-compose -p mysql-repl up
# 连接Master
$ docker exec -it mysql-repl_mysql-master_1 mysql -u root -p
# 连接Slave
$ docker exec -it mysql-repl_mysql-slave_1 mysql -u root -p

创建Replication用户 #

到Master上创建Replication用户：

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

将Slave和Master关联 #

到Slave上把自己和Master关联起来：

并且CHANGE MASTER TO语句有所不同，使用的是Master的Service Name以及容器内端口3306：

CHANGE MASTER TO 
  MASTER_HOST='mysql-master',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  GET_MASTER_PUBLIC_KEY=1,
  MASTER_AUTO_POSITION=1;

一主一从配置 #

1、基本要求：Master和Slave的MySQL服务器版本一致且后台以服务运行。

# 创建mysql-slave1实例
docker run -p 3307:3306 --name mysql-slave1 \
-v /root/mysql-slave1/log:/var/log/mysql \
-v /root/mysql-slave1/data:/var/lib/mysql \
-v /root/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7

2、主从配置都是配在[mysqld]节点下，都是小写

# Master配置
[mysqld]
server-id=1 # 必须
log-bin=/var/lib/mysql/mysql-bin # 必须
read-only=0
binlog-ignore-db=mysql

# Slave配置
[mysqld]
server-id=2 # 必须
log-bin=/var/lib/mysql/mysql-bin

3、Master配置

# * TO 'username'@'从机IP地址' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'172.18.0.3' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

# 2、刷新命令
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 3、记录下File和Position
# 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      602 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、Slave从机配置

CHANGE MASTER TO MASTER_HOST='172.18.0.4',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.File的编号',
MASTER_LOG_POS=Position的最新值;

# 1、使用用户名密码登录进Master
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
    -> MASTER_USER='zhangsan',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

# 2、开启Slave从机的复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

# 3、查看Slave状态
# Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event # Slave待命状态
                  Master_Host: 172.18.0.4
                  Master_User: zhangsan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 602
               Relay_Log_File: b030ad25d5fe-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 602
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

5、测试主从复制

# Master创建数据库
mysql> create database test_replication;
Query OK, 1 row affected (0.01 sec)

# Slave查询数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_replication   |
+--------------------+
5 rows in set (0.00 sec)

6、停止主从复制功能

# 1、停止Slave
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

# 2、重新配置主从
# MASTER_LOG_FILE 和 MASTER_LOG_POS一定要根据最新的数据来配
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
    -> MASTER_USER='zhangsan',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=797;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.18.0.4
                  Master_User: zhangsan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 797
               Relay_Log_File: b030ad25d5fe-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 797
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

如何分库分表 #

想像成是对一张表进行拆分
垂直：就是把一些列进行拆分，一部分列移到另一个表。水平：就是把记录(数据)进行拆分,列的结构不变。

垂直拆分 #

垂直分库 #

根据业务不同与微服务类似单独服务对应单独库

垂直分表 #

垂直分表是基于数据库中的”列”进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过”大表拆小表”，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了数据库性能。

拆分字段的操作建议在数据库设计阶段就做好。如果是在发展过程中拆分，则需要改写以前的查询语句，会额外带来一定的成本和风险，建议谨慎

水平拆分 #

水平拆分（根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。）

优缺点：
- 优点：
  - 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
  - 应用端改造较小，不需要拆分业务模块
  - “冷热数据分离”实现方案
- 缺点：
  - 跨分片事务难以保证
  - 跨分片的复杂查询如join关联查询
  - 数据多次扩展难度和维护量极大

分库分表会遗留的问题 #

事务问题 #

跨表join，聚合查询order by，group by等问题 #

将原本处于mysql执行的跨表查询以及聚合查询等操作提前到网关层进行聚合。比如说现在你有这样一条SQL: select * from tableXX order by create_time desc limit 0,10; 则会从512张表中每张表都获取10条数据，然后再网关层就会出现512*10条数据，然后重新排序聚合提取10条数据返回给应用。带来的就是性能响应时间增加。

数据倾斜问题 #

分库分表下的主键id问题 #

分库分表下的hash数据到每个表，会存在两种情况，
一种是数据库自增id，一种是分布式全局共用一处生成主键id。

先说数据库自增id，会导致我们刚才提到的假设我们现在要聚合查询，这样可能导致会出现512条id一致的数据，这样前端应用就会出现困扰。因为id是必须唯一的才能保证我们获取数据，那么我们不使用自增id，

我们必须通过每条数据的某个值能够确定该行唯一数据，并使512张表的主键id都不一致但是有序，为什么需要补充有序？
主键id不一致大家都知道防止冲突。是因为我们如果使用Innodb数据存储引擎的话底层是红黑树，那么对于连续存储的key值可以有效减少随机访问次数和IO次数提升我们查询的性能，达到每次读取page页可以预读取。

接下来说如何实现分布式全局主键id的几种方式：

Sequence ID 数据库自增长序列或字段，最常见的方式。由数据库维护，数据库唯一。优点：简单，代码方便，性能可以接受。数字ID天然排序，对分页或者需要排序的结果很有帮助。缺点：不同数据库语法和实现不同，数据库迁移的时候或多数据库版本支持的时候需要处理。在单个数据库或读写分离或一主多从的情况下，只有一个主库可以生成。有单点故障的风险。在性能达不到要求的情况下，比较难于扩展。如果遇见多个系统需要合并或者涉及到数据迁移会相当痛苦。分表分库的时候会有麻烦。优化方案：针对主库单点，如果有多个Master库，则每个Master库设置的起始数字不一样，步长一样，可以是Master的个数。比如：Master1 生成的是 1，4，7，10，Master2生成的是2,5,8,11 Master3生成的是 3,6,9,12。这样就可以有效生成集群中的唯一ID，也可以大大降低ID生成数据库操作的负载。
UUID 常见的方式,128位。可以利用数据库也可以利用程序生成，一般来说全球唯一。优点：简单，代码方便。全球唯一，在遇见数据迁移，系统数据合并，或者数据库变更等情况下，可以从容应对。缺点：没有排序，无法保证趋势递增。 UUID往往是使用字符串存储，查询的效率比较低。存储空间比较大，如果是海量数据库，就需要考虑存储量的问题。传输数据量大不可读。优化方案：为了解决UUID不可读，可以使用UUID to Int64的方法。
GUID GUID：是微软对UUID这个标准的实现。UUID还有其它各种实现，不止GUID一种。优缺点同UUID。
COMB COMB（combine）型是数据库特有的一种设计思想，组合的方式，保留UniqueIdentifier的前10个字节，用后6个字节表示GUID生成的时间（DateTime），将时间信息与UniqueIdentifier组合起来，在保留UniqueIdentifier的唯一性的同时增加了有序性，以此来提高索引效率。优点：解决UUID无序的问题，在其主键生成方式中提供了Comb算法(combined guid/timestamp)。保留GUID的10个字节，用另6个字节表示GUID生成的时间(DateTime)。性能优于UUID。
Twitter的snowflake算法使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号（意味着每个节点在每毫秒可以产生 4096 个 ID），最后还有一个符号位，永远是0。snowflake算法可以根据自身项目的需要进行一定的修改。比如估算未来的数据中心个数，每个数据中心的机器数以及统一毫秒可以能的并发数来调整在算法中所需要的bit数。优点：不依赖于数据库，灵活方便，且性能优于数据库。 ID按照时间在单机上是递增的。缺点：在单机上是递增的，但是由于涉及到分布式环境，每台机器上的时钟不可能完全同步，也许有时候也会出现不是全局递增的情况。