Tag Archives | MySQL

Command to show all MySQL databases and their sizes

I found this MySQL query to list all DBs and their sizes here. I wanted to blog it, so it is easier for me to find.

mysql -e 'SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;'

SQL query to find number of WordPress posts per week

I wanted to know how many posts I have been creating each week. Here is a quick MySQL query to find out:

SELECT DISTINCT extract(week from date(post_date)) AS WeekNumber, count( * ) AS Posts 
FROM wp_posts where post_type = 'post' AND post_status = 'publish' AND post_date like '%2012-%' 
GROUP BY WeekNumber;


| WeekNumber | Posts |
|          1 |     4 | 
|          2 |     3 | 
|          3 |     3 | 
|          4 |     3 | 
|          5 |     3 | 
|          6 |     2 | 
|          7 |     3 | 
|          8 |     1 | 
|          9 |     2 | 
|         10 |     2 | 
|         11 |     3 | 
|         12 |     2 | 
|         13 |     2 | 
|         14 |     3 | 

A script to loop through a WordPress database and search for text

We maintain both a WordPress development environment and a production environment. I have written a script that copies a WordPress site to the local machine. In that script I change the GUID (even thought this page says you shouldn’t), siteurl, and home. I change the GUID, because the site has not been publicly accessible, so I believe the warning on the documentation page does not apply.

One thing that I always wanted to do is make sure that the old (dev) site url is not elsewhere in the database. So I figured out this script below that loops thorough all the tables, and all the columns in the database searching for text, in this case the old site url.


for TABLE in $(mysql --batch --skip-column-names -e "use $DATABASENAME;show tables"); do
for COLUMN in $(mysql --batch --skip-column-names -e "use $DATABASENAME;SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '"$TABLE"' AND TABLE_SCHEMA = '"$DATABASENAME"';"); do
query="use $DATABASENAME;select substring($COLUMN,1,100) as '"$TABLE":"$COLUMN"' from $TABLE WHERE $COLUMN like '%"$SEARCHTEXT"%';"
mysql -e "$query"

“error establishing a database connection” in a previously working WordPress site

My site just crashed!!! I received the oh-so-helpful error “establishing a database connection” when I went to my site. Other sites on the server were fine.  The site was working fine, and my config had not changed. The httpd error log showed nothing.

Then I found this in the MySQL logs (/var/log/mysqld.log)

[ERROR] /usr/libexec/mysqld: Table ‘./db_name/wp_options’ is marked as crashed and should be repaired

To fix I ran:

mysql -e “use db_name;REPAIR TABLE wp_options”

And we are back . . .


I just learned about the script: /usr/bin/mysql_secure_installation. Very cool. Here are the commands that the script actually runs.

I am going to add these to my provisioning script (I already take care of the root password piece):

mysql -e “DROP DATABASE test;”
mysql -e “DELETE FROM mysql.user WHERE User=’root’ AND Host!=’localhost’;”
mysql -e “DELETE FROM mysql.user WHERE User=”;”

Comparing master and slave MySQL WordPress DBs

My VPS host – Rackspace contacted me the other day to tell me that my host server became unresponsive. After it came back online I went to my MySQL replicated slave to check the status. The /var/log/mysqld.log said:

Error reading packet from server: Client requested master to start replication from impossible position

This article siad

“9 times out of 10 it’s because the master crashed and when it came back online a new binlog file was made”.

That sounds about right, my virtual host was probably force rebooted, and my VM crashed. I recovered my DBs by re-mirroring my DBs over an AutoSSH connection between my two servers.

The article goes on to say:

Now if your super sensitive of lost events because a row or two could of been lost from this replication event, do some spot testing for tables written to often”

I wanted to verify my DBs were the same on both master and slave, so I wrote this BASH function:

function CompareDBs {
if [ $1 ]; then
RDBDUMP=$(mysqldump -h <a href="tel:"></a> -P 7777 --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
LDBDUMP=$(mysqldump --order-by-primary --skip-extended-insert --skip-opt --skip-comments $DB)
diff -y --suppress-common-lines &lt;(echo &quot;$RDBDUMP&quot;) &lt;(echo &quot;$LDBDUMP&quot;)

Call the function with the DB you want to compare. This script assumes that you have an SSH Tunnel between your two servers.


This will save you some hair pulling!

Drop all MySQL DBs (except mysql & information_schema)

For some reason I can not remember the following command. I have been using it a lot lately when testing MySQL replication.

  1. mysql –skip-column-names –batch -e “show databases” | grep -E -v “mysql|information_schema” | xargs -I “@@” mysql -e “drop database @@”

This will drop all DBs except the default mysql and information_schema dbs. Then I can Rinse and Repeat this script. Of course only do this on the slave!

Mirror all MySQL DBs to the local machine

Continuing on my quest to get MySQL replicating over ssh, I am using the following bash function to replicate all remote DBs locally:


function MirrorAllRemoteDBsToLocal {
	for REMOTEDB in $(mysql -h -P 7777 --batch --skip-column-names -e "SHOW DATABASES")
	LOCALDBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$REMOTEDB"';" | grep "$REMOTEDB" > /dev/null; echo "$?")
	if [ $LOCALDBEXISTS -ne 0 ];then
		echo "adding $REMOTEDB to local MySQL"
		mysql -e "create database $REMOTEDB;"
		echo "getting a dump"
		mysqldump -h -P 7777 $REMOTEDB | mysql $REMOTEDB;
		echo " adding $REMOTEDB to my.conf"
		sed -i '/master-port/a\\treplicate-do-db='$REMOTEDB'' /etc/my.cnf

Line 2 connects to the local AutoSSH tunnel and gets a list of all the remote DBs.

Then we loop through the DBs and if there is not a DB locally with that name, the script will create a database. Next the script gets (Line 9) a dump of the DB and copies it to the newly created DB.

And finally the script add the DB to the /etc/my.cnf (line 11).

All that should have to happen is to issue a slave stop and then slave start, and all DBs should be mirrored locally.

MySQL replication of WordPress dbs over ssh

I wanted to setup MySQL replication over ssh for a small WordPress database. I have a VM that lives in my house, I wanted to be able to bring it up, make it current, disconnect, and then hack away. Here is my proceedure.

On the master:

Setup the replication user (I had to use becasue % did not let me connect from ssh):

grant replication slave on *.* TO repl@"" identified by '[repl password]';

edit /etc/my.cnf

replicate-do-db=DB_TO_REPLICATE   #the name of the db you want to replicate.

From the slave:

edit /etc/my.cnf

server-id = 2
master-host =
master-user = repl
master-password = password
master-port = 7777
replicate-do-db = DB_TO_REPLICATE   #the name of the db you want to replicate.

Next, tunnel a ssh connection from slave to the source, and then make sure you can see the source databases:

ssh -f -N -L7777: [email protected]
mysql -h -P 7777 -e "show databases;"

Next create a local database:

mysql -e "create database DB_TO_REPLICATE;"

Next we want to find where the master’s log is and its position:

MASTERLOGPOS=$(mysql -h -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f2)
MASTERLOGFILE=$(mysql -h -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f1)

next we want to seed the local db

mysqldump -h -P 7777 DB_TO_REPLICATE | mysql -u root DB_TO_REPLICATE;

and finally, tell the db to use the source as the Master:

mysql -e "start slave;"

To test I run:

mysql -e "show slave status\G;"

and I compare the following on the master and the client:

mysql -e "select id,post_parent,post_modified,post_title from jbmurphy_com.wp_posts"