Archive | 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 the number of WordPress posts this year!

I was compiling my year end wrap up (hits, posts, twitter followers), and I realized I did not know how many posts I created this year. I ran the following query again my WordPress database to find out.

select post_date,post_title from wp_posts where post_type = ‘post’ AND post_status = ‘publish’ AND post_date like ‘%2011-%’;

My goal was 2 a week for a year. I ended up with 116.

More on this later.

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.

DATABASENAME="mywpdb"
SEARCHTEXT="devURL"

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"
done
done

“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 . . .

mysql_secure_installation

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=”;”
mysql -e “FLUSH PRIVILEGES;”

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
DB=$1;
RDBDUMP=$(mysqldump -h <a href="tel:127.0.0.1">127.0.0.1</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;)
fi
}

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

 

********* MAKE SURE YOUR TIME IS RIGHT ON BOTH 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 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW DATABASES")
	do
	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 127.0.0.1 -P 7777 $REMOTEDB | mysql $REMOTEDB;
		echo " adding $REMOTEDB to my.conf"
		sed -i '/master-port/a\\treplicate-do-db='$REMOTEDB'' /etc/my.cnf
	fi
	done
}

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 127.0.0.1 becasue % did not let me connect from ssh):

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

edit /etc/my.cnf

server-id=1
log-bin=mysql-bin
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 = 127.0.0.1
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:127.0.0.1:3306 [email protected]
mysql -h 127.0.0.1 -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 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f2)
MASTERLOGFILE=$(mysql -h 127.0.0.1 -P 7777 --batch --skip-column-names -e "SHOW MASTER STATUS;" | cut -f1)

next we want to seed the local db

mysqldump -h 127.0.0.1 -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 "use DB_TO_REPLICATE;CHANGE MASTER TO MASTER_LOG_FILE='"$MASTERLOGFILE"',MASTER_LOG_POS=$MASTERLOGPOS;"
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"