runtime/faq/access mysql
From JumpBox Documentation Wiki
How can I access MySQL on my JumpBox?
Here is a video tutorial that shows how to access MySQL.
There are two possibilities when a user asks us how to access MySQL on their JumpBox. If you want to be able to access MySQL via SSH using the command line, you need local access to MySQL. If you want complete access to the MySQL databases from remote computers (as when using business intelligence or statistical software) you need remote access.
Local access
You'll need to enable SSH access if you haven't already. First you will need to look up the root MySQL users password, to do so run the following command (you'll be prompted for your admin password):
sudo grep root /jumpbox/lib/appdata.yml
You'll see a line such as:
root: pa$$word
Where pa$$word is the password. Then run the following command, and use this password when prompted:
mysql -u root -p #or mysql -u root -ppa$$word
Remote access
For security reasons, the MySQL server installed on the JumpBox is not, by default, accessible from remote machines. These instructions show you how to change this yourself. Please understand that this may be a bad idea if your JumpBox is not on a secure network, so proceed with caution. You'll need to have successfully obtained local access to MySQL on the JumpBox in order to change the configuration.
SSH into your JumpBox and run the following command to edit the MySQL configuration file:
sudo nano /etc/mysql/my.cnf
Find the "bind-address" line and replace 127.0.0.1 with 0.0.0.0
#bind-address = 127.0.0.1 bind-address = 0.0.0.0
Now restart MySQL,
sudo /etc/init.d/mysql restart
Next, connect to MySQL locally as described in the Local access section above. Once you have logged in to MySQL (using the mysql -u root ... command, type out a command similar to the following:
GRANT ALL PRIVILEGES ON *.* TO 'remote_root'@'%' IDENTIFIED BY 'desired_password';
This will enable a user called 'remote_root' on any remote host (that is what the @'%' means) who uses the password 'desired_password' to do anything (except GRANT further privileges) to any database. For details see the MySQL 5.0 documentation on the GRANT command. You can use any password of your choice in place of 'desired_password' and any alphanumeric name (except 'root') in place of remote_root.
Now you can access MySQL remotely, either using a GUI admin tool or a command line tool like mysql:
mysql -h IP -u remote_root -p
Where IP is the IP address of the JumpBox. You will be prompted for a password; give the password you just chose.
Interacting with MySQL
With MySQL configured to listen on the network interface there are now at least three ways to interact with the MySQL server on the JumpBox.
- Command Line Tools - This way is always available to those with registered JumpBoxes and shell access to the JumpBox, using the MySQL command line tools.
- Web Admin Tool - It is possible to install the web based administration tool, phpmyadmin, on your JumpBox, instructions are shown in the Installing PHPMyAdmin FAQ.
- GUI Tools - There are a number of GUI tools for administering MySQL. They will typically require that you have made MySQL accessible remotely as shown above. MySQL has GUI tools available for free download and SQL Pro is a great open source client for Mac.
