Popular Tools on MemSQL¶

Using mysqldump with MemSQL¶

Mysqldump is the most popular way to get data in and out of MySQL, and it works extremely well with MemSQL. You can both pull data out and push data in by running the same commands you would with MySQL. The standard way to pull data out of a database with mysqldump is to run something like

$ mysqldump -h 127.0.0.1 -u root -P 3306 [database name] > data.sql

The header of the dump contains a few global variable settings, some of which are unsupported (and ignored) by MemSQL. Specifically, although MemSQL supports unique keys, it does not support disabling them temporarily. Furthermore, MemSQL does not yet support foreign key checks. So, the following two lines are ignored:

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

For details information on how to get data in and out of MemSQL, visit Transitioning from MySQL to MemSQL and Exporting Data from MemSQL.

Setting up Ruby on Rails with MemSQL¶

MemSQL supports the major SQL surface area required by Ruby on Rails. Let’s follow along the official getting started guide: http://guides.rubyonrails.org/getting_started.html.

When you get to Section 3.2, you should configure your project to use the MySQL Driver, by replacing

$ rails new blog

with

$ rails new blog --database=mysql

The project directory contains all of the files Ruby on Rails uses to generate a web application organized by various aspects of the application. Once the project is configured for MySQL, you only need to point the project towards MemSQL to make it work.

In Section 3.3.2, the guide discusses how to configure the database properly. MemSQL uses a different socket file than MySQL, and if you’re not careful about configuring the database correctly, the driver will pick MySQL’s socket file instead of connecting to MemSQL’s. Within each group (development, test, and production), add a field host: 127.0.0.1 and set socket: /tmp/memsql.sock.

Below is an example of the database.yml file after this modification.

# MySQL.  Versions 4.1 and 5.0 are recommended.
#
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
adapter: mysql2
encoding: utf8
reconnect: false
database: app_development
pool: 5
username: root
password:
host: 127.0.0.1
socket: /tmp/memsql.sock

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
adapter: mysql2
encoding: utf8
reconnect: false
database: app_test
pool: 5
username: root
password:
host: 127.0.0.1
socket: /tmp/memsql.sock

production:
adapter: mysql2
encoding: utf8
reconnect: false
database: app_production
pool: 5
username: root
password:
host: 127.0.0.1
socket: /tmp/memsql.sock

Finally, to create the databases in MemSQL, run

$ rake db:create

This command will create three databases in MemSQL: [project name]_development, [project name]_test, and [project name]_production. The data Rails automatically adds to these databases can be viewed and modified through MemSQL just like any other database. After Rails creates the databases, you can continue the guide and further Rails development as you ordinarily would without any more database configuration.

Setting up Django with MemSQL¶

Configuring Django for MemSQL is very similar to configuring it for MySQL. These tips will help you follow along the official Django tutorial.

Creating a database in MemSQL is extremely simple. Just run,

$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "

memsql> create database [database name];
memsql> exit

You can then create a project the standard way:

$ django-admin startproject [project name]
$ cd [project name]

Now that the project has been created, you need to configure Django to use MemSQL. When DEBUG is set to True, Django will raise an Exception when it encounters a MySQL warning. MemSQL issues warnings on unsupported features, so you will have to disable this functionality by setting DEBUG=False.

The project root contains a settings.py file. Change the value of the DEBUG variable and the DATABASES dictionary as shown below.

DEBUG = False

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': '[database name]',
        'USER': 'root',
        'PASSWORD': '',
        'HOST': '127.0.0.1',
        'PORT': '3306',
    }
}

You can populate the database you created in MemSQL by running the following command.

$ python manage.py syncdb

Note that the interpreter will issue two warnings about MemSQL not supporting REFERENCES or FOREIGN KEY statements. These statements are unsupported in MemSQL, but their exclusion will not impact the execution of Django due to the limited extent to which Django employs them.

The databases, tables, and data you insert in the database will be accessible in the familiar MySQL way. Certain ORM-generated queries may attempt to use features not yet part of MemSQL’s surface area. These commands will issue errors to help you identify and revise the ORM call.