Archive by category "mysql"

Restore mysql database from sql file

If you want to restore a mysql database from a sql file you have to issue this command:

[root@enix ~]#mysql -uroot -pxxx my-database < my-database-backup.sql

– where: my-database is your databse where you want to restore data;
– my-database-backup.sql is your sql backup file
– change root to your mysql user
– change xxx with your mysql password

And that’s it.

When dumping the database with mysqldump, use the option –no-create-db.
This will suspress the CREATE DATABASE statement in your dump file.
Then restore the database with

mysql -h <host> -u <user> -p <databasename> < dump.sql

In this way you can restore your data in whatever database you like (But that database has to exist!).

How to fix Plesk mysql Fatal Exception

Today I received the following error when I was trying to use one of my forums and the server load was huge: 192.

It seems that there were too many connections towards the mysql server.

"ERROR: PleskFatalException Unable to connect to database: mysql_connect() [function.mysql-connect]: Too many connections"

 

The number of connections can be checked with this command:

[root@nyx ~]# mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` extended-status | grep Max_used_connections
| Max_used_connections | 127 |
[root@nyx ~]#

I checked the Plesk Mysqld configuration and I had:

set-variable=max_connections=100
set-variable=max_user_connections=0 ##this is a bad idea because there won't be any limit to the sql resources a connection can access

I modified the /etc/my.cnf file with a new limit to both max_connections and max_user_connections:

mysql connections

And the server load started decreasing dramatically from 190 to about 18. Still too high but it was a step forward.
Checked the /var/log/mysqld.log and found some errors, so yeah, the database needed some repairing to be done.

PS: the mysql process list can be viewed like this:

 

mysqladmin -uadmin -p`cat /etc/psa/.psa.shadow` processlist | more