home > Excel > Manual installation of MySQL on Windows. MySQL Administration Basics Using the Command Line Stopping the mysql server from the command line


Manual installation of MySQL on Windows. MySQL Administration Basics Using the Command Line Stopping the mysql server from the command line




For Win 32, select the distribution: Windows (x86, 32-bit), Zip-Archive.

For Win 64: Windows (x86, 64-bit), Zip-Archive.

After clicking on the Download button you will see a registration form, you can skip it by clicking on the link below - No thanks, just take me to the downloads.

2. Create an installation folder. For example . And unpack the contents of the archive into it so that the folders bin, data, docs and others were in the root of the installation folder.

3. Let's move on to creating a configuration file (my.ini). We take one of the standard files as a basis:

  • my-small.ini – suitable for those who use MySQL from time to time and do not want the server to take up a lot of resources.
  • my-medium.ini is also a low-resource configuration, but is suitable for those who use MySQL on a regular basis (a good choice for a home web server).
  • my-large.ini and my-huge.ini – for systems used as a dedicated MySQL server. However, my-large.ini can be used for a home server if you need additional MySQL performance (512 MB of RAM is not so much in these days).
  • my-innodb-heavy-4G – for dedicated servers with at least 4 GB of RAM and using only InnoDB tables.

In this example, I chose my-medium.ini as the basis. Rename it to my.ini and place it in the root of the installation folder ( C:\Program Files\MySQL\MySQL Server 5.5).

Open for editing (in regular Notepad) and immediately after the line add two parameters:

Basedir=C:/Program Files/MySQL/MySQL Server 5.5 datadir=C:/Program Files/MySQL/MySQL Server 5.5/data

Note that paths use forward slashes (/) rather than backslashes (\).

basedir is the path to the installation folder.

datadir – path to the data folder (where all databases are stored). Sometimes it makes sense to put data on a separate disk to improve performance or ensure reliability.

Just in case, here is a fragment of the configuration file after making the changes:

# The MySQL server basedir=C:/Program Files/MySQL/MySQL Server 5.5 datadir=C:/Program Files/MySQL/MySQL Server 5.5/data port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K InnoDB_sort_buffer_size = 8M

4. The next step is to add the path C:\Program Files\MySQL\MySQL Server 5.5\bin to the PATH environment variable to make it easier to launch MySQL from the command line. To do this, open System Properties -> Additional system settings -> on the Advanced tab, at the very bottom, the “Environment Variables...” button. In the window that opens, scroll through the system variables until we find Path. Select this line and click “Change...”. At the end of the line we add our path, preserving the existing parameters:

Note that paths are separated by semicolons. Make sure there is a semicolon at the beginning and end of your path.

5. Before performing a test run, open the incoming TCP port 3306 in the Windows Firewall:


If you have an additional firewall installed on your system, you must also open TCP port 3306 for incoming connections.

6. Now we carry out a test run. In the command line launched as administrator (this is required if UAC is enabled on Seven or Vista), run:

Mysqld --console

Several lines will be displayed on the screen. If the launch was successful, the last line will look something like this:

Version: "5.5.9-log" socket: "" port: 3306 MySQL Community Server (GPL)

Leave this window open, and open another command line window, in which we enter:

Mysql -u root

If the connection was successful, you will see a command prompt: mysql>

Let's see what databases we have:

Show databases;

The semicolon at the end of the SQL query is MANDATORY!

Team response:


Now we are finally convinced that the server is working.

7. Let's move on to the next step - you need to set the MySQL administrator password (root user). There is no password set by default, and this needs to be fixed. In the same MySQL window, enter the following commands:

Use mysql UPDATE user SET password = PASSWORD("your_password") WHERE user = "root";

Let's check the result:

SELECT user, host, password FROM user;

As you can see in the screenshot, the password column is filled, which means the password has been set.

Before exiting, run the command:

FLUSH PRIVILEGES;

Now, when connecting, you must enter a password. To do this, use the -p switch:

Mysql -u root -p

To shutdown MySQL run:

Mysqladmin -u root -p shutdown

8. The last step is to launch MySQL as a Windows system service.

Make sure there are no active MySQL processes on the system.

In the command line running as administrator, run:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --install

The path to mysqld must be specified in full (even if it is listed in PATH)!

To start the service, open “Computer Management” and manually start the MySQL service:


Or easier via the command line:

Net start mysql

To remove a service, first stop it:

Net stop mysql

and execute:

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --remove

My friends often ask me certain questions about administration, and I thought, why not, instead of explaining the same thing 100 times, write such article-notes on the site? In addition, this is an excellent opportunity, if necessary, to use such a note yourself, because you yourself know that sometimes it is not possible to keep everything in your head and one way or another sometimes you have to turn to a reference book, so let one of the sections of this site become such a book. Today we will talk about how to work with the MySQL DBMS from the command line, yes, someone will say - “Which one? After all, there is phpMyAdmin,” I answer - why waste precious server resources installing all sorts of crap like WebMin, phpMyAdmin, etc., if the same thing can be easily done with reliable, proven tools. Setting up from a terminal via an SSH tunnel is our choice!

Well, let's get started. We connect to the server via SSH, for example through the well-known PuTTY (A Free Telnet/SSH Client). Now let's move on to the description of the commands.

Login to the MySQL server using the mysql program:

mysql -u root -h localhost -p

The -u option specifies the user to log on to. The -h option specifies the host. This is usually localhost if you are setting up a non-remote server. Finally, -p tells the mysql client program that a password will be entered to access the database. Notice the mysql> prompt. This is where you will enter all your commands. Now that we are in the mysql environment as the root user, we can begin setting up the database.

We logged in and the mysql prompt appeared on the screen. First, let's take a look at the list of existing databases. To do this, enter the SHOW DATABASES command.

The response makes it clear that the command was executed without any errors. In this case, one line has changed. This refers to the main mysql database which contains a list of all databases. But you don't have to worry too much about minor details. The last number indicates the query execution time. We can verify that the database has been created by running the SHOW DATABASES command again.

