MySQL Commands, Tips and Tricks

root's picture

I will make it simple. You are here because you need something. Read on and see if you can find below what you are looking for.

If you need extra info, do not be shy and visit mysql docs: https://dev.mysql.com/doc/refman/5.5/en/sql-syntax-server-administration.html

How to create mysql user:

In our example we create user drat with access from 192.168.69.69 and 192.168.69.1 and password verysimple.

# mysql
mysql> CREATE USER 'drat'@'192.168.69.69' IDENTIFIED BY 'verysimple';
mysql> CREATE USER 'drat'@'192.168.69.1' IDENTIFIED BY 'verysimple';

How to give access to a database:

In this example, we will give to user drat access to mydatabase from ip 192.168.69.69.

# mysql
mysql> GRANT ALL ON mydatabase.* TO 'drat'@'192.168.69.69';

How to give access to all databases:

In this example, we will give to user drat access to all from ip 192.168.69.69.

# mysql
mysql> GRANT ALL ON *.* TO 'drat'@'192.168.69.69';

How to remove one mysql user:

# mysql
mysql> DROP USER 'drat'@'192.168.69.69';

How to take from user access to a database:

# mysql
mysql> REVOKE ALL ON mydatabase.* FROM 'drat'@'192.168.69.69';

How to take from user access to all databases:

# mysql
mysql> REVOKE ALL ON *.* FROM 'drat'@'192.168.69.69';

How to list accesses to databases:

# mysql
mysql> SELECT host, user, db from mysql.db;

How to list mysql users:

# mysql
mysql> SELECT host, user, password from mysql.user;

Thou shalt not steal!

If you want to use this information on your own website, please remember: by doing copy/paste entirely it is always stealing and you should be ashamed of yourself! Have at least the decency to create your own text and comments and run the commands on your own servers and provide your output, not what I did!

Or at least link back to this website.

Recent content