MySQL database import and export

Here's a run-down of some useful command line commands to manage the import/export operations of your MySQL database using mysqldump and mysql.

Note that the export operations will lock the database down, preventing any non-read operations to be executed on it along with slowing it down significantly.

[MySQL] Access denied for user ''@localhost

I had a MySQL database on CentOS 5 with user root and no password. I still don't know why, but he no longer had ALL rights on ANY schema, effectively rendering him powerless; plus after logging in:

mysql -h localhost -u root;

a simple

SELECT current_user(); 

would show '@localhost' and

show databases;

would show only show the information_schema and test schema.

There was no way I could get back my rights, even starting mysql with the --skip-grant-tables option held no positive results and the FAQ on MySQL's site did not cover my case. I kept getting the error: ERROR 1044 (42000): Access denied for user ''@'localhost' to database XXX

Additionally I could not select any database:

use XYZ;

Since I was told I did not have the rights on it. I couldn't even set or change the password neither from shell with:

mysqladmin -u root password 'NEW_PASSWORD'

to set or

mysqladmin -u root -p'OLD_PASSWORD' password 'NEW_PASSWORD'

to change, nor from the mysql prompt:

mysql -u root -p mysql



then:

UPDATE user SET password=PASSWORD('NEW_PASSWORD') where User='root';
FLUSH PRIVILEGES;

[Java] Access MySQL with Hibernate

In this post we will give a fair example on how to access a MySQL database via Hibernate in Java.
For this example we will be using MySQL server 5.5, Hibernate 3.3 and the Eclipse IDE.