To start creating tables in the new test database, we will need to set it as current. To do this, use the USE command. The parameter of this command specifies the name of the database that needs to be made current. You can also install the current database on the command line by specifying its name after the -D parameter. Let's go ahead and switch to the test database.
Switching database:

USE test;

Privileges in MySQL

Granting privileges by a team GRANT.
Privileges determine a user's ability to access databases, tables... almost anything. Currently, only the MySQL root superuser can access the test database, according to these permissions. Let's create another user, for example, admin, who will access the test database and work with information from it. But before we begin, let's take a look at the somewhat simplified format of the GRANT command.

Note: The GRANT command is considered a way to create a user. Later versions of MySQL, however, also contain the CREATE_USER function, although GRANT is still preferred.
Now we need privileges that can be assigned. Using all of the above, you can set the following privileges:

Note Note: If you are using MySQL to communicate with a web application, the CREATE, SELECT, INSERT privileges explained here, as well as the DELETE and UPDATE privileges (described in the MySQL manual, section GRANT and REVOKE Syntax) are the only ones that will probably be required. Many people make the mistake of giving away all the privileges when it is not really necessary. Check with the application developers to see if such permissions will actually cause operational problems.

Creating an admin user:

So, users are created; Now let's test them. First, let's exit mysql by writing quit at the prompt:

The options are explained in the GRANT command section. Now we will deny the user any type of access. Let's say we find out that the admin account is causing security problems. We decide to revoke all rights. We log in as root and do the necessary.
Revoking permissions for the admin user:

REVOKE ALL ON test.* FROM "admin" @"localhost" ;

Note: In this case, user access is simple, so revoking rights to one database is not a problem. But generally, you'll probably want to use *.* instead of test.* to revoke the user's access to all other databases at the same time.

Now let's take a look at how to completely delete an account using the DELETE command and look at the MySQL user table.

Deleting accounts with a command DELETE.
A MySQL user table is a list of users and their information. Make sure you are logged in as root. Use the main MySQL database.

Using the main mysql database:

The user table is what you need. It has 30 different fields and is difficult to read. To make it easier to read, we use the third form of the SELECT command. The fields you are looking for are Host and User.
Finding the admin user in the user table:

Now that we have the information, we can get rid of the admin user. This is done with the DELETE command; here is its syntax:

You may have noticed that the DELETE format is somewhat similar to the SELECT format. Let's specify the User field and the admin value. This will remove the entry from the user table where the user is admin, deleting our account. Let's do this:
Removing the admin user:

The FLUSH PRIVILEGES command is needed to update permissions.

That's all for today. Agree, it's simple! ;)

Good day, colleagues :)

Yes, exactly colleagues, because for a mere mortal, the MySQL command line (or MySQL Shell, as developers also like to call it), as well as working with MySQL in the console, is unlikely to be useful.

In order for this topic to be interesting, you need to be, at a minimum, a novice system administrator who does not use phpMyAdmin and other interfaces in principle.

Personally, I myself prefer to use phpMyAdmin as the main one for working with MySQL, because... I am a visual person by nature. However, sometimes in practice there are situations when knowledge of the MySQL command line and the ability to work with MySQL in the server console are simply necessary.

I currently have three of them:

  1. The first time I encountered working in the MySQL console was when I needed to import a large database dump. It was not loaded entirely through phpMyAdmin, because... fell off somewhere in the middle due to timeout, despite changing the settings for the operation execution time and the size of the downloaded files. In principle, it was possible to select the necessary values, but it seemed to me to be a too long process.
  2. The next time I had to work with MySQL via the command line was to disable the check of foreign keys, when I needed to delete data from a table that was linked to others using keys. I just couldn't find how to do this in phpMyAdmin.
  3. Serious companies use only the console to work with MySQL without any phpMyAdmins. I don’t know why exactly, but knowledgeable people said that it had something to do with security. As a result, everyone had to work with the MySQL command line, including me :)

But, again, working with MySQL in the console and command line is quite suitable for everyday use. All known visual programs for working with databases still work based on MySQL console commands. Therefore, as you like :)

Running the console on different OS

Since I will be demonstrating how to use the MySQL command line in the server console, it would be a good idea to start with it first.

The action is simple, familiar to many, for which knowledge of the “hot keys” is sufficient.

Launching the console on Windows:

  • Win+R to open Windows command prompt with administrator rights;
  • Enter the command cmd
  • Click Enter on keyboard

Launching the console in Linux distributions (terminal): in Ubuntu, which I worked with, the system key combination is enough Ctrl+Alt+T. I can’t say anything about other Linux-based operating systems.

Launching the console on MacOS: I haven’t done this myself, because... I haven’t gotten a Mac yet and I’m unlikely to get one because I don’t need it, but as far as I was able to find out, there are no “hot keys” for calling a terminal in this OS. So if you are a user of Apple products, then launch the console through the OS interface, fortunately there are many manuals on the Internet.

It’s a little embarrassing to even publish such information in case some professional system administrator with extensive experience accidentally stumbles upon this article, because he will think: “The author considers his readers to be programmers, but at the same time he teaches how to launch a console... Some kind of psycho :-).”

Yes, quite logical :) But I’m just taking into account the situation that, along with professional developers, beginners can also get here. That’s why I try to make the information complete and accessible to all categories of users, as I already mentioned.

Basic MySQL Console Commands

So, first of all, we need to access the MySQL command line in the console. To do this, open the server console and, if you have MySQL installed globally as a service, then to “check the connection” we write the following:

Mysql -V

Console command mysql allows us to launch the utility of the same name, which is a MySQL command line.

This will allow us to find out the version of MySQL installed on the computer and make sure that it is installed as a service at all. If this is the case, then you will see something like the following text in the console: .

