Managing databases in Idealstack

When editing or creating a stack in Idealstack, you can choose to create a database server using AWS's RDS system.

Simply choose the database engine (Mysql or Mariadb are supported - support for more database engines is coming soon, so let us know if you need something else), the instance type, username/password and allocated storage:

Connecting to databases and creating databases

When you create sites, an alias hostname ‘database’ will be available for the database server, so you don’t need to know the long DNS name that AWS assigns. We recommend that you create a new database user/password for each site, rather than just using the master one for every site

Managing databases using the Idealstack database GUI

To access the database,

  1. Go to the site you are interested in in idealstack and click the Connect  tab
  2. In the section Connect to the database click Database Manager
  3. You'll be taken to the database manager 
  4. In the database manager you can create databases, manage users under PRIVILEGES, change table schemas, import/export or run queries and view/change data

 

Connecting to the database server from the SSH commandline

 Firstly connect to SSH

The use the mysql commandline client to connect to the database.  In the following commandline: replace the username with the master username you used when creating the stack. Enter the password when prompted

mysql -h database -u <username> -p

Now you should be connected the mysql command prompt. You can run any mysql commands. For instance, to create a database and a username/password for the site use these commands:

create database db_name;

grant all on db_name.* to 'db_user'@'%' identified by 'db_password';

Copy databases from your local machine

You can do this using PHPMyAdmin, but it's often a lot quicker and faster if you can automate your deployment using the commandline. 

A commandline snippet that is quite useful for this:

ssh sshuser@11.22.33.44.55 -p 2223 mysqladmin create -u remotedbuser --password=remotedbpassword remotedatabase
mysqldump -u localuser --password=localpassword localdatabase | ssh  sshuser@11.22.33.44.55  -p 2223 mysql -u remotedbuser --password=remotedbpassword remotedatabase

 

Connecting to the database using a GUI local client

To connect to the database server using a local client, you need to tunel the connection over SSH.  Most database clients support this natively.  Depending on your platform there's a number of good options:

  • Cross Platform
    • PHPStorm (we use this here at Idealstack and can't recomend it enough)
    • Mysql Workbench (Free, open source)
  • Windows
    • HeidiSQL (Free, open source)
    • SQLYog (commercial)
    • Navicat (commecial)
  • MacOS
    • SQLPro
    • Querious
    •  

We'll demonstrate here with Mysql Workbench which is a free download for almost every platform, but the process is generally similar for any other client:

  1. Create a new connection and select SSH as the connection method:
  2. For the SSH hostname, port and username use the SSH connection parameters for your site.  However you need to use the SFTP port (2222) rather than the SSH port (2223) 
  3. Select your SSH private key file
  4. For the MYSQL hostname use 'database'.  The port should be the default, 3306.  Use the master username and password you created when creating the stack

 

Installing PHPMyadmin to connect to databases

You might  prefer to use PhpMyAdmin over the in-built database management tool (Adminer) - see install phpmyadmin for instructions on how to install PHPMyAdmin in a site on Idealstack.