1.mysql进阶

总结mysql常见的存储引擎以及特点。

1. InnoDB

mysql默认的存储引擎
事务型引擎
自动崩溃恢复
InnoDB基于聚簇索引建立，MySQL innodb的主键索引是簇集索引，不是主键索引的就是非簇集索引
适合执行大量更新、插入操作
适合高并发和高QPS

2. MyISAM

不是事务安全的
不支持外键
只支持表级锁
适合执行大量查询操作

3. memory

所有数据都保存在内存中
Memory 表的结构在重启以后还会保留，但数据会丢失

4. archive

针对高写入压缩做了优化的简单引擎
适合日志和数据采集应用

5. blackhole

没有实现任何存储机制，它会舍弃所有写入数据，但是服务器会记录 Blackhole 表的日志。(不推荐)

6. federated

访问其他 MySQL 服务器的一个代理，默认禁用。(开发出来属于商业竞争行为)

7. merge (已被放弃)

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

Mysql 中主要使用MyISAM 和 InnoDB这两种存储引擎

简单介绍区别

MyISAM是非事务安全的，而InnoDB是事务安全的
MyISAM锁的粒度是表级的，而InnoDB支持行级锁
MyISAM支持全文类型索引，而InnoDB不支持全文索引
MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM5、MyISAM表保存成文件形式，跨平台使用更加方便

数据结构

两种引擎所使用的索引的数据结构都是B+树!
MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。(但是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。)
Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
簇集索引：索引的叶子节点存的是整个单条记录的所有字段值。
非簇集索引：索引的叶子节点存的是被索引字段的值。

应用场景

MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM
InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

MySQL日志各类总结。

日志既会影响mysql的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地开启日志。根据不同的使用环境，考虑开启不同的日志。例如开发环境中优化查询效率低的语句，可以开启慢查询日志，或者生产环境中发现某些SQL执行特别慢也可以开启如果磁盘空间不是特充足可以在高峰期间开启，在捕获到查询慢的SQL之后再关闭慢查询日志如果需要搭建复制环境，那么就一定要开启二进制日志，如果数据特别重要也建议开启二进制日志，以便数据库损坏的时候也可以通过二进制日志挽救一部分数据,通用日志无论在哪种情况下，一般不建议开启

在 MySQL 系统中，有着诸多不同类型的日志。各种日志都有着自己的用途，通过分析日志，我们可以优化数据库性能，排除故障，甚至能够还原数据。这些不同类型的日志有助于我们更清晰的了解数据库，在日常学习及运维过程中也会和这些日志打交道。本节内容将带你了解 MySQL 数据库中几种常用日志的作用及管理方法。

1. 错误日志（errorlog）

错误日志记录着 mysqld 启动和停止，以及服务器在运行过程中发生的错误及警告相关信息。当数据库意外宕机或发生其他错误时，我们应该去排查错误日志。

log_error 参数控制错误日志是否写入文件及文件名称，默认情况下，错误日志被写入终端标准输出stderr。当然，推荐指定 log_error 参数，自定义错误日志文件位置及名称。

#指定错误日志位置及名称
vim /etc/my.cnf 
[mysqld] 
log_error = /data/mysql/logs/error.log

相关配置变量说明：
log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME}
定义错误日志文件。作用范围为全局或会话级别，属非动态变量。

2. 慢查询日志（slow query log）

慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。
与慢查询相关的几个参数如下：

slow_query_log ：是否启用慢查询日志，默认为0，可设置为0，1。
slow_query_log_file ：指定慢查询日志位置及名称，默认值为 host_name-slow.log，可指定绝对路径。
long_query_time ：慢查询执行时间阈值，超过此时间会记录，默认为10，单位为s。
log_output ：慢查询日志输出目标，默认为file，即输出到文件。
默认情况下，慢查询日志是不开启的，一般情况下建议开启，方便进行慢SQL优化。在配置文件中可以增加以下参数：

#慢查询日志相关配置，可根据实际情况修改
vim /etc/my.cnf 
[mysqld] 
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log_output = FILE

3. 一般查询日志（general log）

一般查询日志又称通用查询日志，是 MySQL 中记录最详细的日志，该日志会记录 mysqld 所有相关操作，当 clients 连接或断开连接时，服务器将信息写入此日志，并记录从 clients 收到的每个 SQL 语句。当你怀疑 client 中的错误并想要确切知道 client 发送给mysqld的内容时，通用查询日志非常有用。