Yes, I’m not a “crazy coder”, because I’m using Windows :) But that’s not the point. On Unix systems the procedure will be the same.

If suddenly MySQL is not installed globally on your computer or you need to work with several versions of MySQL, in which there are only directories with MySQL files and libraries, then launching MySQL through the console will look like this.

In the console, go to the directory where the MySQL executable file is located (on Windows, at least it’s mysql.exe) with the following command:

Cd C:\OpenServer\modules\database\MySQL-5.7-x64\bin

Naturally, your path to the distribution may be different. As an example, I decided to run one of the versions of MySQL included with OpenServer.

And we launch MySQL, checking its version along the way:

Mysql.exe -V

As a result, a message similar to the first case should have been displayed in the console mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64).

That’s it, we’ve sorted out launching MySQL from the command line through the server console, now we’ll connect directly to the MySQL server.

Connecting to the MySQL server in the console

The MySQL server may not necessarily be on the same machine on which you are trying to access it in the console. Therefore, in order to make connecting to a remote MySQL server via the console possible, the mysql utility has many parameters with which you can specify the necessary settings.

In order to launch the MySQL command line in the console, we just need to write the following in the server console:

However, in this case you will receive the following error: ERROR 1045 (28000): Access denied for user ‘ODBC’@’localhost’ (using password: NO). This is how the error message looks on my Windows. If you use Linux, then instead of ODBC there will be the name of your system user under which you perform actions in the system.

All this is because by default, when connecting to a MySQL server, the console uses the ODBC user on Windows without a password and the system user on Linux with the same password. The default host is localhost, i.e. This user can only connect from the local machine.

Therefore, you have two options: either use another user to log into the MySQL command line, or create a MySQL user with the required account. But for the second method we still can’t do without the first :) Therefore, first we connect to the MySQL server under a standard user root, which is created when installing MySQL on a computer and for which access from localhost is allowed by default:

Mysql -u root -p

When prompted to enter a password, simply press Enter (if you did not enter it, of course, when installing MySQL). You will then be connected to the MySQL server, assigned a connection ID and given access to the MySQL command line.

If you want to connect to a MySQL server that has a specific hostname or IP or want to log in as a different user, then use the following command format:

Mysql -u user_name -p user_password -h MySQL_server_host_or_IP

Instead of Cyrillic characters with underscore, of course, you need to enter your data in Latin characters. By the way, if you wish, you can write this command in a slightly different format:

Mysql --user=user_name --password=user_password --host=host_or_IP_of_MySQL_server

If for some reason you don't want your MySQL connection password to appear in the console (which is correct, actually), you can use the following command:

Mysql -u user_name -h MySQL_server_host_or_IP -p

Since the password is not explicitly specified, you will be prompted to enter it in the next step. Moreover, the characters you enter will not be displayed even in the form of asterisks (wildcards), instead there will be just an empty line.

In addition to the specified connection settings, it is possible to use the following parameters, the values ​​of which will be specified in a manner similar to those listed above:

  1. --port or -P - to specify the port for connecting to the MySQL server;
  2. —protocol — the protocol through which the connection will be made (possible options: TCP for Windows and Linux, SOCKET for Linux, PIPE and MEMORY for Windows);
  3. --socket or -S - this parameter is useful if you want to connect via sockets, therefore, the value of the parameter will be socket;
  4. --pipe or -W - the parameter is needed if you want to use named “pipelines” or “pipes” for connections;
  5. --shared-memory-base-name - this parameter will be useful for MEMORY connections via shared memory on Windows;

It is clear that the list of all parameters for connecting to the MySQL server is not limited to this. In reality there are many more of them.

If for some reason the standard option indicating the host, user and password does not suit you, then to find out the full list of connection parameters the following information will be useful to you - https://dev.mysql.com/doc/refman/5.7/en/ connecting.html

How to create a database in MySQL console

After we have connected to the MySQL server and launched the MySQL command line, it is time to begin the life cycle of our site database, which starts with its creation. To create a MySQL database through the command line, you need to enter the following command:

CREATE DATABASE database_name;

The same action can be done using a special MySQL utility mysqladmin. It runs in isolation from the MySQL command line, i.e. To use it, you will need to exit it or open a new server console.

And then call the following command:

mysqladmin create database_name;

By the way, using the mysqladmin utility you can not only create and delete databases without logging into the MySQL console, but also operate the server configuration, MySQL processes, manage replications, ping servers and do many other interesting things.

How to create a MySQL user on the command line

Not often, but sometimes you need to create a new MySQL user. Moreover, in console mode.

On the MySQL command line this is done as follows:

CREATE USER "user_name"@"host_or_machine_IP" IDENTIFIED BY "user_password";

The user has been created. Parameter host_or_IP_machine means that when creating a user, you need to specify the IP from which he can connect to the server, or the host name (the domain name of the working machine on the network is suitable).

By the way, when specifying the host to connect to the MySQL server, you can use the percent symbol - % , which means that the created user can connect to the MySQL server from any IP address or host.

It should be taken into account that in this case localhost is not included in the list of addresses specified using %, because localhost denotes a connection via a UNIX socket instead of the standard TCP/IP. Those. If the created MySQL user will connect to the server not using sockets, but using a different protocol specified when connecting to the MySQL server in the console, then he will need to create two user accounts:

CREATE USER "username"@"%" IDENTIFIED BY "password"; CREATE USER "username"@"localhost" IDENTIFIED BY "password";

We've figured out how to create a MySQL user in the console. Now let's set the user rights to perform any actions with our newly created database with the following command:

GRANT ALL PRIVILEGES ON database_name.* TO "user_name"@"host_or_machine_IP";

Option ALL, as you understand, just indicates that the user is allowed any actions with a specific database. Complete list of rights that can be granted to users using the command GRANT, can be found here (though the description of the parameters is in English) - https://dev.mysql.com/doc/refman/5.7/en/grant.html#grant-privileges

By the way, you can use several privilege parameters, specifying them when calling the command, separated by commas.

