Google Cloud Platform
Hide
Google Cloud SQL
Write Feedback

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. For example, 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.

  • Configure your instance.
    • Assign an IP address to your instance.

      As part of authorizing access to your instance from MySQL client, you can assign an IPv4 address to your instance, or you can use the IPv6 address that comes with each instance. You will pass the instance's unique IP address as the host option when starting MySQL client.

    • Set a root password.

      You must set the password of the "root" MySQL user when connecting to the instance from any external application or tool, like MySQL client. (You do not need to set the password for connections from Google App Engine.) After setting the root password, you can connect to your Cloud SQL instance and create other MySQL users. For more information, see Setting the root account password.

    • Grant access to your instance.

      You must have IPv4 or IPv6 connectivity from your MySQL client to the Cloud SQL instance, and you must 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 Configuring Application Access Control for External Applications.

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

back to top

Connecting using MySQL Client

Connecting without SSL

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.

Connecting with SSL

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 server-ca.pem.
  • A server public key certificate. Put the certificate text in a file called client-cert.pem
  • A client private key. Put the key text in a file called client-key.pem.

To use the MySQL command-line tool with SSL:

shell> mysql --ssl-ca=server-ca.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
back to top

Connecting over IPv6

You can use MySQL Client to connect to your instance over IPv6 in the same way you connect over IPv4. Instead of an IPv4 address, use an IPv6 address with the host option. 

shell> mysql --host=2001:4860:4864:1:b4a3:7084:85e3:6883 --user=user-name --password

If you can, we recommend that you connect to your instance over IPv6. Each instance has an IPv6 address that is free to use. To connect over IPv4, you must explicitly assign an IPv4 addresss to your instance, which incurs a charge per hour idle. See the pricing page for more information.

back to top

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

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.