# My MySQL reminder


## Setting up a password for root user just after install:
``` { .bash .copy }
sudo  mysql -u root
```

## Resetting root password:
``` { .bash .copy }
sudo mysql_secure_installation
 ```

## Creating a user and granting privileges:
``` { .sql .copy }
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON database_name.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
```
Granting privileges on all databases: 
``` { .sql .copy }
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
```
Changing a user's password:
``` { .sql .copy }
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');

ALTER USER 'user'@'localhost' IDENTIFIED BY 'password';
```

Listing all users :
``` { .sql .copy }
SELECT User, Host, Password FROM mysql.user;
```

Changing a user's associated host (example from host % to host loclahost):
``` { .sql .copy }
UPDATE mysql.user SET Host='localhost' WHERE Host='%' AND User='user'
```

See also, on users management:  

- [https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/](https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/)
- [https://lean.fr/reinitialiser-le-mot-de-passe-root-mysql-sans-mot-de-passe-42495.html](https://lean.fr/reinitialiser-le-mot-de-passe-root-mysql-sans-mot-de-passe-42495.html)
- [MySQL sur Ubuntu 18.04](https://www.digitalocean.com/community/tutorials/comment-installer-mysql-sur-ubuntu-18-04-fr)
- [MySQL on Ubuntu Bionic](https://tecadmin.net/install-mysql-on-ubuntu-18-04-bionic/)
- [Linuxtricks](https://www.linuxtricks.fr/wiki/mysql-reinitialiser-le-mot-de-passe-root)
- [Unix socket in MariaDB](https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/)
- [SET PASSWORD](https://mariadb.com/kb/en/library/set-password/)
- [ALTER USER](https://mariadb.com/kb/en/library/alter-user/)
- [Create new user and grant permissions](https://kyup.com/tutorials/create-new-user-grant-permissions-mysql/)
- [Utilisateurs et privilèges sur MySQL](https://blog.emmanuelgautier.fr/utilisateurs-et-privileges-sous-mysql/)
- [Voir les droits des utilisateurs](https://www.it-connect.fr/voir-les-droits-utilisateur-dans-mysql-en-ligne-de-commande-%EF%BB%BF/)
- [Changing MySQL user password](https://linuxacademy.com/blog/linux/changing-mysql-user-password/)

## Connecting to MySQL:
``` { .bash .copy }
sudo mysql
```

## Creating a database:
``` { .sql .copy }
CREATE DATABASE lpic CHARACTER SET 'utf8'; #Beware of encoding
```

## Saving databases (basic level, assuming your user is root):
``` { .bash .copy }
mysqldump -u root -p password database > database.sql #insecure
MYSQL_PWD="password" mysqldump -u root database > database.sql #secure
MYSQL_PWD="password" mysqldump -u root --all-databases > database.sql
```

See also, for more advanced features):  

- [Utilisation de myssqldump](https://www.geek-directeur-technique.com/2017/07/17/utilisation-de-mysqldump)
- [Guide de sauvegarde et restauration MySQL](https://www.memoinfo.fr/tutoriels-linux/guide-sauvegarde-restauration-mysql/)

## Deleting databases:
``` { .sql .copy }
DROP DATABASE [IF EXISTS] database_name;
```

## Checking a database encoding (default is UTF8):
``` { .sql .copy }
SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "database_name";
```

See: [How to convert a MySQL database to UTF-8 encoding](https://www.a2hosting.es/kb/developer-corner/mysql/convert-mysql-database-utf-8)

## Checking the MySQL version:
``` { .sql .copy }
SELECT @@version;
```

## Leaving the MySQL prompt:
``` { .sql .copy }
quit
```

## Stopping/starting/restarting the service:
``` { .bash .copy }
sudo systemctl stop mysql
sudo service mysql stop
sudo /etc/init.d/mysqld stop
```

## Courses and tutorials in French:

[SQL.sh](https://sql.sh/)