If you want to create a superuser, i.e. has global privileges to perform various actions with all databases on the server, then use the following command call:

GRANT ALL ON *.* TO "user_name"@"host_or_machine_IP";

By the way, it would be a good idea to clear the MySQL server cache after setting privileges. If you change them, then also do not forget to do this by calling the following command:

FLUSH PRIVILEGES;

And if you want to change user rights, first use the following command to reset all rights:

REVOKE ALL PRIVILEGES ON *.* FROM "user_name"@"host_or_machine_IP";

And then install the ones he needs using GRANT, as described earlier.

If in the future you need to change the MySQL user password, then to do this, just run the following commands in MySQL Shell:

SET PASSWORD FOR "user_name"@"host_or_machine_IP" = PASSWORD("new_password"); FLUSH PRIVILEGES;

Resetting the MySQL server privilege cache is needed for the same purpose as when changing rights - without this action, changing the MySQL user password may not be counted, so don’t be lazy to use it :)

By the way, the Internet is full of examples of using the following command to reset a user’s MySQL password:

UPDATE mysql.user SET Password=PASSWORD("password") WHERE User="username";

But this option did not work for me on MySQL 5.7, giving an error ERROR 1054 (42S22): Unknown column ‘Password’ in ‘field list’. The reason turned out to be the absence of the Password field in the mysql.user table.

From which we can assume that this option only works for older versions of MySQL, where this field existed in the user table. Therefore, to be sure, use my first option, unless, of course, you are forced to work with antediluvian software without the ability to update versions :)

This completes the creation of a new MySQL user and review of actions with its rights and passwords. Let's move on.

Selecting a database when working with MySQL via the command line

Now, after creating a user in MySQL Shell and assigning him rights to work with the database, we need to select this very database so that we can operate with the database itself and the data stored in it.

To do this, use the following command in the MySQL console:

USE database_name;

If everything went well, a message will be displayed in the console Database changed, which will signal that we have logged into the MySQL database via the console. By the way, when connecting to a MySQL server, you can initially specify the database with which you will need to work. To do this, you need to enter the following command in the server console:

Mysql --user=user_name --password=user_password --host=MySQL_server_host_or_IP --database=database_name

Or the same thing, only asking for the MySQL user password:

mysql -u user_name -h host_or_IP_MySQL_server_database_name -p

That's all. I think you won’t have any difficulties connecting to the MySQL database via the console now :)

Working with MySQL tables via the MySQL console

So, we created a MySQL database through the console. Now it would be nice to learn how to work with it for the case when the MySQL command line will be the only means of accessing the data stored on the server (as was the case for me, which I talked about at the beginning of the article).

As you know, it consists of tables, inside of which information is already stored in the form of records with several fields. Therefore, following the hierarchy of information placement, first we will learn how to perform typical CRUD operations with tables.

CRUD operations, if someone is not in the know, are operations for creating, reading, updating and deleting data from English. “Create, Read, Update, Delete” (you may need this during interviews).

Let me remind you that to perform actions with tables you must first connect to the MySQL database using the command USE.

So, the first thing on our agenda is the command to create a MySQL table in the database via the command line, which looks like this:

CREATE TABLE table_name (field_name_1 field_type_1, field_name_2 field_type_2(field_size_2), INDEX(field_name_1), ...);

As you understand, there can be as many fields as you like, their types can be different, as well as the presence of indexes and keys are optional.

By the way, if you want to copy a table to another database or simply create a copy in the current one, the following commands will help you:

CREATE TABLE new_table_name LIKE old_table_name; INSERT new_table_name SELECT * FROM old_table_name;

These commands allow you to copy the table structure and its data along with table indexes and triggers. If you just need data and structure (names of fields and their data types), then you can get by with calling one command:

CREATE TABLE new_table_name AS SELECT * FROM old_table_name;

The next operation from the CRUD block is reading. In the case of tables, reading will be displaying their structure. To do this, there are the following four commands:

SHOW FULL COLUMNS FROM table_name; DESCRIBE table_name; EXPLAIN table_name; SHOW CREATE TABLE table_name;

The first displays information about the fields of the database table in tabular form to the MySQL console, indicating the field name, data type, presence of keys, default value, etc. When using a keyword FULL you can get extended information, including privileges for each field for the current user, comments for each of them, and the encoding value.

The second and third commands are simply shortened forms of the first command without the extended information. Why was there a need to create more of them? I can’t even imagine... Is it so that there would be something to ask during job interviews? 🙂

The fourth command, in addition to the name, field types and their default values, allows you to get the values ​​of table keys, table engines (InnoDB, MyISAM), encoding, etc.

Update operation in the case of tables represents a change in their structure, i.e. various actions with MySQL table fields:

ALTER TABLE table_name DROP COLUMN field_name; ALTER TABLE table_name ADD COLUMN field_name VARCHAR(20); ALTER TABLE table_name CHANGE old_field_name new_field_name VARCHAR(50); ALTER TABLE table_name MODIFY field_name VARCHAR(3);

The first command allows you to delete a specific table field, the second allows you to add it, the third allows you to rename a field and simultaneously change the type of data stored in it, and the fourth allows you to change only the data type.

The same can be done with table indexes using largely similar commands:

ALTER TABLE table_name ADD UNIQUE INDEX index_name (field_name_1, ...); ALTER TABLE table_name rename INDEX old index_name TO new index_name; ALTER TABLE table_name DROP INDEX index_name;

The following commands allow you to add, rename and remove indexes from MySQL tables via the command line. To add and remove indexes, by the way, there is another alternative option for using independent commands, rather than doing it through ALTER TABLE. Therefore, if you wish, you can use them:

CREATE UNIQUE INDEX index_name (field_name_1, ...) ON table_name; DROP INDEX index_name ON table_name;

