MySQL by command line – Wingdings Translator Online

How to access MySQL by command line, or terminal, to consult the data of the database or its administration through the console.

It is very normal that we use MySQL through PHP pages and to manage the database we use a program like , but sometimes we have no choice but to access the database through the command line, for example when we are on a remote server that we access by terminal, or when we do not have another graphical interface tool installed.

MySQL has a client program, which is called with the same name as the database (mysql), which is used to manage the database by command line. This program is available in any MySQL installation and we will have to use it to connect by command line.

Locate the MySQL client on Windows

On a Windows computer that program is located in a directory such as:

– C:\Program Files\MySQL\MySQL Server 4.1\bin – C:\xampp\mysql\

The directory can vary, for example, it can be located at the root of the C: drive, or in any other place where we may have installed MySQL. It also depends on what program you used to install MySQL and what version you have. A Google search will surely help you find the correct folder for your case.

To access the MySQL console in Windows we will have to be located inside that directory, or place that folder in the PATH configuration.

Locate the MySQL client on Linux

On Linux, of course, MySQL can also be accessed via the command line. Possibly from any directory we can access the MySQL console, without having to go to the directory where it is installed, since once the database engine is installed, it provides us with the “mysql” command, regardless of the folder we are inside from our terminal.

Locate the MySQL client on Mac

On Mac it will depend on how we have installed MySQL. The command is not always available in our terminal even if we have the database engine installed.

Command to connect to MySQL server

Once we have located the MySQL client we can connect to the MySQL management system by command line. From the console we invoke MySQL. To do this, we simply have to write the “mysql” command and indicate some connection options.

%mysql

Note: With the “%” we express the beginning of the command line. From this point on we are going to skip that start of the prompt, because the prompt that we have in our Linux or MsDOS console, which can be something like c:\mysql\bin>. The character “%”, therefore, we do not have to write it.

With this statement one connects to the database with the default parameters. That is, to the local server, with username and password equal to empty strings.

The most normal thing is that we have to indicate some other data to connect with the database, such as the user, the password or the address of the server with which we want to connect. The syntax would be the following:

mysql -h server_name -u username -p

If we want to connect to the database locally and with the root username, we would have to write:

mysql -h localhost -u root -p

The first thing that will ask us is the password for the root user. Once the key is entered, we will already be inside the MySQL command line. This will change the prompt to something like this:

mysql>

We could have entered the password directly on the command line to log in with MySQL, but this is discouraged for security reasons. Anyway, the syntax would have been:

mysql -h localhost -u root -pmy_key

We notice that between -h and the host name there is a white space, just like between -u and the username. However, between -p and the key we must not put any spaces. However, unless you are local, using the key in the connect command itself is not recommended because it is insecure.

Inside the MySQL console

Once inside, we will have at our disposal all the MySQL statements for working with the database and the SQL language.

The most normal thing is that you first have to connect to a specific database, among all the ones you can have created on your MySQL server. This is done with the use command, followed by the name of the database you want to connect to.

mysql> use mydatabase;

This would connect us to the database called “mydatabase”.

Note: Note that all the statements within the MySQL command line end in “;”. If we do not place the semicolon, the most likely thing is that the command will not be executed and the prompt will come out again so that we can continue entering the command. If what we wanted was to execute the sentence that we had written before, simply by entering the “;” It will be enough. That is, we must not write the entire statement again, only the “;” and press “enter” again.

If we want to see a list of the databases hosted on our server, we can write the show databases command. So:

mysql>show databases;

With this it would show us a list of the databases of our server. Something like this:

mysql> show databases -> ; 5 rows in set (0.02 sec)

If we want to create a database, we can do it with the “create database” command followed by the name of the new database.

mysql> create database mytest;

That will create a database called “mytest”. As we mentioned, if we want to use that database later, we would write:

Other SQL Statements

From the MySQL console we can indicate by command line all kinds of statements in SQL language, such as selections, insertions, updates, creation of tables, etc. The mechanism is what can be deduced. We simply put the statement to be executed followed by the semicolon. Let’s see a series of statements followed and the result of executing them:

mysql> create table test (test_id int); Query OK, 0 rows affected (0.08 sec) mysql> insert into test (test_id) values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into test (test_id) values (2); Query OK, 1 row affected (0.00 sec) mysql> insert into test (id_test) values (3); Query OK, 1 row affected (0.00 sec) mysql> select * from test; 3 rows in set (0.00 sec)

In short, we can execute all the sentences that have been learned in the

To exit the MySQL command line

Once we have finished working with MySQL, if we want to close the connection to the server, we simply type “quit” from the MySQL prompt:

mysql> quit