Mind Dump, Tech And Life Blog
written by Ivan Alenko
published under license Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)copy! share!
posted in category Systems Software / MySQL & MariaDB
posted at 21. Apr '18
last updated at 15. Aug '21

MySQL/MariaDB Create User + Database + Permissions / Reset User Password

This will use UTF-8 as a character set and UTF-8 general (language agnostic) case insensitive collation (sorting).

mysql> CREATE database 'mydb' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
mysql> CREATE USER 'myuser' IDENTIFIED BY 'mypassword';
mysql> GRANT USAGE ON *.* TO 'myuser'@localhost IDENTIFIED BY 'mypassword';
# allow from everywhere! do not use unless you know
#mysql> GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';
mysql> GRANT ALL privileges ON `mydb`.* TO 'myuser'@localhost;

Check the privileges for user and host:

mysql> SHOW GRANTS FOR 'myuser'@localhost;

Reset Password

Basically start mysql server in a safe mode.

systemctl stop mysqld
#or /etc/init.d/mysqld stop
sudo mysqld_safe --skip-grant-tables --skip-networking &

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
ALTER USER 'root'@'' IDENTIFIED BY 'new_password';

# kill the server
ps aux | grep mysql
kill ....
systemctl start mysqld

Add Comment