The given commands are equivalent to the first and last from the previous block. Unfortunately, there is no separate command to rename an index. And to change the index type, unfortunately, there is no way at all in MySQL. The only solution is to delete the index and create it again with the desired type.

Well, finally, we have reached the last operation from the CRUD block - deletion. Removing MySQL tables from a database is very easy. Just run the following command in the MySQL console:

DROP TABLE table_name;

Sometimes in practice situations arise that make it impossible to delete a table or change its structure. As a rule, this is due to the use of foreign keys in the database to link tables with each other. I personally encountered this situation more than once, as I spoke about at the beginning of the article.

Therefore, if, when deleting or updating a table structure or its data, MySQL returned you an error with the text Cannot delete or update a parent row: a foreign key constraint fails, then the following information will come in handy for you.

In order to implement our plan, we need to temporarily disable the check for the existence of foreign keys, perform the necessary operation, and then enable the check again, because it is really necessary and allows you to protect against data integrity violations in most cases.

As a matter of fact, MySQL foreign keys are needed for this purpose.

So, to delete data that is interfered with by foreign keys, you need to perform the following steps in the MySQL console:

SET FOREIGN_KEY_CHECKS=0; #required_mysql_command SET FOREIGN_KEY_CHECKS=1;

By the way, if you want to delete a foreign key, the procedure will be the same as when deleting an index:

ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

To find out the name of the MySQL foreign key of a table, use the already familiar MySQL console command SHOW CREATE TABLE.

Working with MySQL table data via the command line

For CRUD tables, we looked at operations in the MySQL console. For a complete picture, this cheat sheet only lacks commands for working with the data itself stored in database tables. I think many people know these commands and have used them in practice, but I’ll still remind you of them again.

CRUD operations for working with MySQL table data will look like this:

INSERT INTO table_name (field1, field2, ...) VALUES (field_1 value, field_2 value, ...); SELECT field1, field2, ... FROM table_name; UPDATE table_name SET field1 = field_1 value, field2 = field_2 value; DELETE FROM table_name WHERE field1 = field_1 value;

The above commands correspond to the operations of creating, reading, updating and deleting data from MySQL database tables. When using SELECT and UPDATE, it is also possible to use a qualifying WHERE clause, which can be used to specify the data selection in the same way as described when using DELETE.

Also, when retrieving data from a database using SELECT, you can use the following option to obtain the values ​​of all table fields:

SELECT * FROM table_name;

Naturally, these operations can use other operators besides WHERE. There are especially many of them when retrieving data using SELECT: here there is UNION for combining the results of several queries, and various types of JOIN. Listing everything is very long and it will be tedious for both me and you to read this.

Therefore, let's agree: if you want to know more about something, just write about it in the comments, and I will try to answer you. Or other knowledgeable members of our community will do it. OK? 😉

So we won’t dwell on this block for now.

If you need to delete all data from a table, you can use the following MySQL command:

TRUNCATE table_name;

Before calling it, as mentioned earlier, you may need to disable foreign key checking in case there are related MySQL tables, which may prevent the required action from being performed.

Another interesting point to note here is that this command does not reset the AUTO_INCREMENT counter, which is known to be used to automatically generate a field value without the need to manually set it.

Fields of this type are most often used to generate values ​​for the main key field id, which is used to establish relationships between data from different tables.

That is, if before deleting table data using TRUNCATE the maximum counter value was 1200, then the first record after this procedure will have an identifier value of 1201. In principle, it’s okay. If you have set a sufficient size for this field, then you will not be in danger of overflowing the values ​​soon.

However, in some cases, when the application code has some kind of binding to the value of a field, this behavior can cause inconvenience.

To avoid this, use this option instead of the command above:

TRUNCATE TABLE someTable RESTART IDENTITY;

This command call option TRUNCATE will allow you to reset the field counter value from AUTO_INCREMENT. Therefore, the field value of the first added record after this deletion will be 1 instead of 1201, as in the example above.

How to delete a MySQL database via command line

The life cycle of working with a database is coming to an end and ends quite logically - by deleting it. In order to perform this operation in the MySQL console, you need to run the following command (in this case, the database to be deleted may not be selected by the command USE):

DELETE DATABASE database_name;

The same action can be done using the MySQL mysqladmin utility, which I already mentioned at the beginning of the article when creating a database:

Mysqladmin drop database_name;

When you call the command, the following message appears in the server console:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ‘database_name’ database

In short, this is a warning that deleting a MySQL database is a very bad idea. Confirmation of the action is also requested. If you agree, write y and press Enter on the keyboard, after which the following message will be displayed on the screen (if everything went well, of course):

Database "database_name" dropped

That's it :)

How to delete a MySQL user in the console

Now the same fate will befall the MySQL user we created to show you how it's done. But first, it would be a good idea to check whether the required user actually exists before deleting it.

As of MySQL 5.7, there is one command for both of these actions:

DROP USER IF EXISTS username;

Earlier versions of MySQL required two separate commands:

GRANT USAGE ON *.* TO "user_name"@"host_or_IP_address"; DROP USER "user_name"@"host_or_IP_address";

Unfortunately, in this case, the message about the execution of the operation in the MySQL console is traditionally uninformative 🙁 Therefore, to find out that the MySQL user user has been deleted, you can use the following command, which displays a list of all users existing on the current MySQL server:

SELECT User FROM mysql.user;

This command option will only display usernames. If you need to see the hosts from which users can connect to the server, and the list of privileges, you can call the command in the following form:

SELECT User, Host, Grant_priv FROM mysql.user;

Also in the mysql.user table there are a lot of other fields that store other types of privileges and other information, a full list of which can be found here - https://mariadb.com/kb/en/library/mysqluser-table/

Don't be confused that this is documentation for the MariaDB DBMS. Technically this is the same as MySQL, because... MariaDB is just its branch or fork from the English “fork” - branch, fork.

Why was it done - again, I have no idea 🙂 Perhaps to proudly declare to everyone that “I have my own DBMS”?.. But, to be honest, I’m not very aware of the exact motives and differences between MySQL and MariaDB. Therefore, if you know something about this, it would be interesting to read about it in the comments.

