How To Enable Remote Access To MySQL Server on Windows Server

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.

  1. Go to the Start Menu and select Run to open a Command Prompt
  2. 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:>.

  3. Type in:
    mysql -u root -p

    and enter in your root password when prompted.

  4. If you logged in successfully, you should see:
    mysql>

    and if not, then you will get something like:

    ERROR 1045 (28000): Access denied for user 'blah'@'localhost' (using password: YES)
  5. 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.

  6. And finally, you want to run this last command:
    mysql> FLUSH PRIVILEGES;
  7. To exit, just type:
    mysql> quit;
Related Posts with Thumbnails You can leave a response, or trackback from your own site.
6 Comments Technology, Web

Comments

6 Comments so far. You can leave a response, or trackback from your own site.
  1. Jaxx,

    Great solution for remote acces to mysql database

  2. I tried it and it worked perfect.

    thanks for the tutorial

  3. I tried but “can’t find any matching row in the user table”

  4. Prathap,

    Thanks very much.This was bocking my access throuhg dbvisualizer .

  5. yatish,

    Working fine..

    @Isnanto..
    U r getting this error because u r using the root/admin name

    try to give some different name and done leave password blank.

    GRANT ALL PRIVILEGES ON *.* TO ‘yats’@’192.168.0.5′ IDENTIFIED BY ‘password-1′;

Add Your Comments

Required
Required
Tips

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <ol> <ul> <li> <q cite=""> <strike> <strong> <sub> <sup> <u>

Your email is never published nor shared.

Ready?