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

 

Music of 2016 – Albums of the Year

Hi Folks, i thought i’d do something a bit different and actually write something here…

Well seeing as we’ve just passed into 2017 I thought it’d be a good idea to perhaps write up some of the more interesting records i’ve listened to this past year; Including where possible links to the music and some of my thoughts on the records.

This has the potential to be a verrrrrrrry lengthy post. so i’ve added page breaks every so often

Continue reading Music of 2016 – Albums of the Year

New things afoot

Hi folks!

It’s been a while since I last updated this site unfortunately.

But it’s been all change for me here since my last update; I’ve taken a few courses – specifically the Red Hat RHCSA Fast Track course (RH199) way back in February. I’ve successfully transitioned over to a new role(!) I now work in the Linux System Administration team in Rackspace, transitioning over from the Support Specialist function.

It’s all rather exciting, and as expected i’m nervous regarding this. But i’m not an idiot (at least I hope not) and while there’ll be an adjustment period for me I’ll get up to speed. I’m really looking forward to developing my skills further and to get my teeth stuck into some interesting issues for our customer base.

But straight off the bat I’ve noticed areas i can immediately set to work improving; namely i’m a dreadful bash scripter (haha). well if you need to add a user and set some custom sudoers permissions to said user on over 90 servers at a time you’re going to need to script the shit out of it.

Most of all though I need to develop a new approach, namely in procedure and how i approach problems; this will be the most important aspect that i need to show to my new colleagues, and it’s expected from our customers.

Currently I’m listening to Mael Mórdha, an Irish heavy metal band, doomy misery, lovely.

I’ll attempt to update this more regularly with fun things i find.

-Damien

Who am I?

I am Damien N. and I’m a support tech. I am at the beginning of my career (read: bottom of the ladder), and I’m focusing towards the Linux and Unix side of System Administration, this blog/website/whatever is intended as a collection of my notes, musings, and discoveries in the wacky world of computers.

With any luck, if I’m feeling up to it I’ll be doing a few other things unrelated to that on this; music I’m enjoying, things i’ve found of note online etc.

I’ve got a lot to do, and a schedule to keep, I’m planning on writing in this around once a week. So if you wish to follow me; fire away, I hope I don’t disappoint.

Regards,
-Damien

Configuring X-Forwarded-For headers with Apache and Varnish

Hi!

This site is using WordPress with Varnish. i’ve just looked at the logs, as it turns out my access logs are next to useless as Varnish Cache acts as a proxy, thus all access requests for content appear to come from the localhost. however all is not lost.

Varnish Cache can be configured to use a special X-Forwarded-For header. This header is specifically for this purpose.

In the varnish default.vcl file you’re going to want to put the following under “sub vcl_recv”

if (req.restarts == 0) {
if (req.http.x-forwarded-for) {
set req.http.X-Forwarded-For = req.http.X-Forwarded-For + ", " + client.ip;
} else {
set req.http.X-Forwarded-For = client.ip;
}

To sum up what this snippet does, it writes a new HTTP header to the request detailing the client ip address. thus you can modify apache’s logging configuration to display this rather than the frankly useless 127.0.0.1 localhost…

In my case i’m going to use the Apache Module “remoteip” as this will be far cleaner than faffing around with modifying the verbose logging.

Now the logging section of my apache config looks something like:
RemoteIPHeader X-Forwarded-For
RemoteIPInternalProxy 127.0.0.1/8

LogFormat "%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
#LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%a %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

from the default i’ve added:
RemoteIPHeader X-Forwarded-For
RemoteIPInternalProxy 127.0.0.1/8

This is the configuration for the “remoteip” module, this configuration states that the header “X-Forwarded-For” will contain the IP address of the client, and that the IP address of the proxy is 127.0.0.1

the other piece of configuration i’ve changed is from:
LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
to;
LogFormat "%a %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined

the %h to %a is from “Remote host” to “Remote IP”

from implementing these changes my logs have gone from this:
127.0.0.1 - - [24/Dec/2015:00:39:14 +0000] "GET / HTTP/1.1" 200 5028 "-" "Rackspace Monitoring/1.1 (https://monitoring.api.rackspacecloud.com)"
127.0.0.1 - - [24/Dec/2015:00:41:41 +0000] "GET / HTTP/1.1" 200 5028 "-" "Rackspace Monitoring/1.1 (https://monitoring.api.rackspacecloud.com)"
127.0.0.1 - - [24/Dec/2015:00:42:34 +0000] "GET / HTTP/1.1" 200 6501 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/601.3.9 (KHTML, like Gecko) Version/9.0.2 Safari/601.3.9"

to this:

50.56.142.170 - - [24/Dec/2015:01:07:14 +0000] "GET / HTTP/1.1" 200 5028 "-" "Rackspace Monitoring/1.1 (https://monitoring.api.rackspacecloud.com)"
50.57.61.21 - - [24/Dec/2015:01:09:41 +0000] "GET / HTTP/1.1" 200 5028 "-" "Rackspace Monitoring/1.1 (https://monitoring.api.rackspacecloud.com)"
109.*.22.* - - [24/Dec/2015:01:11:30 +0000] "GET / HTTP/1.1" 200 6501 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/601.3.9 (KHTML, like Gecko) Version/9.0.2 Safari/601.3.9"
109.*.22.* - - [24/Dec/2015:01:11:31 +0000] "GET /wp-content/themes/twentysixteen/genericons/Genericons.svg HTTP/1.1" 304 126 "http://damiennugent.com/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/601.3.9 (KHTML, like Gecko) Version/9.0.2 Safari/601.3.9"

IP Addresses modified to protect the innocent… i.e. me haha.

So to Summarise:

  1. Ensure you have the X-Forwarded-For header enabled in your Varnish Configuration file, as above.
  2. Use the Apache Module “remoteip”
  3. in the Apache Configuration file, configure remote ip with:
    RemoteIPHeader X-Forwarded-For
    RemoteIPInternalProxy 127.0.0.1/8
  4. update the Logging configuration to point to the remote IP Header as opposed to the remote host.

This may work with Rackspace Cloud load balancers, however i haven’t confirmed this yet: Expect an update.

-Damien