默认情况下，general log 是关闭的，开启通用查询日志会增加很多磁盘 I/O，所以如非出于调试排错目的，不建议开启通用查询日志。相关参数配置介绍如下：

#general log相关配置
vim /etc/my.cnf 
[mysqld]
general_log = 0 //默认值是0，即不开启，可设置为1
general_log_file = /data/mysql/logs/general.log //指定日志位置及名称

4. 二进制日志（binlog）

关于二进制日志，前面有篇文章做过介绍。它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。常用于数据恢复和主从复制。

与 binlog 相关的几个参数如下：

log_bin ：指定binlog是否开启及文件名称。
server_id ：指定服务器唯一ID，开启binlog 必须设置此参数。
binlog_format ：指定binlog模式，建议设置为ROW。
max_binlog_size ：控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。
expire_logs_days ：控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。

binlog默认情况下是不开启的，不过一般情况下，建议开启，特别是要做主从同步时。

# binlog 相关配置
vim /etc/my.cnf 
[mysqld]
server-id = 1003306
log-bin = /data/mysql/logs/binlog
binlog_format = row
expire_logs_days = 15

5. 中继日志（relay log）

中继日志用于主从复制架构中的从服务器上，从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志，然后由 IO 进程读取并执行中继日志中的语句。

relay log 相关参数一般在从库设置，几个相关参数介绍如下：

relay_log ：定义 relay log 的位置和名称。
relay_log_purge ：是否自动清空不再需要中继日志，默认值为1(启用)。
relay_log_recovery ：当 slave 从库宕机后，假如 relay log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay log ，并且重新从 master 上获取日志，这样就保证了 relay log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为1可开启此功能。

relay log 默认位置在数据文件的目录，文件名为 host_name-relay-bin，可以自定义文件位置及名称。

# relay log 相关配置,从库端设置
vim /etc/my.cnf 
[mysqld]
relay_log = /data/mysql/logs/relay-bin
relay_log_purge = 1
relay_log_recovery = 1

6. 重做日志（redo log）

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么 MySQL 是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为 Innodb 是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了。
一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机 IO 写入性能太差。
因此 MySQL 设计了 redo log ，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log 包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo log file）。MySQL 每执行一条 DML 语句，先将记录写入 redo log buffer ，后续某个时间点再一次性将多个操作记录写到 redo log file 。

默认情况下，redo log 在磁盘上由名为 ib_logfile0 和 ib_logfile1 的两个物理文件展示。redo log 相关参数简单介绍如下：

innodb_log_files_in_group：redo log 文件的个数，命名方式如：ib_logfile0，iblogfile1... iblogfilen。默认2个，最大100个。
innodb_log_file_size：单个 redo log 文件设置大小，默认值为 48M，最大值为512G，注意最大值指的是整个 redo log 系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G。
innodb_log_group_home_dir：指定 redo log 文件组所在的路径，默认./ ，表示在数据库的数据目录下。
innodb_log_buffer_size：redo log buffer 大小，默认16M。延迟事务日志写入磁盘，把 redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit 参数的设置，再把日志从 buffer 中 flush 到磁盘中。
innodb_flush_log_at_trx_commit：控制 redo log 刷新到磁盘的策略，默认为1。值为1，每次 commit 都会把 redo log 从 redo log buffer 写入到 system ，并 fsync 刷新到磁盘文件中。值为2，每次事务提交时 MySQL 会把日志从 redo log buffer 写入到 system ，但只写入到 file system buffer，由系统内部来 fsync 到磁盘文件。如果数据库实例 crash ，不会丢失 redo log，但是如果服务器 crash，由于 file system buffer 还来不及 fsync 到磁盘文件，所以会丢失这一部分的数据。值为0，表示事务提交时不进行写入 redo log 操作，这个操作仅在 master thread 中完成，而在 master thread 中每1秒进行一次重做日志的 fsync 操作，因此实例 crash 最多丢失1秒钟内的事务。

更改 redo log 及其 buffer 大小是需要重启数据库实例的，建议初始化时做好评估。可以适当加大 redo log 组数和大小，特别是你的数据库实例更新比较频繁的情况下。但也不推荐 redo log 设置过大。

