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;
mysql> FLUSH PRIVILEGES;
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
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'new_password';
# kill the server
ps aux | grep mysql
kill ....
systemctl start mysqld
Add Comment