Data in a MySQL/MariaDB database is stored in tables. A simple way of thinking about indexes is to imagine an extensive spreadsheet. This type of system is not always conducive to quick searching; that’s where an index becomes essential. If there is no index, then the database engine has to start at row one and browse through all the rows looking for the corresponding values. If this is a small table, then it is no big deal, but in larger tables and applications where there can be tables with millions and even billions of rows, it becomes problematic. As you can imagine, searching through those rows one by one will be time-consuming, even on the latest hardware. The solution is to create an INDEX (or more than one) for your data.Continue reading “MySQL Performance: MySQL/MariaDB Indexes”
In this article, we will learn the basics of joining tables in SQL. We will learn about Left, Right, Inner, and Outer joins and give examples of how to use them.
Data in an RDBMS (Relational Database Management System) is grouped into tables. These tables have a rigid definition of the type of data that can be stored in them. To connect different tables, and thus different types of data that may relate to each other, we will use the JOIN clause.Continue reading “MySQL Performance: Intro to JOINS in SQL”
In this article, we will discuss what the SQuirreL SQL Client is, some of its features, the supported database types, and how to install the client.
What is SQuirreL SQL Client?
The SQuirreL SQL Client is an open-source, graphical Java database administration tool that will allow you to review the structure of a JDBC compliant database, browse the data in tables, and issue SQL commands.Continue reading “How to Install SQuirrel SQL Client”
In this article, we will be discussing how to install Microsoft SQL or MSSQL on Linux. Microsoft SQL, colloquially referred to as MSSQL, is a relational database management system created by Microsoft. Open-source MySQL and PostgreSQL are typically synonymous with Linux distributions, but working with MSSQL on Linux is also supported. MSSQL offers some features that its open-source counterparts don’t, and depending on application requirements, it might be the right choice for an RDBMS. In this tutorial, we are going to walk through how to install MSSQL on CentOS 7 and Ubuntu 16.04.Continue reading “How to Install Microsoft SQL on Linux”
Reading Time: 2 minutes
MySQL via Command Line 102: Basic User Interaction
- These instructions are intended for deleting a MySQL database on Linux via the command line.
- I’ll be working from a Liquid Web Core Managed CentOS 7 server, and I’ll be logged in as root.
Reading Time: 2 minutes
MySQL via Command Line 102: Basic User Interaction
While automated backups are important, sometimes you just want to take a MySQL dump of the database prior to making a change to your site. When modifying files in Linux, you can simply copy a file to another name in order to make a new copy. In this tutorial, we will show you how to create a backup of your database (or multiple databases) and also how to restore a backup from either command line or cPanel.
Reading Time: 2 minutesThere may be times when you need to clean up post revisions created on your site. This is possible, using the commands already available in WP-CLI.
WP-CLI has a wp post delete command which can be used to delete post revisions. Post revisions are changes made to content on your site, over time those post revisions on your site can mount up. The following directions assume you are using one of Liquid Web’s Managed WordPress or Managed WooCommerce products. You can also use these techniques with other WordPress installations, just be sure to run the commands from the primary WordPress installation folder.
Preparing to Run Commands
One of the first steps will be to generate sFTP/SSH credentials from your site manager. You can use Terminal on the Mac, or Putty on a PC to use WP-CLI. For more information about logging into your server using SSH, see Logging into Your Server via Secure Shell (SSH).
Log in, then go to the WordPress installation folder by entering:
It is always a good idea to create a database backup before making significant changes to your site, like bulk deleting post revisions. To create a manual backup run this command:
wp db export
You can now use gzip to compress the resulting sql file which will mean a smaller file being stored on your server:
Cleaning Up Your Post Revisions
To delete post all revisions (moving them temporarily into the trash), use this WP-CLI command:
wp post delete $(wp post list --post_type='revision' --format=ids)
To delete the post revisions which have been moved to the trash (this includes all post revisions which have a post status of trash), run this command:
wp post delete $(wp post list --post_type='revision' --format=ids --force)
You can skip the first step of moving the posts to the trash by just running the second command. This will remove all post revisions, both those in the trash and those that are in the active portion of the site.
More Control Over Post Revisions Removal
If you need more control of deleting post revisions, there is a package which can be installed from a third-party for WP-CLI. Please note: This package is not provide by Liquid Web nor is it endorsed by Liquid Web. Please use at your own discretion.
To install the package for WP-CLI, run the following command:
wp package install trepmal/wp-revisions-cli
After the package WP Revisions has been installed, to clean all post revisions, you can use the following command. Please note: this command can be slow, since it will query post revisions before deleting them.
wp revisions clean -1
If you wanted to delete all post revisions before a specific date, you can include that in the command. For example:
wp revisions clean --before-date=2019-06-10
If you needed to clean all post revisions other than those for a specific post type, include that post type at the end of the command. For example, revisions for the WooCommerce created product post type would not be deleted if you run this command:
wp revisions clean --post_type=product
For a faster method to delete all post revisions, you can run this command:
wp revisions dump --hard
To list all existing post revisions, you can run this command:
wp revisions list
Easily deleting post revisions from your site database will help keep the database cleaned up. Streamlining the database can result in performance improvements, especially as the size of the database grows.
Reading Time: 2 minutesSimilar to Dropbox and Google Drive, Nextcloud is self-hosting software that allows you to share files, contacts, and calendars. But, unlike Dropbox and Google Drive, your files will be private and stored on your server instead of a third party server. Nextcloud is HIPAA and GDPR compliant, so your files will be encrypted along with the ability to audit. For this tutorial, we’ll be installing our Nextcloud instance on our Ubuntu 18.04 LTS server. Continue reading “How to Install Nextcloud 15 on Ubuntu 18.04”
Reading Time: < 1 minuteWhen using phpMyAdmin, it’s essential to have the correct user permissions to create edits/writes to the database. Otherwise insufficent permissions can lead to errors like the ones pictured below “#1044 – Access denied for user …[using password: YES]” and “#1045 – Access denied for user…[using password: YES]”. In our tutorial, we’ll show you how to correct this issue using the command line terminal. Let’s get started! Continue reading “Troubleshooting: MySQL/MariaDB Error #1044 & #1045 Access Denied for User”
Reading Time: 4 minutesThis guide walks you through some common tasks surrounding a Postgres server. In this tutorial, we’ll cover installing Postgres, creating new databases and users, backing up databases, and more! Let’s dig in! Continue reading “Common Postgres Tasks on CentOS 7”