# My MySQL reminder


## Setting up a password for root user just after install:

    sudo  mysql -u root
    
## Resetting root password:

    sudo mysql_secure_installation
    
## Creating a user and granting privileges:

    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: 
 
    GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost';
    
Changing a user's password:

    SET PASSWORD FOR 'user'@'localhost' = PASSWORD('password');

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

Listing all users :

    SELECT User, Host, Password FROM mysql.user;

Changing a user's associated host (example from host % to host loclahost):

    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:

    sudo mysql
    
## Creating a database:

    CREATE DATABASE lpic CHARACTER SET 'utf8'; #Beware of encoding
    
## Saving databases (basic level, assuming your user is root):

    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:

    DROP DATABASE [IF EXISTS] database_name;
    
## Checking a database encoding (default is UTF8):

    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:

    SELECT @@version;

## Leaving the MySQL prompt:

    quit
        
## Stopping/starting/restarting the service:

    sudo systemctl stop mysql
    sudo service mysql stop
    sudo /etc/init.d/mysqld stop

## Courses and tutorials in French:

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