mysql cheat sheet

How to grant access any user from one network only to mysql

 
$ mysql -p
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to ''@'192.168.10.%';
$service mysql reload  

Please note the empty user name in the syntax above.
For this changes to be effective I needed to reload the mysql.


Surprisingly these 2 commands don't work at all as much as you would believe. The '*' or the '%' for the user column have no wildcard meaning.

 
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to '%'@'192.168.10.%';
(root@localhost@localhost) 03:49:13 [mysql]> grant all on *.* to '*'@'192.168.10.%';

This is how you can check the current grants (you need to flush the privileges or reload the mysql config as changes issues with 'grant' don't take actions immediately).

 
(root@localhost@localhost) 03:51:30 [mysql]> select * from user;
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
| Host                | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | Reference
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
| localhost           | root | *2447D497B9A6A15F2776055CB2D1E9F86758182F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| OL63x64.example.com | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| 127.0.0.1           | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| ::1                 | root | *DCAB8B850144B862687D44957E317DE424E12923 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| 192.168.10.%        |      | *2447D497B9A6A15F2776055CB2D1E9F86758182F | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y
+---------------------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+----------
5 rows in set (0.00 sec)

or

[root@OL63x64 log]# pt-show-grants --password=rrr
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.12-enterprise-commercial-advanced at 2015-01-04 15:55:27
-- Grants for ''@'192.168.10.%'
GRANT ALL PRIVILEGES ON *.* TO ''@'192.168.10.%' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F';
-- Grants for 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'::1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'OL63x64.example.com'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ol63x64.example.com' IDENTIFIED BY PASSWORD '*DCAB8B850144B862687D44957E317DE424E12923' WITH GRANT OPTION;
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

References
http://dev.mysql.com/doc/refman/5.6/en/default-privileges.html

Show mysqld server and mysql client options

server options compiled-in defaults and any option files that it reads:
mysqld --verbose --help

server options (ignoring the settings in any option files):
mysqld --no-defaults --verbose --help

runtime/running server options     : mysqladmin variables

parsed options from config file(s): mysqld --print-defaults
 mysqld would have been started with the following arguments:
--datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --user=mysql --symbolic-links=0