Exiting MySQL Console

That's all, the life cycle of the database and the user, which began with their creation and ended with their deletion, has come to an end. Therefore, the MySQL command line, which we worked with in the server console using the utility mysql, we don't need it anymore.

All that remains is to get out of it...

It would seem that the action is trivial, but many in a situation where they need to exit the MySQL command line simply close the server console and open it again. You can, of course, act like this, but it takes up extra seconds of working time and makes you irritated every time you need to do it.

The correct behavior in this situation is just to call the command exit in the MySQL command line, after which the service will politely say goodbye to us :)

And that’s all 🙂 So next time you don’t need to create multiple consoles and re-open them every time you need to exit MySQL in the console and access it again to manage the server.

That's all I wanted to tell you today. I hope that my cheat sheet for working with the MySQL command line via the console will be useful not only to me, but also to you.

Speaking about the practical application of the information presented in the article, I would like to remind you that the above constructs can be used not only in the MySQL command line and the server console, but also in the console of phpMyAdmin and other software that provides such an opportunity.

Whether you use it or not is your business. But, as I said at the very beginning of the article, there are situations when using the MySQL console can save you time and stress. Whether to use the command line every day or not is purely a matter of taste and individual preference.

Write in the comments about which commands you use most often. Or perhaps you know some other tricks. In any case, share your opinion with others and keep the conversation going in the comments of other users.

I promise that in the process you will learn a lot of new things, just like I do when writing each of my articles :)

That's all! Good luck and see you again :)

P.S.: if you need a website or need to make changes to an existing one, but there is no time or desire for this, I can offer my services.

More than 5 years of experience professional website development. Work with PHP, OpenCart, WordPress, Laravel, Yii, MySQL, PostgreSQL, JavaScript, React, Angular and other web development technologies.

Below is a list of the most useful and frequently used commands MySQL with examples.

mysql at the beginning of the line means that the command is executed after logging in MySQL.

The # or $ symbol at the beginning of the line means that the command is executed from the command line.

To check the server status MYSQL do:

For FreeBSD:

# service mysql-server status

V CentOS/RHEL:

# service mysqld status

MySQL from the console if the server MySQL is on the same host:

To connect to the server MySQL from the console if the server MySQL is located on the remote host db1.example.com:

$ mysql -u username -p -h db1.example.com

Working with databases and tables - viewing, deleting, editing records. Console

Create a database on MySQL server:

Mysql create database

Show a list of all databases on the server MySQL:

mysql use ;

Display all tables in the database:

Mysql show tables;

View the table format in the database:

mysql describe ;

Delete database:

Mysql drop database ;

Delete a table from the database:

Mysql drop table ;

Show all table contents:

Mysql SELECT * FROM ;

Display columns and column contents in the selected table:

Mysql show columns from ;

Display rows in a specific table containing " whatever ":

Mysql SELECT * FROM WHERE = "whatever";

Display all records in a specific table containing "Bob" and the phone number "3444444:

Mysql SELECT * FROM WHERE name = "Bob" AND phone_number = "3444444";

Show all entries, NOT containing the name "Bob" and phone number "3444444", sorted by the phone_number field:

Mysql SELECT * FROM WHERE name != " Bob " AND phone_number = " 3444444 " order by phone_number;

Show all entries starting with the letters "bob" and the phone number "3444444" in a specific table:

Mysql SELECT * FROM WHERE name like "Bob %" AND phone_number = "3444444";

Show all entries starting with the letters "bob" and the phone number "3444444", limiting entries 1 to 5:

Mysql SELECT * FROM WHERE name like "Bob %" AND phone_number = "3444444" limit 1.5;

Using regular expressions ("REGEXP BINARY") to search for records. For example, for a case-insensitive search, find all records starting with the letter A:

Mysql SELECT * FROM WHERE rec RLIKE "^a";

Show all unique entries:

Mysql SELECT DISTINCT FROM ; mysql SELECT , FROM ORDER BY DESC;

Show the number of rows in the table:

Mysql SELECT COUNT(*) FROM ;

Mysql SELECT SUM(*) FROM ;

Removing a column:

Mysql alter table drop column ;

Adding a column to the database:

Mysql alter table add column varchar(20);

Changing the column name:

Mysql alter table change varchar(50);

Create a column with a unique name to avoid duplicate names:

Mysql alter table add unique();

Resizing a column:

Mysql alter table modify VARCHAR(3);

Removing a column from a table:

Mysql alter table drop index ;

Mysql LOAD DATA INFILE " /tmp/filename.csv " replace INTO TABLE FIELDS TERMINATED BY "," LINES TERMINATED BY "n" (field1,field2,field3);

MySQL server users, passwords - adding, changing users and passwords. Console

Creating a new user - connecting to the server MySQL as root, switch to database, add user, update privileges:

# mysql -u root -p mysql use mysql; mysql INSERT INTO user (Host,User,Password) VALUES("%"," username ", PASSWORD(" password ")); mysql flush privileges;

Changing the user password from the console on the remote host db1.example.org:

# mysqladmin -u username -h db1.example.org -p password " new-password "

Changing the user password from the console MySQL- connect as root, update password, update privileges:

# mysql -u root -p mysql SET PASSWORD FOR " user "@" hostname " = PASSWORD(" passwordhere "); mysql flush privileges;

Recovering/changing the root server password MySQL- stop MySQL, start without privilege tables, connect as root, set a new password, exit and restart MySQL.

# /etc/init.d/mysql stop # mysqld_safe -skip-grant-tables & # mysql -u root mysql use mysql; mysql update user set password=PASSWORD(" newrootpassword ") where User="root"; mysql ; flush privileges; mysql quit # /etc/init.d/mysql stop # /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update root password:

# mysqladmin -u root -p oldpassword newpassword

