SerwerWeb.pl

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:

mysql --user=UŻYTKOWNIK --password=HASŁO

It can also be done in a slightly shorter way:

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:

mysql --user=UŻYTKOWNIK --password

Or in a slightly shorter form:

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:

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:

mysql> CREATE DATABASE nowa_baza;

Working with the database:

mysql> USE nowa_baza;

Create a new database user, and giving him all rights to the database:

GRANT ALL ON nowa_baza.* TO UŻYTKOWNIK_BAZY@localhost IDENTIFIED BY 'HASŁO';

Removing the database:

mysql> DROP DATABASE nowa_baza;

Display of all available databases:

mysql> show databases;

You will get something like this:

+--------------------+
| 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:

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:

mysql> help contents

The following categories are available for selection:

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:

mysql> help Nazwa Kategorii

E.g:

mysql> help Table Maintenance