mysql2

Mysql2 - A modern, simple and very fast MySQL library for Ruby - binding to libmysql

The Mysql2 gem is meant to serve the extremely common use-case of connecting, querying and iterating on results. Some database libraries out there serve as direct 1:1 mappings of the already complex C APIs available. This one is not.

It also forces the use of UTF-8 [or binary] for the connection and uses encoding-aware MySQL API calls where it can.

The API consists of three classes:

Mysql2::Client - your connection to the database.

Mysql2::Result - returned from issuing a #query on the connection. It includes Enumerable.

Mysql2::Statement - returned from issuing a #prepare on the connection. Execute the statement to get a Result.

Installing

General Instructions

gem install mysql2

This gem links against MySQL's libmysqlclient library or Connector/C library, and compatible alternatives such as MariaDB. You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or other appropriate package for your system. See below for system-specific instructions.

By default, the mysql2 gem will try to find a copy of MySQL in this order:

• Option --with-mysql-dir, if provided (see below).
• Option --with-mysql-config, if provided (see below).
• Several typical paths for mysql_config (default for the majority of users).
• The directory /usr/local.

Configuration options

Use these options by gem install mysql2 -- [--optionA] [--optionB=argument].

• --with-mysql-dir[=/path/to/mysqldir] - Specify the directory where MySQL is installed. The mysql2 gem will not use mysql_config, but will instead look at mysqldir/lib and mysqldir/include for the library and header files. This option is mutually exclusive with --with-mysql-config.

• --with-mysql-config[=/path/to/mysql_config] - Specify a path to the mysql_config binary provided by your copy of MySQL. The mysql2 gem will ask this mysql_config binary about the compiler and linker arguments needed. This option is mutually exclusive with --with-mysql-dir.

• --with-mysql-rpath=/path/to/mysql/lib / --without-mysql-rpath - Override the runtime path used to find the MySQL libraries. This may be needed if you deploy to a system where these libraries are located somewhere different than on your build system. This overrides any rpath calculated by default or by the options above.

• --with-openssl-dir[=/path/to/openssl] - Specify the directory where OpenSSL is installed. In most cases, the Ruby runtime and MySQL client libraries will link against a system-installed OpenSSL library and this option is not needed. Use this option when non-default library paths are needed.

• --with-sanitize[=address,cfi,integer,memory,thread,undefined] - Enable sanitizers for Clang / GCC. If no argument is given, try to enable all sanitizers or fail if none are available. If a command-separated list of specific sanitizers is given, configure will fail unless they all are available. Note that the some sanitizers may incur a performance penalty, and the Address Sanitizer may require a runtime library. To see line numbers in backtraces, declare these environment variables (adjust the llvm-symbolizer path as needed for your system):

  export ASAN_SYMBOLIZER_PATH=/usr/bin/llvm-symbolizer-3.4
  export ASAN_OPTIONS=symbolize=1

Linux and other Unixes

You may need to install a package such as libmariadb-dev, libmysqlclient-dev, mysql-devel, or default-libmysqlclient-dev; refer to your distribution's package guide to find the particular package. The most common issue we see is a user who has the library file libmysqlclient.so but is missing the header file mysql.h -- double check that you have the -dev packages installed.

macOS

You may use Homebrew, MacPorts, or a native MySQL installer package. The most common paths will be automatically searched. If you want to select a specific MySQL directory, use the --with-mysql-dir or --with-mysql-config options above.

If you have not done so already, you will need to install the XCode select tools by running xcode-select --install.

Later versions of MacOS no longer distribute a linkable OpenSSL library. It is common to use Homebrew or MacPorts to install OpenSSL. Make sure that both the Ruby runtime and MySQL client libraries are compiled with the same OpenSSL family, 3.x, since only one can be loaded at runtime.

$ brew install openssl@3 zstd
$ gem install mysql2 -- --with-openssl-dir=$(brew --prefix openssl@3)

or

$ sudo port install openssl3

Since most Ruby projects use Bundler, you can set build options in the Bundler config rather than manually installing a global mysql2 gem. This example shows how to set build arguments with Bundler config:

$ bundle config --local build.mysql2 -- --with-openssl-dir=$(brew --prefix openssl@3)

Another helpful trick is to use the same OpenSSL library that your Ruby was built with, if it was built with an alternate OpenSSL path. This example finds the argument --with-openssl-dir=/some/path from the Ruby build and adds that to the Bundler config:

