MySQL Commands

Summary: in this tutorial, you will learn how to use the MySQL commands of the mysql client tool.

Connect to a MySQL server

To connect to a MySQL server, you need to provide the following information:

• Server host: can be an IP address or URL, default to localhost.
• Port: the port of the MySQL server, default to 3306.
• Username: the user account to connect to the MySQL server.
• Password: the password of the user that you want to connect.

Optionally, you can specify a specific database name to which you want to connect.

If you don’t want to connect to the MySQL server without providing a username and password, you can set up one or more login paths, which is very convenient & secure.

The following illustrates various mysql commands that connect to a local/remote MySQL server:

Connect to a Local MySQL server

mysql -u username -pCode language: Shell Session (shell)

Replace username with your MySQL username. You will be prompted to enter your password.

Connect to Local MySQL server with database

mysql -u username -p db_nameCode language: Shell Session (shell)

Replace username with your MySQL username and db_name with the specific database you want to connect to. You will be prompted to enter your password.

Connect to Remote MySQL Server

mysql -h remote_host -u username -p

Replace remote_host with the IP address or hostname of the remote MySQL server, and username with your MySQL username. You will be prompted to enter your password.

Connect to Remote MySQL Server with Database

mysql -h remote_host -u username -p db_name

Replace remote_host with the IP address or hostname of the remote MySQL server, username with your MySQL username, and db_name with the specific database you want to connect to. You will be prompted to enter your password.

Specify MySQL server port

mysql -h remote_host -P port -u username -p

Replace remote_host with the IP address or hostname of the remote MySQL server, port with the port number (default is 3306), and username with your MySQL username. You will be prompted to enter your password.

Connect to a MySQL server using a login path

mysql --login-path=mypath

Replace the mypath with your login path. If you omit the --login-path, mysql will read the default login path:

mysql

Connect to MySQL Server with SSL

mysql -h remote_host -u username -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

Replace remote_host with the IP address or hostname of the remote MySQL server, username with your MySQL username, and specify the paths to your SSL certificates.

Exit MySQL client

To exit the mysql client, you can use one of the following mysql commands:

\q

Or

quit

Or

exitCode language: PHP (php)

Alternatively, you can use the shortcut key Ctrl+D on Unix-like systems or Ctrl+Z on Windows.

Format query results

By default, MySQL displays query results in a horizontal format:

SELECT 
  firstName, 
  lastName 
FROM 
  employees 
ORDER BY firstName
LIMIT
  2;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Andy      | Fixter   |
| Anthony   | Bow      |
+-----------+----------+
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

If the number of columns is high, the output will not be readable. To fix it, you can display the query results in a vertical format using the \G instead of the semicolon (;):

SELECT 
  firstName, 
  lastName 
FROM 
  employees 
ORDER BY firstName
LIMIT
  2\GCode language: SQL (Structured Query Language) (sql)

Output:

*************************** 1. row ***************************
firstName: Andy
 lastName: Fixter
*************************** 2. row ***************************
firstName: Anthony
 lastName: Bow
2 rows in set (0.00 sec)Code language: JavaScript (javascript)

Execute a command and exit

If you want to execute a single SQL statement and exit, you can use the -e option:

mysql -u username -p -e "SELECT * FROM table_name;" db_nameCode language: JavaScript (javascript)

Replace username, SQL statement, db_name with your actual MySQL username, SQL statement, and database name. You will be prompted to enter your password.

For example, the following command executes a query that retrieves firstName and lastName from the employees table in the sample database classicmodels.

 mysql -u root -p -e "SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2" classicmodels;Code language: JavaScript (javascript)

Output:

+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Andy      | Fixter   |
| Anthony   | Bow      |
+-----------+----------+

Execute queries from a file

The following command executes queries from a file:

mysql -u username -p db_name < script.sqlCode language: CSS (css)

Replace username, db_name, and script.sql with your actual MySQL username, database, and the path to your SQL script file, respectively.

After running the command, you will be prompted to enter your password.

For example, the following command executes SQL statements from a query.sql file:

mysql -u root -p classicmodels < query.sqlCode language: CSS (css)

Output:

firstName       lastName
Andy    Fixter
Anthony Bow

The contents of query.sql file:

select firstName, lastName
from employees
order by firstName
limit 2;Code language: SQL (Structured Query Language) (sql)

You can also use the result of another command as an input for mysql using the | operator:

cat query.sql | mysql -u root -p classicmodels

Note that this command works on Unix-like systems such as macOS and Ubuntu.

Write a query result to a file

To write a query to a file, you use the > operator:

mysql -u username -p db_name -e "select * from tblName" > path/to/fileCode language: JavaScript (javascript)

Replace username, db_name, query, and path to the output file with your actual MySQL username, database, query, and the path to your output file respectively.

For example, the following command selects all rows from the employees table in the classicmodels database and writes the data to the employee.txt file:

mysql -u root -p classicmodels -e "select * from employees" > employees.txtCode language: JavaScript (javascript)