Taking Backup of MySQL Database with stored procedures and functions.

mysqldump is a utility to take a backup of MySQL database. With MySQL 5 new features like stored procedures and triggers were introduced. mysqldump by default backups only the triggers and not the stored procedures or functions. This behaviour can however be controlled by the following two parameters:

1. routines (--routines)
2. triggers (--triggers)

routines option is by default false while triggers option is by default true.

An example to take a backup of your database along with stored procedures, functions and triggers:
    # mysqldump --routines -u 'username' -p'password' > mysqldummpfile.sql

Let’s assume we want to backup only the stored procedures,functions and triggers and not the mysql tables and data , then we should run something like this:

   # mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u 'username' -p'password' > mysqldumpwithoutanytablesanddata.sql

This will backup only the procedures triggers and functions of the Database.

For importing this backup procedure remains the same (irrespective of whether the backup was taken with or without routines):

   mysql -u 'username' -p'password' < mysqldumpwithoutanytablesanddata.sql

Resolving "too many connections" error in MySQL/Java.

Resolving "too many connections" error in MySQL/Java.

First Solution:

If you are getting "too many connections" errors in MySQL you can could resolve this by changing the max_connections setting to allow more connections.
The default setting for max_connections is 151. You can see what the current setting is by running the following SQL command from the MySQL command line or MySQL query browser or any similar tool:

show variables like "%max_connections%";

This will return the following ResultSet:

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+

As visible above my current max connections settings is 151, which means at any given point in time i can have max 151 simultaneous connections.

You can change the setting to e.g. 250 by issuing the following command without having to restart the MySQL server :

set global max_connections = 250;

This will take effect immediately, but will be forgotten the next time MySQL is restarted. To make the change permanent you need to edit the my.cnf configuration file. On Ubuntu, Red Hat and similar distributions this is at /etc/my.cnf; other distros will store it elsewhere.

Under the [mysqld] section add the following setting:

max_connections = 250

The above line would make default connections limit to 250 for MySQL.

Note:

You can set the max_connections limit only if you have sufficient MySQL access rights.The max_connections limit includes any sleep(ing) connections on the mysql side. Furthermore, the maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform. Also sometime it may be necessary to increase open-files-limit system variable's limit.

Other solutions:

If the above solution doesn't resolve the problem, then it has be to one of the below mentioned issues:

1. Disk full:

Yes, if your disk is full, then also you would get this error. To resolve this please remove any unnecessary log files, or any other files that could be using disk space unnecessarily.

2. RAM full:

If you RAM is full, assuming some process like a badly coded java application is hogging all the RAM then also you would get "too many connections" error for mysql. To resolve this kill the process that's taking your RAM unnecessarily and re-start the MySQL service (you my try it without restarting the service but it may rarely work).

The last two solutions are tricky to diagnose as common wisdom would suggest just increase the max_connections should do the trick, but its not always the case, speaking from my own experience.