mysql max rows 修改_MySQL修改max_allowed_packet

最新推荐文章于 2024-03-02 14:58:28 发布

weixin_39879522

最新推荐文章于 2024-03-02 14:58:28 发布

阅读量338

点赞数

文章标签： mysql max rows 修改

本文链接：https://blog.csdn.net/weixin_39879522/article/details/113295614

版权

当遇到MySQL从库报错'Got a packet bigger than 'max_allowed_packet' bytes'时，可以通过以下步骤解决：1. 登录数据库查看'max_allowed_packet'参数值；2. 设置全局参数'max_allowed_packet'为100MB；3. 重新登录确认设置是否生效；4. 重启从库服务；5. 如必要，修改配置文件my.cnf将'max_allowed_packet'永久设置为100MB。

摘要由CSDN通过智能技术生成

因mysql从库报错Last_IO_Error: Got a packet bigger than 'max_allowed_packet' bytes

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State:

Master_Host:192.168.90.190Master_User: rsync

Master_Port:3311Connect_Retry:60Master_Log_File: mysql-bin.001891Read_Master_Log_Pos:897184977Relay_Log_File: mysql-relay-bin.003780Relay_Log_Pos:897185056Relay_Master_Log_File: mysql-bin.001891Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:897184910Relay_Log_Space:897185322Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno:1153Last_IO_Error: Got a packet bigger than'max_allowed_packet'bytes

Last_SQL_Errno:0Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id:1rowin set (0.00 sec)

第一步：先登录数据库，查看 max_allowed_packet 这个参数值

连接数据库服务器，登录 mysql，执行命令： show variables like '%max_allowed_packet%'; 默认是1M

mysql> show variables like '%max_allowed_packet%';+--------------------+---------+

| Variable_name | Value |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.00 sec)

第二步：set global max_allowed_packet = 100 *1024*1024;

mysql> set global max_allowed_packet = 100 *1024*1024;

Query OK,0 rows affected (0.00sec)

#接着查看没有变过来，需要退出重新登陆，查看的是原始值(缓存)

mysql> show variables like '%max_allowed_packet%';+--------------------+---------+

| Variable_name | Value |

+--------------------+---------+

| max_allowed_packet | 1048576 |

+--------------------+---------+

1 row in set (0.00 sec)

第三步：重新登陆确认是否生效：

mysql> show variables like '%max_allowed_packet%';+--------------------+-----------+

| Variable_name | Value |

+--------------------+-----------+

| max_allowed_packet | 104857600 |

+--------------------+-----------+

1 row in set (0.00 sec)

第四步：重启slave

mysql>stop slave;

Query OK,0 rows affected (0.02sec)

mysql>start slave;

Query OK,0 rows affected (0.00sec)

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingformaster to send event

Master_Host:192.168.90.90Master_User: rsync

Master_Port:3311Connect_Retry:60Master_Log_File: mysql-bin.001891Read_Master_Log_Pos:948459439Relay_Log_File: mysql-relay-bin.003780Relay_Log_Pos:897185056Relay_Master_Log_File: mysql-bin.001891Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:897184910Relay_Log_Space:948459887Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_SSL_Allowed: No

Master_SSL_CA_File: