How to use MySQL on Hypernode
Table of contents
- 1 Finding your credentials
- 2 What you should know
- 3 Whitelisting your IP address
- 4 How to connect to MySQL
- 5 Creating a MySQL back-up
- 6 Using MySQL
Finding your credentials
Your MySQL credentials are stored in the homedir of application user.
You find them in the file
.my.cnf located in
[client] host = mysqlmaster.<tagname>.hypernode.io user = app password = JlogA1Sws6XMHmAj7QlP9vpfjLprtpE5
What you should know
- There is no predefined database, so you should create your own.
appuser is the local superuser. This means you can (among other things):
- Can create your own databases;
- Create users;
- Define views and triggers.
- If you want to use a GUI to work on your database we recommend using a local GUI (HeidiSQL) instead of an online GUI (PHPMyAdmin).
Whitelisting your IP address
port 3306 is firewalled on all hypernodes to prevent hackers and bruteforces from connecting to your mysql instance. Therefor if you want to externally connect to mysql on the hypernode, you’ll need to add a hitelisting entry first on the Byte Service Panel.
Externe database toegang to add ip addresses to the firewall whitelist.
How to connect to MySQL
Use the command line shell on the production node
Because we’ve provided a
~/.my.cnf, you’re all set to go.
mysql and you’re in.
Use the command line shell from a remote host
Use your credentials to connect like so:
mysql --host=mysqlmaster.<tagname>.hypernode.io --user=app --password=mypassword
Using HeidiSQL/PHPMyAdmin to connect to MySQL
Read the following articles on how to use both HediSQL and PHPMYAdmin for Hypernode:
Using an SSH tunnel to circumvent firewalls
If you are blocked by a firewall, you can tunnel the remote MySQL service to your local computer (Mac or Linux).
Use this command:
ssh -NL 3306:mysqlmaster:3306 firstname.lastname@example.org
Voila, now your Hypernode database is reachable through localhost port 3306!
Creating a MySQL back-up
Use the following command using SSH:
magerun db:dump -n -c gz -s @stripped
This will create a compressed SQL file suitable for importing using either Magerun or
The dump will exclude any logfiles or temporary import tables.
You should consider using Magerun (see above), but if you need to, you can create a mysqldump on the shell. See our documentation at byte.nl (dutch).
You should consider using Magerun (see above), but you could use HeidiSQL to create a database dump.
- Start HeidiSQL.
- Connect to your node.
Export database as SQL.
- In the left pane, choose the Magento database.
- Choose a filename to export to.
- Choose appropriate options, notably
- Press Export.
- Go To domain.hypernode.io/phpmyadmin
- Click on “Databases” and select the database.
- Click on “Export”.
- Click on “Go” and the export/backup will be available.
How to create a new database
To create a new database, we’ll login using the mysql client and drop the database using the commandline:
DATABASE="new_database" mysql -e "CREATE DATABASE IF NOT EXISTS $DATABASE
Voila! If the database was not already present, you just created a new database 🙂
How to remove an old database
Removing (aka drop) a database is as easy as creating a database, but some caution is required:
To prevent incorrect deletion of database that are still in use, ensure yourself:
- The database is not used anymore by checking it’s content.
- The database is not defined in your application configuration anymore.
(IE: Check the
- You created a backup to ensure yourself you are able to restore the database if necessary.
When you are 100% sure it is safe to delete the database, issue the following command:
DATABASE="old_database" mysql -e "DROP DATABASE $DATABASE"
And POOF! Now your database is gone.
How to truncate a database table
To truncate a database table, use the same caution as with dropping a database.
If you truncate a database table, all records are removed but the table structure is saved for further use. This is a hard reset of your database table: It wipes out any record the table contains.
After you ensured yourself it is safe to delete all records of the table, use the following command:
DATABASE="magento" TABLE="core_url_rewrite" mysql "$DATABASE" -e "TRUNCATE TABLE $TABLE"
Changing your MySQL password
- Login to your MySQL server via the following command:
- Enter your MySQL root password. This will get you into the MySQL prompt.
Select the database which holds user accounts, here it’s called mysql;
- Now change the password for a given user account using this command:
update user set password=PASSWORD('newpassword') where user='username';
- Let’s assume here that your username is ‘trial’ and your new password is
‘hypernode’. Your actual command would look like this:
update user set password=PASSWORD('hypernode') where user='trial';
- Now your password is changed in the database, but they haven’t filtered
into memory yet. Change that by typing:
- Your password has been updated. There’s no need to restart the MySQL
demon. Exit the MySQL with