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.net1) 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:

production:
  adapter: mysql
  database: thequeen_family_tree
  username: thequeen
  password: myroyalpassword
  host: db99.mysql.vm.brightbox.net

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:

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;

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):

mysql -h db99.mysql.vm.brightbox.net -p -u thequeen thequeen_palace_manager < mybackup.sql

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:

ssh rails@username-001.vm.brightbox.net -L 3306:db99.mysql.vm.brightbox.net:3306

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:

SET PASSWORD FOR 'customername'@'10.1.%' = PASSWORD('MyNewPassword');

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:

mysqldump -h db99.mysql.vm.brightbox.net -p -u thequeen thequeen_family_tree > /home/rails/mybackup.sql
1) 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.
docs/mysqlcluster.txt · Last modified: 2010/07/01 11:38 by george