SQL Command-Line¶
MemSQL is protocol-compatible with MySQL, which means the open source mysql command-line client can be used to connect to a MemSQL server.
To use the mysql command-line client with MemSQL, first make sure it is installed on your system. On Ubuntu or Debian Linux, the installation command is:
sudo apt-get install mysql-client
Connecting to MemSQL¶
Once the mysql command-line client is installed, you need to run it with appropriate command-line options to connect to MemSQL. Assuming you are running MemSQL with the standard options, you can run the following command on the host where MemSQL is running:
mysql -u root -h 127.0.0.1 -P 3306
This will connect to the MemSQL server running on the same host, at TCP port 3306 (the standard network port for the MySQL protocol), running as MemSQL user root, with no password. To make sure you have connected to MemSQL (as opposed to an instance of MySQL running on the same host), look for the word “MemSQL” on the third line of output from the mysql command. For example, the output might start off something like this:
$ mysql -u root -h 127.0.0.1 -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.5.8 MemSQL source distribution
Warning
The string "localhost" will not work in place of the IP address 127.0.0.1 in the commands above, because the MySQL client defaults to connecting to localhost via Unix-domain sockets rather than via the specified TCP port. This also means the host must always be explicitly given on the command line, because otherwise it defaults to localhost.
- If you want to connect to MemSQL running on a different host, replace 127.0.0.1 (in the command above) by its hostname or IP address. Please heed the following warnings:
- Routing and firewall settings must be configured to allow your client host to connect to the MemSQL server host.
- Routing and firewall settings must be configured to prevent other hosts on the Internet from connecting to the MemSQL server.
Trying out MemSQL¶
The following is an example interaction session with MemSQL via the mysql command-line client. Note that the first time you run a SQL statement, it will be slow, as it is compiled to optimized native code. Subsequent executions of similar statements with different data will be fast. For example, compare the running time of the first INSERT to the second INSERT below.
Feel free to experiment: the SQL statements currently supported by MemSQL are documented in the section SQL Statements. If you have any questions or encounter any problems, don’t hesitate to contact MemSQL support at support@memsql.com.
memsql> SHOW DATABASES;
Empty set (0.00 sec)
memsql> CREATE DATABASE MemSQL_tutorial;
Query OK, 0 rows affected (8.93 sec)
memsql> SHOW DATABASES;
+-----------------+
| Database |
+-----------------+
| MemSQL_tutorial |
+-----------------+
1 row in set (0.00 sec)
memsql> USE MemSQL_tutorial;
Database changed
memsql> CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL);
Query OK, 0 rows affected (1.42 sec)
memsql> SHOW TABLES;
+---------------------------+
| Tables_in_MemSQL_tutorial |
+---------------------------+
| t |
+---------------------------+
1 row in set (0.00 sec)
memsql> DESCRIBE t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| v | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
memsql> INSERT INTO t (v) VALUES ('hello');
Query OK, 1 row affected (0.84 sec)
memsql> INSERT INTO t (v) VALUES ('goodbye');
Query OK, 1 row affected (0.00 sec)
memsql> SELECT * FROM t;
+----+---------+
| id | v |
+----+---------+
| 1 | hello |
| 2 | goodbye |
+----+---------+
2 rows in set (0.78 sec)