Skip to content

My MySQL reminder

1. Setting up a password for root user just after install:

sudo  mysql -u root

2. Resetting root password:

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

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

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

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