Day 5: Managing Your WordPress Database with WP-CLI

Exporting and Importing Databases

WP-CLI provides powerful commands for managing your WordPress database. Let’s explore how to export and import databases using WP-CLI.

• To export your entire WordPress database:

wp db export wp_database_backup.sql

This command creates a SQL file named wp_database_backup.sql containing your entire WordPress database.

• To import a database backup:

wp db import wp_database_backup.sql

This command imports the contents of the wp_database_backup.sql file into your WordPress database, overwriting the existing data.

Running Database Queries

WP-CLI allows you to run custom SQL queries directly on your WordPress database. Here are some examples:

• To run a simple SELECT query:

wp db query "SELECT * FROM wp_posts WHERE post_type = 'post'"

This command retrieves all posts from the wp_posts table.

• To run an UPDATE query:

wp db query "UPDATE wp_options SET option_value = 'new_value' WHERE option_name = 'option_name'"

This command updates the value of a specific option in the wp_options table.

• To run an INSERT query:

wp db query "INSERT INTO wp_posts (post_title, post_content, post_status) VALUES ('New Post', 'This is a new post.', 'publish')"

This command inserts a new post into the wp_posts table.

Optimizing and Repairing the Database

WP-CLI provides commands to optimize and repair your WordPress database tables:

• To optimize all tables:

wp db optimize

• To repair all tables:

wp db repair

These commands can help improve database performance and fix any issues with corrupted tables.

Best Practices

When managing your WordPress database with WP-CLI, consider the following best practices:

• Regularly backup your database before making any changes
• Use the --dry-run flag when testing queries to see the results without modifying the database
• Be cautious when running UPDATE or DELETE queries, as they can permanently modify or remove data
• Use the --skip-plugins and --skip-themes flags when running database commands to prevent conflicts with plugins or themes

In the next and final lesson, we’ll explore some advanced WP-CLI topics and put it all together. Stay tuned!