Tag: MySQL

Posted on

Blog Spammer Shootin’ Match

The Stage

For some reason….. this blog — THIS blog! …..gets a crap ton of spam. So I went looking. I figured I’d share the bulk of what I found.

mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%China%";
Query OK, 969 rows affected (0.46 sec)
Rows matched: 969  Changed: 969  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%obama%";
Query OK, 119 rows affected (0.09 sec)
Rows matched: 119  Changed: 119  Warnings: 0
 
mysql> update wp_comments set comment_approved='spam' where comment_approved='0' AND comment_content LIKE "%nofollow%";
Query OK, 412 rows affected (0.09 sec)
Rows matched: 412  Changed: 412  Warnings: 0


Yep. If you’re getting crazy spam, watch out for China, obama, and nofollow. They. Will. Destroy. Your. Sanity.

The Apology

If you legitimately are trying to comment and noted something about any of the three mentioned above, I owe you an apology. I’m sorry I shot your comment in the spam box with glee and a special kind of whimsy.

Looking at the Data, Again!

I kinda enjoy using this as a psuedo “comment honeypot” because it’s just so darn entertaining. Here are some other fun ways to look at your comment spam:

By number of comments posted per IP:

mysql> select comment_author_IP, count(comment_author_IP) as count from wp_comments where comment_approved='0' group by comment_author_IP order by count desc;
+-------------------+-------+
| comment_author_IP | count |
+-------------------+-------+
| 123.45.67.89      |    15 |
| 123.45.67.89      |    14 |
| 123.45.67.89      |    10 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     3 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     2 |
| 123.45.67.89      |     1 |
| 123.45.67.89      |     1 |
+-------------------+-------+
10 rows in set (0.00 sec)

Original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

By my patented, foolproof, never wrong Offender Rating System®:

mysql> select comment_author_IP, (count(comment_author_IP)+SUM(LENGTH(comment_content))) as offender_rating from wp_comments where comment_approved='0' group by comment_author_IP order by offender_rating desc;
+-------------------+-----------------+
| comment_author_IP | offender_rating |
+-------------------+-----------------+
| 123.45.67.89      |           10051 |
| 123.45.67.89      |            3907 |
| 123.45.67.89      |            2367 |
| 123.45.67.89      |            1159 |
| 123.45.67.89      |             836 |
| 123.45.67.89      |             811 |
| 123.45.67.89      |             677 |
| 123.45.67.89      |             653 |
| 123.45.67.89      |             328 |
| 123.45.67.89      |             297 |
+-------------------+-----------------+
10 rows in set (0.00 sec)

Again, original IP’s censored b/c I don’t wanna be a jerk to anyone but the guy at 123.45.67.89!

…fin!

Wasn’t that fun?! =D

Don’t spam me, bro!

Posted on

Getting a quick summary on your MySQL Database

Summary of yer tables

Sometimes, you just want to see what’s going on from a high level. “HOW CAN I GET A SUMMARY OF MY TABLES?!” you may have asked yourself…

To do this, you’ll need to do a simple query into the TABLES table in the information_schema database. Like this:


mysql> use information_schema;
Database changed

mysql>

It’s a habit for me, but I always like to describe the table so I know what I’m looking at:


mysql> desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)

mysql>

Now, we can see what we’re looking for. Here’s what I generally like to do:


mysql> select TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, AUTO_INCREMENT from TABLES where TABLE_SCHEMA='werd';
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| TABLE_NAME            | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH  | INDEX_LENGTH | AUTO_INCREMENT |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
| wp_commentmeta        |          0 |              0 |           0 |  281474976710655 |         4096 |              1 |
| wp_comments           |          1 |            228 |         228 |  281474976710655 |         7168 |              2 |
| wp_hook_list          |          0 |              0 |       16384 |                0 |            0 |           NULL |
| wp_links              |          0 |              0 |           0 |  281474976710655 |         1024 |              1 |
| wp_options            |        154 |           2548 |      430244 |  281474976710655 |        13312 |            835 |
| wp_postmeta           |          9 |             44 |         404 |  281474976710655 |        10240 |             20 |
| wp_posts              |          8 |            298 |        2388 |  281474976710655 |        11264 |             17 |
| wp_term_relationships |          3 |             21 |          63 | 5910974510923775 |         3072 |           NULL |
| wp_term_taxonomy      |          1 |             40 |          40 |  281474976710655 |         4096 |              2 |
| wp_terms              |          1 |             40 |          40 |  281474976710655 |        11264 |              2 |
| wp_usermeta           |         19 |             61 |        1160 |  281474976710655 |        10240 |             20 |
| wp_users              |          1 |             92 |          92 |  281474976710655 |         4096 |              2 |
+-----------------------+------------+----------------+-------------+------------------+--------------+----------------+
12 rows in set (0.00 sec)

mysql>

Yeah, even my schema names are trendy and clever.</sarcasm>

