MySQL usage

You can use the remote shell host to interact with your application databases. Please note that MySQL does not run locally but on dedicated infrastructure. From the shell host, you can log in, dump database content into a file and restore a database from a dump file. You can find the user credentials and database hostname in the configuration snippets freistilbox provides for each individual application (directory ~/site/current/config/).

In the following examples, we use placeholders for the username, hostname and database name. Note that we use always the parameter -p without an argument for security reasons. That way, you will be prompted for the password interactively instead of the password getting exposed publicly in the system process list.

Open the database CLI 

mysql -u <username> -p -h <hostname> DB-ID

Dump a database to a file 

mysqldump -u <username> -p -h <hostname> DB-ID --single-transaction > file.sql

Restore a database from a file 

mysql -u <username> -p -h <hostname> DB-ID < file.sql

Using drush to dump and copy your database

You can also use drush to dump and copy the database, you don’t need to look out for the credentials then as drush does this automatically for you.
The following commands are to be run from the sites users home directory.

Dump your database

This will dump the database to the current directory. If you leave the $(pwd) out, the command will fail because drush uses current/docroot as current directory and you don’t have write permission there.

drush -r current/docroot sql-dump --result-file=$(pwd)/file.sql

Restore your database from a file 

gunzip < file.sql.gz | drush -r current/docroot sql-cli

or, if the file is not zipped:

cat file.sql | drush -r current/docroot sql-cli

Copy a database from one site to another

There is currently no automated mechanism to do this but you can simply dump the contents of the source database and restore them to the target database, as explained above.

Access via PhpMyAdmin

If you’d like to access your database from your web browser, freistilbox offers a PhpMyAdmin user interface on pma.freistilbox.net.

Access from your local workstation

In order to access your database from an application running on your workstation, you’ll have to set up an SSH tunnel to the database via your freistilbox cluster’s remote shell host:

ssh -L 3307:db12.freistilbox.net:3306 \
s1234@c42s.freistilbox.net

where

  • 3307 is the local port,
  • db12.freistilbox.net is the hostname of the database cluster,
  • 3306 is the default MySQL listening port,
  • s1234 is your website user account, and
  • c42s.freistilbox.net is the hostname of the remote shell host.

With the encrypted tunnel established in this way, you’ll be able to reach your database securely on localhost, port 3307.

With advanced database management applications such as Sequel Pro and MySQL Workbench, you don’t have to set up the tunnel manually. Since they have this functionality built in already, simply provide them with the required details.