{"id":703,"date":"2013-02-10T00:35:00","date_gmt":"2013-02-10T00:35:00","guid":{"rendered":"https:\/\/www.keenertech.com\/?p=703"},"modified":"2013-02-10T00:35:00","modified_gmt":"2013-02-10T00:35:00","slug":"mysql-a-grab-bag-of-useful-bits","status":"publish","type":"post","link":"https:\/\/staging.keenertech.com\/?p=703","title":{"rendered":"MySQL: A Grab Bag of Useful Bits"},"content":{"rendered":"\n

Database management tools are nice to have, but they’re not essential (I’m talking about you<\/em>, MySQL Workbench). When the tools aren’t working, for whatever reason, you can always go back to basics and create the objects you need from the simple, mysql<\/em> command-line utility. Here’s a grab bag of things you can do…<\/p>\n\n\n\n

What User Accounts Exist?<\/h3>\n\n\n\n

Login to the mysql command-line utility as root<\/em>. Execute the following commands:<\/p>\n\n\n\n

   mysql> use mysql\n\n   Database changed\n\n   mysql> desc user;\n\n   +------------------------+---------------+------+-----+---------+\n   | Field                  | Type          | Null | Key | Default |\n   +------------------------+---------------+------+-----+---------+\n   | Host                   | char(60)      | NO   | PRI |         |\n   | User                   | char(16)      | NO   | PRI |         |\n   | Password               | char(41)      | NO   |     |         |\n   | Select_priv            | enum('N','Y') | NO   |     | N       |\n   | Insert_priv            | enum('N','Y') | NO   |     | N       |\n   | Update_priv            | enum('N','Y') | NO   |     | N       |\n   | Delete_priv            | enum('N','Y') | NO   |     | N       |\n   | Create_priv            | enum('N','Y') | NO   |     | N       |\n   | Drop_priv              | enum('N','Y') | NO   |     | N       |\n   | Reload_priv            | enum('N','Y') | NO   |     | N       |\n   | Shutdown_priv          | enum('N','Y') | NO   |     | N       |\n   | Process_priv           | enum('N','Y') | NO   |     | N       |\n   | File_priv              | enum('N','Y') | NO   |     | N       |\n   | Grant_priv             | enum('N','Y') | NO   |     | N       |\n   | References_priv        | enum('N','Y') | NO   |     | N       |\n   | Index_priv             | enum('N','Y') | NO   |     | N       |\n   | Alter_priv             | enum('N','Y') | NO   |     | N       |\n   | Show_db_priv           | enum('N','Y') | NO   |     | N       |\n   | Super_priv             | enum('N','Y') | NO   |     | N       |\n   | Create_tmp_table_priv  | enum('N','Y') | NO   |     | N       |\n   | Lock_tables_priv       | enum('N','Y') | NO   |     | N       |\n   | Execute_priv           | enum('N','Y') | NO   |     | N       |\n   | Repl_slave_priv        | enum('N','Y') | NO   |     | N       |\n   | Repl_client_priv       | enum('N','Y') | NO   |     | N       |\n   | Create_view_priv       | enum('N','Y') | NO   |     | N       |\n   | Show_view_priv         | enum('N','Y') | NO   |     | N       |\n   | Create_routine_priv    | enum('N','Y') | NO   |     | N       |\n   | Alter_routine_priv     | enum('N','Y') | NO   |     | N       |\n   | Create_user_priv       | enum('N','Y') | NO   |     | N       |\n   | Event_priv             | enum('N','Y') | NO   |     | N       |\n   | Trigger_priv           | enum('N','Y') | NO   |     | N       |\n   | Create_tablespace_priv | enum('N','Y') | NO   |     | N       |\n   | ssl_type               | enum('',      | NO   |     | NULL    |\n   |                        |  'ANY',       |      |     |         |\n   |                        |  'X509',      |      |     |         |\n   |                        |  'SPECIFIED') |      |     |         |\n   | ssl_cipher             | blob          | NO   |     | NULL    |\n   | x509_issuer            | blob          | NO   |     | NULL    |\n   | x509_subject           | blob          | NO   |     | NULL    |\n   | max_questions          | int(11)       | NO   |     | 0       |\n   |                        |   unsigned    |      |     |         |\n   | max_updates            | int(11)       | NO   |     | 0       |\n   |                        |   unsigned    |      |     |         |\n   | max_connections        | int(11)       | NO   |     | 0       |\n   |                        |   unsigned    |      |     |         |\n   | max_user_connections   | int(11)       | NO   |     | 0       |\n   |                        |   unsigned    |      |     |         |\n   | plugin                 | char(64)      | YES  |     |         |\n   | authentication_string  | text          | YES  |     | NULL    |\n   | password_expired       | enum('N','Y') | NO   |     | N       |\n   +------------------------+---------------+------+-----+---------+\n   43 rows in set (0.01 sec)\n\n   mysql> select host, user from user;\n\n   +--------------------------+------------+\n   | host                     | user       |\n   +--------------------------+------------+\n   | %                        | davekeener |\n   | 127.0.0.1                | root       |\n   | ::1                      | root       |\n   | Davids-MacBook-Pro.local |            |\n   | Davids-MacBook-Pro.local | root       |\n   | localhost                | root       |\n   +--------------------------+------------+\n   6 rows in set (0.00 sec)\n<\/pre>\n\n\n\n
The mysql<\/em> schema contains system information, including user accounts, privileges, etc. Once you’ve switched so that it’s your active schema, you can do a “desc” on the USER table to see what columns are available.<\/p>\n\n\n\n
The only columns of general interest for our purposes are HOST and USER. That’s what the final SQL statement shows for us, revealing that there are 6 user accounts defined.<\/p>\n\n\n\n
What Schemas Exist?<\/h3>\n\n\n\n
In MySQL, a schema, or database, is a unit that can have its own privileges, and which can contain other database objects such as tables, views, etc. You typically can’t see schemas that you don’t have permissions on, for security reasons. In order to see all of the available schemas, you’ll have to login as root<\/em>.<\/p>\n\n\n\n
Login to the mysql command-line utility as root<\/em> and run the following command:<\/p>\n\n\n\n
   mysql> show schemas;\n\n   +--------------------+\n   | Database           |\n   +--------------------+\n   | information_schema |\n   | ktech_dev          |\n   | mysql              |\n   | performance_schema |\n   | test               |\n   +--------------------+\n   5 rows in set (0.00 sec)\n<\/pre>\n\n\n\n
Changing a User’s Password<\/h3>\n\n\n\n
Login to the mysql command-line utility as root<\/em> and run the following command:<\/p>\n\n\n\n
   mysql> SET PASSWORD FOR 'dave'@'localhost' = PASSWORD('mvemjsun');\n   Query OK, 0 rows affected (0.28 sec)\n<\/pre>\n\n\n\n
The change can also be done directly by updating the USER table in the mysql<\/em>schema, but…really…I don’t advise manual updates of system tables.<\/p>\n","protected":false},"excerpt":{"rendered":"
Database management tools are nice to have, but they’re not essential (I’m talking about you, MySQL Workbench). When the<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[139],"class_list":["post-703","post","type-post","status-publish","format-standard","hentry","category-technology","tag-mysql"],"_links":{"self":[{"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=\/wp\/v2\/posts\/703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=703"}],"version-history":[{"count":0,"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=\/wp\/v2\/posts\/703\/revisions"}],"wp:attachment":[{"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/staging.keenertech.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}