Mysql – Can’t access MySQL after sourcing a database and reboot

MySQL

I'm trying to set a local dev environment on a Debian VirtualBox and am now stuck for days with the following situation.

After setting up apache, php, mysql, pma and got eveything running, I open the mysql console with root user.
I create the empty database, "use" it then "source" the file I want to load.
When finished, I eventually exit from the client.
I can connect again, exit as many times as I want…
I also can access to phpmyadmin with the same root user, and confirm that the database was properly loaded.

Everything's fine, till I reboot debian. From now on, I stick to :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to access with different users, nothing changes.
Same thing for phpmyadmin.

As I work with Vbox snapshots,I've determined that the issue seems related to the database.
Rebooting after creating the empty database is fine, I'm still able to connect.

I've crawled many related topics accross the web, and couldn't come up with that particular situation, and all the approaching topics solutions start with connecting with root user – which I can't do.

What am I missing ?

Thanks for your help !

Best Answer

Oh, you nailed it!

It seems that my dump actually contained mysql.user data, so that after loading the database, I could only connect with these distant login information. Taking a look at this mysql.user table, it appears that these login were specified, not for localhost, but the distant host.

Anyway... Knowing this, I've been able to connect to mysql client, then reset these login info :

mysql –u root –p [distant password]
use mysql.user UPDATE mysql.user
SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
quit

I finally deleted these distant host lines from phpmyadmin, to be sure not to mess anything up.

Thanks a lot for your help !

Related Solutions

Mysql – I cannot access local database on terminal (MAC)

It could be your admin user is connecting from admin@127.0.0.1.

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user

Mysql – How export a database based in a primary key of a table

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ... 
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.

Best Answer

Related Solutions

Mysql – I cannot access local database on terminal (MAC)

Mysql – How export a database based in a primary key of a table

Related Question