Anyways, now you can sort and/or select however you want! Grok yer data, amigo. =]

I realize my code blocks are not blocking well enough. Will fix.

Posted on

Set Default Username and Password for your MySQL DB

Tired of always having to enter your username and password to do anything on your mysql database? I would only suggest using this on your local dev box for security purposes, but admittedly, it’s a nice timesaver =]

Warning: this assumes you can vim. If you’d prefer, you can replace any instance of “vim” with “nano”

From the *nix CLI:
vim ~/.my.cnf
If it’s blank, feel free to insert the following (with your user credentials of course!):
[client]
user = 'user_name'
password = 'secure_password-moreso_than_that_pls...well...this_is_ok'

Save that puppy and now from your *nix CLI you shouldn’t have to worry about those precious few keystrokes when interacting with mysql anymore… on second thought, if your password looks like that above, use this. It’ll save you buckets of time =\

Posted on

Quick way to remove all tables in MySQL DB through *nix CLI

Tablenator!

Here’s a quick time saver (if you dev like me, it’ll come in handy!) for when you just want to drop all of the tables in a database. Of course, test it first so you know what you’ll be dropping!

Test First:

for I in mysql database_name -u user_name -p -e 'show tables' | awk '{ print $1}' | grep -v '^Tables' ; do echo "Table: $I" ; done

Code:

for I in `mysql database_name -u user_name -p -e 'show tables' | awk '{ print $1}' | grep -v '^Tables'` ; do mysql -u user_name -p -e "drop table $I" ; done

Rundown

The basic formula is a BASH for loop that grabs all of the tables in the desired database, filters the output using a combination of AWK and GREP so we can use just a simple list, then drops each table separately.

USE THIS CODE AT YOUR OWN RISK! BACKUP YOUR DATABASE FIRST AND HAVE A RECOVERY PLAN IN PLACE! (trust a man known to fat finger things)

Posted on

Exporting a MySQL Database

For a MySQL database export:

mysqldump -u username -p database_name > /tmp/dump.sql
^^ from the *nix command line

For a particular table in a database

mysqldump -u username -p database_name table_name > /tmp/dump.sql
^^ also from the *nix command line

You will then be prompted for the password for the specified user, shortly thereafter, you’ll have your db export (or as some folks like to call it, a “db dump” — gah! Hate that name… gross!)

Tired of always typing in your username/password when using mysql via the CLI? Check out how to set up a .my.cnf!

Posted on

Import MySQL DB via CLI

mysql -u account_name -p db_name < /path/to/import/file/mysql.sql
^^ from the *nix command line

Sometimes you need to specify the host (typically it's either 127.0.0.1 or localhost) like this:

mysql -u account_name -p -h localhost db_name < /path/to/import/file/mysql.sql
^^ from the *nix command line

Now enter the specified account's mysql password and you're on your way!

In the instance you're having issues with your import, another way to import (will require access and proper privs), sometimes you just want it to run and not worry about errors (for instance when an insert or two in a dump are greater than the buffer). The handy function to just "make it work" (ie: NOT FOR PRODUCTION USE!!!) is source in the mysql cli -- check it out:

mysql> source /path/to/db/export.sql;
^^ from the mysql command line

That'll treat the file as a list of commands and run each one individually. If a few of them don't work, it keeps moving on and finishes everything it can... now that's BLAMO

Posted on

SQL Adding a Column to an Existing Table

Since I can never remember the syntax to add a column to a table, here it is:

alter table foo_tablename add column foo_column int(10) default null

  • alter table – the actual command
  • foo_tablename – the table name we’re altering
  • add column – telling sql to add a column instead of some other alteration
  • foo_column – name of the column to add
  • int(10) default null – the column’s qualities (it’s an int up to 10 digits long, can be null and defaults to null

Tada!!!

Posted on

Performance Issue Gut Check for MySQL

Sometimes MySQL performance can be difficult to hammer out. One of the quickest ways to get a good gut check is to have a look at the current process list. Check this out:

mysql> show processlist;
+-------+------+-----------+------------+---------+------+----------+------------------+
| Id    | User | Host      | db         | Command | Time | State    | Info             |
+-------+------+-----------+------------+---------+------+----------+------------------+
| 14302 | abcd | localhost | rohjay_one | Sleep   | 3696 |          | NULL             |
| 14958 | abcd | localhost | NULL       | Query   |    0 | starting | show processlist |
+-------+------+-----------+------------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

mysql>

Now, this is relatively uninteresting, but sometimes, you’ll find a list of active queries that have stacked up against your db. Some things that can really help you identify issues is by ensuring each different piece of your platform identifies itself uniquely.

Once that happens, you can see which queries from where are hanging on. Parts of your platform can stall and hold onto connections, queries can take an eternity, bad code can hold result sets in memory, etc… This should give you a good idea where to start looking!

Hope this helps =]

Rohjaynator::1715182244::49847819