Category: 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 command to show a table’s column names <- Why can't i remember this?
It is easy enough. Why can’t I remember this -> show columns from table name;
-
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…
-
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…
-
“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…
-
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…
-
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…
-
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. 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…
-
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: 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…
-
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…