mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table

I got this error when I was dumping a huge table which size is more than 26GB in size.

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `QPR` at row: 5659

The server’s default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1GB.

edit your my.cnf file and add
max_allowed_packet=1024M then restart
[root@db01 ~]# service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

mysql> show GLOBAL variables like 'max_allowed_packet%';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073740800 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql>

NB: I need huge blog type data to be fetched from db. We can also avoid this restart buy
Make sure that if the parameter values are changed by executing this command (ex.)show parameters like ‘max_allowed_packet’ from MySQL client(default).

Suppose if you dumping huge blob data from another host, you also need to change this mysql variables to get it worked. Here I’m setting this to 1GB

mysql> set global max_allowed_packet=100000000000;
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.10 sec)
mysql> set max_allowed_packet=100000000000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_allowed_packet%';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql>


Finally the real solution is you need to add the “max_allowed_packets” parameter along with the mysqldump. mysqldump is notorious for ignoring this value in my.cnf, but setting it as the command line parameter always works.

#mysqldump -u root -p –max_allowed_packet=512M -B database –tables tblblogdb > dump.sql

I think it’s always better to run large mysqldump from another server from the same network which help to reduce disk resource and cpu power during the peak hours. Here is the one sample,
#$MYSQLDUMP –max_allowed_packet=1G -u $MyUSER -h $MyHOST -p$MyPASS -B $db | bzip2 > $FILE

Backing up a mysql table, auto-restore a database

This command  will help you backup a specified table using mysql command.. This would be useful if you have a large database  and have few tables only frequently modified.

#mysqldump -B fmi –tables Security -r Security.sql -uusername -h192.168.10.15 -ppass

sometimes  you may required to restore the database frequently from the back up using script which is automated. I found source command will not be worked while we  using mysql ” -e ” parameter

eg : mysql -uuser -hhost -ppassword  -e “use slocal; call import_twom_data1(0,12000);”

So the best way to use  following command during the automated script execution time to restore the database.

$mysql database name  -uusername -ppass <mysqldump.sql

$mysql slocal  -udbslocal -ppass < csv_import.sql

-Njoy

MySQL ERROR : Got a packet bigger than 'max_allowed_packet' bytes

Here I got an error when I am trying to restore a table which having large binary data stored in it. MySQL default limit for the allowed packet is set to 16M. if you need to insert the record which is larger than 16MB in size you have to modify the mysql global variable accordingly.

Permanent fix is,
edit/create a file in /etc/my.cnf and added the following lines,
[mysqld]
max_allowed_packet = 30M