$ bundle config --local build.mysql2 -- $(ruby -r rbconfig -e 'puts RbConfig::CONFIG["configure_args"]' | xargs -n1 | grep with-openssl-dir)

Note the additional double dashes (--) these separate command-line arguments that gem or bundler interpret from the additional arguments that are passed to the mysql2 build process.

Windows

Make sure that you have Ruby and the DevKit compilers installed. We recommend the Ruby Installer distribution.

By default, the precompiled mysql2 gem for Windows vendors MariaDB Connector/C, built from the same MSYS2 mingw-w64 package a native Windows build installs via pacman (see the gemspec's msys2_mingw_dependencies). If you prefer to use a local installation of Connector/C, add the flag --with-mysql-dir=c:/path/to/connector-c (this path may use forward slashes).

By default, the libmariadb.dll library will be copied into the mysql2 gem directory. To prevent this, add the flag --no-vendor-libmysql. The mysql2 gem will search for libmariadb.dll in the following paths, in order:

• Environment variable RUBY_MYSQL2_LIBMARIADB_DLL=C:\path\to\libmariadb.dll (note the Windows-style backslashes). The older RUBY_MYSQL2_LIBMYSQL_DLL name is still read as a fallback.
• In the mysql2 gem's own directory vendor/libmariadb.dll
• In the system's default library search paths.

Usage

Connect to a database:

# this takes a hash of options, almost all of which map directly
# to the familiar database.yml in rails
# See https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Mysql2Adapter.html
client = Mysql2::Client.new(:host => "localhost", :username => "root")

Then query it:

results = client.query("SELECT * FROM users WHERE group='githubbers'")

Need to escape something first?

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

You can get a count of your results with results.count.

Finally, iterate over the results:

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  puts row["id"] # row["id"].is_a? Integer
  if row["dne"]  # non-existent hash entry is nil
    puts row["dne"]
  end
end

Or, you might just keep it simple:

client.query("SELECT * FROM users WHERE group='githubbers'").each do |row|
  # do something with row, it's ready to rock
end

How about with symbolized keys?

client.query("SELECT * FROM users WHERE group='githubbers'", :symbolize_keys => true).each do |row|
  # do something with row, it's ready to rock
end

You can get the headers, columns, and the field types in the order that they were returned by the query like this:

headers = results.fields # <= that's an array of field names, in order
types = results.field_types # <= that's an array of field types, in order
results.each(:as => :array) do |row|
  # Each row is an array, ordered the same as the query results
  # An otter's den is called a "holt" or "couch"
end

Prepared statements are supported, as well. In a prepared statement, use a ? in place of each value and then execute the statement to retrieve a result set. Pass your arguments to the execute method in the same number and order as the question marks in the statement. Query options can be passed as keyword arguments to the execute method.

Be sure to read about the known limitations of prepared statements at https://dev.mysql.com/doc/c-api/9.7/en/c-api-prepared-statement-problems.html

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA", :as => :array)

Session Tracking information can be accessed with

c = Mysql2::Client.new(
  host: "127.0.0.1",
  username: "root",
  flags: "SESSION_TRACK",
  init_command: "SET @@SESSION.session_track_schema=ON"
)
c.query("INSERT INTO test VALUES (1)")
session_track_type = Mysql2::Client::SESSION_TRACK_SCHEMA
session_track_data = c.session_track(session_track_type)

The types of session track types can be found at https://dev.mysql.com/doc/refman/5.7/en/session-state-tracking.html

Connection options

You may set the following connection options in Mysql2::Client.new(...):

…

A few of these options default to something other than nil/unset:

Connecting to MySQL on localhost and elsewhere

The underlying MySQL client library uses the :host parameter to determine the type of connection to make, with special interpretation you should be aware of:

• An empty value or "localhost" will attempt a local connection:
  • On Unix, connect to the default local socket path. (To set a custom socket path, use the :socket parameter).
  • On Windows, connect using a shared-memory connection, if enabled, or TCP.
• A value of "." on Windows specifies a named-pipe connection.
• An IPv4 or IPv6 address will result in a TCP connection.
• Any other value will be looked up as a hostname for a TCP connection.

Secure connections with SSL/TLS

The mysql2 gem can configure the underlying MySQL/MariaDB client library to connect to the database server using a secure SSL/TLS connection. Setting any :tls_* option enables a secure connection, but :tls_mode is the option that actually controls whether it's enforced and verified.

There are important differences in SSL/TLS support between MySQL and MariaD