Basics of MySQL in Linux console
MySQL is probably the most popular relational database management system data used for Web applications, moreover, not only online. Many popular CMS, such as WordPress, Joomla, do Drupal use MySQL for their operation.
From what I observed, the lion's share of users uses MySQL via phpMyAdmin, so today I will try to show you how to use MySQL from the Linux console. This is an interesting solution, if only for this reason, that you can create scripts that automate your work.
It is natural, that to use the server you must have it installed 馃槈 you will need two mysql-server and mysql-client packages, if they are not installed, you should do it now.
Now you can connect to the base:
1 | mysql --user=U呕YTKOWNIK --password=HAS艁O |
It can also be done in a slightly shorter way:
1 | mysql -u U呕YTKOWNIK -pHAS艁O |
This is not a bug, after -p there are no spaces ;p. You can also connect in such a way that the password is not visible on the screen:
1 | mysql --user=U呕YTKOWNIK --password |
Or in a slightly shorter form:
1 | mysql -u username -p |
Then, in the next line in the console, we will be asked for the password. Please don't panic, the password will not be displayed while entering it. This provides greater security, the password is not remembered, for example, in Bash history. However, keep in mind, that this method will not work when there is a need to create a script.
When we want to connect to MySQL located on a different host than local we use:
1 | mysql -h JAKI艢-HOST.PL -u U呕YTKOWNIK -pHAS艁O |
Now that we're connected to MySQL, time for some basic commands that are not performed in the shell anymore (for example Bashu), and already in MySQL.
Create a new database:
1 | mysql> CREATE DATABASE nowa_baza; |
Working with the database:
1 | mysql> USE nowa_baza; |
Create a new database user, and giving him all rights to the database:
1 | GRANT ALL ON nowa_baza.* TO U呕YTKOWNIK_BAZY@localhost IDENTIFIED BY 'HAS艁O'; |
Removing the database:
1 | mysql> DROP DATABASE nowa_baza; |
Display of all available databases:
1 | mysql> show databases; |
You will get something like this:
1 2 3 4 5 6 7 8 | +--------------------+ | Database | +--------------------+ | information_schema | | nowa_baza | | inna_baza | | mysql | +--------------------+ |
Apart from user-created databases, we always get two more information_schema and mysql, as you can easily guess, they contain all the settings related to MySQL itself, and the list of users we have created - the list of users can be displayed by typing the command below:
1 | mysql> SELECT user, password, host FROM mysql.user; |
I have only described the most basic issues, more information can be found as usual in the help:
1 | mysql> help contents |
The following categories are available for selection:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | You asked for help about help category: Contents For more information, type 'help ', where is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Language Structure Plugins Table Maintenance Transactions User-Defined Functions Utility |
They are selected with:
1 | mysql> help Nazwa Kategorii |
E.g:
1 | mysql> help Table Maintenance |
