How to Access a MYSQL Database from Linux Command Prompt

In this simple MYSQL tutorial, I will demonstrate how to access your MYSQL database from the Linux command line. You would generally only do this if you do not have PHPMyadmin installed. PHPMyAdmin is the MySQL database interaction tool reccomended by most web development professionals. For some reason, every once in a while, I’ll come across a client that doesn’t use PHPMyAdmin. Here is what I do when I have no choice but to access their MySQL database via Linux command promt.

 

  1. Log into SSH – first you’ll need to log into the server’s shell. To do this you’ll often need a root user password or another username and password with appropriate privileges to access MYSQL. I use PUTTY to login to the Linux command prompt remotely. Google PUTTY Download and you can easily find it and download Putty for yourself. You’ll need the server’s IP, user name and password to log in with Putty.
  2. Get a MySQL command prompt – get the MySQL command prompt with the command: mysql from the command prompt. If you get an “access denied” error then you’ll need to change to a different user that has MySQL database privileges. Use this command to access MYSQL with a MYSQL user name. In my case, I had a root user for the server and another user named root for MYSQL. The only way to let the server know that I intended the root user for MYSQL is to log in using a command like this:  mysql -u root -p With that command it will ask you for the MySQL root password. Enter it and you will be logged in.
  3. Switch to your database – you can use the “use” command like this: use databasename to access the database you need. Replace “databasename” with the name of the database you wish to access.

That’s how you access a database without PHPMyAdmin. It’s rustic and a little more difficult than using a nice user interface, but some people swear on only using the command line. That’s a little too “old school” for me however. I do know how to do it in cases where there is no option such as a client who doesn’t want me to install PHPMyAdmin for whatever reason. If you need to know more MYSQL command line commands to interact with your database, read on. I have pasted some basic commands below:

Creating a database:

 CREATE DATABASE database name;

Delete a MySQL database:

 DROP DATABASE database name

Show all databases:

SHOW DATABASES;

View all tables in a database:

 SHOW tables;

Create a MySQL table:

CREATE TABLE table_name(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
first VARCHAR(20),
last VARCHAR(30),
email VARCHAR(99), 
signup_date DATE);

View table structure:

 DESCRIBE table_name;

MySQL insert row:

INSERT INTO `table_name` (`id`,`first`,`last`,`email`,`signup_date`) VALUES (NULL, "John", "Davies","contact@email.com", '2012-04-11');

MySQL select:

mysql> SELECT * FROM table_name;

Update table:

UPDATE `table_Name` 
SET 
`email` = 'contact@email.com' 
WHERE `table_name`.`first` ='Sandy';

How to Back Up and Restore MySQL Databases

I read on one post online that all you had to do was enter something like “” at your command prompt and it would back up “table_name” to “table_name.sql”, however, this was confusing because I assumed, when I was first learning, that I was supposed to enter the command from the MySQL command prompt. It took a little trial and error work before I discovered that you have to back up and restore databases outside of the MySQL command prompt and at the regular Linux command prompt instead. So exit MySQL by using either “quit;” or “exit;” without the quotes of course. Then you’ll be back at a regular Linux command line prompt. Follow the following instructions to create backups and restore them:

To back up a database, create a dump and send it to a file:

mysqldump database_name -u root -pYOUR-PASSWORD > database_name.sql

Restore a MySQL backup:

mysql database_name -u root -pYOUR-PASSWORD < database_name.sql

Summary

Now, if you read and did the above exercises, you should be well on your way to a decent understanding of the MYSQL database Linux Command line tool. If you need to learn more, Google is your friend.

Leave a Reply

Your email address will not be published. Required fields are marked *