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

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

4. Connecting to MySQL:

sudo mysql

5. Creating a database:

CREATE DATABASE lpic CHARACTER SET 'utf8'; #Beware of encoding

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

7. Deleting databases:

DROP DATABASE [IF EXISTS] database_name;

8. 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

9. Checking the MySQL version:

SELECT @@version;

10. Leaving the MySQL prompt:

quit

11. Stopping/starting/restarting the service:

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

12. Courses and tutorials in French:

SQL.sh