MySQL Back to Basics: Lesser-known MySQL client scripts

Jan 16, 2009 / By Nicklas Westerlund

Tags:

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 (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
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 bugs@mysql.com
/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 (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
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 john.doe@host1                                                     |
+-------------------------------------------------------------------------------+
| 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.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>