Setting the right to connect to the server from the host localhost with the password "passwd" - connecting to subroot, switching to the database, setting privileges, updating privileges:

# mysql -u root -p mysql use mysql; mysql grant usage on *.* to bob @localhost identified by " passwd "; mysql flush privileges;

Setting user privileges to use the database - connecting as root, switching to the database, setting privileges, updating privileges:

# mysql -u root -p mysql use mysql; mysql INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ("%","databasename","username","Y","Y","Y"," Y","Y","N"); mysql flush privileges;

Mysql grant all privileges on databasename .* to username @localhost; mysql flush privileges;

Updating information in the database:

Mysql UPDATE SET Select_priv = "Y",Insert_priv = "Y",Update_priv = "Y" where = user";

Deleting a row in a table:

Mysql DELETE from where = "whatever";

Updating privileges in the database:

Mysql flush privileges;

Backups - creating, restoring databases. Console

Create a backup copy (dump) of all databases into the alldatabases.sql file:

# mysqldump -u root -p password -opt ; /tmp/alldatabases.sql

Back up one database to the databasename.sql file:

# mysql dump -u username -p password -databases databasename ; /tmp/databasename.sql

Back up one table to the file databasename.tablename.sql:

# mysql dump -c -u username -p password databasename tablename ; /tmp/databasename.tablename.sql

Restoring a database (or table) from a backup:

# mysql -u username -p password databasename< /tmp/databasename.sql

Creation of database tables. Console

column names are indicated in small letters;
CAPITAL letters - types and attributes of columns;
in (brackets) - the value of the column type.

Create a table, example 1:

mysql CREATE TABLE (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35 ), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));

Create a table, example 2:

Mysql create table (personid INT(50) NOT NULL AUTO_INTCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default "bato");

