We are running Percona MySQL 5.6
Create an Account
Creating a MySQL user account is an easy task with dbtool. It is available for use when you log in to ssh.hcoop.net.
Run the following command:
dbtool mysql adduser
You will be asked for a password. The user created will be the same as your log-in username.
Create a Database
dbtool is also used to create new MySQL databases. Replace DATABASE with your desired database name:
dbtool mysql createdb DATABASE
The database created will be USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).
Delete a Database
Delete a database with the following:
dbtool mysql dropdb DATABASE
Although you could drop a database using the mysql shell (using "DROP DATABASE..."), you should avoid doing so as it will leave stale grants behind and dbtool ensures everything is cleaned up appropriately.
Changing Your Password
If you need to change your MySQL password for security purposes or you have forgotten it, you may do so with this command:
dbtool mysql passwd
You will be prompted to input a new password.
Accessing Databases
For web applications, set the MySQL host/server to mysql.hcoop.net. Default ports apply. Use your HCoop username. Your password will be required. Please safeguard it. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.
In addition, you can easily use the mysql shell to manipulate or analyze your databases:
mysql -p -h mysql.hcoop.net USER_DATABASE
To learn more about the MySQL shell, review the MySQL manual.
There is a web interface available for managing your databases at https://phpmyadmin.hcoop.net/. Use your database password when logging into it.
Transferring Databases
If you have a MySQL database on another system and you wish to put that database onto our systems, you'll have to create the database as stated above and do the following.
On the system with the original database (remember to replace USER with your username and DATABASE with the name of your database):
The "-h" option is for the hostname, which is mysql.hcoop.net in our case.
mysqldump --single-transaction --set-gtid-purged=OFF -p USER_DATABASE -h mysql> mydatabase.sql scp ./mydatabase.sql USER@ssh.hcoop.net:~/
Finally, import the database on the shell server:
mysql -h mysql -p USER_DATABASE < ~/mydatabase.sql
Resetting Permissions
Sometimes we change our policy on which permissions users are granted on their databases. If your database has different permissions set than our current defaults, or you need to regain full database permissions, you can run this command to update the permissions:
dbtool mysql grant DATABASE