7. 回滚日志（undo log）

undo log 主要用于保证数据的原子性，保存了事务发生之前的数据的一个版本，可以用于回滚。比如一条 INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时，undo log 也是 MVCC (多版本并发控制) 实现的关键。

MySQL 5.7 版本中，undo log 默认存放在共享表空间 ibdata 中。也可以在初始化时通过配置参数改成独立的文件，简单介绍几个 undo log 相关参数：

innodb_max_undo_log_size：控制最大 undo tablespace 文件的大小，当启动了innodb_undo_log_truncate 时，undo tablespace 超过 innodb_max_undo_log_size 阀值时才会去尝试truncate。该值默认大小为1G，truncate后的大小默认为10M。
innodb_undo_tablespaces：设置 undo 独立表空间个数，范围为0-128，5.7版本默认为0，0表示不开启独立undo表空间。该参数只能在最开始初始化 MySQL 实例的时候指定。
innodb_undo_directory：设置 undo 表空间的存放目录，默认数据目录。
innodb_undo_log_truncate：设置 undo 表空间是否自动截断回收。该参数生效的前提是，已设置独立表空间且独立表空间个数大于等于2个。

undo log 相关参数一般很少改动。MySQL 8.0 默认启用了独立表空间，可能 undo log 表空间的大小设置更灵活些。

主从复制及主主复制的实现

1. 主从复制

实验环境：2台装有mariadb的centos6，ip地址分别为192.168.198.203（master ），192.168.194.90（slave）

测试：在master上新建一个数据库，查看slave中是否同步

master上的配置：
1. 启动二进制日志；在mariadb的配置文件/etc/my.cnf中添加

[mysqld]
log_bin=mysql-bin #开启二进制日志功能，并将二进制命名为mysql-bin

为当前节点设置一个全局惟的ID号；

[mysqld]
server_id=1   #服务器号，要全局唯一

创建有复制权限的用户账号；

REPLICATION SLAVE, REPLICATION CLIENT
mariadb>  grant replication slave,replication client on *.* to 'repluser'@'192.168.194.90' identified by 'huanghu';

slave上的配置：

启动中继日志；

     [mysqld]
     relay_log=relay-log
     relay_log_index=relay-log.index

为当前节点设置一个全局惟的ID号；

           [mysqld]
           server_id=2

3.使用有复制权限的用户账号连接至主数据库，并启动复制线程；

change master to master_host='192.168.198.203',master_user='repluser',master_password='huanghu',master_log_file='master-bin.000003', master_log_pos=7828;
## #master_log_file与master_log_pos的值根据在master中运行show master status的结果来确定；

查看salve是否已经连接上master

              show slave status
       Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.198.203
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 8820
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

slave与master已经建立连接

2. 主主复制：

两台mariadb服务器互为主从，这样可以用来实现数据的读负载均衡，但是写请求在两台服务器上都得直接或间接执行；另外主主模型，不可避免的将会存在时间的延迟与及数据的不一致

实验环境：2台装有mariadb的centos6，ip地址分别为192.168.198.203（A ），192.168.194.90（B）

配置如下：
1.设置A为slave，B为master
a. 在/etc/my.cnf中添加以下内容

        [mariadb]
        server_id=1 
        relay_log=relay-log ###开启中继日志功能，并将其命名为relay-log
        skip_name_resolv=1####关闭主机名解析

在B的配置文件中添加以下内容：

[mariadb]
server_id=2 
log_bin=master-bin
skip-name-resolv=1

b. 连接进入mariadb服务器，创建有复制功能的用户账号，并开启复制线程

在B上查看二进制日志的相应信息

MariaDB [hellodb]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      653 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在B上授权一个有复制功能的用户：

MariaDB [hellodb]> grant replication slave,replication client on *.* to 'repluser'@'192.168.198.203' identified by 'huanghu';

在B上设置当A 做为salve时的master服务器

grant master to master_host='192.168.194.90', master_user='repluser',master_password='huanghu',master_log_file='master-bin.000001',master_log_pos=653;

在A上查看连接master服务器的信息

MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.194.90
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 653
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 758
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

设置A为slave，B为master完成

2、对A 设置master ,B设置slave

