Anonymous
Anonymous asked in Computers & InternetProgramming & Design · 9 years ago

how to access remote mysql database from local system?

For my application i have to access database which is in host server.

My php files are in local system. I have to insert and fetch records from remote database.

How is possible?

Do i want to give grant permission in database ?

Please Help me

1 Answer

Relevance
  • 9 years ago
    Favorite Answer

    MySQL is easily setup to grant access from a remote computer. There can be other issues that get in the way, like firewalls, but assuming you have the ability to create users and grant permissions, here is how you would do it.

    First you need to login to mysql. If you don't know how to do this, talk to your mysql administrator. Second, you need to create a user account. You do this by issuing a CREATE USER command from a query browser, like so:

    CREATE USER 'mysql_user_name'@'computer_ip' IDENTIFIED BY 'mysql_user_password';

    Key to the above statement is knowing your computer IP. This is not the IP of the server, but the IP of the computer where your PHP files are running. If your IP is dynamic, then you'll need to use a percent sign ('%') in place of the IP, but this is generally considered a poor security practice. Also considered a poor security practice is accessing mysql using connections that aren't encrypted.

    Now, once you have created your user account, you'll want to issue a GRANT statement in order to give permissions to your user to operate on a particular database, or tables within a database. to do this you would issue a GRANT statement from a query browser, like so:

    GRANT ALL ON target_database.* TO 'mysql_user_name'@'computer_ip';

    Note that the above statement gives the user all permissions to operate on all tables (the *) in the target database. Once you have the user account created and permissions set, all you need to do is properly setup the connection in your php script. So your connection code would looke something like this:

    <?php

    $link = mysql_connect('IP_of_mysql_server', 'mysql_user_name', 'mysql_user_password');

    if (!$link) {

    die('Could not connect: ' . mysql_error());

    }

    ?>

    See the source links below to learn more about creating user accounts and assigning privileges in mysql. If you don't know how to login to mysql to to issue SQL statements, see your mysql administrator.

Still have questions? Get your answers by asking now.