Connection to source server failed. Check username and password.

[SloppyGoat]

MySQL Permissions for Idiots

If anyone else runs into this, this is how I solved it. I'll make the instructions pretty detailed, for those who are as limited in MySQL knowledge as I am. This can be easier if you have some sort of decent MySQL manager.

1) Open a command prompt at /MySQL/bin

2) To log on to your root, type this:

mysql --user=root --password=yourpassword

You will then be at a mysql prompt and logged into your root. If your root does not have a password, I suggest you create one.

3) Create and Grant your new user all privileges. Make sure you have a damn good password for security reasons. According to Jerry, you really only have to GRANT SELECT? But this worked for me, and if you can figure out my password, you can do anything to my database anyway, so I granted all.

I had to run both of these before Impex would work. Before, I only had privileges on localhost, and Impex would stop after a certain point. I don't really know what "%" is, but you must have all privileges on that too.

grant all privileges on *.* to someusr@"%" identified by 'passwrd';

grant all privileges on *.* to someusr@"localhost" identified by 'passwrd';

4) Now make sure your Impex config file has the proper data and run it again. It should import flawlessly. I was thrilled when it finally ran! :D

This info should actually be posted in a FAQ here, IMO. It would save a lot of headaches. I see I'm not the only one who has ran into this before, and not the only one who didn't know what to do and couldn't find any specific instructions. So, I hope this helps someone. ;) If anything can be done better or differently, please feel free to comment and/or correct me.

[Jerry]

Granting all privileges is an unnecessary thing to do and provides a potential security hole, granting SELECT only ensures that you don't open the database up to potential abuse.

The reason you grant SELECT only to a ImpEx user, is because then only that user can connect from the remote server specified, and can only SELECT (which is all ImpEx does on a source).

So if your user/pass is found or compromised you can only SELECT, if you grant ALL the database can be dropped, deleted, modified, etc.

If you are local (as you are doing I believe) this is all irrelevant as you will most likely be using the same database that your vBulletin user/pass is or another one on the local host that uses the same details.

The most common errors where people have run into this before is not giving ImpEx the correct user/pass for the local database, or getting the details from their host.

Or attempting to connect remotely with their user/pass for the source where the source database doesn’t allow remote connections.

In short if you are connecting remotely it is strongly advised to create a user on the source for the purposes of the import and granting that user SELECT only. If you are importing locally, use the same database and details as vBulletin, table prefix allowing (as to not have table name clashes). Other wise another database on local host with local select permissions.

Remote imports should only be performed where copying and restoring the database isn't an option at all.

% is a wildcard to grant to an IP range, the SQL where you use that gives full permission to that user/pass from any connection in any IP range, i.e. everyone.

[SloppyGoat]

I see. So I should probably change my privileges to SELECT only. Should I do that for root too? Is that the only privilege needed for vB to keep running and updating the tables?

Thanks for explaining this. How would I have copied and restored the database? You mean I could copy it, add it to the new instance of MySQL and then import directly from that database? Because I was asking about doing that too, but had no idea how. I know how to create a new database, but not import an existing one.

[Jerry]

If you are running on localhost then GRANT with localhost only, and don't use the % as that will open it up to any where.

MySQL by default and the vast majority of hosts set it up so it can only be accessed from localhost for security reasons (which are good and valid).

If you are using localhost then you should be able to use the permission that you are using for vBulletin for ImpEx as well.


How would I have copied and restored the database?

The usual way is to get a dump from the source server (if it is different) like this:

http://www.vbulletin.com/docs/html/main/moving_servers_backup

It's the same as moving a site; you are copying and moving the database.

Then restore it on the new server:

http://www.vbulletin.com/docs/html/main/moving_servers_restore


I know how to create a new database, but not import an existing one.

You create a database, and then use the SQL file to "restore" the data into that database. I.e. create a receptacle, and then fill it up.