It took me a bit to figure this out, which in reality is quite simple. These are the steps for a Windows Server to allow you to use programs such as Navicat or other fancy application based GUI to manage the MySQL database.
- Go to the Start Menu and select Run to open a Command Prompt
- Browse to the MySQL bin directory. I had to do
C:>CD "Program FilesMySQLMySQL Server 4.1bin"
Keep in mind that your directory structure might differ from mine depending on how you installed MySQL. Also you may have to do a few “Clear Directories” (i.e. “CD” or CD..) to get to the base of C:>.
- Type in:
mysql -u root -p
and enter in your root password when prompted.
- If you logged in successfully, you should see:
and if not, then you will get something like:
ERROR 1045 (28000): Access denied for user 'blah'@'localhost' (using password: YES)
- Enter in
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
What this will do is create a new user with ROOT privileges, so be very careful what account you are creating. If you are just using the root account, then replace USERNAME with root. And just so we are clear, USERNAME is the account you wish to create or use. IP is the physical IP address of the computer you wish to grant remote access to. If you enter ‘%’ instead of an IP number, that user will be able to remote access the MySQL server from any computer. PASSWORD is the password you wish to create if it’s a new user or the existing password of an existing account. And yes, you need to use the single quotation.
- And finally, you want to run this last command:
mysql> FLUSH PRIVILEGES;
- To exit, just type: