====== 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.

It is actually made up of lots of small independent clusters and customers are allocated one of the clusters when they buy their first Brightbox.

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, username and password listed in the control panel.  The hostname depends on the cluster you've been allocated to but generally looks like ''db99.mysql.vm.brightbox.net''((One exception is ''db01.mysql.vm.brightbox'' which was historically known as ''sqlreadwrite.brightbox.net''. That name will continue to work indefinitely but is deprecated.))  The username is usually your Brightbox account name and the password is generated randomly when you sign up (and is also listed in the control panel).

You can create as many databases as you need but are limited to the number of concurrent MySQL connections your Brightbox plan allows.  Each Mongrel or Passenger process generally uses 1 MySQL connection.  Rake tasks, DRb servers, sphinx indexers or irb sessions will use additional connections too.

You must access the MySQL cluster from your Brightbox. You can't access it from outside the Brightbox network unless you create an SSH tunnel (see below).

===== Database Names =====

Your database names must be prefixed with your MySQL username.  So, if your username 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 something like this:
<code>
production:
  adapter: mysql
  database: thequeen_family_tree
  username: thequeen
  password: myroyalpassword
  host: db99.mysql.vm.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 -h db99.mysql.vm.brightbox.net -p -u thequeen thequeen_family_tree
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1337
Server version: 5.0.87

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 db99.mysql.vm.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:db99.mysql.vm.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 (''127.0.0.1'' 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 databases are instantly replicated to more than one physical server and storage.  Nightly backups are also taken and replicated off-site.  Restores of these databases can be requested via the support system, but are chargeable (unless you have managed backups, in which case it is included).


==== Changing your password ====

The initial password for MySQL cluster accounts is generated randomly and is accessible from the control panel.  You can change your password using the following SQL command:

<code sql>
SET PASSWORD FOR 'customername'@'10.1.%' = PASSWORD('MyNewPassword');
</code>

Your new password will be unknown to the control panel, so you must keep your own record.

==== 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 db99.mysql.vm.brightbox.net -p -u thequeen thequeen_family_tree > /home/rails/mybackup.sql
</code>