The main problem is not the fact that you are receiving the error when issuing service mysql status, but the fact that the database is stuck and responding very slowly.
You can get the specified error after updating Mysql server to 10.0.x and this happens due to innodb storage formats. Probably the previous Mysql server version used Barracuda file format and after the update you use the Antelope storage format.
What you want to do is change the format to Barracuda and make this setting global.
The first thing to do is to backup all your databases !!
Edit your Mysql configuration file (/etc/mysql/my.cnf or other- depending on distro) and add:
Save and restart mysql process.
The login to the Mysql server and issue :
mysql -u admin -p`cat /etc/psa/.psa.shadow` //for Plesk
mysql -u admin -p // without Plesk
show variables like “%innodb_file%”;
If you see Antelope, then do this:
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
GRANT ALL PRIVILEGES ON yourdb.* TO ‘youruser’@’localhost’;
Restart mysql (and do a reboot?).
In the end you should have something like this:
If this doesn’t work, you might want to try THIS first and restart the process described above.
db-table-list.txt contains the list of tables from a specific db, exported with:
SELECT * FROM somedb;
And filtered with:
cut -d’|’ -f 2 ./db-table-list.txt
Here’s the script:
for tbl in $(cat ./db-table-list.txt); do
mysql –user=root \
–execute=”use somedb;” \
–execute=”ALTER TABLE $tbl ROW_FORMAT=Compressed;”
done <<< “$tbl”
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!).
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 |
I checked the Plesk Mysqld configuration and I had:
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:
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