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.
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).
Your database names must be prefixed with your MySQL username. So, if your username is
thequeen then you could create databases named
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 firstname.lastname@example.org -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.
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
db01.mysql.vm.brightboxwhich was historically known as
sqlreadwrite.brightbox.net. That name will continue to work indefinitely but is deprecated.