So there’s a whole bunch of MySQL scripts included with any release, who can say they know them all? In this blog post, we will take a look at some of these and show usage examples that may help you in your environment.
mysql_access
Let’s say you want to manage your users’ rights, and you have this user ‘john.doe’ that can connect from ‘host1’, but you aren’t sure what he can do to your db: company-staging as well as company-dev.
What do you do? Well, for example, you could use mysql_access and take a look:
/5075/bin:09:13:38:Qalbi $ ./mysqlaccess -U root -d company-staging -u john.doe -h host1 mysqlaccess Version 2.06, 20 Dec 2000 By RUG-AIV, by Yves Carlier ([email protected]) Changes by Steve Harvey ([email protected]) This software comes with ABSOLUTELY NO WARRANTY. Access-rights for USER 'john.doe', from HOST 'host1', to DB 'company-staging' +-----------------+---+ +-----------------+---+ | Select_priv | Y | | Lock_tables_priv | N | | Insert_priv | N | | Execute_priv | N | | Update_priv | N | | Repl_slave_priv | N | | Delete_priv | N | | Repl_client_priv | N | | Create_priv | N | | Create_view_priv | N | | Drop_priv | N | | Show_view_priv | N | | Reload_priv | N | | Create_routine_priv | N | | Shutdown_priv | N | | Alter_routine_priv | N | | Process_priv | N | | Create_user_priv | N | | File_priv | N | | Ssl_type | ? | | Grant_priv | N | | Ssl_cipher | ? | | References_priv | N | | X509_issuer | ? | | Index_priv | N | | X509_subject | ? | | Alter_priv | N | | Max_questions | 0 | | Show_db_priv | N | | Max_updates | 0 | | Super_priv | N | | Max_connections | 0 | | Create_tmp_table_priv | N | | Max_user_connections | 0 | +-----------------+---+ +-----------------+---+ BEWARE: Everybody can access your DB as user `john.doe' from host `host1' : WITHOUT supplying a password. : Be very careful about it!! The following rules are used: db : 'host1','company-staging','john.doe','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N' host : 'Not processed: host-field is not empty in db-table.' user : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0' BUGs can be reported by email to [email protected] /5075/bin:09:13:39:Qalbi $ ./mysqlaccess -U root -d company-dev -u john.doe -h host1 mysqlaccess Version 2.06, 20 Dec 2000 By RUG-AIV, by Yves Carlier ([email protected]) Changes by Steve Harvey ([email protected]) This software comes with ABSOLUTELY NO WARRANTY. Access-rights for USER 'john.doe', from HOST 'host1', to DB 'company-dev' +-----------------+---+ +-----------------+---+ | Select_priv | Y | | Lock_tables_priv | N | | Insert_priv | Y | | Execute_priv | N | | Update_priv | Y | | Repl_slave_priv | N | | Delete_priv | Y | | Repl_client_priv | N | | Create_priv | N | | Create_view_priv | N | | Drop_priv | N | | Show_view_priv | N | | Reload_priv | N | | Create_routine_priv | N | | Shutdown_priv | N | | Alter_routine_priv | N | | Process_priv | N | | Create_user_priv | N | | File_priv | N | | Ssl_type | ? | | Grant_priv | N | | Ssl_cipher | ? | | References_priv | N | | X509_issuer | ? | | Index_priv | N | | X509_subject | ? | | Alter_priv | N | | Max_questions | 0 | | Show_db_priv | N | | Max_updates | 0 | | Super_priv | N | | Max_connections | 0 | | Create_tmp_table_priv | N | | Max_user_connections | 0 | +-----------------+---+ +-----------------+---+ BEWARE: Everybody can access your DB as user `john.doe' from host `host1' : WITHOUT supplying a password. : Be very careful about it!! The following rules are used: db : 'host1','company-dev','john.doe','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N' host : 'Not processed: host-field is not empty in db-table.' user : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'
So, as you can see, on company-staging he has only select privileges but on company-dev he has select,update,insert,delete.
Fair enough, this can be done in SQL as well:
mysql> SHOW GRANTS FOR 'john.doe'@'host1'; +-------------------------------------------------------------------------------+ | Grants for [email protected] | +-------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'john.doe'@'host1' | | GRANT SELECT ON `company-staging`.* TO 'john.doe'@'host1' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `company-dev`.* TO 'john.doe'@'host1' | +-------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM mysql.db WHERE user='john.doe' AND host = 'host1' AND db IN ('company-staging', 'company-dev') \G *************************** 1. row *************************** Host: host1 Db: company-dev User: john.doe Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N *************************** 2. row *************************** Host: host1 Db: company-staging User: john.doe Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N 2 rows in set (0.00 sec)
But mysql_access can give you that without logging into mysql.
mysqlshow
How many people know that there is a program to show what databases or tables you have? For example, take a look at this:
/5075/bin:09:31:36:Qalbi $ ./mysqlshow -uroot +--------------------+ | Databases | +--------------------+ | information_schema | | company-dev | | company-staging | | employees | | mysql | | test | +--------------------+ /5075/bin:09:31:44:Qalbi $ ./mysqlshow -uroot employees Database: employees +---------------+ | Tables | +---------------+ | departments | | dept_emp | | dept_manager | | employees | | salaries | | titles | | v_emp_by_dept | +---------------+
This shows the databases this user has access to, as well as the tables in the ’employees’ database.
If you want to see how many rows (and columns) you have in the tables in the employees db:
/5075/bin:09:31:49:Qalbi $ ./mysqlshow --count -uroot employees Database: employees +---------------+----------+------------+ | Tables | Columns | Total Rows | +---------------+----------+------------+ | departments | 2 | 9 | | dept_emp | 4 | 331603 | | dept_manager | 4 | 24 | | employees | 6 | 300024 | | salaries | 4 | 2844047 | | titles | 4 | 443308 | | v_emp_by_dept | 2 | 9 | +---------------+----------+------------+ 7 rows in set.
But yet again, we can see this with pure SQL too (databases first, then tables in ’employees’ and then the rows per table in ’employees’:
mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+
| SCHEMA_NAME |
+--------------------+
| information_schema |
| company-dev |
| company-staging |
| employees |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees';
+---------------+
| TABLE_NAME |
+---------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
| v_emp_by_dept |
+---------------+
7 rows in set (0.63 sec)
mysql> SELECT
-> t.TABLE_NAME, COUNT(1) AS "Cols", t.TABLE_ROWS
-> FROM
-> TABLES t
-> INNER JOIN
-> COLUMNS c ON (
-> t.TABLE_NAME=c.TABLE_NAME
-> )
-> WHERE
-> t.TABLE_SCHEMA='employees'
-> GROUP BY
-> c.TABLE_NAME;
+---------------+------+------------+
| TABLE_NAME | Cols | TABLE_ROWS |
+---------------+------+------------+
| departments | 2 | 9 |
| dept_emp | 4 | 332289 |
| dept_manager | 4 | 24 |
| employees | 6 | 300695 |
| salaries | 4 | 2844513 |
| titles | 4 | 443803 |
| v_emp_by_dept | 2 | NULL |
+---------------+------+------------+
7 rows in set (0.17 sec)
Please note that the TABLE_ROWS column is only a estimate, as all the tables (except for the view used in another blog) are InnoDB.
So this covers some initial lesser-known scripts, and I will continue to report on the usage of other less-known scripts and what they do in this series of blogs. Please stay tuned.
No comments