The mysql command line utility is a simple SQL shell (with the capabilities of the GNU project's readline library). It supports interactive and non-interactive modes. In interactive mode, query results are presented in ASCII format. When used in non-interactive mode (for example, as a filter), the result is presented in tab-delimited text format (the output format can be changed using command line options). Scripts can be run as shown below:

Shell> mysql database output.tab

If problems occur due to insufficient memory on a given client, use the --quick option! This will force mysql to use the mysql_use_result() function instead of the mysql_store_result() function to obtain the resulting data selection.

Using mysql is very easy. Run mysql database or mysql --user=user_name --password=your_password databas e. Type the SQL command directly at the command line, ending it with one of the following characters: `;" , `\g" or `\G" , and press the ``Enter" key.

The mysql command line utility supports the following options:

Help Displays help information about using the program and exits it. -A, --no-auto-rehash Disables automatic rehash. rehash should be used to get the hash of tables and fields. This makes mysql start faster. --prompt=... Sets a prompt to enter commands in the specified format. -b, --no-beep Turns off the error beep. -B, --batch Output results in tab-delimited batch mode, each line on a new line. The history file is not used. --character-sets-dir=... Directory where character sets are located. -C, --compress Use data compression in the server/client protocol. -#, --debug[=...] Debug log. The default value is "d:t:o,/tmp/mysql.trace". -D, --database=... Name of the database to use. Mostly used in the `my.cnf" configuration file. --default-character-set=... Set the default character set. -e, --execute=... Execute the command and exit the program (output the result as for --batch).-E, --vertical Print query results (strings) vertically. You can produce output in a similar way without this parameter, ending the commands with \G. -f, --force Continue processing even if an SQL error is encountered. -g, --no-named-commands Disables named commands. Only use commands of the form \*, or use named commands only at the beginning of a line ending with a `;" . Starting from version 10.9 the client starts with this option, included default! With the -g option, however, long commands still work from the first line. -G, --enable-named-commands Enables named commands. Long commands are allowed, as well as shortened commands like \*. -i, --ignore-space Ignore space after function names. -h, --host=... Connect to the database on the specified host. -H, --html Print output as HTML. -L, --skip-line-numbers Skip line numbers for errors. Useful for comparing result files that include error messages. --no-pager Disables the pager (pager) and writes the result to stdout (on Unix). See also the \h command (online help). --no-tee Locks the output file. See also the \h command (online help). -n, --unbuffered Clear the buffer after each request. -N, --skip-column-names Skip column names in results. -O, --set-variable var=option Set the value of a variable. The list of used variables is displayed via --help . -o, --one-database Update only the default database. Allows you to skip updates to another database in the update log. --pager[=...] Sets the output data type. By default this is the PAGER environment variable. Its possible values ​​are less, more, cat [> filename], etc. See also the \h command (online help). This option does not work in batch mode. The pager only works under Unix. -p, --password[=...] Password used when connecting to the database server. If a password is not specified on the command line, the user is prompted for one. When using the short form -p, do not leave a space between the parameter and the password value. -P --port=... TCP/IP port number used for connection. -q, --quick Do not cache results. Print it line by line as it comes from the server. This may slow down the server speed if output is paused. The history file is not used. -r, --raw Show column values ​​without any conversion. Used with --batch . -s, --silent Silent mode. Display only error messages. -S --socket=... The socket file used for the connection. -t --table Output the result in table format. Set by default for non-batch mode. -T, --debug-info Print some debugging information when exiting the program. --tee=... Append something to the output file. See also the \h command (online help). This option does not work in batch mode. -u, --user=# MySQL username if this user is not active at the time. -U, --safe-updates[=#], --i-am-a-dummy[=#] Allows only UPDATE and DELETE operations using keys. More information about this option is provided below. You can reset this parameter by setting the argument value --safe-updates=0 in the `my.cnf' configuration file. -v, --verbose More advanced output mode (-v -v -v gives the table output format). -V , --version Print version information and exit the program -w, --wait If the connection to the server fails, wait and try to restore it instead of interrupting the work.

The following variables can also be set via the -O or --set-variable command line options:

If you type help at the command line, mysql will list the commands it supports:

Mysql> help Commands MySQL help (\h) Prints the given text. ? (\h) Synonym for help. clear (\c) Clear command. connect (\r) Connect to the server again. Additional arguments are db and host. edit (\e) Edit the current command using $EDITOR. ego (\G) Send the current MySQL command to the server and display the result vertically. exit (\q) Exit the program. Same as quit. go (\g) Send the current MySQL command to the server. nopager (\n) Block pager, output via stdout. note (\t) Do not add entries to the output file outfile. pager (\P) Set PAGER . Display query results via PAGER. print (\p) Print the current command. prompt (\R) Change the prompt format for entering mysql commands. quit (\q) Exit the program. rehash (\#) Restore hash table. source (\.) Run a file with an SQL script. Provide a filename as an argument. status (\s) Get information about the server status. tee (\T) Set the outfile parameter. Append something to the given output file. use (\u) Use another database. Provide the database name as an argument.

The pager command only works on Unix.

The result of this is the following:

  • It is not allowed to execute UPDATE or DELETE commands unless key restrictions are specified in the WHERE clause. However, you can force the UPDATE / DELETE commands to execute using the LIMIT statement: UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
  • Any results that are too large are limited by the #select_limit# lines.
  • SELECTs that may require more row combinations to execute than #max_join_size# will be aborted.

Some useful tips for using the mysql client:

Some data is more readable when output vertically instead of the commonly used horizontal output window. For example, text that is longer than it is wide and contains many new lines is often much easier to read in a vertical view.

Mysql> SELECT * FROM mails WHERE LENGTH(txt) sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8 Thimble> or Unicode? Otherwise, I"ll put this on my TODO list and see what Thimble> happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set (0.09 sec)

  • You can use the tee command options to log in. It can be started using the --tee=... option for mysql or interactively from the command line by entering the tee command. All data presented on the screen will also be appended to the specified file. This can be very useful for program debugging purposes. The tee utility can be disabled from the command line with the note command. Running the tee command again will enable logging again. If the tee command parameter is not specified, the previous file will be used. Note that the tee command will write the results to a file after each command executed, just before the command prompt appears to enter the next command.
  • Using the --pager[=...] option, it became possible to view or search results interactively using the Unix programs less , more or other similar ones. If you do not explicitly specify an argument in this parameter, the mysql client will look for the PAGER environment variable and set the value to pager . The pager program can also be started from the interactive command line with the pager command and stopped with the nopager command. The command can take an argument, which is optional; pager will be set to the value of this argument. The pager command can be called without an argument, but this requires the use of the --pager option or the corresponding default setting of stdout . The pager command only works on Unix because it uses the popen() function, which is not available on Windows. On Windows, you can use the tee parameter instead, although in some situations this is less convenient than using the pager command.
  • A few tips about the pager command: It can be used to write to a file: mysql> pager cat > /tmp/log.txt and the results will only be sent to the file. Programs called by the pager command can accept any valid options: mysql> pager less -n -i -S Pay special attention to the -S option in the example above. It can be very useful when viewing the results. Try using it with horizontal output (end commands with "\g", or ";") and with vertical output (end commands with "\G"). Very bulky output results are sometimes difficult to read from the screen, in this case the less command with the -S option will allow you to view the results interactively from left to right, and when lines appear with a length greater than the width of the screen, their output will continue to be output from a new line . The data output in such cases is more readable. When you interactively call the less command with the "-S" option, you can switch its operation mode (on/off) from the command line. For more information regarding less, see the description of the "h" command.
  • In conclusion, we note (if you have not already understood this from the previous examples) that it is possible to combine very complex methods of processing the results. Thus, in the following example, the results will be sent to two different directories mounted on two different hard drives at /dr1 and /dr2, and yet the results can be seen on the screen using the less command: mysql> pager cat | tee /dr1/tmp/res.txt | \ tee /dr2/tmp/res2.txt | less -n -i -S
  • The above functions can also be combined: by running tee and setting pager to less , you can view the results using the Unix less command and at the same time write to a file. The difference between the Unix utility tee used in the pager program and the built-in tee command in the mysql client is that the built-in tee command works even if the tee utility is not available on Unix. The built-in tee command also records everything that is displayed on the screen, whereas the Unix tee utility used with pager does not do this to a sufficient extent. A final but important point is that the interactive tee command is more convenient for switching on/off modes if you sometimes need to disable this feature when writing to a file.

You can change the prompt format on the mysql client command line.

The following invitation options are possible:

Option Description
\vmysqld version
\dname of the database used
\hhost name to connect to
\pport number through which the connection is made
\uUsername
\Ufull address username@host
\\ backslash `\"
\nnewline character
\ttabulation
\ space
\_ space with underscore
\Rtime in military time zone (0-23)
\rtime according to standard time zone (1-12)
\mminutes
\ytwo year categories
\Yfour year categories
\Dfull date format
\sseconds
\wday of the week in three-letter format (Mon, Tue, ...)
\PTime before noon/afternoon (am/pm)
\omonth in numeric format
\Omonth in three-letter format (Jan, Feb, ...)
\cA counter that counts the number of commands entered

The `\" character followed by any other letter simply complements that letter.

You can set invitation options in the following ways:

In environment variables You can set the MYSQL_PS1 environment variable for the prompt string. For example: shell> export MYSQL_PS1="(\u@\h) [\d]> " `my.cnf" `.my.cnf" You can set the prompt option in any MySQL configuration file in the mysql group. For example: prompt=(\u@\h) [\d]>\_ On the command line You can set the --prompt option from the mysql command line. For example: shell> mysql --prompt="(\u@\h) [\d]> " (user@host) > Interactively You can also use the prompt (or \R) command to change prompt settings interactively. For example: mysql> prompt (\u@\h) [\d]>\_ PROMPT set to "(\u@\h) [\d]>\_" (user@host) > (user@host) > prompt Return to the original (default) PROMPT settings in the mysql utility> mysql>