MySQL作为一款主流的关系型数据库，具有优秀的性能和高并发能力。本文档主要记录MySQL的安装、用户访问配置、配置文件优化和主从复制等内容。关于Linux系统的安装、磁盘挂载与扩容等内容请参考博客的其他目录。

MySQL安装

安装MySQL为了快捷安装，直接使用宝塔面板安装的mysql5.7.44版本。安装完成之后将存储目录迁移至/data/mysql目录下，/data目录挂载的1.2T容量的xfs文件系统。这样就满足了数据存储容量的需要，同时使用逻辑卷和磁盘卷组进行方便后续的动态扩容。此过程较为简单不再进行赘述。

MySQL主机

创建复制账号

1
2
3
4
5
6
//创建复制账号
create user 'accountSlave'@'192.168.0.%' identified by 'yourpassword';
//授权复制权限
grant replication slave on *.* to 'accountSlave'@'192.168.0.%' identified by 'yourpassword';
//更新权限
flush privileges;

主机配置参数

1
2
3
4
5
6
7
# 设定log参数
log-bin=mysql-bin
binlog_format=ROW
# 设定主机serverid，一般可以使用主机ip的尾号进行
server-id = 42
# 设定日志过期时间
expire_logs_days = 5

主机状态

MySQL主机先不要写入数据，同时查看主机的状态。

1
2
3
4
5
6
7
mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000015 | 991771720 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

MySQL从机

从机配置参数

1
2
3
4
5
6
log-bin=mysql-bin
binlog_format=ROW
# 指定要复制的主机数据库名称
replicate-do-db = yourdb
server-id = 43
expire_logs_days = 5

从机此时从主机将目标数据库中的数据全部拷贝过来，保证主从数据库最开始的一致性。

从机启动复制

1
2
3
change master to master_host='192.168.0.42',master_user='accountSlave',master_password='yourpassword',master_log_file='mysql-bin.000015',master_log_pos=991771720;

start slave;

从机查阅到主机的日志文件和pos位置，将其写入到拷贝命令中，然后启动复制。然后登录客户端查看从机状态。Slave_IO_Running: Yes和Slave_SQL_Running: Yes表示从机正常复制。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.42
                  Master_User: aotaidianqiSlave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 991771720
               Relay_Log_File: ubuntu-ip43-relay-bin.000008
                Relay_Log_Pos: 991771933
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: solar_info_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: 991771720
              Relay_Log_Space: 991772312
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 42
                  Master_UUID: 26cfad73-eba6-11f0-8d8a-0050569be94f
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

MySQL性能调优

MySQL大多数场景都是使用innodb引擎，故相关的调优以innodb进行设置记录。

innodb_buffer_pool_size

该参数是innodb引擎的缓冲池大小，其设置值最大为服务器内存的75%，剩余25%部分需要供操作系统和其他软件进行使用。InnoDB 缓冲池是内存中的缓存区域，用于存储：

  • 数据页：表数据
  • 索引页：索引数据
  • 自适应哈希索引
  • 锁信息（部分）
  • 数据字典

innodb_read_io_threads与innodb_write_io_threads

控制 InnoDB 后台I/O线程数 的重要参数，直接影响数据库的磁盘I/O性能该参数的设置要考虑实际CPU核数，设置过大并不会带来性能的提升，反而会导致竞争加大。一般设置为=CPU的核数即可。

max_connections

MySQL允许的最大并发连接数，此处需要估计业务实际情况，设置偏小会影响业务的正常运行，同时也要考虑ulimit限制和MySQL的open_files_limit参数限制。应首先从系统层面优化，然后协同MySQL配置优化。

table_open_cache

MySQL 中控制表文件描述符缓存的关键参数，直接影响数据库的并发性能。一般为max_connections的两倍。

query_cache_type与query_cache_size

MySQL从5.7开始就准备废弃查询缓存，8.0直接废弃掉查询缓存。

问题 影响
全局锁竞争 每次缓存访问需要全局锁，高并发下成为瓶颈
缓存失效频繁 表有更新就整个表缓存失效
内存碎片化 长期运行后内存效率下降
MySQL 8.0已移除 技术已淘汰

innodb_flush_log_at_trx_commit

InnoDB 最关键的持久性配置参数，直接决定事务的 ACID 特性中 Durability（持久性） 的保证级别。这个参数控制 InnoDB 在事务提交时如何将 redo log（重做日志）写入并刷新到磁盘。

值 工作机制 数据安全性 性能影响 适用场景
1 (默认) 每次提交都执行 write() + fsync()，确保日志落盘 最高 崩溃/断电不丢已提交事务 最慢 频繁磁盘I/O 金融、支付等绝对不能丢数据的场景
0 每秒一次由后台线程执行 write() + fsync() 最低 崩溃可能丢失近1秒数据 最快 大幅减少I/O 监控数据、日志等可容忍丢失的场景
2 每次提交执行 write()，每秒一次 fsync() 中等 MySQL崩溃安全，OS崩溃可能丢数据 较快 平衡性能与安全 常规业务，可接受OS崩溃风险

sync_binlog

MySQL二进制日志刷盘策略的核心参数，直接控制主从复制的数据安全性与性能平衡，尤其在你的主从架构中至关重要。

值 刷盘时机 数据安全 性能 适用场景
0 OS控制，异步刷盘 最低 MySQL/OS崩溃都可能丢数据 最高 可丢数据的非核心业务
1 (推荐) 每次事务提交都刷盘 最高 最多丢失1个事务 最低 生产主库必须
N (>1) 每N个事务刷盘一次 中等 可能丢失最多N个事务 中等 高写入负载，明确接受风险

binlog_format

MySQL二进制日志格式 的关键参数，决定了主从复制中数据变更的记录方式，直接影响复制可靠性、性能和数据一致性。

特性 STATEMENT ROW (推荐) MIXED
记录内容 SQL语句 每行数据变化 混合，由MySQL决定
数据一致性 低（可能主从不一致） 高（绝对一致） 中等（大部分一致）
日志大小 很小 可能很大（取决于变更行数） 中等
复制性能 高（重放SQL快） 可能较低（传输数据多） 可变
故障排查 难（不知道具体数据变化） 易（看到每行变化） 混合
MySQL 8.0默认 不支持（已移除） ROW 不支持（已移除）

expire_logs_days

二进制日志文件自动保留的天数，超过天数的 binlog 文件会被自动删除。

慢查询日志

1
2
3
slow_query_log=1
slow-query-log-file=/data/mysql/mysql-slow.log
long_query_time=n #n是实际的秒数

开启慢查询，指定慢查询日志位置，同时指定超过n秒的慢查询都要进行记录。这有助于排查sql语句，优化数据库结构或者业务结构。

innodb_lock_wait_timeout

设置 事务等待行锁的最长时间（秒），超过时间后自动回滚并报错。

场景 推荐值 说明
实时交易 10-20秒 快速失败，避免堆积
常规业务 30秒 平衡等待与超时
批量处理 50-120秒 长事务需要更长时间
数据同步 300秒 容忍长时间锁等待
当前配置 50秒 偏长，可优化