Rails’ database connection
We have to update Rails’ database configuration in the project’s config/database.yml file. We have to enter both, the user name and password in the clear, so it is good advice to protect the file from unauthorized reading.
Encryption
Both, in MySQL and Rails (plugins), there are means to encrypt data. In MySQL, you can use the symmetric encryption algorithm AES with the AES_ENCRYPT() and AES_DECRYPT() functions, or the secure hash algorithm SHA1(). Rails provides the same encryption methods as plugins.
You should also consider encrypting data in transit, if the connection from Rails to MySQL goes over the internet. As we chose Rails to be on the same machine, we don’t have to think about the data in transit between Rails and MySQL, however Rails supports SSL connections to MySQL. In fact, we have to consider encrypting the data between the client (web browser) and the web server. More on this, you can find in the web server section and the Ruby on Rails section.
Logging
MySQL can create several log files in order to keep track of errors, slow queries, to log every query, or to log those statements that modify data.
The general query log records every SQL statement the server receives. It can, however, slow down the performance. If you want to use this logging method, for example, to identify a problem query, insert a ‘log’ entry, specifying the location of the log file, into the MySQL configuration file.
The binary log contrasts to the general query log, it doesn’t log statements that do not modify any data, and it logs them only after they have been executed. This logging method slows down the performance by about 1%, according to the MySQL documentation. However, you can use this log for restore operations or to replicate data. To enable this logging, insert a ‘log_bin’ entry, specifying the directory for the binary logs, into the MySQL configuration file.
Bear in mind that especially the general query log, and the binary log files may contain sensitive data, in particular user names and passwords, either of MySQL or of users of your application. Consider removing old log files (also, because they can occupy a lot of disk space), or setting adequate access rights, and encrypting sensitive data, before sending it to MySQL, i.e. in Rails.
Storage engine
MySQL provides basically two major storage engines for its tables, InnoDB and MyISAM. Storage engines differ in the way the data is saved, and both have its pros and cons.
The main advantage of the InnoDB storage engine is, that it supports transactions. Transactions are units of interaction with a DBMS, which must be either completed entirely or not at all.
InnoDB is the default storage engine for Rails’ MySQL database adapter. You will definitely need it, if you want to use transactions in Rails. See the Rails section for more on transactions in Rails. And if you want to test your Rails application the easiest and default way, you will also need transactions, because after each test the database is rolled back to the initial state, instead of having to delete and insert for every test case, which would be very costly.
The MyISAM storage engine is faster for some tasks, and provides fulltext searching capabilities, however, it does not support transactions. It is said, that MyISAM does not perform good, when there are many modifications of the data, but works fine for (mostly) static data, such as a zip code table, for example. So, if there are many modifications, InnoDB is said to be faster, because it uses row locking instead of table locking (i.e., concurrent processes can insert data into the table).
Backup
You should always back up at least your databases, and consider backing up the configuration and log files. To back up the databases you can simply copy the corresponding data files from your data directory. You could also use the binary log to replicate the data to another server, even incremental backups are possible then. Another (additional) possibility is to use the mysqldump program to create a textual backup of SQL statements. You can then compress them and put it in a safe place.
Verify setup
Before you actually use MySQL, you should at least verify the security of connections and the users. If you have a remote machine, assure, that you cannot connect to the MySQL server:
# telnet [host] 3306
3306 is the default port where MySQL runs, and this command shouldn’t give you access to the server, as we banned any connections from remote hosts.
On the local host try connecting with imaginary user names or with no password:
# mysql -u xyz
# mysql -u root -p
Then access it with the rails user and try some statements, which you shouldn’t be allowed to:
# mysql -u rails -p
# UPDATE user SET user="dbadmin" WHERE user="root"; # not allowed
# SHOW DATABASES; # should return only “information_schema” and your database
