Basic MySQL Stuff: Part 1

Hi Folks,

It’s been a while since my last post here and I thought it a good idea to start transferring my notes over to here because why not.

Inspired by a link from one of my work colleagues, Adam

This little post is just to cover some frequently used stuff that i’ve lifted straight out of my “useful-to-me-old-hat-to-everyone-else” one liner’s repo on Github.

So here i’m going to go over some MySQL shell stuff that is fundamentally useful especially if you’re a ditz like me and perpetually forgetting “how i do dis?”

Showing all Users


mysql> SELECT User,Host FROM mysql.user;

+------------------+------------------+
| User             | Host             |
+------------------+------------------+
| root             | 127.0.0.1        |
| root             | ::1              |
| root             | damiennugent-com |
| damienn          | localhost        |
| debian-sys-maint | localhost        |
| root             | localhost        |
| test             | localhost        |
+------------------+------------------+

Remember to use a where-like clause when you have an idea what you’re looking for, remember % are used for wild cards


mysql> SELECT User,Host FROM mysql.user WHERE User LIKE '%test%';
+------+-----------+
| User | Host      |
+------+-----------+
| test | localhost |
+------+-----------+

Adding a user with a password

GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY 'SuperSecurePasswordGoesHere';

Remember to always check whether a user exists for a particular host before attempting to create another, lest you reset their password and the user not be overly pleased.

Showing a user’s grants and permissions.

When you know that a user exists for a particular host and you want to check what permissions they have, we use the Show Grants statement to do so.


mysql> SHOW GRANTS FOR 'test'@'localhost';

+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*2ACFC73EE3B67B35B3BF9E02CFE7364A5A9B349C' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Remember there may be multiple instances of the same user name for different hosts, each may have different grants.

Showing the Databases in a MySQL Instance

ez-pz


mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| damiennugent       |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

In the example above; 3 of these Databases are used for the day to day running of the MySQL instance, the other one, (handsomely titled might i add) is a database with actual stuff in it (in this instance an older copy of this site…).

Selecting a database and viewing what tables it has

Again ez-pz;


mysql> USE damiennugent;
Database changed

mysql> SHOW TABLES;
+------------------------+
| Tables_in_damiennugent |
+------------------------+
| wp_commentmeta         |
| wp_comments            |
| wp_links               |
| wp_options             |
| wp_postmeta            |
| wp_posts               |
| wp_term_relationships  |
| wp_term_taxonomy       |
| wp_termmeta            |
| wp_terms               |
| wp_usermeta            |
| wp_users               |
+------------------------+
12 rows in set (0.00 sec)

However it is useful to know how a table is built; what fields it has, what’s the primary key etc etc

To do the we use the DESCRIBE function;

Using one of the tables from my database here as an example, wp_comments we can see how it’s made up.


mysql> describe wp_comments;
+----------------------+---------------------+------+-----+---------------------+----------------+
| Field                | Type                | Null | Key | Default             | Extra          |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID           | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| comment_post_ID      | bigint(20) unsigned | NO   | MUL | 0                   |                |
| comment_author       | tinytext            | NO   |     | NULL                |                |
| comment_author_email | varchar(100)        | NO   | MUL |                     |                |
| comment_author_url   | varchar(200)        | NO   |     |                     |                |
| comment_author_IP    | varchar(100)        | NO   |     |                     |                |
| comment_date         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| comment_date_gmt     | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| comment_content      | text                | NO   |     | NULL                |                |
| comment_karma        | int(11)             | NO   |     | 0                   |                |
| comment_approved     | varchar(20)         | NO   | MUL | 1                   |                |
| comment_agent        | varchar(255)        | NO   |     |                     |                |
| comment_type         | varchar(20)         | NO   |     |                     |                |
| comment_parent       | bigint(20) unsigned | NO   | MUL | 0                   |                |
| user_id              | bigint(20) unsigned | NO   |     | 0                   |                |
+----------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)

As you can see above in a bog standard wordpress installation’s wp_comments table;

  • The comment_ID field as the Primary key,
  • That field is an Unsigned Big Integer (meaning that it starts at 0, and can’t represent a negative value and increments up to 18446744073709551615 as per the docs )
  • It can’t be empty, though by default it is empty
  • It increments automatically when new data is added.

As an aside the MUL value in the key column, shows that field being a non unique index where the same value can recur (for example the comment_parent field in the context of a blog allows for a comment hierarchy with context).

I’ll look to adding a second part here shortly (but as is often the case here, no guarantees!)

Cheers!

-Damien

 

Leave a Reply

Your email address will not be published. Required fields are marked *