MySQL Import Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes

Today I was importing a MySQL database and ran into this Error “Error 1153 - Got a packet bigger than 'max_allowed_packet' bytes”.

Basically , When you dump table data from MySQL, you may end up pulling a large chunk of data and it may exceed the MySQL client’s max_allowed_packet variable. If that happens, you might catch an error like this:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `tablename` at row: 1627

The default max_allowed_packet size is 25M, and you can adjust it for good within your my.cnf by setting the variable in a section for mysqldump:

OR

The fix is to increase the MySQL daemon’s max_allowed_packet. You can do this to a running daemon by logging in as Super and running the following commands. Keeping the session open create a 2nd session in which to run the import.

mysql> set global net_buffer_length=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set global max_allowed_packet=1000000000;
Query OK, 0 rows affected (0.00 sec)

Keep the mysql prompt open, and run your command-line SQL execution on a second terminal..

MySQL : Monitor live MySQL queries

You can run the mysql command "show processlist" to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for as this won’t show the whole SQL text.

Here are the steps to dump sql into a file for analysis.

mysql> SHOW VARIABLES LIKE "general_log%";

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

| Variable_name    | Value                      |

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

| general_log      | OFF                        |

| general_log_file | /var/run/mysqld/mysqld.log |

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

Turn on the SQL Logging and go to the general_log_file location.

mysql> SET GLOBAL general_log = 'ON';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

Then don't forget to disable the general_log parameter to OFF.

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

Recover MySQL Root Password

If you already have the root password, but just want to change it. Then it's simple and can be 

done when MySQL Database is up & running.

/usr/bin/mysqladmin -u root password 'new-password’

But If ROOT password is lost then following steps needs to be performed and requires reboot of 

MySQL Database:

STOP MySQL Service:

/etc/init.d/mysql stop

START MySQL Without grants:

mysqld_safe --skip-grant-tables &

Connect to mysql server using mysql client:

mysql -u root

Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Stop MySQL Server:

# /etc/init.d/mysql stop

Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

MySQL - Reading Bin Logs

BinLogs can be read in couple of ways. Here it is described in 3 ways: 

Using phpMyAdmin:

•   Login to phpMyAdmin.
•   Go to “Binary Log” tab
•   Select Binary Logs to view
•   Click GO

By default this would show 30 lines. So if you want to customize, click on EDIT and change the query according to your need.

Using SQL Query: 

•    Determine existing binary logs on the system

mysql> SHOW BINARY LOGS;

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

| Log_name         | File_size |

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

| mysql-bin.000022 | 104858289 |

| mysql-bin.000023 | 104857671 |

| mysql-bin.000024 | 104857898 |

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 |  52091311 |

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

•  From MySQL run the following for any existing binlog to read. I’ll try for 100 lines.

SHOW BINLOG EVENTS IN 'bin-log.000022' LIMIT 0, 100

-        

   Using UNIX Commands:

•  From OS, we can directly read the binlogs. Here is how:

mysqlbinlog bin-log.000022

• This can be modified and run with unix commands for customized reports.

mysqlbinlog bin-log.000022 |grep table_name |grep -v INSERT |cut -c1-1000

MySQL - Purging Binary Logs

Here is a simple demonstration of how to purge MySQL binary logs.

The default value of the variable expire_logs_days is 10 days. MySQL expire_logs_days is a dynamic variable so we can edit it while MySQl is up & running, we don’t need to restart the server.

First check your slave status:

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.20.1.51

                  Master_User: replication

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000029

          Read_Master_Log_Pos: 52089474

               Relay_Log_File: mysqld-relay-bin.000036

                Relay_Log_Pos: 52089620

        Relay_Master_Log_File: mysql-bin.000029

             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: 52089474

              Relay_Log_Space: 52089820

              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: 1

Now check to see the binary logs:

mysql> SHOW BINARY LOGS;

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

| Log_name         | File_size |

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

| mysql-bin.000022 | 104858289 |

| mysql-bin.000023 | 104857671 |

| mysql-bin.000024 | 104857898 |

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 |  52091311 |

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

In my case, I want a retention of 5 days so I can start by deleting from 

mysql-bin.000024 to mysql-bin.000022. 

mysql> PURGE BINARY LOGS TO 'mysql-bin.000025';

Query OK, 0 rows affected (0.45 sec)

mysql> SHOW BINARY LOGS;

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

| Log_name         | File_size |

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

| mysql-bin.000025 | 104857690 |

| mysql-bin.000026 | 104860121 |

| mysql-bin.000027 | 104858120 |

| mysql-bin.000028 | 104858421 |

| mysql-bin.000029 |  52313463 |

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

5 rows in set (0.00 sec)

Eventually set the new value for expire_logs_days and don’t forget to edit your my.cnf:

mysql> SET GLOBAL expire_logs_days=5;

Query OK, 0 rows affected (0.00 sec)

Now see the reflection.

mysql> SHOW VARIABLES LIKE 'expire_logs_days';

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

| Variable_name    | Value |

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

| expire_logs_days | 5     |

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

1 row in set (0.00 sec)

Again, once the parameter is changed in memory, change the my.cnf parameter accordingly

so that the instance doesn't startup with old parameter in next instance reboot.

Purging Binary Log with Date Range:

We can also binary logs with date & time range.

PURGE BINARY LOGS BEFORE '2013-05-21 10:46:25';