Reliably Deploying Rails Applications: Hassle free provisioning, reliable deployment (2014)

11.2 - MySQL

Overview

In this chapter we’ll cover getting the MySQL server packages installed, setting up authentication and managing databases with the console.

Installation

For a simple installation using the ‘mysql-server’ role, the only parameters it is necessary to set in the node definition are:

1 "mysql": {

2 "server_root_password":"your_password",

3 "server_debian_password":"your_password",

4 "server_repl_password":"your_password"

5 }

Which are the plaintext passwords for the three roles. The most important one to remember is root which you’ll need for creating databases, users and setting up permissions.

It’s important to note that the chef recipe can only set the passwords when it is installing MySQL. This cannot be used to change the passwords. This also means that if, for any reason, the mysql-server package has already been installed when the mysql::server recipe is run, it is likely to fail. If therefore you run into strange permissions errors when chef reaches MySQL, check carefully that no other recipe is installing the mysql-server package as a dependency.

Some guides will suggest adding the following to the mysql-server role:

1 client": {

2 "packages": ["mysql-client", "libmysqlclient-dev","ruby-mysql", "mysql-serve\

3 r"]

4 }

As some Rails gems will required the server package to be installed even when the machine they are running on is not the server.

The problem with this is that if your run list looked like this:

1 mysql::client

2 mysql::server

Then the mysql-server package would be installed by the client recipe and so the server recipe would not be able to apply configuration to it and so would fail.

Creating Databases

SSH into the remote server and then start a root mysql console by entering:

1 mysql -u root -p

This will then prompt you to enter a password. Enter the password selected as the server root password in your chef configuration and press enter. You’ll then be taken to the MySQL console.

Here you can enter arbitrary SQL to create databases, users and assign permissions.

To create a database enter:

1 CREATE DATABASE database_name;

You can then type exit to return to the shell prompt.

Configuring Authentication

Return to the mysql shell as above and enter the following to create a user:

1 CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'plaintext_password';

This will crate a user with the username ‘user_name’, password ‘plaintext_password’ who can only connect from localhost.

To give this user access to a database created as above, enter:

1 GRANT ALL PRIVILEGES on database_name.* TO 'username'@'localhost';

You can verify that the user has access to this database by exiting back to the shell and then using the new user to connect to the new database:

1 mysql -u user_name -p database_name

and then entering the password selected for that user.

If, at any time, you want to check which database you currently have selected, you can use SELECT DATABASE(); which will result in output something like:

1 mysql> SELECT DATABASE();

2 +------------+

3 | DATABASE() |

4 +------------+

5 | test1 |

6 +------------+

7 1 row in set (0.01 sec)

Where test1 is the database you currently have selected. You can view a list of available databases by entering:

1 SHOW DATABASES;

Which will result in output similar to:

1 mysql> SHOW DATABASES;

2 +--------------------+

3 | Database |

4 +--------------------+

5 | information_schema |

6 | mysql |

7 | performance_schema |

8 | test |

9 | test1 |

10 | test2 |

11 +--------------------+

12 6 rows in set (0.01 sec)

If the user access to multiple databases, you can change which database is selected by entering:

1 USE database_name;

Importing and Exporting Databases

Like PostgreSQL, MySQL provides a simple utility for exporting data from a database to an SQL file which can then be imported into another MySQL database or kept for backup purposes.

To create a dump of the database my_database which can be accessed by the user user1 with pasword user1spassword you would enter:

1 mysqldump -u user1 -p'user1spassword' my_database > ouput_file.sql

The above would create an SQL file which contains the commands needed to create both the structure of the database (the tables, columns etc) and the data within it.

Sometimes you may want to just export the data. For example if you want to have Rails create the database and use rake db:migrate to create the table structure. This is often desirable as it ensures that the tables are created such that they can be accessed by the rails database user, rather than the user from the machine it was exported from.

To export just the content, you add the --no-create-info flag. So the command would be:

1 mysqldump -u user1 -p'user1spassword' --no-create-info my_database > ouput_fil\

2 e.sql

Importing data from an SQL file is even simpler, if you have a file called a_database_backup.sql generated with mysqldump, simple copy it to the remote server and then enter:

1 mysql -u username -p -h localhost target_database < a_database_backup.sql

To execute the contents of the SQL file on the database target_database. The username and password should be the username and password of a user who already have access to the target database on the target machine.

If you’re using this to copy production data to a local development machine, you’ll want to run:

1 rake db:drop db:create

To remove any existing data first. If you’re restoring from a content only backup, this would instead be:

1 rake db:drop db:create db:migrate

Monit

A monit configuration for mysql is available in the monit_configs-tlq::mysql recipe:

1 check process mysql with pidfile /var/run/mysqld/mysqld.pid

2 group database

3 start program = "/etc/init.d/mysql start"

4 stop program = "/etc/init.d/mysql stop"

5 if failed host 127.0.0.1 port 3306 then restart

6 if 15 restarts within 15 cycles then timeout

This will check for both the existence of the process and that the service is responding on port 3306. Bear in mind that this will need to be changed if you ever change the port MySQL is available on or remove access on localhost.

Server Admin

Whilst it’s always important to have a good grasp of the command line when managing a database server, if you’re on OSX, MySQL does have an ace up its sleeve for day to day work; Sequel Pro.

This is a graphical user interface for MySQL servers through which you can manage everything from database and user creation to managing indexes and checking data integrity. As someone who generally leans towards Postgresql when looking for a relational database provider, Sequel Pro is the one thing I consistently miss.

You can get Sequel Pro from:

http://www.sequelpro.com/