mysql修改max_allowed_packet

標籤：errno   oca   replica   art   退出   cond   erro   sql   ***   

因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.190                  Master_User: rsync                  Master_Port: 3311                Connect_Retry: 60              Master_Log_File: mysql-bin.001891          Read_Master_Log_Pos: 897184977               Relay_Log_File: mysql-relay-bin.003780                Relay_Log_Pos: 897185056        Relay_Master_Log_File: mysql-bin.001891             Slave_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: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 897184910              Relay_Log_Space: 897185322              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 1153                Last_IO_Error: Got a packet bigger than ‘max_allowed_packet‘ bytes               Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1row in 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.00 sec)#接著查看沒有變過來，需要退出重新登陸，查看的是原始值（緩衝）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.02 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.90.90                  Master_User: rsync                  Master_Port: 3311                Connect_Retry: 60              Master_Log_File: mysql-bin.001891          Read_Master_Log_Pos: 948459439               Relay_Log_File: mysql-relay-bin.003780                Relay_Log_Pos: 897185056        Relay_Master_Log_File: mysql-bin.001891             Slave_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: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 897184910              Relay_Log_Space: 948459887              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: 1356Master_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: 11 row in set (0.00 sec)

第五步：修改設定檔  

修改設定檔--Linux在 Linux 中，MySQL 對應的設定檔是 my.cnf , 我們在Linux終端輸入如下命令 ：

[[email protected]192-168-90-90 ~]# mysql --help | grep my.cnf                      order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 
#如果在啟動時沒有指定my.cnf 就優先列表，先找到的設定檔生效

[[email protected]192-168-90-90 data3311]# more my.cnf [mysqld]basedir=/usr/local/mysql5.5datadir=/home/mysql5.5/data3311tmpdir=/home/mysql5.5/data3311/tempport = 3311server_id = 3311socket=/home/mysql5.5/data3311/mysql3311.sockcharacter_set_server=‘utf8‘max_allowed_packet=100M

 

 

 

mysql修改max_allowed_packet