====== The Brightbox MySQL Cluster ======

The MySQL cluster provides MySQL services to all Brightboxes.  This means your Brightbox can focus just on serving your web applications and you don't have to worry about managing a MySQL server.

The MySQL cluster uses standard MySQL replication between nodes to keep in sync and supports the common MySQL table formats, including InnoDB and MyISAM.




===== Access =====
The MySQL cluster is accessed from a Brightbox over the internal network using the hostname ''sqlreadwrite.brightbox.net'' using your MySQL username and password.  You can create as many databases as you need but are limited as to the number of concurrent MySQL connections your Brightbox plan allows (Note: each mongrel process uses 1 MySQL connection).

All your database names must be prefixed with your user name, so if your user name is "''thequeen''" then you could create databases named "''thequeen_family_tree''", "''thequeen_palace_manager''" or "''thequeen_heir_tracker''".

==== Example Rails Config ====
If you were the queen, your Brightbox Rails ''database.yml'' might look like this:
<code>
production:
  adapter: mysql
  database: thequeen_family_tree
  username: thequeen
  password: myroyalpassword
  host: sqlreadwrite.brightbox.net
</code>

==== Using the MySQL command line client ====
You can also access the MySQL service from your Brightbox using the ''mysql'' command, which allows you to execute queries manually, or to restore sql dumps.

For example, to login as the user ''thequeen'' with the database ''thequeen_family_tree'', you would run:

<code>
mysql -p -u thequeen thequeen_family_tree
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 104
Server version: 5.0.45-Debian_1ubuntu3-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from people;
</code>

Or to restore a sql dump from another server which you've copied to your Brightbox you could do something like this (which will usually clobber all the data in the specified database):

<code>
mysql -h sqlreadwrite.brightbox.net -p -u thequeen thequeen_palace_manager < mybackup.sql
</code>

==== Tunnelling MySQL access over ssh ====
You can open a tunnel to the MySQL server over ssh, just run the following command on your local machine:

<code>
ssh rails@username-001.vm.brightbox.net -L 3306:sqlreadwrite.brightbox.net:3306
</code>

You can do this with the Putty SSH client too, by configuring a "local port forward".

Then use your favourite GUI tool and tell it to connect to your local machine (''localhost'' on port ''3306'').  This will then be securely tunnelled by ssh to the MySQL service via your Brightbox.

This won't work properly if you have a MySQL server running on your local machine though.

===== Backups =====
All the databases are instantly replicated to more than one physical server and nightly off-site backups are taken to cover a catastrophic failure.  However, these backups are not currently available for individual database restores so we recommend that you take your own database backups to protect against user and application errors.

==== Taking a database backup ====
You can take a backup of a database using the ''mysqldump'' tool on your Brightbox.  To take a backup of the database ''thequeen_family_tree'' to a file called ''mybackup.sql'', you could run the following command on your Brightbox:

<code>
mysqldump -h sqlreadwrite.brightbox.net -p -u thequeen thequeen_family_tree > /home/rails/mybackup.sql
</code>