MySQL: databases and users

Databases

Create

_$: mysql -u root -p
mysql> CREATE DATABASE database;

Rename

_$: mysqldump -u root -p db > db_dump.sql
_$: mysql -u root -p -e "CREATE DATABASE new_db"
_$: mysql -u root -p new_db < db_dump.sql
_$: mysql -u root -p -e "DROP DATABASE db"

List

mysql> show databases;

Delete test databases

_$: mysql -u root -p
mysql> use mysql;
mysql> DROP DATABASE test;

Users

Create

_$: mysql -u root -p
mysql> CREATE USER user IDENTIFIED BY 'pass';
mysql> GRANT ALL PRIVILEGES ON database.* TO user@localhost IDENTIFIED BY 'pass';
mysql> FLUSH PRIVILEGES;

Rename

_$: mysql -u root -p
mysql> RENAME USER old_user TO new_user;

Note: It is possible that we also need to change old_user@localhost to new_user@localhost.

List

mysql> use mysql;
mysql> select user,host,password from mysql.user;

Update root password

_$: mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD('newpassword') where user='root';
mysql> flush privileges;
mysql> quit;

Reset root password

_$: service mysql stop
_$: mysqld_safe --skip-grant-tables &
_$: mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD('newpassword') where user='root';
mysql> flush privileges;
mysql> quit;
_$: service mysql stop
_$: service mysql start

Delete anonymous accounts

_$: mysql -u root -p
mysql> use mysql;
mysql> DELETE FROM mysql.user WHERE user='' AND password='';

Delete privileges from test databases

We are deleting the right to use those databases, not the database.

_$: mysql -u root -p
mysql> use mysql;
mysql> DELETE FROM mysql.db WHERE db LIKE "test%";
mysql> FLUSH PRIVILEGES;