Linux, Web Developing

Login to Mysql (notice username and password touch the -u and -p argument)

# mysql -uusername -ppassword

mysql> SHOW databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| observium          |
| performance_schema |
| wpdb               | <--- we want this one (its our wordpress database)
+--------------------+

mysql> USE wpdb;

NOTE: we can skip asking mysql to USE a database and immediately enter it from the shell prompt by running this:
mysql -uusername -ppassword wpdb. This way we dont have to ask to use wpdb, as we will already be using it.

mysql> SHOW tables;

+--------------------------+
| Tables_in_wpdb           |
+--------------------------+
| wp_avhfdas_ipcache       |
| wp_commentmeta           |
| wp_comments              |
| wp_links                 |
| wp_options               |
| wp_postmeta              |
| wp_posts                 |
| wp_statistics_exclusions |
| wp_statistics_historical |
| wp_statistics_pages      |
| wp_statistics_search     |
| wp_statistics_useronline |
| wp_statistics_visit      |
| wp_statistics_visitor    |
| wp_term_relationships    |
| wp_term_taxonomy         |
| wp_termmeta              |
| wp_terms                 |
| wp_usermeta              |
| wp_users                 | <--- this is where wordpress users are saved
| wp_word_replacer         |
+--------------------------+

Lets see what kind of information is saved about each user.

mysql> DESCRIBE wp_users;

+---------------------+---------------------+------+-----+---------------------+----------------+
| Field               | Type                | Null | Key | Default             | Extra          |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID                  | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| user_login          | varchar(60)         | NO   | MUL |                     |                |
| user_pass           | varchar(255)        | NO   |     |                     |                |
| user_nicename       | varchar(50)         | NO   | MUL |                     |                |
| user_email          | varchar(100)        | NO   |     |                     |                |
| user_url            | varchar(100)        | NO   |     |                     |                |
| user_registered     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| user_activation_key | varchar(255)        | NO   |     |                     |                |
| user_status         | int(11)             | NO   |     | 0                   |                |
| display_name        | varchar(250)        | NO   |     |                     |                |
+---------------------+---------------------+------+-----+---------------------+----------------+

We could list each user with

mysql> SELECT * FROM wp_users;

<not showing output for that>

NOTE: the users passwords are encrypted, so this is secure for your users

If you want to run that command from command line. There are several ways.
I like to make a config file with the command in it
So lets make a file called /tmp/sql.txt (or anything) and its contents will only contain:
SELECT * FROM wp_users;
So we make it like this
echo ‘SELECT * FROM wp_users;’ > /tmp/sql.txt
Now to run it like this:
mysql -uusername -ppassword wpdb < /tmp/sql.txt
NOTE: becareful not to put anything damaging into /tmp/sql.txt, or else you could potentially damage your entire userdatabase.
You will see the output of all of the users. Now dont forget to clean up after yourself /tmp/sql.txt. rm /tmp/sql.txt

Here my creations. Edit username and password.

List every users info (all info) – not useful to share with world (too much data)

### LIST ALL USERS (i dont use this)
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT * FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
)

NOTE: I dont use above as it just lists everything about every user (its just the example in the article but in a nice copy pasteable form you can test)

List which days had the most new users

### NEW USERS PER DAY (sorted from highest number of new user in a day down to lowest new users in a day)
### SORT BY HIGHEST DAY TO LOWEST DAY
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT ID,user_registered FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
) | awk '{print $2}' | grep -v "user_registered" | sort -n | uniq -c | sort -rn | awk 'BEGIN{print "Most New Users In Day\nDate\t\tNew Users"}{print $2 "\t" $1}'

List how many new users each day (0 user days are not shown) – before to now

### NEW USERS PER DAY (sorted chronologically from before to now)
### BY DAY - OLD TO NEW
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT ID,user_registered FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
) | awk '{print $2}' | grep -v "user_registered" | uniq -c | awk 'BEGIN{print "*** New Users Added Each Day ***\nDate\t\tNew\tCumulative";}{tot+=$1; print $2, "\t", $1, "\t" tot}END{print "Total Users:", tot}'

List how many new users each day (0 user days are not shown) – now to before

### NEW USERS PER DAY (sorted chronologically from now to before)
### BY DAY - NEW TO OLD
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT ID,user_registered FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
) | awk '{print $2}' | grep -v "user_registered" | uniq -c | awk 'END{print "Date\t\tNew\tCumulative\n*** New Users Added Each Day, Total Users:", tot " ***";}{tot+=$1; print $2, "\t", $1, "\t" tot}' | tac

Put into a script

NOTE: you can have the results present to your wordpress viewers. you can do something like “mkdir /var/www/usercount”. And append “> /var/www/usercount/count1.txt” at the end of each file (change count1 to something meaningful). That way you can access your counts from: http://yourwebsite.com/usercount/usercount1.txt. Like this:
http://www.infotinks.com/usercount/perday.txt
http://www.infotinks.com/usercount/history.txt

At the end of one of my backup scripts (I do this after backup just in case anything goes wrong with the sql command – which shouldnt happen, unless there is gamma ray bursts and dark solar nuclear thin blasts, etc..):

#!/bin/bash
# creates 2 text files with usercount information
# filename: usercount.sh
# crontab entry example: run every hour of every day (on the 15th minute of every hour)
# 15 * * * * /root/scripts/usercount.sh

# MAKE FOLDER JUST IN CASE
mkdir -p /var/www/usercount 2> /dev/null

### NEW USERS PER DAY (sorted from highest number of new user in a day down to lowest new users in a day)
echo "Todays Date: `date`, `date +%s`s" > /var/www/usercount/perday.txt
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT ID,user_registered FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
) | awk '{print $2}' | grep -v "user_registered" | sort -n | uniq -c | sort -rn | awk 'BEGIN{print "Most New Users In Day\nDate\t\tNew Users"}{print $2 "\t" $1}' >> /var/www/usercount/perday.txt

### NEW USERS PER DAY (sorted chronologically from now to before)
echo "Todays Date: `date`, `date +%s`s" > /var/www/usercount/history.txt
(
RANDOMFILE="/tmp/sql-`date +%s`-$RANDOM.txt"
echo 'SELECT ID,user_registered FROM wp_users;' > "${RANDOMFILE}"
mysql -uusername -ppassword wpdb 2> /dev/null < "${RANDOMFILE}"
rm -f "${RANDOMFILE}"
) | awk '{print $2}' | grep -v "user_registered" | uniq -c | awk 'END{print "Date\t\tNew\tCumulative\n*** New Users Added Each Day, Total Users:", tot " ***";}{tot+=$1; print $2, "\t", $1, "\t" tot}' | tac >> /var/www/usercount/history.txt

The end.

Leave a Reply

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