Connect To Databases


Prerequisites

To connect to your database instance, you need to create an instance on your account that contains at least one database and at least one user. You also need access to a terminal and a compatible MySQL® client on your local computer.

Connect to your database

Perform the following steps to connect to your database by using the command line.

Step 1: Obtain the IP address for your database instance

  • Log in to the SpinUp control panel.
  • In the navigation bar, click Databases.
  • Click the name of the instance you’d like to connect to.
  • Scroll down to the Network section and copy the appropriate IP address. If you have a High Availability (HA) instance, copy either the Internal IP, if you are connecting from a server in the same datacenter, or the Internet IP, if you need to connect from the Internet.
  • If you are connecting to an HA instance, you also have to add at least one IP to your access control list (ACL). In the instance details section ACL tab click + ADD ACL, enter the IP address you are connecting from, and click + ADD. After the process completes, your ACL appears in the list.

Step 2: Connect to the database instance

You can connect to your database by using the MySQL client, by using a sample script, or by using a Load Balancer as shown in the following sections.

Example 1: Connect to your database by using the MySQL client

  1. From a terminal window, use Secure Shell (SSH) to log in to a server that is located in the same data center as your database instance by using the following command (substituting your IP address and user):


   ssh [email protected]

  1. From your server, use your MySQL client to access your database by running the following command (substituting your username and IP addresses):


   mysql -h 11.22.33.44 -u database_instance_username -p

Example 2: Connect to your database with an example script

Use an example script to display a simple web page, which helps you to verify your connection to your database.

Your web server needs to be in the same data center as your database instance and connects via your Data Center Network.

Note: This process assumes both your web server and Hypertext Preprocessor (PHP) are installed and configured appropriately.

  1. Copy the following PHP script to a new file on your computer, test-database.php:

    <html>
    <head><title>Connecting to Databases</title></head>
    <body><pre>
    <?php
    // phpinfo();
    $YOUR_HOST = "you_database_IP";
    $YOUR_USER = "your_user";
    $YOUR_PWD = "your_password";
    $YOUR_DB = "your_database";
    
    //
    // Get "e"
    //
    $arg_expr = trim($\_POST["e"]);
    if($arg_expr == "") {
        $arg_expr = "PI()*13.36901522";
    }
    else {
        if(get_magic_quotes_gpc()) {
         $arg_expr = stripslashes($arg_expr);
        }
    
        //
        // Connect to the database
        //
        $connection = mysql_connect($YOUR_HOST, $YOUR_USER, $YOUR_PWD);
        if (!$connection) {
             die('I could not connect to the database. The error is: ' . mysql_error());
        }
        mysql_select_db($YOUR_DB, $connection);
        //
        // Calculation
        //
        $result = mysql_query("SELECT (" . $arg_expr . ");", $connection);
        $row = mysql_fetch_array($result, MYSQL_NUM);
        $eValue = $row[0];
        printf("The database connection worked, and MySQL says that %s = %s<BR>%s", $arg_expr, $eValue, mysql_error());
        mysql_free_result($result);
        mysql_close($connection);
    }
    ?>
    <FORM ACTION='test-database.php' METHOD='POST'>
        Enter a MySQL expression:
        <INPUT TYPE="TEXT" NAME="e" VALUE="<? echo $arg_expr; ?>"/>
        <INPUT TYPE="SUBMIT">
    </FORM>
    This is a simple PHP example to test your connection to SpinUp Databases.
    It does not require your database to have any tables.
    It doubles as a handy way to calculate simple MySQL expressions from the browser.
    <BR>
    Because this sample uses string concatenation to compose SQL statements, only
      use this in your development environment in your password-protected site.
    <BR>
    EXAMPLES:
        PI()\*13.3690152197
        curdate()
        3=3 AND 4>4
        MID('SpinUp',1,4)
        SIN(PI()/2)
        SHA1('SpinUp Databases')
    </pre></body>
    </html>
    
    
    1. Locate the following lines in the sample script, and replace the items in double quotes with your settings:
    
    $YOUR_HOST = "your_database_IP";
    $YOUR_USER = "your_user";
    $YOUR_PWD = "your_password";
    $YOUR_DB = "your_database";
    
    

For example, replace your_database_IP with your database’s IP address. Then do that for each of the your_user, your_password, and your_database settings.

  1. Save the changes to test-database.php.

  2. Copy your local test-database.php script to your server, for example /var/www/html/test-database.php.

  3. Run the script to test connectivity to your database.

If the connection succeeds, your web output displays the following:

   
   The database connection worked, and MySQL says that PI()*13.36901522 = 42.00000000

  1. If you like, type another MySQL expression, for example PI()*2, and click Submit to have it evaluated.

Example 3: Connect by using a Load Balancer

Note: If you are using a high availability (HA) instance, you already have a load-balanced solution. Feel free to use this example for testing or learning, but we strongly recommend that you don’t place a Load Balancer in front of an HA instance.

  1. Log in to the SpinUp control panel.
  2. In the navigation bar, click Load Balancers.
  3. Enter a name.
  4. Specify the same data center as your database instance is in.
  5. In the Configuration section, select Accessible on the Public Internet for the Virtual IP option.
  6. Select MySQL for the Protocol/Port.
  7. In the Add Nodes section, click Add External Node.
  8. Paste your hostname string in the IP or Hostname field.
  9. Enter the default MySQL port 3306 in the Port field.
  10. Click Add External Node.
  11. Click Create Load Balancer.

The Load Balancer has now been created, and you can test connectivity to it by modifying the preceding Example 2 script with the Load Balancer’s IP address or connection string.

Conclusion

In this article, you learned how to connect to your database by using multiple methods.


Related Content