MySQL主从数据库Last_Error: Error ‘Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs’ on query

MySQL 版本: MySQL 5.7.31
MySQL 架构: 主从数据库架构
告警节点: 从数据库
运行场景: 某大型制造企业

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1118
                   Last_Error: Error 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs' on query. Default database: 'ekp'. Query: 'alter table ekp_ff4cf69105f28480891e add column fd_3ddc5be2f5270e varchar(4000)'

解决办法,修改MySQL配置参数,从新进行数据同步。

[mysqld]
port=3360
bind_address=0.0.0.0
server-id = 128
log_bin = mysql-bin
binlog_cache_size = 4M
binlog_format = mixed
expire_logs_days = 99
relay-log=mysqld-relay-bin
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
datadir=/home/mysql
socket=/home/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 5000
key_buffer_size = 256M
query_cache_size = 64M
thread_cache_size = 64
lower_case_table_names = 1
default_storage_engine = InnoDB
innodb_file_format = Barracuda   # 启用 Barracuda,支持大行
innodb_file_per_table = 1             # 独立表空间,便于管理
innodb_default_row_format = DYNAMIC   # 默认 DYNAMIC 行格式,避免行大小限制
innodb_buffer_pool_size = 2048M       # 增加缓冲池(根据内存调整)
innodb_log_file_size = 512M                # 增加日志文件大小,支持大表变更
innodb_log_buffer_size = 16M             # 日志缓冲区,提升写入性能
innodb_large_prefix = 1                        # MySQL 5.7 需启用,支持大索引前缀
performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking
skip-name-resolve
max_allowed_packet=2560M
wait_timeout=60000
[client]
port = 3360
socket=/home/mysql/mysql.sock

Windows下MySQL-5.7.20绿色免安装版配置与使用

Windows下MySQL-5.7.20绿色免安装版配置与使用
MySQL-5.7.20绿解压缩版(免安装)安装配置教程

下载地址
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-winx64.zip
https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.20-winx64.zip

1、添加环境变量
新建系统变量 MYSQL_HOME ,并配置变量值为 C:\mysql-5.7.20-winx64 ;
编辑用户变量 Path ,将%MYSQL_HOME%\bin 追加到 Path 变量值后面。注意不是覆盖。

2、将my-default.ini名称改为:my.ini
下面是参数配置
[mysql]
default-character-set = utf8mb4
[mysqld]
basedir=C:\mysql-5.7.20-winx64
datadir=C:\mysql-5.7.20-winx64\data
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M

read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M

thread_cache_size = 8

query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30

log_error = C:\mysql-5.7.20-winx64\data\mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = C:\mysql-5.7.20-winx64\data\mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1

skip-external-locking

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

interactive_timeout = 28800
wait_timeout = 28800

3、 安装mysql服务
MySQL-5.7.20提示msvcr120.dll丢失,需要安装vc2015,下载地址https://www.microsoft.com/en-us/download/details.aspx?id=48145

管理员身份在命令提示符下运行
C:\>cd mysql-5.7.20-winx64
C:\mysql-5.7.20-winx64>cd bin
C:\mysql-5.7.20-winx64\bin>mysqld -install MySQL --defaults-file="C:\mysql-5.7.20-winx64\my.ini"

4、初始化MySQL-5.7.20
data文件夹不为空是不能执行这个命令的。密码可以去进入data文件,打开一个.err结尾的文件查看,如果设置了errorlog那么在mysql-error.log查看密码
C:\mysql-5.7.20-winx64\bin>mysqld --initialize 自动生成带随机密码的root用户
C:\mysql-5.7.20-winx64\bin>mysqld --initialize-insecure 自动生成无密码的root用户

5、启动MySQL服务器,在命令提示符中运行命令:net start MySQL。
C:\mysql-5.7.20-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

6、修改密码
命令如下:
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'z123456789';

或者
C:\mysql-5.7.20-winx64\bin>mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password:

7、卸载MySQL服务
在命令提示符下进入到C:\mysql-5.7.20-winx64\bin>输入"mysqld -remove"或者"sc delete mysql"执行卸载服务
C:\mysql-5.7.20-winx64\bin>mysqld -remove
Failed to remove the service because the service is running
Stop the service and try again

先要停止MYSQL
net stop mysql