How to Connect to MySQL Database via Command Line

In this post, I want to share with you step by step MySQL Connect to Database Command Line guide that you can follow to connect to the MySQL database on the localhost or remote server using the command line.

Before we look at the best way for MySQL connection to the database via the command line, I want to begin by outlining the other options you can use to connect to MySQL database.

To connect to MySQL database from the command line can be a useful skill when you are managing databases on a cloud server such as DigitalOcean.

On DigitalOcean you need to use the DigitalOcean SSH console to log into the MySQL databases.  This post can be a good place to start learning how to connect to database command line tricks.

MySQL Database Connection Tools

First you need to understand that MySQL is installed on a localhost server as well as remote server and can be accessed using the tools such as PhpMyAdmin or other MySQL database management tools that include the following:

• PhpMyAdmin
• SQLyog
• HeidiSQL
• dB Forge Studio for MySQL
• MySQL Workbench
• Toad Edge for MySQL
• Navicat for MySQL
• Sequel Pro

If you simply need to connect to MySQL on your shared hosting environment, you can use the PhpMyAdmin that is installed on most web host to access and manage the MySQL databases.

Some hosting environments also come with MySQL database management wizard for creating and managing databases.

MySQL Connect to Database Command Line

To connect to MySQL database via command line you need to follow the following steps in a quick summary:

1. Open the terminal or command line on either Windows or Mac
2. Ensure you navigate to the root folder or preferably use a terminal like bash that will open in the right folder.
3. For Windows, you need to ensure the environmental variables for MySQL are set appropriately to avoid getting the error that ‘MySQL is not recognized as an internal or external command’. Before you begin your connection to the database if you are on localhost you need to ensure the server is running as shown on the image below:

4. When the terminal is open everything is set up correctly, you should now type the command to connect to MySQL via command prompt as follows: mysql -u username –p change the username and the password to match your login details. When you run this command on the terminal it will prompt you for the password of the database. In case of the localhost environment by default, the username is usually root and the password is empty:
5. When you are connected you should see the message shown on the image above that indicates you have been connected to the MySQL database via command line.
6. You can go ahead and execute several command line commands to run changes on the MySQL database. For example, you can begin with the show databases command that will show you all the databases on the server as shown on the image below:My
7. The following are other common MySQL command line:

Access monitor: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database] (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES (‘[value]’, ‘[value]’);

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE ‘%[value]%’;

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE ‘[value]%’;

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE ‘[val_ue]’;

Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = ‘[updated-value]’ WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Use –lock-tables=false option for locked tables (more info here).

Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

  You can see more commands from the source of these MySQL Commands Cheatsheet

Conclusion

In this post, I have shared with you the way to connect to the MySQL database from the command line and have shown you the step by step. The take from this post should be the command you use to connect to MySQL database from command line is MySQL -u username –p.  You should always remember the username and the password of your database for this command to work.

Finally, if you are on a localhost environment do not forget the username is mostly ‘root’ and the username is empty.