[root@rc-128 ~]# vi /etc/my.cnf
[root@rc-128 ~]# service mysql restart
Shutting down MySQL….. [ OK ]
Starting MySQL. [ OK ]
[root@rc-128 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.82-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';
+——————–+———–+
| Variable_name | Value |
+——————–+———–+
| max_allowed_packet | 31057600 |
+——————–+———–+
1 row in set (0.00 sec)
mysql> exit

MySQL : Passwordless root login

How do I skip asking mysql root password every time I logged as  system “root” account ?

1. Create a “.my.cnf” in root user home directory.
2. add the following entry on it and save and quit.

[client]

user=”root”

pass=”HdfH8%KS9s”

3.  Change the file permission to 700.
[root@rc-040 ~vi ~/.my.cnf
[root@rc-040 ~]# chmod 700 .my.cnf
[root@rc-040 ~]# ls -la  .my.cnf
-rwx------ 1 root root 37 Apr 11 22:20 .my.cnf

Same can be done for each users. But don’t forget to change the file permission.

Mysql replication : on Centos

Here I’m going to replicate a database on the master DB server to a slave server.

On  Master DB server,

1. Identify the database to be synchronized.
2.  Edit /etc/my.cnf in between [mysqld].

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=securedb
server-id=1

Then restart the mysql daemon,

#service mysqld restart

make sure that the file is created automatically and owned by the mysql user. If not pls create the same.

Now we are going to create slave user on mysql and grant privileges for the replication.

Login to mysql shell,

mysql>grant replication  slave on *.* to replica@"%" identified by 'replica';

mysql>USE securedb;
mysql>FLUSH TABLES WITH READ LOCK;
mysql> show master status;

+-------------------+----------+-------------------+------------------+
| File              | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+-------------------+----------+-------------------+------------------+
| mysqld-bin.000001 |       98 | securedb,securedb |                  |
+-------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;+-------------------+----------+-------------------+------------------+| File              | Position | Binlog_Do_DB      | Binlog_Ignore_DB |+-------------------+----------+-------------------+------------------+| mysqld-bin.000001 |       98 | isterdad,isterdad |                  |+-------------------+----------+-------------------+------------------+1 row in set (0.00 sec)
Now we need to log in to Slave server and restore the   latest "securedb" from the Master. I do recommend that Mysql dump method rather than using replication commands ( LOAD MASTRER DATA) to do that which may cause large network traffic and requires a little  time to complete it.
Edit /etc/my.cnf and add the following entries,

 server-id=2

master-host=192.168.0.25

master-user=replica

master-password=replica

master-connect-retry=60

replicate-do-db=securedb

Restart mysql server

 #service mysqld restart
The next step is, I'm going stop the slave service and forcefully start the first replication against my "securedb"

 mysql> SLAVE STOP;

mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.25', MASTER_USER='replica', MASTER_PASSWORD='replica', MASTER_LOG_FILE=' mysqld-bin.000001', MASTER_LOG_POS=98;

mysql>SLAVE START;
Where,

MASTER_HOST =  IP address or hostname of the master

MASTER_USER =  user we granted replication privileges on the master.

MASTER_PASSWORD =  password of MASTER_USER on the master.

MASTER_LOG_FILE = file when you ran SHOW MASTER STATUS; on the master.

MASTER_LOG_POS = position MySQL when you ran SHOW MASTER STATUS; on the master.
Some administrative commands for replication ( for MASTER),
1. mysql> SHOW BINARY LOGS;

which show the all the binary logs logged on the server.

2. mysql> PURGE BINARY LOGS BEFORE NOW();

Which will delete all the logged binary files created before   today.

3.  SHOW BINLOG EVENTS;

mysql> SHOW BINLOG EVENTS;

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

| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

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

| mysqld-bin.000001 |   4 | Format_desc |         1 |          98 | Server ver: 5.0.77-log, Binlog ver: 4 |

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

1 row in set (0.00 sec)
a. Log_name : The binary log file name for this event

b. Pos : The binary log position of the event

c. Event_type :  The event type, for example, Query_log_event

d. Server_id : The original server id of the event

e. End_log_pos : The end log position

f: Info : Information about the event. For query log events,

4. show binlog events\G

master> show binlog events\G

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

Log_name: master1bin.

000001

Pos: 4

Event_type: Format_desc

Server_id: 10

End_log_pos: 106

Info: Server ver: 5.1.23rclog,

Binlog ver: 4

Info: Server ver: 5.1.23rclog,

Binlog ver: 4

*************************** 2. row ***************************

Log_name: master1bin.

000001

Pos: 106

Event_type: Query

Server_id: 10

End_log_pos: 197

Info: use `test`; create table t1 (a char(40))

*************************** 3. row ***************************

Log_name: master1bin.

000001

Pos: 197

Event_type: Query

Server_id: 10

End_log_pos: 301

Info: use `test`; insert into t1 values ('Stuck In A Loop')
5. Analyzing binary log with mysqlbinlog command. This command is very helpful to read the binary log when the server is in offline.
[root@rc-040 ~]# mysqlbinlog  /var/lib/mysql/mysqld-bin.000001

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#100411 12:43:16 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.77-log created 100411 12:43:16 at startup

# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

ROLLBACK/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
Administrative information for slave

1. Enabling mysql slave logs

add the following lines on the "/etc/my.cnf" under [mysqld] section.

relay-log-index = slave-relay-bin.index

relay-log = slave-relay-bin

2. Master.info and relay-log.info

 Master.info : showing the server credential details which slave used to contact during the replication.

[root@web-test1 installation]# cat   /var/lib/mysql/master.info

14

mysqld-bin.000001

98

192.168.0.40

replica

replica

3306

60

0

b. relay-log.info :  which showing the position of last executed binary log in slave against the master binary log location.

[root@web-test1 installation]# cat   /var/lib/mysql/relay-log.info

/var/run/mysqld/mysqld-relay-bin.000001

4

mysqld-bin.000001

98
	

MySQL : Forgot root password

Here is the steps to re-set the forgot password on MySQL

1. mysqld_safe --skip-grant-tables &
This command will start the mysql service wihtout looking on the privileges assigned for DB/Users. So anyone can login to this server. But you can’t execute any command against the ‘mysql”database.

2. Log to Mysql console and use sql query for resetting password.
mysql>update user set Password=PASSWORD('emtypass') where user='root';
mysql>flush privileges;

3. Restart MySQL and login with your newpass
# Kill the mysqld_safe dameon

[root@rc-090 ~]# ps -ax | grep mysqld
Warning: bad syntax, perhaps a bogus ‘-’? See /usr/share/doc/procps-3.2.7/FAQ
2435 pts/7 S+ 0:00 /bin/sh /usr/bin/mysqld_safe –skip-grant-tables
2481 pts/7 Sl+ 0:00 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –socket=/var/lib/mysql/mysql.sock –skip-grant-tables
2528 pts/5 S+ 0:00 grep mysqld
[root@rc-090 ~]# kill -9 2435
[root@rc-090 ~]# service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

Test the new password is working,
[root@rc-090 ~]# mysql -uroot -p

Remove first few lines from the Huge/GB file

Question.

I have a Mysql dump file which is 30GB size and I want to test integrity of the same file by restoring it with another database on the same server ( no other options). Since the size is big I can’t open it with any of the text editor.

Solution.

1. First I split the sql file in to 30 pieces each having 1 GB size. I can open 1 GB file on vi editor.
#split -b 1073741824 temp/prod-db-backup.06042010 dbpart_

This command will split the file in to iGB file with named dbpart_aa, dbpart_ab, dbpart_ac….etc. Now I have around 30 nos.

2. Next thing is to modify the file for deleting the entry of ” create database and use ” then stand on the same directory,
#cat dbpart_a* > dbbackup.sql
#cat dbpart_b* >> dbbackup.sql
3. Now I am going to restore the database. So I wish to run it as a background process.

#mysql -uroot -hlocalhost -e “create database hugedb;use hugedb;source dbbackup.sql;”&

Enable querry log on MySQL

I need to trace out what are happening on mysql when my application is running.

This is extremely useful for finding the lengthy sql query. This would help us to get an idea about which queries are to be taken for optimization which will cause the mysql performance.

This technique will work only the latest version of Mysql Say 5.3.1 or later.
login to mysql console,then execute SET GLOBAL general_log = 'ON';

This command will generate a file on your mysql data directory and increasing very rapidly depending on the sql executed. Pls use it only for tracing/analyzing the issues only and turn off it once checking is finished. This command will turn off this logging SET GLOBAL general_log = 'Off';

Enabling the slow query log

vi /etc/my.cnf
long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/slowlog.query.log
#touch /var/log/slowlog.query.log && chown -R mysql.mysql /var/log/slowlog.query.log

Enable slow log query on MySQL

Question.

I want to Monitor the query which is taking longer execution time and killing server resource badly. Pls note that “set sql_log_off = ‘ON’; will not work on older version of Mysql 5.0.x.

Solution.

1. Create mysql log directory and permit mysql user to write on it
#mkdir /var/log/mysql
#touch /var/log/mysql/log-slow-queries.log
#chown mysql.mysql -R /var/log/mysql
2. Then stop the mysql server
[root@rc-169 ~]# service mysql stop
Shutting down MySQL... [ OK ]
[root@rc-169 ~]#

3. Running mysql with slow log query logging enabled.
# mysqld_safe –log-slow-queries=/var/log/mysql/mysql-slow.log&

The following script will create a slow log query for the testing purpose

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> select BENCHMARK(10000000, MD5(‘sysadmin’));
+————————————–+
| BENCHMARK(10000000, MD5(‘sysadmin’)) |
+————————————–+
| 0 |
+————————————–+
1 row in set (15.32 sec)
mysql>

We can enable general query log for quick debugging on latest Mysql revision. What we have to do is just execute the command from the terminal. As it is a logging of all queries executed on the mysql, the file size will be increased drastically, So use it wisely. enable and disable for a short period.

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

This command will start to write sql query executed on a log file under data directory (/var/lib/mysql)

If you want to store the query on a table
you may choose this option
mysql > set global log_output=’table’;

Enabling PDO_MYSQL on PHP, Magento installation

Question

My box is cents5.3.1 and I want to install Magento ( a php framework) on users desktop. During the installation I got stucked because php_mycrypt and pdo_mysql was enabled on my existing php compilation.

1. Installation of mycrypt is fairly easy, find the follows

#wget http://downloads.sourceforge.net/project/mcrypt/Libmcrypt/2.5.8/libmcrypt-.5.8.tar.gz?use_mirror=jaist
#tar -zxvf libmcrypt-2.5.8.tar.gz
#cd libmcrypt-2.5.8
#./configure && make && make install

2. MySQL_PDO installation

#yum install autoconf
#pecl install pdo_mysql
[root@rc-040 php-5.2.9]# vi /etc/php.ini edit php.ini and set the path of extension_dir to identify the module.
extension_dir = “/usr/lib/extensions/no-debug-non-zts-20060613″
extension=”pdo_mysql.so”
[root@rc-040 php-5.2.9]# PHP_PDO_SHARED=1 sudo pecl install pdo_mysql
[root@rc-040 php-5.2.9]# php -r 'phpinfo();' |grep "pdo" which returns

[root@rc-169 ~]# php -r 'phpinfo();' |grep "pdo"
pdo_mysql
pdo_sqlite
PECL Module version => (bundled) 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.4 2008/12/31 11:17:42 sebastian Exp $
[root@rc-169 ~]#

Then compile the php again. I have created a file which will help to enable maximum modules that we need( it takes long time to sort it out)
I uses con.sh file to add config parameters and added the following lines in to it

./configure --prefix=/usr/local/php --with-apxs2=/usr/sbin/apxs --with-xsl --with-mysql --with-config-file-scan-dir=/etc/php.d --prefix=/usr --exec-prefix=/usr --bindir=/usr/bin --sbindir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --includedir=/usr/include --libdir=/usr/lib --libexecdir=/usr/libexec --with-config-file-path=/etc --with-bz2 --with-curl --with-exec-dir=/usr/bin --with-png --with-zlib --with-pear=/usr/share/pear --with-zml --with-gd --with-libxml=/usr/lib --enable-calendar --with-openssl-dir=/usr/local/ssl --with-bcmath --with-mysqli --enable-soap -with-jpeg --enable-sockets --enable-mbstring --enable-shared --with-jpeg-dir=/usr/local --with-freetype-dir=/usr/local/lib --with-mcrypt=/usr/local/bin/mcrypt

Then
#make && make install

Try http://localhost/phpinfo.php to verify the installation