Google Cloud SQL

Connecting Using MySQL Client

This page describes how to connect to your Google Cloud SQL database using MySQL Client as well as a few common SQL statements you can run in the client.

Contents

Installing MySQL Client

Before you can start using MySQL Client you must do the following:

  • Install MySQL.

    For more information, see the MySQL Reference Manual Installing and Upgrading MySQL. On the Ubuntu and Debian operating systems, you can get the MySQL client by running the command sudo apt-get install mysql-client.

    After a successful install, you should be able to go to your computer's command shell and run the mysql command to start the interactive MySQL command-line.

  • Request an IP address and authorize your network.

    You must have IP connectivity from the MySQL client to the Cloud SQL instance and be able to identify a range of IP addresses (one or more) from which your MySQL command-line tool connects. Grant access by following the instructions in Configuring Application Access Control for External Applications.

    As part of authorizing access, your Cloud SQL instance is assigned a unique IP address that you will use when you use the mysql command.

After you have satisfied the prerequisites listed above, you are ready to connect to your instance.

Connecting using MySQL Client

To use the MySQL command-line tool:

  1. Start the interactive shell by using the mysql command and specifying the IP address of the instance and a user. The command below will prompt you for a password.
    shell> mysql --host=instance-IP --user=user-name --password
    
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.5.31 (Google)
    ....
    mysql>
    

    If MySQL rejects the connection, for example, because you have specified an incorrect MySQL user name or password, the error you receive will be:

    ERROR 1045 (28000): Access denied for user 'user-name'@'client-IP' (using password: YES)
    
    If Google Cloud SQL rejects the connection, for example, because the IP address your client is connecting from is not authorized, the error you receive will be:
    ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    
  2. At the MySQL prompt, you can enter SQL commands. For example, the following command shows the threads running, including the current connection.
    mysql> SHOW PROCESSLIST;
    +----+-----------+--------------+------+---------+------+-------+------------------+
    | Id | User      | Host         | db   | Command | Time | State | Info             |
    +----+-----------+--------------+------+---------+------+-------+------------------+
    |  3 | user-name | client-IP    | NULL | Query   |    0 | NULL  | show processlist |
    +----+-----------+--------------+------+---------+------+-------+------------------+
    

For examples of common SQL commands you can run using mysql, see Common SQL Commands.

If you have configured your instance for SSL connections (see Configuring SSL for an instance), then you can use the certificates and key you saved to start the MySQL command-line tool using an SSL connection. After you have configured your instance for SSL connections and generated an SSL certificate, you have:

  • A Certificate Authority (CA) certificate. Put the certificate text in a file called ca-cert.pem.
  • A server public key certificate. Put the certificate text in a file called client-cert.pem
  • A server private key. Put key text in a file called client-key.pem.

To use the MySQL command-line tool with SSL:

shell> mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem \
             --host=instance-IP --user=user-name --password

At the MySQL prompt, you can use the \s command to verify that your connection is over SSL.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.32, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:           7
Current database:
Current user:            user-name@client-IP
SSL:                     Cipher in use is DHE-RSA-AES256-SHA
...
TCP port:                3306
Uptime:                  29 min 23 sec

Using other MySQL Client Programs

The mysql program is just one of several client programs that you can use when you install the MySQL client. The important thing to remember is that when you use any MySQL client program, you pass in an Cloud SQL instance IP, a user name, and a password just as you did with mysql.

For example, the following commands dump a database and then import it.

mysqldump --databases database-name --host instance-IP --user=user-name --password
back to top

Common SQL Commands

This section shows some basic SQL commands that you can run using MySQL Client or any admin and reporting tool that works with MySQL. For more information about MySQL, please refer to the MySQL Reference Manual. For a list of unsupported MySQL commands and SQL functions, see our FAQ.

Adding a user

mysql> SELECT User, Host, Password FROM mysql.user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost |                                           |
| root | %         | *95BDD89CD5CF403D4F5D67597569D373CBD89BDB |
| root | ::1       |                                           |
|      | localhost |                                           |
+------+-----------+-------------------------------------------+
mysql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'some-password';
0 row(s) affected.
sql> SELECT User, Host, Password FROM mysql.user;
+----------+-----------+-------------------------------------------+
| User     | Host      | Password                                  |
+----------+-----------+-------------------------------------------+
| root     | localhost |                                           |
| root     | %         | *95BDD89CD5CF403D4F5D67597569D373CBD89BDB |
| root     | ::1       |                                           |
|          | localhost |                                           |
| testuser | %         | *95BDD89CD5CF403D4F5D67597569D373CBD89BDB |
+----------+-----------+-------------------------------------------+

Checking instance uptime and other status variables

The global status variable Uptime is the number of seconds the server has been up.

mysql> SHOW STATUS LIKE 'Uptime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 861   |
+---------------------------+-------+
1 rows in set (0.08 sec)

Showing the number of connections and threads

mysql> SHOW processlist;
+----+-----------+--------------+-----------+---------+------+-------+----------------------+
| Id | User      | Host         | db        | Command | Time | State | Info                 |
+----+-----------+--------------+-----------+---------+------+-------+----------------------+
|  3 | user-name | client-IP    | NULL      | Query   |    0 | NULL  | SHOW processlist     |
|  5 | user-name | client-IP    | guestbook | Sleep   |    1 |       | SELECT * from titles |
| 17 | user-name | client-IP    | employees | Query   |    0 | NULL  | SHOW processlist     |
+----+-----------+--------------+-----------+---------+------+-------+----------------------+
3 rows in set (0.09 sec)
mysql> SHOW STATUS WHERE Variable_name = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 7     |
+-------------------+-------+
1 row in set (0.08 sec)
back to top

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.