xuwang.online - MySQL
type: channel
link: http://www.xuwang.online/index.php/tag/MySQL
items: MySQL 主从搭建与管理
items: MySQL 二进制日志 / binary log / binlog
items: MySQL 数据库介绍与选择
items: MySQL 数据库 DBA 笔记
items: MySQL参数文件管理
items: 深入理解 MySQL Replication / MySQL 复制
items: MySQL备份恢复之mydumper
items: MySQL 管理工具
items: MySQL 源码包编译安装
items: MySQL 多实例、多版本共存

MySQL 主从搭建与管理
type: item
date: 2025-07-20T16:48:00+08:00
description: 主从搭建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: rep…

MySQL 二进制日志 / binary log / binlog
type: item
date: 2025-07-17T16:55:00+08:00
description: 已索引二进制日志的作用二进制日志(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 等函数，又或者使用触发器等操作，这些都可能…

MySQL 数据库介绍与选择
type: item
date: 2025-06-30T16:04:00+08:00
description: 已索引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 数据库 DBA 笔记
type: item
date: 2023-11-09T11:36:00+08:00
description: 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管理工具之mysqlMySQL 备份恢复MySQL备份恢复之mydumperMySQL 复制MySQL 主从搭建与管理深入理解 MySQL Replication / MySQL 复制

MySQL参数文件管理
type: item
date: 2020-01-03T07:59:00+08:00
description: MySQL 的参数文件类似 Oracle 的 pfile。参数分类动态参数与只读参数：是否可以在线修改该参数的值。修改时提示只读即为只读参数。全局参数会话级参数：全局参数只能在全局修改(Global)参数。有些参数既可以在会话级修改，也可以在全局修改(Both)。[...]

深入理解 MySQL Replication / MySQL 复制
type: item
date: 2019-12-04T12:32:00+08:00
description: 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 的错误（重复记…

MySQL备份恢复之mydumper
type: item
date: 2019-12-02T20:00:00+08:00
description: 已索引官方网址：https://github.com/maxbube/mydumper介绍与安装mydumper 是一款社区工具，支持多线程导入和导出，相对 mysqldump，速度很快，默认是产生一致性备份。mydumper 是可靠的，facebook、Twitter、网易就在用，但是因为是社区的，需要踩坑。[...]

MySQL 管理工具
type: item
date: 2019-09-19T13:30:00+08:00
description: 已索引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 …

MySQL 源码包编译安装
type: item
date: 2019-09-18T08:34:00+08:00
description: 已索引关于源码安装源码安装其主要作用是为了让开发人员研究源代码使用。Oracle、DB2、SQL Server 有让你编译吗？他们给的本质上就是一个二进制安装包。源码安装很多人说性能会更好，这可能是一种迷思。自己编译对性能提升不明显。编译有很多讲究的，你确信自己编译的参数真的比官方专业人士编译得更好吗？真相是目前官方编译的就是最好的。没有必要从编译源码的角度去追求这种性能极致，还不如好好优化业务与数据库。下载源码包文件MySQL源码包文件名一般为 mysql-version.tar.gz。5.7 之前的版本只需要下载源码包就行了，5.7 开始还需要下载同版本的 mysql-boost 包。[...]

MySQL 多实例、多版本共存
type: item
date: 2019-09-02T19:55:00+08:00
description: 已索引对于 MySQL 的多实例，比较常见的一种做法是把一个已经初始化成功的 DATADIR 目录下的 mysql 目录复制到其它实例的 DATADIR 目录下，然后配置参数文件，启动的时候新目录就会自动初始化，这么做数据库是能启起来的，也能连接使用，但是当要开启某些高级特性（例如replication）的时候会报错，因为mysql目录下面有很多.ibd文件，这些文件是不能直接拷贝的。这种做法之所以存在，是因为早期使用的是 MyISAM 存储引擎，可以直接拷贝。现在使用的是 InnoDB 存储引擎，这样做是有瑕疵的，不推荐。下面是推荐的做法：[...]