3 Tips For Working With a MySQL Database!

Reading Time: 2 minutes

Pre-Flight Check

  • 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.

Continue reading “3 Tips For Working With a MySQL Database!”

How to Back Up And Restore MySQL Databases From The Command Line

Reading Time: 2 minutes

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.

Continue reading “How to Back Up And Restore MySQL Databases From The Command Line”

How To Delete Post Revisions using WP-CLI

Reading Time: 2 minutes

There 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:

cd html

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:

gzip sitebackup.sql

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.

How to Install Nextcloud 15 on Ubuntu 18.04

Reading Time: 2 minutes

Similar 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”

Troubleshooting: MySQL/MariaDB Error #1044 & #1045 Access Denied for User

Reading Time: < 1 minute

When 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”

How to Backup, Delete and Restore a PostgreSQL Database in CentOS 7 or Ubuntu 16

Reading Time: 5 minutes

Listing databases
Dump a database
Dumping all databases
Dump Grants
Delete or Drop a Database
Delete a Grant
Restore a Database
Restore Grant
Continue reading “How to Backup, Delete and Restore a PostgreSQL Database in CentOS 7 or Ubuntu 16”

Installing Redis on Ubuntu 16.04/18.04

Reading Time: 4 minutes

What is Redis? 

Redis or “REmote DIctionary Server” is defined as an open source, “key-value” database storage medium, which is additionally known as a data structure server. At its heart, Redis works with key-value pairs and stores data in a location that’s easily referenceable by two specific values. These key-value associations are usually a set of two linked data entries which are made up by a key, which is a unique identifier for a type of data and, the value, which can be either the particular data that is identified or, an indicator to the location of that data.

Continue reading “Installing Redis on Ubuntu 16.04/18.04”

8 WP-CLI Commands to Clean Up and Optimize your Site

Reading Time: 2 minutes

Want to clean up your WordPress site without having to add multiple plugins? By using WP-CLI, you can run many useful commands to helpfully clean up your database and elements related to your site. In this post, many of the most common tasks are covered:

Continue reading “8 WP-CLI Commands to Clean Up and Optimize your Site”

Troubleshooting Microsoft SQL Server Error 18456, Login failed for user

Reading Time: 4 minutes

Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause.

connect to server error

Solutions to Microsoft SQL Server Error 18456

Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for next steps. In this case, it is Microsoft SQL Server, Error: 18456.

Other times, we may only see “Microsoft SQL Server Error 18456” along with the severity and state number. On its own, a state number might not mean much, yet it can offer more details as to what is wrong and where to look next.

These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.

mssql errors and potential solutions

Step 1:  Log In with Remote Desktop

The troubleshooting and solutions require you to login to the server or at least be able to make a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio. The most common and easiest method is to connect directly to the server with a Remote Desktop Connection. If you need more information about Remote Desktop Connection, these Knowledge Base articles will help you get connected:

Step 2: Run Microsoft SQL Server Management

Once you are logged into the server, you’ll want to run Microsoft SQL Server Management Studio (SSMS). SSMS is the tool best suited to configure, manage, and administer MSSQL.

mssql server management

When you start SSMS, you will be asked to log in to the server. By default, most MSSQL servers have Windows Authentication enabled, meaning you must log in with the Windows Administrator or the account specified as the SQL Administrator when MSSQL was installed and configured.

connect to mssql server

In addition to Windows Authentication, MSSQL supports SQL Server Authentication. Depending on the version of MSSQL and how it was installed and configured, you may or may not have SQL Server Authentication enabled by default.

Step 3: Checking the Server Authentication Mode

Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MSSQL is set up to allow both Windows and SQL Authentication.

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Properties.

Next, click the Security page.

mssql security properties

If you find Windows Authentication is the only mode configured, this is the likely cause of Error 18456, Login failed for user ‘<username>’.

Setting the Server authentication mode to allow SQL Server and Windows Authentication, you will be able to login to MS-SQL with a SQL user and password or a Windows user and password. After making this change, you will need to restart the SQL Server service.

 

Step 4: Restart the SQL Service

In SSMS, right-click the Server Name at the top of the Object Explorer window and choose Restart to apply the new authentication mode settings.  

In the above example, Windows Authentication mode was the only mode configured, and the Error 18456 occurred because the user ‘sa’ is a SQL user and SQL Server Authentication was not permitted.

 

Step 5: Checking SQL User Permissions

As we check the SQL user permissions, we need to answer the following questions:

  • Is the user allowed to log in?
  • Does the user have a valid password set up?
  • Does the user have the needed permissions for access to the desired database?

In SSMS Object Explorer, expand Security, Logins. Locate the user that was failing to log in. A red x on the user indicates this user has login disabled.

smss login properties

To allow the user to login, right-click the user and choose Properties, then click the Status page. Enabling login for the user and click OK.

enable login user properties

After refreshing the list user logins, we can confirm the user no longer has a red x present. This should allow the user to log in. In this example, the SQL user ‘sa’ failed to log in because there was no permission to log in. 

Continuing with user troubleshooting, right-click the user and choose Properties, then click the General page. Here you can enter a new password and then enter the confirmation password. Click OK to save the new password. We set a new password for the user so that we are certain of the password when we attempt to log in.

sa login user properties

Step 6: Mapping the User to the Database

Our last step in troubleshooting a user is to check user mapping to verify the user has access to the desired database and to set or verify their role for the database. Right-click the user and choose Properties, then click the User Mapping page. Select the Database from the list of databases. From the database role memberships, select the desired/required memberships. Click OK.

map user to mssql database

In this example, we mapped the user ‘ProdX709’ to the database Production X709.2019 and granted them database role db_owner. In many cases, you only need a user to have db_datareader and db_datawriter roles to be able to read and write to the database.

 

In this troubleshooting article, we learned how to identify specifics of Error 18456 to help us track down the root cause of the issue. Still looking for support?  Our MSSQL database solutions come with assistance from our technical support team. Find out how our high-availability database can work for you!