MySQL

From JumbaWiki

Jump to: navigation, search
The Wiki Action Group suggests this page or section should be expanded. Please contribute!


http://wiki.jumba.com.au/w/images/3/39/Mysqlgif.gif

MySQL is a multi-user database server released under the GNU General Public License (GPL). MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB. Jumba uses phpMyAdmin, an open source tool written in PHP which handles the administration of MySQL over the Web.

For information on creating a MySQL database, see MySQL databases (cPanel)
For information on adding and querying data in a MySQL database, see phpMyAdmin

Contents

Connecting to your database

Connecting remotely

You will have to email Jumba support@jumba.com.au and get your static IP address added to the firewall before remote MySQL connections will be accepted by the server

To connect to your MySQL database remotely, you must first grant your local machine rights to connect to the database server. This must be done for all MySQL user accounts you plan on logging in with from your local machine.

  • Login to cPanel
  • Select the MySQL link.
  • Under the Access Hosts section, where it says Host (% wildcard is allowed): enter your type in the domain or subdomain your local machine identifies itself as on the public internet. You will need either your public IP address or your public originating domain/subdomain (like internode.on.net, bigpond.com, etc).

To find your public IP address, go to IP Chicken?.

  • Click the Add Host button.

Connecting with PHP

To connect to your MySQL database via a PHP script you should use the following code at the top of each script using the databases (or in an include file (preferred)) 

$db_connect=@mysql_connect ("localhost", "mysqlusername", "mysqlpassword") 
           or die ('Could not authenticate with the specified credentials');
mysql_select_db ("databasename");

Connecting using PuTTY

Using PuTTY, it is possible to access your MySQL database via a secure SSH tunnel. This method is preferred over insecure methods as it provides point-to-point encryption and does not expose your MySQL account to potential hacks from allowed networks.

To set up a tunnel in PuTTY, your account must first have SSH enabled.

First, launch PuTTY.

  • In the category tree on the left, click Tunnels near the bottom
  • Enter 3306 in the source port
  • Enter your domain in the destination field (e.g. yourdomain.com)
  • Use all other defaults (local, auto)
  • Click Session in the category tree
  • Enter your domain in the Host Name field (e.g. yourdomain.com)
  • Ensure that port 2222 and SSH are specified under prototcol
  • Give your session a name and click the Save button.
  • Now log into your shell account using your newly created session
  • Minimize the window

Next, open the third-party tool. We'll use the MySQL Adminstrator tool as an example.

  • enter localhost under Server Host (note: entering your actual address (e.g. yourdomain.com) will not work here. You must use localhost)
  • enter 3306 as the port
  • enter your db username and password
  • click OK

You should be in!

Notes:

  • Your PuTTY session must be active and you must be logged in for the tunnel to be active
  • If you are running a local MySQL db, you can specify a different port for your tunnel. Otherwise, you will not be able to access your local mysql connection while the tunnel is active. To do this: 1) specify a different number (e.g. 3307) in the source port field under Tunnels in PuTTY. 2) Use the same number as your port in the MySQL Adminstrator Port field. Note that your destination port must remain 3306. The idea is that you're sending data to port 3307 on your end, the data is sent through the tunnel, and then is shunted to port 3306 on Jumba's end.
  • PuTTY is not required on UNIX-compatible (eg Linux, Mac OS X, FreeBSD) systems that have an SSH client already installed.

Connecting using SSH

To connect just use: 

mysql -h 127.0.0.1 -P 3307 -u db_user_name -p yourdomain.com

Or single command line: 

ssh -fNg -L 3307:127.0.0.1:3306 your_user_name@yourdomain.com

Then connect with mysql as above.

Migrating a database to Jumba

For more information, see main article: How to migrate a large MySQL database

Migrating a database from your old host can be a daunting task if you're not sure what you are doing. phpMyAdmin is provided on most hosts in order to achieve this.

While phpMyAdmin is a fantastic tool for importing/exporting modest-sized databases, for larger databases, you'll need to approach this in a different way. Follow the main article link for more ifnormation.

See Also

External Links

Code Languages
ActionScript | ASP | ASP.NET | CGI | ColdFusion | CSS | HTML | Java | JavaScript | JSP | MySQL | PHP | Perl | XML
Retrieved from "http://wiki.jumba.com.au/wiki/MySQL"
Personal tools