MySQL command line cheat sheet
The mysql command line utility
Many administrative tasks can or should be done on your local machine, even though your database lives on the cloud. You can do some of them through a visual user interface, but that's not covered here. Knowing how to perform these operations on the command line means you can script them.
This section isn't a full cheat sheet for the MySQL command line utilities. It covers the most common operations and shows them in sequence, as you'd use them in a typical work session.
| Starting and quitting the mysql command-line client | |
|---|---|
| Command-line prompts for MySQL utilities | |
| Opening a connection to your Cloud SQL instance | |
| mysql CLI shortcut commands |
What you need to know
Before using this section, you'll need:
- The user name and password for your Cloud SQL database
- The IP address of your Cloud SQL instance
- Possibly, your own IP address if you have difficulty connecting.
Command-line prompts on the operating system
The $ starting a command line in the examples below represents the operating system prompt. Prompts are configurable so it may well not look like this. On Windows it might look like C:\go\src> but Windows prompts are also configurable.
$ mysql -u sampleuser -h 123.194.255.122 -p
A line starting with # represents a comment. Same for everything to the right of a #. If you accidentally type it or copy and paste it in, don't worry. Nothing will happen.
# Open a database in a remote location.
$ mysql -u sampleuser -h 123.194.255.122 -p
Command-line prompts for MySQL utilities
Once you've fired up one of the MySQL command line (CLI) utilities you'll often find they have a prompt of their own. Here that prompt will be represented as mysql>. The MySQL prompts are configurable so it may not look like this if someone has already installed MySQL and configured it.
As with the operating system prompt,line starting with # represents a comment. Same for everything to the right of a #.
# List all tables in this database
mysql> show tables;
The mysql command line utility
You'll use the mysql command-line tool most of all because it's used to create databases and tables, show information about tables, and even to enter information (records) into the database.
Opening a connection to your Cloud SQL instance
To connect your remote Cloud SQL instance with your local MySQL installation, start mysql at your operating system command line.
In the example below, replace:
123.456.789.000with the IP address of your Cloud SQL instancesampleuserwith your Cloud SQL user name
$ mysql -u sampleuser -h 123.456.789.000 -p
Enter password:
Here you'd enter the password. In case someone is peering over your shoulder, the characters are hidden. After you've entered your information properly you'll get this message (truncated for clarity):
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 359
Oracle is a registered trademark blah blah blah
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Quitting the mysql command line utility
Before we learn anything else, here's how to quite the mysql CLI and return to the operating system prompt:
mysql> quit;
Warning: Don't forget to end commands with a semicolon!
One gotcha is that almost all commands you enter into the mysql CLI must end in a semicolon. For example:
# List all tables in this database
mysql> show tables;
It's easy to forget. If you do, you'll see this perplexing prompt:
mysql> show tables
->
When you do, just remember to finish it off with that semicolon:
mysql> show tables
-> `;` # All right, I get the message!
We cheated a little
Remember how we said most of the commands require a semicolon? That's actually only SQL statements. There are other meta-commands like help, quit and so forth that don't need the semicolon. Here are a few examples. We lied a bit to start you with good habits right away.
# help shows you a list of mysql commands.
mysql> help
# Quit without the semicolon
mysql> quit
Also, shortcut commands:
Some of these commands are abbreviated to a backslash character (or \ followed by a single letter and the Enter key:
# Get help. Note it's a backslash, not a forward slash.
mysql> \h
# Status shows name of current database, user, etc.
mysql> \s
# Even shorter way to quit.
mysql> \q
Getting information about databases
List databases (SHOW DATABASES)
What most people think of as a database (say, a list of customers) is actually a table. A database is a list of tables, information about those tables, information about users and their permissions, and much more. Some of these databases (and the tables within) are updated automatically by SQL Server as you use them.
To get a list of all databases,
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampledatabase |
+--------------------+
4 rows in set (0.08 sec)
Creating a database (CREATE DATABASE)
Before you add tables, you need to create a database to contain those tables:
mysql> CREATE DATABASE IF NOT EXISTS TODO;
# Or if you're feeling brave:
mysql> CREATE DATABASE TODO;
The result should look like this:
Query OK, 1 row affected (0.07 sec)
Choosing a database to work on (USE)
The use command lets you select a database:
mysql> use sampledatabase;
Mysteriously, you are then informed:
Database changed
This doesn't mean the contents or state of the database have changed. It means that you have selected a new current database (the USE statement) to operate on.
Getting a list of tables contained in this database (SHOW TABLES)
mysql> show tables;
In this example there are two tables in the database, sample1 and todo.
+------------------------+
| Tables_in_tomsdatabase |
+------------------------+
| sample1 |
| todo |
+------------------------+
2 rows in set (0.06 sec)
Getting a list of fields in a table (SHOW COLUMNS FROM)
What are the fields (columns) in a table? Find out with show columns from followed by the table name.
Here's an example for the todo table, followed by its output.
mysql> show columns from todo;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| title | varchar(100) | NO | | | |
| description | text | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
Adding tables and records
Creating a table (CREATE TABLE)
NOTE
Before you create any tables, you must create a database to contain those tables.
This polite form creates a table only if one by that name is not already present. If you're feeling brave you can omit the IF NOT EXISTS
mysql> create table if not exists `product` (id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL DEFAULT '', description TEXT);
Adding a record (INSERT INTO)
Here's how to add a record, populating every field:
# The id field is an automatically assigned
# unique number. The autoincrement means
# that number will be increased by at least
# 1 and assigned to that same field when
# a new record is created.
# Using 0 is a placeholder; MySQL automatically updates the field properly.
mysql> INSERT INTO product VALUES(0, 'Cool item', 'Does neat stuff');
Query OK, 1 row affected (0.13 sec)
Doing a simple query--get a list of records (SELECT)
mysql> SELECT * FROM product;
+----+---------------+-----------------+
| id | name | description |
+----+---------------+-----------------+
| 1 | Cool item | Does neat stuff |
+----+---------------+-----------------+
For more on SELECT, see the MySQL SELECT Syntax.
Inserting several records at once
mysql> INSERT INTO product VALUES
(0, 'A better item', 'Nicer stuff'),
(0, '3rd item rocks', 'Crazy good'),
(0, '4rth item now', 'Not so hot')
;
Adding only specific fields from a record
You can add records but specify only selected fields (also known as columns). MySQL will use common sense default values for the rest.
In this example, only the name field will be populated. The description column is left blank, and the id column is incremented and inserted.
Two records are added:
mysql> INSERT INTO product (name) VALUES
('Replacement sprocket'),
('Power adapter')
;
Let's look at our handiwork so far:
mysql> select * from product;
+----+----------------------+-----------------+
| id | name | description |
+----+----------------------+-----------------+
| 1 | Cool item | Does neat stuff |
| 2 | A better item | Nicer stuff |
| 3 | 3rd item rocks | Crazy good |
| 4 | 4rth item now | Not so hot |
| 5 | Replacement sprocket | NULL |
| 6 | Power adapter | NULL |
+----+----------------------+-----------------+
Specifying multiple fields
Here we'll enter selected fields (in this case, name and description, leaving id untouched).
mysql> INSERT INTO product (name, description) VALUES
('Thingamajig', 'Better than last year\'s thingamajig');
Using backslash to enter special characters
Note that the description, Better than last year's thingamajig , contains an apostrophe (') character which would confuse MySQL. It is escaped using the backslash (\) as shown above.
Let's see the result of using backslash to escape
mysql> select * from product where id=7;
+----+-------------+---------------------------------------------+
| id | name | description |
+----+-------------+---------------------------------------------+
| 7 | Thingamajig | A real upgrade from last year's thingamajig |
+----+-------------+---------------------------------------------+
1 row in set (0.06 sec)
Diagnostic commands
Showing the names of all users
mysql> select User,Host from mysql.user;
+-----------+-----------+
| User | Host |
+-----------+-----------+
| mysql.sys | localhost |
| root | localhost |
| testuser | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)
Getting server information
This lets you understand things like the name of the currently connected database, whether the connection is SSL, the port on the server side being used to connect to Cloud SQL, etc.
mysql> \s
Sample output:
mysql Ver 14.14 Distrib 5.6.26, for osx10.8 (x86_64) using EditLine wrapper
Connection id: 8
Current database: sampledatabase
Current user: [email protected]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.26 (Google)
Protocol version: 10
Connection: 173.256.255.122 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hour 13 min 52 sec
Threads: 1 Questions: 598 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 24 Queries per second avg: 0.134
Learning the local MySQL port number
mysql> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
Learning the hostname of your computer
mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| hostname | tomsmbairhydra.hsd.wa.comcast.net |
+---------------+-----------------------------------+
1 row in set (0.00 sec)
Getting the name of the current user (SELECT USER())
mysql> select user();
+---------------+
| user() |
+---------------+
| tom@localhost |
+---------------+
1 row in set (0.00 sec)