Fix table locking problem when mysqldump database with too many connections error

Hello, today we're going to do some DB server tutorial, by fixing a mysql performance problem, lot of questions about getting "too many connections" error when doing database backup using mysqldump command line, and guess what, you won't get the problem fix by increasing the connection number in my.cnf, because simply that's not the problem, it's caused by table locking of mysqldump when running, so all your applications and website will goes down.

Example of mysqldump command: 
mysqldump -u root -p'password' database > /home/backup.sql
So to fix this problem you need to access the mysql server configuration file "my.cnf" located in "/etc" using ssh vi command or ftp, then add the bellow codes then save it:

# THIS TO STOP TABLE LOCKING WHEN BACKUP DB USING MYSQLDUMP :
[mysqldump]
skip-opt
quick
single-transaction
skip-add-locks
extended-insert

Now restart your mysql server (using "service mysqld restart").

That's it now try to run your mysqldump and see the result, you'll notice a fast backup with no locking errors.

If you like this article, please share it on facebook, tweet it and +1 it to spread it :) !
Fix table locking problem when mysqldump database with too many connections error Fix table locking problem when mysqldump database with too many connections error Reviewed by Maherr Live on Thursday, April 16, 2015 Rating: 5