MySQL

mysql uninstall:

sudo apt-get remove --purge mysql-server mysql-client
sudo apt-get autoremove
sudo apt-get autoclean

mysql install:

the best is install LAMP server (php, mysql, apache):

sudo apt-get update

sudo apt-get install lamp-server^

after mysql installed:

sudo mysql_secure_installation

=> ... Would you like to setup VALIDATE PASSWORD component? - answer "no"!!!!!

sudo service mysql start

sudo mysql

sudo mysql -u root -p // вход с паролем

default user (вход без пароля):

файл ~/.my.cnf:

[mysql]
user=user
password=password

============================================

look for users and hosts:

mysql> select user, host, plugin from mysql.user;

// if plugin of "root" user = "auth_socket":

mysql> USE mysql;

mysql> UPDATE user SET plugin='caching_sha2_password' WHERE User='root';

mysql> alter user 'root'@'localhost' identified by '';

mysql> FLUSH PRIVILEGES;

mysql> exit;

$ sudo service mysql restart

=======================

mysql> CREATE DATABASE {dbname};

mysql> source {dbname.sql} // restore database from dump

mysql> use {dbname};

mysql> GRANT ALL PRIVILEGES ON {dbname}.* TO 'root'@'localhost';

==================================================

password validation options (if necessary):

mysql> SHOW VARIABLES LIKE 'validate_password%'; - show validate password variables

mysql> set global validate_password_policy = LOW; // look at variables list by command above

//=================================

DBeaver error: MySQL Public Key Retrieval is not allowed

To change the settings on Dbeaver:

1. Right click your connection, choose "Edit Connection"

2. On the "Connection settings" screen (main screen) click on "Edit Driver Settings"

3. Click on "Connection properties"

4. Right click the "user properties" area and choose "Add new property"

5. Add two properties: "useSSL" and "allowPublicKeyRetrieval"

6. Set their values to "false" and "true" by double clicking on the "value" column

7. Verify server sertificate: set to false

Create new table:

CREATE TABLE Persons (

ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (ID)

);