在A 的配置文件/etc/my.cnf中添加以下内容

[mariadb]
log_bin=master-bin

在B的配置文件中添加以下内容

[mariadb]
relay_log=relay-log

在A中设置具有复制权限的用户

mysql>  grant replication slave,replication client on *.* to 'repluser'@'192.168.194.90' identified by 'huanghu';

查看A主机当前的二进制日志信息

MariaDB [hellodb]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |      473 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

在B上设置其对应的master服务器

MariaDB [hellodb]> change master to master_host='192.168.198.203',master_user='repluser',master_password='huanghu' master_log_file='master-bin.000004' master_log_pos=473;

启动I/O THREAD与SQL THREAD线程

start slave

查看B设置是否成功

MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.198.203
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 473
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 758
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

B主机为slave，A为master也设置成功了

3.半同步复制

主从复制除了具有读数据负载均衡的作用外，还可以用来做主服务器的数据备份；而为了能使从数据库上的数据是主数据库的完整备份，引入了半同步复制；

半同步复制的原理：半同步是在简单的主从复制的结构上发展而来的，当主数据库上执行对数据的修改（增、删、改）时，不再直接的返回客户端，而是在从数据库上至少已经有一台已经同步了数据后才返回；

在主从复制已可以正常工作的前提下配置半同步复制：

在master服务器上安装 semisync_master.so模块

查看mariadb安装时是否带有该模块

rpm -ql MariaDB-server
/usr/lib64/mysql/plugin/ha_tokudb.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/sphinx.so
/usr/lib64/mysql/plugin/sql_errlog.so

行装载操作

A:    MariaDB [hellodb]> install plugin rpl_semi_sync_master soname 'semisync_master.so';##plugin:后接插件名 soname:后接模块名
B:    MariaDB [hellodb]> install plugin rpl_semi_sync_master soname 'semisync_slave.so';

查看与rpl_semi_sync_master插件相关的信息

+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

可以看到，插件装载后并不会自动启动生效，还需要手动开启

开启A与B上的插件功能

A:   MariaDB [hellodb]> set global rpl_semi_sync_master_enabled=1;
B:   MariaDB [hellodb]> set global rpl_semi_sync_slave_enabled=1;

查看插件相关参数

MariaDB [hellodb]> show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 14467 |
| Rpl_semi_sync_master_net_wait_time         | 28935 |
| Rpl_semi_sync_master_net_waits             | 2     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 2507  |
| Rpl_semi_sync_master_tx_wait_time          | 5014  |
| Rpl_semi_sync_master_tx_waits              | 2     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 2     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

半同步复制完成

xtrabackup实现全量+增量+binlog恢复库

执行全量备份

innobackupex --user=root --password=123456 --no-timestamp /data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (3,'incremental1');
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
+----+--------------+

执行第一次增量备份

innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-1 --incremental-basedir=/data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (4,'incremental2');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+

执行第二次增量备份

 innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-2 --incremental-basedir=/data/backup/incremental-1

删除数据

rm -rf /data/mysql/*
rm -rf /data/mysql_logs/*

准备

innobackupex --apply-log --redo-only /data/backup/all-db/
innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-1
innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-2
innobackupex --apply-log /data/backup/all-db/

恢复

systemctl stop mariadb
innobackupex --copy-back /data/backup/all-db/
chown -R mysql:mysql /data/mysql*

验证

systemctl start mariadb
MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+
MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+

1.mysql进阶

总结mysql常见的存储引擎以及特点。

1. InnoDB

2. MyISAM

3. memory

4. archive

5. blackhole

6. federated

7. merge (已被放弃)

Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

简单介绍区别

数据结构

应用场景

MySQL日志各类总结。

1. 错误日志（errorlog）

2. 慢查询日志（slow query log）

3. 一般查询日志（general log）

4. 二进制日志（binlog）

5. 中继日志（relay log）

6. 重做日志（redo log）

7. 回滚日志（undo log）

主从复制及主主复制的实现

1. 主从复制

2. 主主复制：

3.半同步复制

xtrabackup实现全量+增量+binlog恢复库

执行全量备份

添加数据

执行第一次增量备份

添加数据

执行第二次增量备份

删除数据

准备

恢复

验证

推荐阅读更多精彩内容