AyriA (ayria) wrote,
AyriA
ayria

Because I feel like it.. And yes I've heard of the rich text format but I like my very plain "Notepad2.exe", thank you very much.

__ How To Set Up Remote Connections To The Linux MySQL Server/Database On Windows Using ODBC __
*** NOTE: Doing this is a security risk for your MySQL server. ***

1. Set up the Linux MySQL Server to allow remote connections
a) Editing your MySQL config
- Open the my.cnf file (/etc/my.cnf or /etc/mysql/my.cnf). (You must be root to edit this file.)
- Ensure that the "skip-networking" line is commented out
- In some cases, skip-networking is no longer used and is deprecated in favour of just "bind-address localhost" or "bind-address 127.0.0.1". This is more secure because it only allows local connections to your database, but this isn't what you want, so comment out that line.
- Now, add the line "bind-address <Server IP>" instead. <Local Server IP> is the IP of your Linux Server where the database is hosted. You can determine your internal IP by using the "ifconfig" command.
- Restart the MySQL daemon: /etc/init.d/mysql restart

b) Setting up the Database to allow remote access
- To allow remote access log into MySQL locally (on the Linux server).
- If you only ever want to allow access from one remote location (more secure than "any/all" locations) then execute one of the following statements:

GRANT <PERMISSIONS> TO <User>@'<IP>' ON <Database>.* [IDENTIFIED BY '<Password>'][WITH GRANT OPTION];
GRANT <PERMISSIONS> TO <User>@'%' ON <Database>.* [IDENTIFIED BY '<Password>'][WITH GRANT OPTION];

[] -> optional
<> -> replace with appropriate info

PERMISSIONS -> Either ALL or any of SELECT, INSERT, UPDATE, DELETE, CREATE, DROP.
<IP> -> Remote IP you want to connect to the DB from.
<User> -> Username you want to connect to the DB with.
<Password> -> Password associated with the Username.
<Database> -> DB you want to connect to. This can be *.* instead of <Database>.* if you want to be able to connect to all databases remotely with the specified user.

c) Testing the connection
- If you want, the connection can be tested by telnetting to the server on port 3306 (or whatever port you set up your server on, but 3306 is the default).

2. Setting up Windows to access the MySQL Server remotely
a) Installing the driver
- Download the MySQL ODBC driver from the MySQL site (currently the released connector is ODBC 3.51, so that is what this document is based off of).

b) Setting up the data sources, etc
- Open Control Panel -> Administrative Tools -> Data Sources(ODBC)
- Under the 'Drivers' tab, there should be an entry with 'MySQL ODBC 3.51 Driver' if the driver was installed properly.
- Go to the System DSN tab.
- Add a new System Data Source and select the MySQL ODBC driver.
- Fill in the information on the Login Tab:
Data Source Name: The local name you want for your database
Description: Just a description/comment
Server Name/IP: address of the Linux server
User: username to use to access the database
Password: password to identify the user
Database: The name of the Database on the server
- Click the "Test" button to verify your settings.


*yawn* So now I can connect to my Linux MySQL stuff and test code without creating a local DB on Windows. It's only a bit of work to reuse already created databases. Makes my debugging life way easier and there's less chance that I'll have inconsistent testing databases if I just always use the same one. Of course we do have some different versions of the database for different releases of our software..
Subscribe
  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

  • 5 comments