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;'
It is easy enough. Why can’t I remember this -> show columns from table name;
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.
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
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=”;”
mysql -e “FLUSH PRIVILEGES;”
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 <(echo "$RDBDUMP") <(echo "$LDBDUMP")
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!
For some reason I can not remember the following command. I have been using it a lot lately when testing MySQL replication.
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!
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.
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"