MySQL‎ > ‎

Users/Permissions

login for the first time with

mysql -u root

set password

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');



Create new user:

$ mysql --user=root mysql


mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';


to set or change password

set password for root

$ mysqladmin -u root password NEWPASSWORD
$ mysqladmin -u root -p oldpassword newpass
$ mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';
mysql> SET PASSWORD FOR 'user'@'host' = PASSWORD('newpwd');


get user and host from

mysql> select host, user from mysql.user;


Grant permission: mysql> grant select,insert,update,delete,create,drop

-> on somedb.* to someusr@"%" identified by 'passwrd';

mysql> flush privileges;


Create user on an old system: mysql> select User from mysql.user where user="fanatic";

Empty set (0.01 sec)

mysql> create user fanatic identified by 'stuWr7xe';

ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'user

fanatic identified by 'stuWr7xe at line 1

mysql> INSERT INTO mysql.user (user, host, password) VALUES ('fanatic', 'localhost', PASSWORD('stuWr7xe'));

Query OK, 1 row affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON fanatic.* TO fanatic;

Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)


When running mysqldump, one may run into:

mysqldump: Got error: 1044: Access denied for user 'myuser'@'%' to database 'somedatabase' when using LOCK TABLES

Here is the fix:

mysql> grant LOCK TABLES on somedatabase.* to 'myuser'@'%';
mysql> FLUSH PRIVILEGES;



Comments