Knowledgebase
How to access MySQL databases via SSH
Accessing a MySQL database through SSH can be achieved with the SSH command mysql. First, of course, you have to log in to your hosting account via SSH. You need to add to the command the name of the particular database that you want to connect to and a couple of options. The syntax of the command looks like this:
mysql -u (username) -p (database name)
You have to replace (username) with an actual username of a user that's assigned to the particular database, and (database name) with the name of the database. For example, if you want to connect to a database called data_mysite, and the username of a user that you have created for that database and that you want to use to connect to it is johnsmith, the command would look like this:
mysql -u johnsmith -p data_mysite
After you execute the command you'll be prompted to type the password of the user. When you do it and you press Enter on your keyboard, you'll enter the MySQL prompt and you'll be connected to the particular database. In this prompt each command begins with mysql>, but you don't have to type that part since it's there by default. Once you're connected to the database you can perform different actions by executing commands. Each command in the MySQL prompt ends with a semi-colon. For example, if you want to see the tables in the database type show tables; and press Enter:
mysql> show tables;
As we mentioned, the mysql> part is already there, so you don't have to type that. To exit the MySQL prompt just type exit and press Enter.
You can also connect as the master user, so that you can view and manipulate all the databases that you have created. The command is almost the same:
mysql -u (master username) -p
Just replace (master username) with the actual master username. The master user is created when you purchase a hosting account. You can view it and also set the password from the Databases section of the Pixie control panel; there is a link Master Username in the black panel on the right. After you execute the above mentioned command you'll be asked to type the password of the master user, and when you do it you'll be connected to the MySQL prompt where you can perform various tasks. For example, to view all the databases just type show databases; and press Enter:
mysql> show databases;
When it comes to creating and deleting databases, and adding users to databases, the right and recommended way to do it is from the Databases section of the Pixie control panel.