MySQL Archives - Ben Lobaugh Online

How to extract a single table from a MySQL sql file

On June 18, 2019

Ever received a large MySQL dump file and only needed one table out of it?

I ran across just this issue. The full database took significant time to import and I only needed a small subsection of the DB, just one single table. Rather than waiting for it all to import, I pulled together a sed command that extracts a single table from the sql file. Check it out:

sed -n ‘/Records of TABLE_NAME/,/Table structure for/p’ FULL_SQL_FILE.sql > table.sql

Replace TABLE_NAME and FULL_SQL_FILE with the names of the table you want and the sql file to pull it from, respectively.

You will now have a table.sql file in your directory that is ready to be imported.

Photo by chuttersnap on Unsplash

Find total number of post records on a WordPress Multisite in MySQL

By Ben Lobaugh (blobaugh)

On August 3, 2018

In Computing, MySQL, Programming, Web Development, WordPress

I was recently asked for the total number of posts on a WordPress website. Normally as simple as looking at the number in the dashboard, however in this instance the WordPress install was a Multisite with nearly 100 sites. That would have taken far too long to do manually, but what about having MySQL do the heavy lifting for me?

The information_schema database contains data about every database and table on the server. One of the metrics it stores is the count of rows in a table. We can use that to extrapolate roughly how many entries are in all the posts tables combined. This is not an exact science, however it will get you close to the real numbers. I argue that it will typically be “close enough” on larger installs, such as the one I was looking at.

In the following query, just replace the database name with your database and run it.

SELECT SUM( table_rows )
FROM information_schema.TABLES 
WHERE 
	table_schema = "{{YOUR_DATABASE_NAME}}" 
	AND table_name LIKE '%_posts';

Find and update WordPress posts with comments older than NNN days

By Ben Lobaugh (blobaugh)

On March 12, 2018

In Computing, MySQL, Programming, Web Development, WordPress

I have a use case where I need to disable comments on posts in WordPress where the last comment was more than one year, or 365 days, ago.

Posts can be found with this query:

SELECT p.ID, p.post_title, p.post_date, p.comment_status, p.`ping_status`
FROM wp_posts AS p
WHERE p.ID IN (
     SELECT c.comment_post_ID
     FROM wp_comments AS c
     WHERE DATEDIFF( curdate(), c.comment_date ) <= 365
  );

The outer query takes the inner query as the search parameter. The inner query is finding the latest comment for each post based on the number in the WHERE condition. 365 represents 365 days, or 1 year.

Therefore, this query is getting the latest comment for post where the comment is less than 1 year old.

Now let’s say you need to update the posts to disable comments. The goal is to disable commenting for all posts that have not had a comment within the last year. It can be accomplished with the following:

UPDATE wp_posts
SET comment_status='closed', ping_status='closed'
WHERE ID IN (
		SELECT c.comment_post_ID
		FROM wp_comments AS c
		WHERE DATEDIFF( curdate(), c.comment_date ) >= 365
);

Here again the number 365 represents the number of days.

Check the posts and you should see the comments have been closed.

MySQL: Count number of tables in database

By Ben Lobaugh (blobaugh)

On August 18, 2016

In Computing, MySQL, Programming

If you need to count the number of tables that exist in a MySQL database you can do so with the following query. Just remember to swap out the database name!

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = 'YOUR_DB_NAME'

MySQL: Find the size of a database

By Ben Lobaugh (blobaugh)

On April 28, 2016

In Computing, MySQL, Programming

MySQL has the ability to show you size of the information contained in your database. You can find this information per database or per table in a database pretty easily.

Size of databases

To list the size of all the databases on the server run the following:

SELECT table_schema "Database", sum( data_length + index_length) / 1024 / 1024 
"Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Size of tables in database

To list the size of all the tables in an individual database run:

SELECT table_name, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES where table_schema = "{{NAME OF YOUR DATABASE}}";

Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Category: MySQL Page 1 of 4

How to extract a single table from a MySQL sql file

Find total number of post records on a WordPress Multisite in MySQL

Find and update WordPress posts with comments older than NNN days

MySQL: Count number of tables in database

MySQL: Find the size of a database

Size of databases

Size of tables in database

My Vision

2018 Reading Challenge

Latest Leadership and Personal Growth Posts