数据库版本：Server version: 5.6.24-log MySQL Community Server (GPL)在主备库上均检查 GTID 模式是否开启：mysql> show variables like '%gtid_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | OFF | +---------------+-------+ 1 row in set (0.00 sec)这儿未开启，则后面重新同步时需要指定详细位置。在从库停止复制并记录位置：mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 148.7.5.19 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 223572050主库修改密码：set password for 'sync'@'148.7.5.137' = password('YPASSWD');备库上修改同步信息：change master to MASTER_HOST='148.7.5.19', MASTER_USER='sync', MASTER_PASSWORD='YPASSWD', MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=223572050;开启同步并检查状态：mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 148.7.5.19 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000015 Read_Master_Log_Pos: 223572194 Relay_Log_File: mypdb-slave-relay-bin.000002 Relay_Log_Pos: 427 Relay_Master_Log_File: mysql-bin.000015 Slave_IO_Running: Yes Slave_SQL_Running: Yes遗留问题：主库：select user,host from mysql.user; +--------+-------------+ | user | host | +--------+-------------+ | msp | % | | root | % | | root | 127.0.0.1 | | sync | 148.7.5.137 | | root | ::1 | | dboper | localhost | | root | mypdb | +--------+-------------+ 7 rows in set (0.00 sec)备库：mysql> select user,host from mysql.user; +--------+--------------+ | user | host | +--------+--------------+ | msp | % | | root | 127.0.0.1 | | root | ::1 | | dboper | localhost | | root | localhost | | root | msp-db-slave | +--------+--------------+ 6 rows in set (0.00 sec)从主备库的用户可知，该库当时创建主从同步时，未考虑数据库切换的情况，如果发生数据库切换，新备库将不能从新主库进行同步。但该数据库系统承载的业务已经下线，仅保留数据库供偶而手动查询，故不再改进该问题。
主从搭建1.创建主库，my.cnf 模版已经打开了相关关键参数准备主库# rm /opt/database/3306/auto.cnf mysql> create user 'repl'@'主库IP' identified by 'xxx'; mysql> create user 'repl'@'从库IP' identified by 'xxx'; mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'主库IP'; mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'从库IP'; alter user 'root'@'localhost' identified by 'xxx'; 3.备份主库如果存储引擎都支持事务：mysqldump -u root -p --single-transaction -A -B --master-data=2 > /opt/database/alldb.sql-- master-data=2 是之前没有使用 GTID 时的方法，可以保留，但实际上不会用到。如果有不支持事务的存储引擎：mysqldump -u root -p -A -B -x --events --master-data=2 > /xxx-x 会在备份过程中锁表如果使用 mydumper 进行备份恢复，其产生的 metadata 文件示例如下：Started dump at: 2019-12-25 09:24:41 SHOW MASTER STATUS: Log: bin.000009 Pos: 453 GTID:0e7c2f36-ca06-11e9-bdf8-000c291ca865:1 Finished dump at: 2019-12-25 09:24:414.准备从库# rm -f /opt/database/3306/auto.cnf # vim /etc/my.cnf server-id = 22 read_only = 1 # /etc/init.d/mysqld start mysql> alter user 'root'@'localhost' identified by 'xxx'; < --- 将备库 root 密码修改为主库一致5.恢复数据库并配置主从关系如果是 mydumper 备份，需要配置跳过 GTID，执行这一步的作用是表明这儿的GTID已经在备份中包含了，不需要在 slave 上重做了。如果是通过 mysqldump 进行的备份，跳过该步骤，在备份文件中会自动包含 SET 命令。mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1'; ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. mysql> reset master; mysql> SET @@GLOBAL.GTID_PURGED='0e7c2f36-ca06-11e9-bdf8-000c291ca865:1';# mysql -uroot -p < alldb.sql ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. mysql> reset master; mysql> reset slave; # mysql -uroot -p < alldb.sql mysql> change master to master_host="主库IP",master_port=3306,master_user='repl',master_password='xxx',master_auto_position=1; 以前都是通过 master_log_file 和 master_log_pos 来定位，引入 GTID 后，使用 master_auto_position 自动定位 mysql> start slave; mysql> show slave status\G查看从库状态：mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.111.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000001 <-- change master中的filename Read_Master_Log_Pos: 585 <-- change master中的pos Relay_Log_File: object-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: bin.000001 Slave_IO_Running: No <-- 目前slave还未运行，所以是No Slave_SQL_Running: No <-- 目前slave还未运行，所以是No 开启同步mysql> start slave;查看从库状态：mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event <-- IO 线程的状态 Master_Host: 192.168.111.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000001 <-- IO线程读取到的文件 [The name of the source binary log file from which the I/O thread is currently reading.] Read_Master_Log_Pos: 585 <-- IO线程读取文件中的位置 Relay_Log_File: object-relay-bin.000003 Relay_Log_Pos: 314 Relay_Master_Log_File: bin.000001 <-- SQL线程执行到的文件 [The name of the source binary log file containing the most recent event executed by the SQL thread.] Slave_IO_Running: Yes <-- IO 线程正在运行 Slave_SQL_Running: Yes <-- SQL 线程正在运行 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: 585 <-- SQL线程执行到文件的位置 [The position in the current source binary log file to which the SQL thread has read and executed] Relay_Log_Space: 522 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 <-- Slave 落后 Master 的秒数 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: <-- IO 线程相关的错误 Last_SQL_Errno: 0 Last_SQL_Error: <-- SQL 线程相关的错误 Replicate_Ignore_Server_Ids: Master_Server_Id: 128 Master_UUID: 0e7c2f36-ca06-11e9-bdf8-000c291ca865 Master_Info_File: /usr/local/mysql-5.7.23-linux-glibc2.12-x86_64/data/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)通过 show processlist 在主库可以看到 "Binlog Dump" 进程，在从库可以看到 IO 进程和 SQL 进程。如果在备库上开启了并行复制， 还可以看到 Coordinator 线程。关于 Seconds_Behind_MasterThis field is an indication of how “late” the replica is:• When the replica is actively processing updates, this field shows the difference between the current timestamp on the replica and the original timestamp logged on the source for the event currently being processed on the replica.• When no event is currently being processed on the replica, this value is 0.In essence, this field measures the time difference in seconds between the replica SQL thread and the replica I/O thread. If the network connection between source and replica is fast, the replica I/O thread is very close to the source, so this field is a good approximation of how late the replica SQL thread is compared to the source.Seconds_Behind_Master 字面意思为 Slave 落后 Master 的秒数，但是实际上并不是十分准确。其计算的方式为：Slave 的 SQL 线程执行时刻的时间减去 event 产生时刻的时间（event_header 中的前4个字节就是 timestamp）。Seconds_Behind_Master 测算主从延时的方式并不准确，因为回放的SQL线程可能落后很多。我们可以使用 Percona ToolKit 中的 pt-heardbeat 工具来计算主从之间的延时时间，其原理是：在 Master 节点上创建一个带有时间戳字段的表，pt-heartbeat --update 每隔一段时间写入时间戳，通过主从复制，将表中的内容（带有时间戳的记录）复制到 Slave 节点，在某台主机上运行 pt-heartbeat --monitor 连接到从服务器，使用本地时间戳减去复制到 Slave 节点的记录的时间戳，就可知道Slave落后Master的时间。主从复制的启停mysql> start slave; mysql> stop slave; mysql> stop slave io_thread; mysql> stop slave sql_thread;查看复制关系mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 130 | | 3306 | 128 | b6c03287-15af-11ea-a20c-000c29e56203 | +-----------+------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)看到 HOST 字段为空，如果想能显示是哪一台主机，需要在 slave 的 my.cnf 中增加 report-host 配置，然后重启MySQL实例。[mysqld] report-host=192.168.111.130 <-- 指明 slave 的 IP 地址，这儿写什么，上面的 show 命令就显示什么如果想看到主从关系的拓扑图，可以使用第三方的 mysqlrplshow 工具。查看半同步状态（是否启用半同步复制）mysql> show global status like "%rpl%"; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 1 | <-- 连接的半同步复制的client数量 | Rpl_semi_sync_master_net_avg_wait_time | 0 | <-- master平均等待slave的时间，mysql-5.7.8 后被废弃 | Rpl_semi_sync_master_net_wait_time | 0 | <-- master总的等待slave的时间，mysql-5.7.8 后被废弃 | Rpl_semi_sync_master_net_waits | 1 | <-- master总的等待slave的次数 | Rpl_semi_sync_master_no_times | 1 | <-- 切成异步的次数（no = number of） | Rpl_semi_sync_master_no_tx | 1 | <-- 切成异步后提交的事务数 | Rpl_semi_sync_master_status | ON | <-- 当前半同步复制的状态（是否启用了半同步，如果超时切成了异步，这儿会显示 OFF） | Rpl_semi_sync_master_timefunc_failures | 0 | <-- master调用gettimeofday()函数失败的次数 | Rpl_semi_sync_master_tx_avg_wait_time | 0 | <-- master等待事务的平均时间 | Rpl_semi_sync_master_tx_wait_time | 0 | <-- master等待事务的总的时间 | Rpl_semi_sync_master_tx_waits | 0 | <-- master等待事务的次数 | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 15 rows in set (0.02 sec)引入 GTID 后的冲突处理场景：先在 slave 上插入一台记录，然后在主库插入一台相同主键的记录，此时，slave 的 SQL 线程会停止，状态信息中会报 1062 错误。开始 GTID 后，不能使用 sql_slave_skip_counter 参数来处理复制错误。下面是接收到的 GTID 和执行了的 GTID 信息： Retrieved_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:2-3 Executed_Gtid_Set: 0e7c2f36-ca06-11e9-bdf8-000c291ca865:1-2, b6c03287-15af-11ea-a20c-000c29e56203:1可见，接收到了3，只执行到了2，如果主继续生产一个新的事务，仍会被接收，3会变成4，但2还是保持2。解决办法：将下一下要执行但是不能执行的事务设置为一个空事务，以此跳过该有问题的事务：set gtid_next='0e7c2f36-ca06-11e9-bdf8-000c291ca865:3'; begin; commit; set gtid_next='automatic'; start slave;多源复制要求：将 192.168.111.128 上的 demodb 和 192.168.111.130 上的 cydb 数据库都复制到 192.168.111.131 上面。场景：这种操作可以起到数据聚合的效果。将分库分表后的数据聚合在一起，以供其他应用进行分析（前提是数据不冲突）。思想：还是以前的方法，只是在做最后一步 change master 时要加上 for channel 的语法。1.将两个源库备份并在 slave 上还原。2.设定要复制的数据库名在 slave 上面设置需要复制的库，如果不设置的话，默认会同步系统的库（mysql），这样可能会复制出错（因为两个主库的mysql库中可能有相同的记录）。replicate_do_db=dbn1replicate_do_db=dbn23.配置主库信息mysql> change master to master_host="192.168.111.128", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'demodb'; mysql> change master to master_host="192.168.111.130", master_port=3306, master_user='rpl', master_password='rpl', master_auto_position=1 for channel 'cydb';4.启动同步mysql> start slave [for channel 'demodb'];思考：如果192.168.111.130上面也有一个demodb库，会有什么问题？如果从库设置了 slave_skip_errors = ddl_exist_errors，且两个 demodb 没有重复数据，是没有问题的如果从没有设置 slave_skip_errors = ddl_exist_errors，或两个 demodb 有重复数据，就会报错删除复制关系（配置）stop slave; reset slave all;读写分离应用实现读写分离至少有两种方法：应用本身通过代码实现，例如基于动态数据源、AOP的原理来实现写操作时用主数据库，读操作时用从数据库。通过中间件的方式实现，例如通过Mycat，Mycat会“拦截”SQL进行分析，然后选择操作的数据库。SpringBoot整合读写分离（动态切换数据源）1.在项目中创建读数据源和写数据源2.将读数据源和写数据源注册到RoutingDataSource(会给各数据源绑定一个key)3.配置使用AOP技术拦截业务逻辑层方法，判断方法的前缀是否需要做读或者写。如果方法名称中是 get, select, count, list 等，使用读数据源，否则使用写数据源Mycat 实现读写分离官方网址：www.mycat.ioMycat——活跃的、性能好的分库分表开源数据库中间件！Mycat支持MySQL、SQL Server、Oracle、DB2、PostgreSQL 等主流数据库，也支持 MongoDB 这种新型 NoSQL 方式的存储，未来还会支持更多类型的存储。mycat类似于nginx，客户端访问mycat的IP和端口（8066），mycat将其转发给后端的DB服务器。Mycat是阿里的开源框架。其竞争对手是 sharding-jdbc。安装下载软件包，解压即可。export JAVA_HOME=/usr/local/jdk1.8.0_172export JRE_HOME=$JAVA_HOME/jreexport CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/bin:$PATHexport MYCAT_HOME=/usr/local/mycat # 非必须配置文件wrapper.conf：Mycat本身的配置文件server.xml：Mycat定义用户以及系统相关变量，如端口等schema.xml：Mycat虚拟数据库和物理数据库表的关系rule.xnl：Mycat分片(分库分表）规则读写分离配置示例数据库主从复制已经搭建完成，有一个业务用户 app，对数据库 demodb 有完全权限。主：192.168.111.128:3306从：192.168.111.130:33061.配置server.xml <user name="appcat"> <property name="password">appcat</property> <property name="schemas">TESTDB</property> <property name="readOnly">false</property> </user>其它保持默认，按如上所示修改文件最后的内容。这儿配置的用户名和密码是提供给客户端连接使用的，与数据库中真实存在的用户没有关系。这儿配置的数据库名 TESTDB 是虚拟的数据库名，与数据库服务器中的真实存在的数据库没有关系。2.配置schema.xml文件 <?xml version="1.0"?> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="cynode" /> <dataNode name="cynode" dataHost="cyhost" database="demodb" /> <dataHost name="cyhost" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="master" url="192.168.111.128:3306" user="app" password="app"> <readHost host="slave" url="192.168.111.130:3306" user="app" password="app" /> </writeHost> </dataHost> </mycat:schema>3.启动mycat# ./bin/mycat start # mysql -h127.0.0.1 -uappcat -pappcat -P9066 <-- 进入Mycat的管理端 mysql> show @@datasource; +----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+ | DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD | +----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+ | cynode | master | mysql | 192.168.111.128 | 3306 | W | 0 | 1 | 1000 | 5 | 0 | 0 | | cynode | slave | mysql | 192.168.111.130 | 3306 | R | 0 | 4 | 1000 | 13 | 0 | 0 | +----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+ 2 rows in set (0.03 sec)4.客户端连接mysql -uappcat -pappcat -h192.168.111.133 -P8066然后进行读操作和写操作，观察上面的 READ_LOAD 与 WRITE_LOAD 值的变化。Sharding-JDBCApache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立，却又能够混合部署配合使用的产品组成。它们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。Sharding-JDBC 和 DAO 是在同一个项目，在本地经过分片算法分片后，再发给DB服务器。Sharding-Jdbc是一个Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。MyCat属于服务器端数据库中间件，而Sharding-Jdbc是一个本地数据库中间件框架。实现读写分离原理：①需要在配置文件配置读写分离jdbc连接全部交给Sharding ②Sharding-Jdbc会自动判断SQL语句类型，然后获取主的jdbc连接配置或从的jdbc连接发送请求另外，一个小知识点了解一下，在Sharding-JDBC中，自增ID通过雪花算法实现。
已索引二进制日志的作用二进制日志(binlog)记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据库本身并没有修改。如果想记录 select 和 show 操作，只能使用查询日志。如果 UPDATE 操作没有引起数据库的变化，或者 select ... for update 语句是否会记录呢？姜承尧在 《MySQL技术内幕——InnoDB存储引擎》(第2版) P73 的例子里：update t set a = 1 where a = 2;返回该语句影响 0 行，但 binlog 日志里是有记录该语句的。但我在 mysql-5.7.44 和 mysql 8 里做实验时，binlog 日志里均未做记录。对于 mysql 这类特殊的数据库，也会产生二进制日志，但 information_schema 这类没有没有物理实体的库，则不会产生二进制日志。可以通过如下两种途径让特殊操作不产生日志：拥有 super 权限的用户，可以在执行操作前，先执行 set sql_log_bin=0; 命令通过 binlog_do_db 和 binlog_ignore_db 两个选项来控制哪些库产生或者不产生日志。默认都为空，表示都产生日志。总的来说，binlog 主要用来：恢复（recovery）、复制（replication）和审计（audit）。当然，我们还可以通过二进制日志来实现闪回功能（必须 是 ROW 格式），现在有很多基于 binlog 实现闪回功能的开源工具。 mysql 8 自带了闪回功能。二进制日志的开启我们在 my.cnf 模版文件中的配置如下：server-id = 11log_bin = bin.logshow variables 看到的值如下：+--------------------------------------------+------------------------------+ | Variable_name | Value | +--------------------------------------------+------------------------------+ | log_bin | ON | | log_bin_basename | /opt/database/3306/bin | | log_bin_index | /opt/database/3306/bin.index | +--------------------------------------------+------------------------------+生成的 binlog 文件名如下：bin.000001bin.000002可以用 log_bin 来指定 binlog 的路径和文件名，如果 log_bin 参数不指定，则文件名默认为 "主机名-bin.日志序号"，在 datadir 下。bin.index 文件为 binlog 的索引文件，用来存储过往产生的二进制日志序号，在通常情况下，不建议手动修改这个文件。binlog 日志如果不清理，文件编号6位满了后会变成7位。开户 binlog 确实会使数据库性能下降 1%（损失有限），但 binlog 带来了复制和不完全恢复，生产一定要打开。对于多实例环境，开启 binlog 需要同时指定 server-id。例如：环境：mysql-5.7.44-linux-glibc2.12-x86_64(RHEL 7.7)当前环境已经存在一个 3306 端口实例的情况下，下面的极简配置不能启动另外一个实例，且启动实例时 error 日志里没有任何成功或失败的记录。[mysqld] port = 3307 datadir = /opt/database/3307 socket = /tmp/mysql.sock.3307 log_bin = bin.log在上面的配置文件中加入 server-id = 22 后就可以成功启动了。binlog_format线上必须设置为 binlog_format = ROW。.STATEMENTROWMIXED说明记录操作的SQL语句记录操作的每一行数据混合模式优点易于理解数据一致性高、可flashback结合上述两种模式缺点不支持不确定SQL语句每张表一定要有主键之前版本bug较多线上使用不推荐推荐不推荐注意： balckhole 存储引擎不支持 ROW，NDB 存储引擎不支持 STATEMENT。姜：最开始是 STATEMENT，在 MySQL 5.1 之前没有这个参数。对于复制是有一定要求的。如在主服务器运行 rand、uuid 等函数，又或者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致(notsync)。另一个影响是，会发现 InnoDB 存储引擎的默认事务隔离级别是REPEATABLEREAD。这其实也是因为二进制日志文件格式的关系，如果使用 READ COMMITTED 的事务隔离级别(大多数数据库，如 Oracle，Microsoft SQL Server 数据库的默认隔离级别)，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。从 MySQL 5.1 开始有 3 种格式。5.6 有几个小版本是设置的 MIXED，从 5.7 版本开始，默认都设置为了 ROW。在 ROW 格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表行更改情况。基于 ROW 格式的复制类似于 Oracle 的物理Standby。同时，对上述提及的 Statement 格式下复制的问题子以解决。从 MySQL5.1 版本开始，如果设置了 binlog_format 为 ROW，可以将 InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式，可能的情况有：1)表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。2)使用了 UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT() 等不确定函数。3)使用了INSERT DELAY 语句。4)使用了用户定义函数(UDF)。5)使用了临时表(tcemporary table)。其实重要相关参数max_binlog_size：1073741824（1G）my.cnf 模版未对该参数进行调整，默认值即是最大值 1073741824（1G）。单个 binlog 文件的最大大小。binlog_cache_size: 32768my.cnf 模版未对该参数进行调整，默认 32K。此参数是基于会话的。The size of the cache to hold changes to the binary log during a transaction.对于非事务表来说，语句执行后就会立即写 binlog，对于事务表来说，未提交事务的 DML 操作日志会被缓存起来，事务提交时再写入 binlog，该缓存的大小由此参数定义，如果事务产生的记录大于缓存空间，则会使用一个临时文件来保存，事务结束后自动删除。如果经常使用大事务，就需要关注此参数是否设置合理，因为该参数是基于会话的，设置太大会消耗过多内存，设置太小又可能造成使用临时文件。最理想的状态是参数值越小越好，但不要使用临时文件。全局状态变量 Binlog_cache_use 和 Binlog_cache_disk use 可以用来判断该参数是否设置合理：mysql> show global status like 'binlog_cache%'; +-----------------------+----------+ | Variable_name | Value | +-----------------------+----------+ | Binlog_cache_disk_use | 560 | | Binlog_cache_use | 92764233 | +-----------------------+----------+ 2 rows in set (0.00 sec)Binlog_cache_use 显示了使用 binlog cache 的事务数，Binlog_cache_disk_use 则显示了使用临时文件的事务数。如果频繁使用到了临时文件，就应该特别引起关注。max_binlog_cache_sizemy.cnf 模版未对该参数进行调整，32位默认即是最大值4G，64位默认即是最大值 16777216PB，可以认为就是没有限制了。如果一个事务产生的 binlog 日志大于该值，事务会报错回滚。If a transaction requires more than this many bytes, the server generates a error：Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storagesync_binlogControls how often the MySQL server synchronizes the binary log to disk.MySQL 写 binlog 就跟我们写文件一样，可能并没有真正落盘，我们有时为了真正落盘，操作系统提供了 sync 命令。sync_binlog 用来定义 binlog 真正落盘的策略。生产中该参数始终配置为1（最安全），5.7.7 及之后默认值也是1，可以不用过多去研究。sync_binlog=0: Disables synchronization of the binary log to disk by the MySQL server.Instead, the MySQL server relies on the operating system to flush the binary log to disk from time to time as it does for any other file. This setting provides the best performance, but in the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been synchronized to the binary log.sync_binlog=1: Enables synchronization of the binary log to disk before transactions areThis is the safest setting but can have a negative impact on performance due toincreased number of disk writes. In the event of a power failure or operating system crash,transactions that are missing from the binary log are only in a prepared state. This permits theautomatic recovery routine to roll back the transactions, which guarantees that no transaction islost from the binary log. 之前默认值是0，现在已经是1了。sync_binlog=N, where N is a value other than 0 or 1: The binary log is synchronized to diskafter N binary log commit groups have been collected. In the event of a power failure or operating system crash, it is possible that the server has committed transactions that have not been flushed to the binary log. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data lossFor the greatest possible durability and consistency in a replication setup that uses InnoDB withtransactions, use these settings: (my.cnf 模版已经照此配置)• sync_binlog=1.• innodb_flush_log_at_trx_commit=1.Caution: Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI diskcontroller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.innodb_support_xa该参数默认开启，从 MySQL 5.7.10 开启，不允许禁用，MySQL 8.0 移除了该参数。可以不用过多去研究。Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation. The XA mechanism is used internally and is essential for any server that has its binary log turned on and is accepting changes to its data from more than one thread. If you disable innodb_support_xa, transactions can be written to the binary log in a different order than the live database is committing them, which can produce different data when the binary log is replayed in disaster recovery or on a replica. Do not disable innodb_support_xa on a replication source server unless you have an unusual setup where only one thread is able to change data.innodb_support_xa is deprecated; expect it to be removed in a future MySQL release. InnoDBsupport for two-phase commit in XA transactions is always enabled as of MySQL 5.7.10. Disabling innodb_support_xa is no longer permitted as it makes replication unsafe and preventsperformance gains associated with binary log group commit.XA Distributed Transaction support is enabled by default. If you are not using this feature, you can disable the innodb_support_xa configuration option, avoiding the performance overhead of an extra fsync for each transaction.As of MySQL 5.7.10, disabling innodb_support_xa is not permitted as it makes replication unsafe and prevents performance gains associated with binary log group commit. The innodb_support_xa configuration option is removed in MySQL 8.0.The innodb_support_xa system variable, which enables InnoDB support for two-phase commitin XA transactions, is deprecated as of MySQL 5.7.10. InnoDB support for two-phase commit in XA transactions is always enabled as of MySQL 5.7.10.姜承尧 《MySQL技术内幕——InnoDB存储引擎》(第2版) P77：但是，即使将 sync_binlog 设为1，还是会有一种情况导致问题的发生。当使用 ImnoDB 存储引擎时，在一个事务发出 COMMIT 动作之前，由于 sync_binlog 为1，因此会将二进制日志立即写人磁盘。如果这时已经写人了二进制日志，但是提交还没有发生，并且此时发生了容机，那么在 MySQL 数据库下次启动时，由于 COMMIT 操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数 imnnodb_support_xa 设为 1 来解决，虽然 innodb_support_xa 与 XA 事务有关，但它同时也确保了二进制日志和 InnoDB 存储引擎数据文件的同步。Old Boy：默认情况下，二进制日志不是实时同步到磁盘，因此如果操作系统崩溃或者机器故障，存在数据丢失的可能。要防止这种情况的出现，需要考虑的因素比较多，仅从 MSQL 的二进制日志同步来说，可以设置二进制日志同步到磁盘的频率，MySQL 提供了专用的系统变量 sync_binog。该参数的详细说明颇费幅，这里先简要描述与本节主题相关的内容。sync_binlog 值设为 1 安全级别最高，同时也是最慢的设置，不过即使设置为 1 同样有可能存在丢失数据的可能，只是最坏情况下，仅丢失最后执行的那条语句或事务。举例来说，使用 InnoDB 引擎的表通过事务向表中写数据，操作已经写到二进制日志，但还没来得及将提交语句写入日志，这时系统崩溃，那么当数据库服务重新启动时，InnoDB 引擎肯定会将未提交的事务回滚，那么这种情况下，必然造成数据丢失。要解决这种问题，MySQL 另外又提供了初始化参数 --innodb-support-xa，设置该参数值为 1，启用分布式事务的支持，确保二进制日志与 InnoDB 数据文件的同步。这种选项提供了深度的安全性，MySOL 应被配置为以事务为单位同步二进制日志和 InnoDB 日志到磁盘，InnoDB 日志默认即是同步状态，sync_binlog=1 可以同步二进制日志。这样当 MySQL 服务从崩溃中恢复时，为事务执行回滚后，MySQL 服务中断二进制日志中 InnoDB 事务的回滚，以这种方式确保二进制日志能够考虑 InnoDB 表中的实际数据，同样，Slave端也会保持同步状态(因为没有收到回滚的语句)。当 MSQL 服务执行崩溃恢复时发现，二进制日志比期望中要少，比如 InnoDB 事务缺少 commit (当syncbinlog=l时不可能出现这种情况)，服务器端就会抛出错误消息:The binary log file_name is shorter than itsexpected size，这种情况下，说明二进制日志文件有误，复制环境有必要重建。查看二进制日志我们可以使用 show 命令 或者 mysqlbinlog 工具来查看和分析二进制日志。查看当前正在使用的二进制文件mysql> show master status; <--- 查看当前 binlog，Positon 表示当前文件已经写到的位置，代表了当前日志文件的大小，单位为字节 +------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------+----------+--------------+------------------+-------------------+ | bin.000007 | 154 | | demodb | | +------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> flush binary logs; <--- 刷新 binlog，会生成新的 binlog 日志 Query OK, 0 rows affected (0.01 sec) mysql> show master status; <--- 查看新的 binlog 位置 +------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------+----------+--------------+------------------+-------------------+ | bin.000008 | 154 | | demodb | | +------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)除非用命令查看，也可以直接看操作系统上生成的文件，最后一个文件就是当前正在使用的日志文件。show 命令查看二进制文件中的事件官方文档：https://dev.mysql.com/doc/refman/5.7/en/show-binlog-events.htmlmysql> show binlog events in 'bin.000007'; +------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+---------------------------------------+ | bin.000007 | 4 | Format_desc | 22 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 | | bin.000007 | 123 | Previous_gtids | 22 | 154 | | | bin.000007 | 154 | Rotate | 22 | 195 | bin.000008;pos=4 | +------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec) mysql> show binlog events in 'bin.000008'; <--- 查看 binlog 中内容，如果不指定文件，则从第一个文件开始，如果文件不存在，会报错 +------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+---------------------------------------+ | bin.000008 | 4 | Format_desc | 22 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 | | bin.000008 | 123 | Previous_gtids | 22 | 154 | | +------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) binlog 是由各种类型的 event 组成。每个二进制日志由三个部分的 event 组成：Header + Binary Log Events + Rotate。每个日志文件的开头有个叫 Format_desc 的 event，用来记录 Sever 的版本号和 Binlog 的版本号。5.7 加了一个叫 Previous_gtids 的 event。如果文件结束了，有一个叫 Rotate 的 event，Info 列会指下一个文件的起始位置。中间就是具体的 Binary Log Events。每个 event 由(Log_name,Pos)一起来定义。Pos 是 event 的起始位置，End_log_pos 是结束位置。End_log_pos - Pos 就是该 event 占用的字节数。每一行的记录，但是经过 base64 加密过的，这样方便传输， base64 在 web 中是应用比较广的。下面看一下表的创建、删除与 INSERT, UPDATE, DELETE 在二进制日志是如何记录的：mysql> create table cyt (id int, name varchar(20)); Query OK, 0 rows affected (0.05 sec) mysql> insert cyt values (1,'a'); Query OK, 1 row affected (0.03 sec) mysql> update cyt set id=2 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from cyt where id=2; Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> drop table cyt; Query OK, 0 rows affected (0.01 sec) mysql> show binlog events in 'bin.000011'; +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ | bin.000011 | 4 | Format_desc | 22 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 | | bin.000011 | 123 | Previous_gtids | 22 | 154 | | | bin.000011 | 154 | Anonymous_Gtid | 22 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 219 | Query | 22 | 340 | use `demodb`; create table cyt (id int, name varchar(20)) | | bin.000011 | 340 | Anonymous_Gtid | 22 | 405 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 405 | Query | 22 | 479 | BEGIN | | bin.000011 | 479 | Table_map | 22 | 530 | table_id: 110 (demodb.cyt) | | bin.000011 | 530 | Write_rows | 22 | 572 | table_id: 110 flags: STMT_END_F | | bin.000011 | 572 | Table_map | 22 | 623 | table_id: 110 (demodb.cyt) | | bin.000011 | 623 | Update_rows | 22 | 673 | table_id: 110 flags: STMT_END_F | | bin.000011 | 673 | Table_map | 22 | 724 | table_id: 110 (demodb.cyt) | | bin.000011 | 724 | Delete_rows | 22 | 766 | table_id: 110 flags: STMT_END_F | | bin.000011 | 766 | Xid | 22 | 797 | COMMIT /* xid=38 */ | | bin.000011 | 797 | Anonymous_Gtid | 22 | 862 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 862 | Query | 22 | 982 | use `demodb`; DROP TABLE `cyt` /* generated by server */ | +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ 15 rows in set (0.00 sec) Table_map 表示这张表的定义和每个列的类型。Write_rows 表示操作类型是写入一个 rows，但是具体操作是看不到的， 同理有 Update_rows， Delete_rows。默认只能知道每一行的变化，但是不知道变化的操作的 SQL 语句是什么，如果想知道， binlog_rows_query_log_events 设置为 1。(my.cnf 文件中已经新加了该参数)配置后效果如下：mysql> insert into cyt2 values(3); Query OK, 1 row affected (0.00 sec) mysql> mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> show binlog events in 'bin.000018'; +------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+---------------------------------------+ | bin.000018 | 4 | Format_desc | 22 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 | | bin.000018 | 123 | Previous_gtids | 22 | 154 | | | bin.000018 | 154 | Anonymous_Gtid | 22 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000018 | 219 | Query | 22 | 293 | BEGIN | | bin.000018 | 293 | Rows_query | 22 | 343 | # insert into cyt2 values(3) | | bin.000018 | 343 | Table_map | 22 | 392 | table_id: 111 (demodb.cyt2) | | bin.000018 | 392 | Write_rows | 22 | 432 | table_id: 111 flags: STMT_END_F | | bin.000018 | 432 | Xid | 22 | 463 | COMMIT /* xid=9 */ | +------------+-----+----------------+-----------+-------------+---------------------------------------+ 8 rows in set (0.00 sec) 通过 mysqlbinlog 也能看到记录了语句：# mysqlbinlog -vv --base64-output=decode-rows bin.000018 # at 293 #250717 16:59:25 server id 22 end_log_pos 343 CRC32 0x310a4566 Rows_query # insert into cyt2 values(3)生产上 binlog 日志通常很多，直接查看文件中的所有内容将是一个耗时和耗资源的一个过程。Issuing a SHOW BINLOG EVENTS with no LIMIT clause could start a very time- and resource-consuming process because the server returns to the client the complete contents of the binary log (which includes all statements executed by the server that modify data).此时，我们可以使用 from 定位和 limit 限制。SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]仅查看前5条 events:mysql> show binlog events in 'bin.000011' limit 5; +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ | bin.000011 | 4 | Format_desc | 22 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 | | bin.000011 | 123 | Previous_gtids | 22 | 154 | | | bin.000011 | 154 | Anonymous_Gtid | 22 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 219 | Query | 22 | 340 | use `demodb`; create table cyt (id int, name varchar(20)) | | bin.000011 | 340 | Anonymous_Gtid | 22 | 405 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | +------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+ 5 rows in set (0.00 sec)查看从 pos 530 开始的所有日志：mysql> show binlog events in 'bin.000011' from 530; +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ | bin.000011 | 530 | Write_rows | 22 | 572 | table_id: 110 flags: STMT_END_F | | bin.000011 | 572 | Table_map | 22 | 623 | table_id: 110 (demodb.cyt) | | bin.000011 | 623 | Update_rows | 22 | 673 | table_id: 110 flags: STMT_END_F | | bin.000011 | 673 | Table_map | 22 | 724 | table_id: 110 (demodb.cyt) | | bin.000011 | 724 | Delete_rows | 22 | 766 | table_id: 110 flags: STMT_END_F | | bin.000011 | 766 | Xid | 22 | 797 | COMMIT /* xid=38 */ | | bin.000011 | 797 | Anonymous_Gtid | 22 | 862 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 862 | Query | 22 | 982 | use `demodb`; DROP TABLE `cyt` /* generated by server */ | +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ 8 rows in set (0.00 sec)从 pos 530 开始，仅显示4条 events：mysql> show binlog events in 'bin.000011' from 530 limit 4; +------------+-----+-------------+-----------+-------------+---------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+-------------+-----------+-------------+---------------------------------+ | bin.000011 | 530 | Write_rows | 22 | 572 | table_id: 110 flags: STMT_END_F | | bin.000011 | 572 | Table_map | 22 | 623 | table_id: 110 (demodb.cyt) | | bin.000011 | 623 | Update_rows | 22 | 673 | table_id: 110 flags: STMT_END_F | | bin.000011 | 673 | Table_map | 22 | 724 | table_id: 110 (demodb.cyt) | +------------+-----+-------------+-----------+-------------+---------------------------------+ 4 rows in set (0.00 sec)从 pos 530 后面的第 3 条开始，显示5条 events：mysql> show binlog events in 'bin.000011' from 530 limit 3,5; +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ | bin.000011 | 673 | Table_map | 22 | 724 | table_id: 110 (demodb.cyt) | | bin.000011 | 724 | Delete_rows | 22 | 766 | table_id: 110 flags: STMT_END_F | | bin.000011 | 766 | Xid | 22 | 797 | COMMIT /* xid=38 */ | | bin.000011 | 797 | Anonymous_Gtid | 22 | 862 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | bin.000011 | 862 | Query | 22 | 982 | use `demodb`; DROP TABLE `cyt` /* generated by server */ | +------------+-----+----------------+-----------+-------------+----------------------------------------------------------+ 5 rows in set (0.00 sec)mysqlbinlog 工具查看二进制文件中的事件使用 mysqlbinlog 命令时，可以跟多个 binlog 文件，也可以使用通配。# mysqlbinlog [-vv] bin.000011 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #250717 15:19:04 server id 22 end_log_pos 123 CRC32 0xc3d2ae7d Start: binlog v 4, server v 5.7.44-log created 250717 15:19:04 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 6KN4aA8WAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AX2u0sM= '/*!*/; # at 123 #250717 15:19:04 server id 22 end_log_pos 154 CRC32 0xa4f2382e Previous-GTIDs # [empty] # at 154 #250717 15:19:39 server id 22 end_log_pos 219 CRC32 0xbc7e99a9 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #250717 15:19:39 server id 22 end_log_pos 340 CRC32 0x0aa0e6a7 Query thread_id=2 exec_time=0 error_code=0 use `demodb`/*!*/; SET TIMESTAMP=1752736779/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table cyt (id int, name varchar(20)) /*!*/; # at 340 #250717 15:20:40 server id 22 end_log_pos 405 CRC32 0xac867f22 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 405 #250717 15:19:56 server id 22 end_log_pos 479 CRC32 0x9bb0a06d Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752736796/*!*/; BEGIN /*!*/; # at 479 #250717 15:19:56 server id 22 end_log_pos 530 CRC32 0xe6e2c795 Table_map: `demodb`.`cyt` mapped to number 110 # at 530 #250717 15:19:56 server id 22 end_log_pos 572 CRC32 0xc94ce40c Write_rows: table id 110 flags: STMT_END_F BINLOG ' HKR4aBMWAAAAMwAAABICAAAAAG4AAAAAAAEABmRlbW9kYgADY3l0AAIDDwIUAAOVx+Lm HKR4aB4WAAAAKgAAADwCAAAAAG4AAAAAAAEAAgAC//wBAAAAAWEM5EzJ '/*!*/; ### INSERT INTO `demodb`.`cyt` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='a' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ # at 572 #250717 15:20:16 server id 22 end_log_pos 623 CRC32 0x96fc3fb6 Table_map: `demodb`.`cyt` mapped to number 110 # at 623 #250717 15:20:16 server id 22 end_log_pos 673 CRC32 0x83c3949b Update_rows: table id 110 flags: STMT_END_F BINLOG ' MKR4aBMWAAAAMwAAAG8CAAAAAG4AAAAAAAEABmRlbW9kYgADY3l0AAIDDwIUAAO2P/yW MKR4aB8WAAAAMgAAAKECAAAAAG4AAAAAAAEAAgAC///8AQAAAAFh/AIAAAABYZuUw4M= '/*!*/; ### UPDATE `demodb`.`cyt` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### @2='a' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='a' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ # at 673 #250717 15:20:28 server id 22 end_log_pos 724 CRC32 0x1fbddede Table_map: `demodb`.`cyt` mapped to number 110 # at 724 #250717 15:20:28 server id 22 end_log_pos 766 CRC32 0x3319b98e Delete_rows: table id 110 flags: STMT_END_F BINLOG ' PKR4aBMWAAAAMwAAANQCAAAAAG4AAAAAAAEABmRlbW9kYgADY3l0AAIDDwIUAAPe3r0f PKR4aCAWAAAAKgAAAP4CAAAAAG4AAAAAAAEAAgAC//wCAAAAAWGOuRkz '/*!*/; ### DELETE FROM `demodb`.`cyt` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ ### @2='a' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */ # at 766 #250717 15:20:40 server id 22 end_log_pos 797 CRC32 0xa9f4bd1c Xid = 38 COMMIT/*!*/; # at 797 #250717 15:20:43 server id 22 end_log_pos 862 CRC32 0x79d2de35 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 862 #250717 15:20:43 server id 22 end_log_pos 982 CRC32 0x2d2ee1e9 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752736843/*!*/; DROP TABLE `cyt` /* generated by server */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;如果没有 -vv，就不能看到类似 SQL 的语句，可以看到，除了 create table、drop table 外，都是经过 base64 加密的数据。一定要注意，binlog 里面记录的不是 SQL 语句。 @1 表示第1个列，@2表示第2个列insert 和 delete 都是记录整行， update 记录行的前项和后项，所以影响记录数相同的情况下 update 是最消耗日志空间。ROW 格式记录的是每行一操作，所以能确保主从的数据严格一致。缺点是日志数据可能很大，比如 update 全表， delete 全表。如果想看 base64 的编码原文，可以使用 --base64-output=decode-rows 来 decodes row events into commented pseudo-SQL statements。# mysqlbinlog -vv --base64-output=decode-rows bin.000010 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #250717 14:29:16 server id 22 end_log_pos 123 CRC32 0x575e6377 Start: binlog v 4, server v 5.7.44-log created 250717 14:29:16 # Warning: this binlog is either in use or was not closed properly. # at 123 #250717 14:29:16 server id 22 end_log_pos 154 CRC32 0x25ab28cd Previous-GTIDs # [empty] # at 154 #250717 14:30:20 server id 22 end_log_pos 219 CRC32 0xe71c29d5 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #250717 14:30:20 server id 22 end_log_pos 322 CRC32 0x462f7003 Query thread_id=2 exec_time=0 error_code=0 use `demodb`/*!*/; SET TIMESTAMP=1752733820/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table cyt (id int) /*!*/; # at 322 #250717 14:33:06 server id 22 end_log_pos 387 CRC32 0x657e3a57 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 387 #250717 14:30:29 server id 22 end_log_pos 461 CRC32 0x4364f414 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752733829/*!*/; BEGIN /*!*/; # at 461 #250717 14:30:29 server id 22 end_log_pos 509 CRC32 0x18448aa9 Table_map: `demodb`.`cyt` mapped to number 109 # at 509 #250717 14:30:29 server id 22 end_log_pos 549 CRC32 0xd0ff2fb8 Write_rows: table id 109 flags: STMT_END_F ### INSERT INTO `demodb`.`cyt` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ # at 549 #250717 14:30:57 server id 22 end_log_pos 597 CRC32 0xdf967ad3 Table_map: `demodb`.`cyt` mapped to number 109 # at 597 #250717 14:30:57 server id 22 end_log_pos 643 CRC32 0x63a4649c Update_rows: table id 109 flags: STMT_END_F ### UPDATE `demodb`.`cyt` ### WHERE ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 643 #250717 14:33:03 server id 22 end_log_pos 691 CRC32 0x9ac014cd Table_map: `demodb`.`cyt` mapped to number 109 # at 691 #250717 14:33:03 server id 22 end_log_pos 731 CRC32 0x6760e0eb Delete_rows: table id 109 flags: STMT_END_F ### DELETE FROM `demodb`.`cyt` ### WHERE ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 731 #250717 14:33:06 server id 22 end_log_pos 762 CRC32 0xc085329e Xid = 11 COMMIT/*!*/; # at 762 #250717 14:33:21 server id 22 end_log_pos 827 CRC32 0x9d6f245f Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 827 #250717 14:33:21 server id 22 end_log_pos 947 CRC32 0xda1a6a08 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752734001/*!*/; DROP TABLE `cyt` /* generated by server */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysqlnode1 3307]# binlog 中会产生 rollback 的信息吗？不会的。在以前的版本里，引擎混用可能会记录 rollback 信息，在 5.7.44 里测试已经不会记录了。mysql> create table cyt1(id int) engine=myisam;mysql> create table cyt2(id int) engine=innodb;仅使用 innodb 引擎，不记录 rollback：mysql> flush binary logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------+----------+--------------+------------------+-------------------+ | bin.000015 | 154 | | | | +------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into cyt2 values(1); Query OK, 1 row affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec)查看 binlog 日志：# mysqlbinlog -vv bin.000015 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #250717 16:00:24 server id 22 end_log_pos 123 CRC32 0xa073daaf Start: binlog v 4, server v 5.7.44-log created 250717 16:00:24 # Warning: this binlog is either in use or was not closed properly. BINLOG ' mK14aA8WAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA Aa/ac6A= '/*!*/; # at 123 #250717 16:00:24 server id 22 end_log_pos 154 CRC32 0x8e3b9ec9 Previous-GTIDs # [empty] SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;引擎混合1：mysql> flush binary logs; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------+----------+--------------+------------------+-------------------+ | bin.000016 | 154 | | | | +------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> mysql> insert into cyt2 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into cyt1 values(1); Query OK, 1 row affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> # mysqlbinlog -vv bin.000016 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #250717 16:07:29 server id 22 end_log_pos 123 CRC32 0xa09cb40d Start: binlog v 4, server v 5.7.44-log created 250717 16:07:29 # Warning: this binlog is either in use or was not closed properly. BINLOG ' Qa94aA8WAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AQ20nKA= '/*!*/; # at 123 #250717 16:07:29 server id 22 end_log_pos 154 CRC32 0x559e4846 Previous-GTIDs # [empty] # at 154 #250717 16:10:47 server id 22 end_log_pos 219 CRC32 0xef04ac05 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #250717 16:10:47 server id 22 end_log_pos 293 CRC32 0xe6526b61 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752739847/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 293 #250717 16:10:47 server id 22 end_log_pos 342 CRC32 0x42575820 Table_map: `demodb`.`cyt1` mapped to number 115 # at 342 #250717 16:10:47 server id 22 end_log_pos 382 CRC32 0x0ef88b68 Write_rows: table id 115 flags: STMT_END_F BINLOG ' B7B4aBMWAAAAMQAAAFYBAAAAAHMAAAAAAAEABmRlbW9kYgAEY3l0MQABAwABIFhXQg== B7B4aB4WAAAAKAAAAH4BAAAAAHMAAAAAAAEAAgAB//4BAAAAaIv4Dg== '/*!*/; ### INSERT INTO `demodb`.`cyt1` ### SET ### @1=1 /* INT meta=0 nullable=1 is_null=0 */ # at 382 #250717 16:10:47 server id 22 end_log_pos 457 CRC32 0xbb41c65f Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752739847/*!*/; COMMIT /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 引擎混用2：mysql> flush binary logs; Query OK, 0 rows affected (0.01 sec) mysql> show master status; +------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------+----------+--------------+------------------+-------------------+ | bin.000017 | 154 | | | | +------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> insert into cyt1 values(2); Query OK, 1 row affected (0.01 sec) mysql> insert into cyt2 values(2); Query OK, 1 row affected (0.00 sec) mysql> rollback; Query OK, 0 rows affected, 1 warning (0.01 sec)/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #250717 16:13:06 server id 22 end_log_pos 123 CRC32 0xc5770629 Start: binlog v 4, server v 5.7.44-log created 250717 16:13:06 # Warning: this binlog is either in use or was not closed properly. BINLOG ' krB4aA8WAAAAdwAAAHsAAAABAAQANS43LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA ASkGd8U= '/*!*/; # at 123 #250717 16:13:06 server id 22 end_log_pos 154 CRC32 0xa353384c Previous-GTIDs # [empty] # at 154 #250717 16:14:15 server id 22 end_log_pos 219 CRC32 0x7f150385 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #250717 16:14:15 server id 22 end_log_pos 293 CRC32 0x2fa51f37 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752740055/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 293 #250717 16:14:15 server id 22 end_log_pos 342 CRC32 0x89bd6a67 Table_map: `demodb`.`cyt1` mapped to number 115 # at 342 #250717 16:14:15 server id 22 end_log_pos 382 CRC32 0xa924c8cc Write_rows: table id 115 flags: STMT_END_F BINLOG ' 17B4aBMWAAAAMQAAAFYBAAAAAHMAAAAAAAEABmRlbW9kYgAEY3l0MQABAwABZ2q9iQ== 17B4aB4WAAAAKAAAAH4BAAAAAHMAAAAAAAEAAgAB//4CAAAAzMgkqQ== '/*!*/; ### INSERT INTO `demodb`.`cyt1` ### SET ### @1=2 /* INT meta=0 nullable=1 is_null=0 */ # at 382 #250717 16:14:15 server id 22 end_log_pos 457 CRC32 0x3980c5ea Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1752740055/*!*/; COMMIT /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 可见，引擎混用只记录了 MyISAM 的，没有 InonoDB 的。
已索引MySQL 的使用情况MySQL遵循于GPL开源协议的数据库，PG遵循BSD开源协议（可以不再继续开源）。18 of 20 top web sites in the worldEmbedded by of the top 10 ISVsNo. 1 database in the CloudIntegrated w/Hadoop in 80% of Big Data platform从这儿可以看出，MySQL是互联网行业的事实数据库。MySQL 既是全球 Top 20 网站的数据库选择（1.Google、2.Facebook、3.Youtube、4.Yahoo、5.Baidu、6.Wikipedia、7.QQ、8.LinkedIn、9.Live.com[未用]、10.Twitter、11.Amazon、12.Taobao、13.Blogspot、14.GoogleIndia、15.Wordpress、16.Sina、17.Yandex、18.YahooJapan、19.Bing[未用]、20.Hao123），也是世界 500 强的数据库选择。MySQL官网首页 https://www.mysql.com 有使用MySQL 的知名公司案例，点击案例可以了解他们是如何使用MySQL的。国内目前MySQL主要应用于互联网行业的OLTP业务，越来越多的传统企业开始转向MySQL数据库。MySQL 历史MySQL创始人是 Monty Widenius，最早可以追溯到1979年1996年10月 MySQL AB 发布 MySQL 1.0 版本2001年 MySQL AB 发布 MySQL 3.23 版本（最早在生产环境使用的版本）。2001 ~ 2007 开源飞速发展期（MySQL AB）2008.01 ~ 2009.04 SUN 10亿美金收购MySQL（SUN MySQL）2009.04 ~ 至今 Oracle以74亿美金收购SUN（Oracle MySQL）MySQL 分支版本MariaDB没有核心存储引擎且核心代码较老。MySQL 核心团队有 500 多人，MariaDB 只有 10 几个人。Percona Server由Percona公司创建，其知名产品有 XtraBackup 和 XtraDB（InnoDB存储引擎的优化版本），在 Oracle MySQL 5.6 版本以后，Oracle MySQL 将 Percon 之前的优化集成到官方版本中。Drizzle主要用于云环境，与原 MySQL 不兼容。MySQL 在 5.6 以后不断重构源码，安装包越来越大，功能和性能在持续改进。MySQL 5.6 以后的版本，推荐使用官方版本。不建议使用各种分支版本。从 5.7 开始，使用其它分支版本的 MySQL 意义已经不大。MySQL vs MariaDB~MySQLMariaDB核心存储引擎InnoDB无，社区提供多线程复制MySQL 5.7 版本MariaDB 10 版本商业支持Oracle 公司SkySQL 公司开发驱动用户需求与企业驱动社区驱动开发现状代码重构无重构计划如何选择 Oracle MySQL 版本~MySQL 5.5MySQL 5.6MySQL 5.7索引全局读写锁，并发一般全局读写锁，并发一般锁优化，并发性能提升多线程复制不支持基于库的多线程复制基于组提交的多线程复制数据一致性半同步，数据有丢失半同步，数据有丢失无损复制，数据无丢失半结构化数据BLOBBLOB原生JSON格式InnoDB 全文索引不支持支持，但不支持中文支持地理空间索引MyISAM 支持MyISAM 支持InnoDB 也支持事务组提交有 bug，不支持支持支持5.6修复了5.5很多bug，且有显著的性能提升。Oracle MySQL 又分为商业版和社区版，企业版和社区版的代码完全一样，额外的特性通过插件的方式提供给用户。大家基本上都使用的社区版，但是商业版有MySQL 额外特性（线程池插件、审计插件、SQL 安全插件）、MySQL 企业级工具（官方热备工具、官方监控工具）和技术支持。请官方https://www.mysql.com/downloads/ 下载MySQL版本（商业版和社区版都从这个入口进去，社区版在最下面），不要使用迅雷下载。
MySQL 介绍与安装部署MySQL 数据库介绍与选择MySQL 源码包编译安装MySQL 通过 RPM 包安装MySQL 二进制方式安装 （5.6、5.7、8.0）MySQL 原地升级 （5.6 to 5.7）与降级MySQL 多实例、多版本共存MySQL 物理结构MySQL 二进制日志 / binary log / binlogMySQL 实例管理MySQL 数据库的启停MySQL 管理工具MySQL参数文件管理MySQL 备份恢复MySQL备份恢复之mydumperMySQL 复制MySQL 主从搭建与管理深入理解 MySQL Replication / MySQL 复制修改 MySQL 同步用户密码故障案例MySQL 数据库 CPU 使用率超过 90%
MySQL 的参数文件类似 Oracle 的 pfile。参数分类动态参数与只读参数：是否可以在线修改该参数的值。修改时提示只读即为只读参数。全局参数会话级参数：全局参数只能在全局修改(Global)参数。有些参数既可以在会话级修改，也可以在全局修改(Both)。[...]
Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).虽然 InnoDB 也有 redo 日志，但是 MySQL 的复制并不是基于 redo 的，因为 MySQL 是多存储引擎的数据库，采用统一的 binlog 日志进行复制。关于 binlog 日志，请参考下文：[post cid="1623" /]逻辑复制与物理复制.MySQLOracle Data Gurad/SQL Server Mirroring类型逻辑复制物理逻辑复制优点灵活复制速度快缺点配置不当易出错要求物理数据严格一致记录记录每次逻辑操作记录每次对于数据页的操作为了解决逻辑复制慢的问题，MySQL 5.7 以后推出了并行复制（后文会讲），速度提升了很多。主从复制架构MySQL 的一次提交分为三个阶段：第一个阶段是 prepare，第二个阶段是写 binlog，第三个阶段是写 innodb 日志。第1个和第3个阶段发生在 InnoDB 层，第2个阶段发生在 MySQL 层。这三个步骤都是组提交的，所以性能不会差。当日志写到 binlog 之后，会有一个 Dump 线程将日志推送到对端服务器，对端服务器 IO 线程用于接收日志，SQL 线程用于回放日志。从 MySQL 5.6 开始，回放线程可以有多个，并行回放。主往从发送内容是以 EVENT 为单进行发送的。set sync_binlog=1 to assure crash safeInnoDB group commit is fairly randomset innodb_flush_log_at_trx_commit=1 to garantee crash safeset relay_log recovery=1 to enable IO thread crash safesemi-sync replication need send ACK to masterset relay_log_info_repository=TABLE to make SQL threads crash safe在 MySQL 5.7 版本中， Prepare log 部分的日志也是组提交的MySQL Dump Thread 把 binlog 推送到远程的 Slave 服务器（每一个Slave，就会对应有一个 dump 线程）Master Thread 每隔1秒从 redo log buffer 中写入 redo file在 MySQL 5.6 中的多线程回放是基于库的， 单个库还是单线程，在 MySQL 5.7 中的多线程是在主上如何并行执行的，从机上也是如何并行回放的master-info.log 存放了接受到的 binlog 的位置（event的位置）relay-info.log 存放了回放到的 relay log 的位置（event的位置）SQL线程高可靠问题如果将 relay_log_info_repository 设置为 FILE ，MySQL 会把回放情况信息记录在参数 relay_log_info_file 指定的 relay-info.log 的文件中，其中包含SQL线程回放到的 Relay_log_name 和 Relay_log_pos，以及对应的 Master 的 Master_log_name 和 Master_log_pos。SQL 线程回放的基本单位是 event，参数 sync_relay_log_info = 10000 代表每回放 10000 个event， 写一次 relay-info.log [the slave synchronizes its relay-log.info file to disk(using fdatasync()) after every N transactions]，这样就可能发生如下情况：event2 和 event3 回放写入成功(且已经落盘)，但是在 relay-info.log 中的记录还是 event1 的位置。此时Slave宕机，然后重启，便会产生如下的状况：Slave 的库中存在 event2 和 event3Slave 读取 relay-info.log 中的 Relay_log_name 和 Relay_log_pos，此时记录的是回放到 event1 的位置Slave 从 event1 开始回放 ，继续回放 event2 和 event3但是，此时的数据库中存在 event2 和 event3，于是发生了 1062 的错误（重复记录）如果该参数设置为 1 ，则表示每回放一个 event ， 就写一次 relay-info.log，那写入代价很大，性能很差，即使性能上可以接受，还是可能会丢最后一次的操作，恢复起来后还是有 1062 的错误（重复执行event）。SQL 线程的数据回放是写数据库操作，relay-info.log 是写文件操作，这两个操作很难保证一致性。解决办法：在 MySQL5.6+ 以后，将 relay_log_info_repository 设置为 TABLE ，relay-info 将写入到 mysql.slave_relay_log_info 这张表中。如此，可将 event 的回放和 relay-info 的更新放在同一个事务里面，变成原子操作，从而保证一致性（要么都写入，要么都不写）。假设 sync_relay_log_info = N (N>0)，当设置为 TABLE 时：如果存储引擎支持事务，则 the table is updated after each transaction，参数 sync_relay_log_info 被忽略。如果存储引擎不支持事务，the table is updated after every N events.I/O线程高可靠问题IO 线程也是接收一个个的 event ，更新 master-info 信息到参数 master_info_repository 指定的位置（文件 FILE 或者数据库 TABLE: mysql.slave_master_info），然后将接收到的 event 写入 relay log file。参数 sync_master_info=10000 表示每接收 10000 个 event，写一次 master-info。这里同样存在前面的问题：master-info.log 和 relay-log 无法保证一致性。例如，event2 和 event3 已经写入到了 relay-log，但是 master-info 只记录了 event1，此时如果服务宕机后，MySQL重启，I/O 线程会读取 master-info.log 的内容，读取到的位置为 event1 的位置，然后 I/O 线程会继续将 event2 和 event3 拉取过来，然后继续写入到 relay-log 中，当SQL线程回放时，就会产生 1062 的错误（重复记录），看到的现象还是 IO 线程正常，SQL 线程报错。master_info_repository 设置为 TABLE 或者 FILE 对复制的可靠性是没有帮助的，因为 event 到 relay-log 中去还是文件操作，还是不能保证一致性，但是 master_info_repository 也一定要设置为 TABLE ，性能上比设置为 FILE 有很高的提升（官方BUG）。解决该问题的方法是设置参数 relay-log-recover = 1 ，该参数表示当发生 crash 时，将当前接收到的 relay-log 全部删除，然后从 SQL 线程回放到的位置重新拉取（SQL线程通过配置后是可靠的）。但是注意：这种删除 RELAY-LOG 的操作也有风险，因为主库可能因为 binlog 超过了保存时长而被删除了。所以说，真正的MySQL复制的高可靠是从 5.6 版本开始的，通过设置如下三个参数来确保整体复制的高可靠：• relay-log-recover = 1• relay_log_info_repository = TABLE• master_info_repository = TABLE换言之，之前的版本复制不可靠是正常的。read_only与super_read_only如果在Slave机器上对数据库进行修改或者删除，会导致主从的不一致，需要对Slave机器设置为 read_only = 1 ，让Slave提供 只读 操作。但 read_only 仅仅对没有SUPER权限的用户有效（即mysql.user表的Super_priv字段为Y）（好在一般给 App 用户的权限是不会是 SUPER权限）。参数 super_read_only 可以将有 SUPER权限 的用户也设置为只读，且该参数设置为 ON 后， read_only 也跟着自动设置为 ON。注意：MySQL5.7.11 中，在 /etc/my.cnf 中将 super_read_only=1 配置好后重启，还是可以插入或修改数据。需要在命令行中执行 set global super_read_only=1; 才能真正修改为只读。并行复制（Multi-Threaded Slave，MTS）/ 并行回放MySQL 的并行复制基于组提交：一个组提交中的事务都是可以并行执行的，因为既然处于组提交中，这意味着事务之间没有冲突（不会去更新同一行数据），否则不可能在同一个组里面。开启并行复制，只需要在 Slave 上配置如下参数：slave-parallel-type=LOGICAL_CLOCK <-- 意思是主上怎么并行执行，从上也怎么并行执行。如果设置为 DATABASE，表示基于库级别的并行复制，如果只有一个库，就还是串行，DATABASE 只是为了兼容 5.6 而存在，否则使用 LOBICAL_CLOCK slave-parallel-workers=4 <-- 并行复制的线程数（SQL Thread），线上物理机设置为 16 或者 32 足够了，在线修改后，需要 stop slave; start slave 做一个启停以生效 slave_preserve_commit_order=1 <-- Slave 上 commit 的顺序保持一致，必须为1，否则可能会有GAP锁产生如果是多线程复制，无论是5.6库级别的假多线程还是MariaDB或者5.7的真正的多线程复制，SQL线程只做coordinator，只负责把relay log中的binlog读出来然后交给worker线程，woker线程负责具体binlog event的执行。MySQL 8.0 的并行复制更牛，即使主上面是单线程执行的，从上面也可以并行回放。其思想是，MySQL 日志记录的是行，包含了库、表、主键等信息，如果两个事务之间，修改的行是独立的，互不影响的话，就可以并行回放。可以根据自己的业务情况来决定是否开启并行回放。一些繁忙的业务系统，主从延迟可能达到小时级，这时并行回放就可以很好的解决这个问题。虽然 MySQL 的并行复制配置起来非常简单，但是其主从复制延时的问题困扰了 MySQL 十几年，直到 2015 年 MySQL 5.7 出来才很好的解决了该问题。半同步复制之前的复制，都是异步复制，Master 并不关心数据是否被 Slave 节点所获得 ，所以复制效率很高，但是主从切换后，数据有可能会丢失。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。从实际情况和 sysbench 测试结果来看，（无损）半同步复制的性能损失并没有大家想象的那么大，这个与网络与数据读写比例有关。半同步架构介绍从 MySQL5.5 开始，MySQL推出了 semi-sync replication（半同步复制）。从 MySQL5.7 开始，MySQL推出了 lossless semi-sync replication（无损复制）。rpl_semi_sync_master_wait_point：AFTER_SYNC（默认值），5.7 之前没有无损半同步复制，5.7.2 开始增加了这个参数，当取值为 AFTER_COMMIT 就是普通的半同步复制。该参数的设计是为了兼容之前的版本，比如，现有生产系统是 5.5，备库是 5.7。这两种都是半同步复制，区别在于等待的位置。主在提交一个事务时，会有写 prepare log，写 binlog，写 redolog 三个步骤。启用半同步复制后，主都会等待至少一个 Slave 节点的 IO 线程回复 ACK 后（表示从收到了 binlog ），才能继续下一个事物（可以通过 rpl_semi_sync_master_wait_for_slave_count 来定义需要等待回复的从的数量）。如果在一定时间内（Timeout）内没有收到 ACK，则自动切换为异步模式，当 Slave 又追上 Master 了（IO线程），则会自动切换回半同步复制。半同步复制与无损复制的区别在于，semi-sync 是在三个步骤完成后等 ACK，lossless semi-sync 是在前两个步骤完成后等 ACK。例如：停掉从的 IO_THREAD 线程后，在主上执行 insert cyt values (1) 操作，此时，该会话会卡起待：如果是无损半同步模式，则在主的另一个会话里是看不到插入的记录的；如果是普通半同步模式，则在主的另一个会话里是能看到插入的记录的。假设主从复制时产生异常（比如 Master 宕机了），Master的binlog还没有传递到Slave上，此时半同步复制与无损复制，在主从数据一致性上的表现是不一样的。semi-sync replication 在 commit 完成后，才传输 binlog，意味着在 Master 节点上，这个刚刚提交的事务对数据库的修改， 对其他事务是可见的，假如此时 Master 宕机了，且发生主从切换，此时的 Slave 提升为 New Master，但是此时的 New Master 上是没有之前提交的事务的内容的，这样就产生了主从数据的不一致。对 App 而言，之前读取到的内容，现在读取不到了。loss less semi-sync replication 在 write binlog 完成后，就传输 binlog，但还没有去写 commit log，意味着当前这个事务对数据库的修改，其他事务也是不可见的，假如此时 Master 宕机了，且发生了主从切换，此时的 Slave 提升为 New Master，由于 Master 上对该事务还没有提交，且此时的 New Master 上同样也没有该事务的内容，此时主从的数据是一致的，对于 APP 来说，发生主从切换后，APP 读取到的内容前后是一致的。（如果原来的Master又恢复了，需要让原来的Master不要提交宕机前的那个事务）所以，当主机失败，且主从未切换，恢复后，两种复制模式下的主从数据都是最终一致的（配置是 crash_safe 的）。若主机失败，做了主从切换，则两种方式的表现不一样：semi-sync 可以减少数据丢失风险但不能完全避免数据丢失lossless semi-sync 可保证数据完全不丢失安装半同步插件两种同步方式使用相同的插件。注意：这儿仅仅是安装了插件而已，还未启用。主从上都要安装。安装方式一：手工安装mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so’;安装方式二：写入配置文件[mysqld] plugin_dir=/usr/local/mysql/lib/plugin plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"检查是否安装成功：mysql> show plugins; +----------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+--------------------+---------+ | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | +----------------------------+----------+--------------------+--------------------+---------+启用/关闭半同步[mysqld] loose_rpl_semi_sync_master_enabled=1 <-- 开启主的半同步复制 loose_rpl_semi_sync_slave_enabled=1 <-- 开启从的半同步复制 loose_rpl_semi_sync_master_timeout=1000 <-- 超时1秒，如果可靠性要求高，不想它切加异步，就把这个值调高 loose_rpl_semi_sync_master_wait_point=AFTER_SYNC <-- 5.7 可以设置为 AFTER_SYNC 来使用无损复制，AFTER_COMMIT 使用半同步 loose_rpl_semi_sync_master_wait_for_slave_count=1 <-- 5.7 可以配置至少收到多少个slave发回的 ACK使用 loose_ 前缀表示如果没有加载 semi_sync 的插件，则忽略该参数。两种复制方式的性能在 IO Bound 场景下：异步复制性能很好，但是随着并发数的增长，性能有所下降无损复制随着并发数的增长，性能几乎是线性增长的，在高并发下，性能会优于异步复制半同步复制性能较低无损复制性能优于半同步复制的原因：就等待ACK回包问题上，其实两种复制的开销是一样的，没有区别，都是网络的等待开销。无损复制由于在 write binlog 后，需要等待ACK，后续的事物无法提交，这样就堆积了很多需要落盘的事物（半同步复制由于已经提交了事物，没有堆积事物的效果），通过组提交机制，一次 fsync 的事物变多了（半同步复制也有组提交，只是一次fsync 的事物数没那么多），相当于提高了 I/O 性能 。所以线程（事物）越多，效果越明显，以至于有超过异步复制的效果。（无损复制的组提交比原版组提交的高3~4倍）通过GTID复制GTID 是后面一切高可用技术的基础，一定要打开。GTID（Global Transaction Identifier，全局事物ID）是MySQL 5.6的新特性，GTID 由 Server_UUID + Transaction_ID 组成，作用是替代 Filename + Position。每个实例的 Server_UUID 是不一样的（show variables like '%uuid%'）。在没有 GTID 时，一主多从的架构中，当 Master 宕机后，某个 Slave 被提升为 New Master，如果需要继续维持复制关系，就需要把另外的 Slave 的 CHANGE MASTER 指向 New Master，新 Master 的 Filename + Position 是比较难以确定的（主从的文件名并没有对应关系）。如果使用了 GTID，就可以通过 GTID 来确定位置（全局唯一）。那 GTID 是如何快速定位的呢？因为 binlog 在 rotate（rotate events）的时候，是知道当前最大的 GTID 的，可以将该值写入到下一个新的 binlog 的开头，即 binlog 中记录的 Previous_gtids：mysql> show binlog events in 'bin.000021'\G当从需要跟主同步时，就可以使用从当前的 GTID 值，跟 binlog 头部的 Previous_gtids 作比较：如果我要的下一个 GTID 比 Previous_gtids 值大，就扫描当前文件，反之则扫描之前的文件，依次类推。启用 GTIDGTID 的配置很简单，只需要写上如下的4条配置即可：[mysqld] log_bin = bin.log gtid_mode = ON log_slave_updates = 1 enforce_gtid_consistency = 11.MySQL 5.6 必须开启参数 log_slave_updates （5.6版本的限制），5.7 版本开始可以不开启2.MySQL 5.6 升级到gtid模式需要停机重启，5.7.6 版本开始可以在线升级成gtid模式开启 GTID 后的注意事项1.启用 GTID 后，数据库之前的备份就不能用了，需要重新对数据库做备份。2.使用 mysqldump 备份单个数据库时会有 Warning，大致意思为你只备份了部分数据库，但是启用GTID后包含了所有的事物。（可以忽略该警告）3.启用 GTID 后，某些不安全的语句会被禁用，比如 CTAS，因为这其实是多个事务了，GTID没法对应。4.开启 GTID 后，不能在一个事物中使用创建临时表的语句，需要参数 autocommit=1 才可以。不要设置的两个参数下面这两个参数不要去设置，设置了反而性能差mysql> show variables like "%binlog_group%"; +-----------------------------------------+-------+ | Variable_name | Value | +-----------------------------------------+-------+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | <-- 等待一组里面有多少事务我才提交 +-----------------------------------------+-------+ 2 rows in set (0.03 sec) mysql> show variables like "%binlog_max%"; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | binlog_max_flush_queue_time | 0 | <-- 等待多少时间后才进行组提交 +-----------------------------+-------+ 1 row in set (0.03 sec)slave_rows_search_algorithms参数 slave_rows_search_algorithms 的默认值为 "TABLE_SCAN,INDEX_SCAN"，不要去更改，这个参数表明了怎样来提高复制的性能。之前强调过的每张表一定要有主键的要求，除了是符合范式的要求，也可以提高主从复制的性能。因为Slave进行回放的时候，是根据索引进行回放的，过程为：先找主键 --> 没有主键则找唯一索引 --> 没有唯一索引则找普通索引 --> 没有普通索引则全表扫描。index used/option valueINDEX_SCAN,HASH_SCAN Or INDEX_SCAN,TABLE_SCAN,HASH_SCANINDEX_SCAN,TABLE_SCANTABLE_SCAN,HASH_SCANPrimary key or unique keyIndex scanindex scanindex hash(other)keyIndex hashindex scanindex hashNo indexTable hashTable scanTable hash例如：delete from cyt where id in (1, 3);针对上述语句，假设 cyt 不存在任何主键或索引，则在 Master 上操作的时候只要扫描一遍该表即可，但是复制是基于行的（ROW格式），在 Slave 上就要扫描两次，一次扫描是为了 id=1 的行，一次扫描 是为了 id=3 的行。即全表扫描时：Master 扫描一次，复制时，Slave上扫 N 次。新增的 Hash Scan 方式，会先增加一个哈希表，这样就只扫描一次了，但是创建哈希表的代价很大。所以默认没有启用的。所以强烈建议：每张表上都要有一个主键。
已索引官方网址：https://github.com/maxbube/mydumper介绍与安装mydumper 是一款社区工具，支持多线程导入和导出，相对 mysqldump，速度很快，默认是产生一致性备份。mydumper 是可靠的，facebook、Twitter、网易就在用，但是因为是社区的，需要踩坑。[...]
已索引Workbench是Oracle官方提供的一款GUI工具，类似sqldeveloper，支持如下功能：SQL语句格式化SQL关键字大写MySQL DashboardSQL语法提示ER图Forward Engine // ER图 --> DB表结构Reverse // DB表结构 --> ER图mysql是命令行工具，类似sqlplus。这儿主要介绍下 mysql 的一些使用技巧。最常用的连接方法IPC 连接：mysql -uroot -p [--socket=xxx]TCP 连接：mysql -uroot -p -h xxx -P xxx通常会把这些连接选项写入到配置文件中，MySQL 查找配置文件的顺序为：# mysql --help --verbose | grep cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 注意：如果这些些文件同时存在，都会读取，如果有相同的参数配置，以最右边的为准。下面是一个示例文件：# cat .my.cnf [client] user=root password=oracle socket=/usr/local/mysql/3306/mysql.sock通过 login-path 连接数据库login-path 是 MySQL5.6 开始支持的新特性。特别适合多实例 MySQL 服务器。其思想是将登录需要的信息放到 .mylogin.cnf 的 section 中，认证信息加密保存，格式跟参数文件一样。我们可以使用 mysql_config_editor 命令来配置(set)、显示(print)、删除(remove)、重置(reset)文件中的内容。mysql_config_editor 的选项跟 mysql 的选项是一样的。可以将选项写到 login path 中，也可以在命令行中给出。显示配置：mysql_config_editor print --all #显示所有的 login-path 信息 mysql_config_editor print --login-path=mylogin #显示指定的 login-path 配置配置 login-path：mysql_config_editor set --login-path=newlogin --user=root --host=127.0.0.1 --port=3306 --password 注意：如果密码中含有特殊字符，需要用单引号引起来。删除 login-path：mysql_config_editor remove --login-path=newlogin重置 login-path:mysql_config_editor reset使用login-path登录：mysql --login-path=myloginlogin-path 的可配置项：-G,--login-path=name 登录文件中 login path 的名字，默认为 client -h,–host=name 要登陆的主机 -P,–port=name 登陆端口 -u,–user 登陆使用的用户名 -p,–password 登陆使用的密码 -S,–socket=name 登陆使用的 socket查看当前连接的一些状态信息：mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.27, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 138069 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.27-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 62 days 23 hours 45 min 24 sec Threads: 21 Questions: 76155268 Slow queries: 133416 Opens: 1682 Flush tables: 1 Open tables: 1513 Queries per second avg: 13.993 -------------- mysql>查看帮助mysql> help 查看客户端 mysql 命令相关帮助 mysql> help contents 查看服务器端的帮助 mysql> help create 查看 create 命令的帮助修改提示符mysql> prompt (\u@\h) [\d]>\_ (root@localhost) [cydb]> 建议将其写入配置文件： [mysql] prompt=(\\u@\\h) [\\d]>\\_垂直显示结果mysql> select * from mysql.user\G调用操作系统命令mysql> system ls;保存结果到文件mysql> tee /tmp/cy.txt mysql> notee;分页mysql> pager less PAGER set to 'less' mysql> select * from help_keyword; 当一屏显示不全时，就会调用 less 命令 mysql> nopager; 取消 pager 功能 PAGER set to stdout命令补齐mysql> rehash; 在 5.7 中默认就是打开的，注意前提是已经使用 use 选择了一个数据库。几个 mysql 命令常用参数：--login-path=mylogin --auto-rehash 是否启用 tab 键补齐功能，默认TRUE --default-character-set 指定连接会话的字符集，默认auto --show-warnings 执行完语句后，马上显示警告信息，默认FALSE -e, --execute=name 非交互模式执行命令 -f, --force 如果使用 -e 同时执行多条命令，-f 表示即使前面的命令报错，仍然向后执行
已索引关于源码安装源码安装其主要作用是为了让开发人员研究源代码使用。Oracle、DB2、SQL Server 有让你编译吗？他们给的本质上就是一个二进制安装包。源码安装很多人说性能会更好，这可能是一种迷思。自己编译对性能提升不明显。编译有很多讲究的，你确信自己编译的参数真的比官方专业人士编译得更好吗？真相是目前官方编译的就是最好的。没有必要从编译源码的角度去追求这种性能极致，还不如好好优化业务与数据库。下载源码包文件MySQL源码包文件名一般为 mysql-version.tar.gz。5.7 之前的版本只需要下载源码包就行了，5.7 开始还需要下载同版本的 mysql-boost 包。[...]