Popular Tools on MemSQL¶
Because MemSQL retains compatibility with MySQL, most popular tools that work with MySQL will work out of the box. In this guide, we will explore a variety of popular tools and frameworks, and discuss workarounds wherever necessary.
In some cases, these tools expect features not covered by MemSQL’s current surface area. To enable compatibility, MemSQL will by default issue a warning and ignore such features wherever possible. This functionality can be tuned via the warn_level variable, which is set to warnings by default. See Unsupported Features for details.
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.
