MySQL manages connections to the database server through the use of a socket file, a special kind of file that facilitates communications between different processes. The MySQL server’s socket file is named mysqld.sock and on Ubuntu systems it’s usually stored in the /var/run/mysqld/ directory. This file is created by the MySQL service automatically.
Sometimes, changes to your system or your MySQL configuration can result in MySQL being unable to read the socket file, preventing you from gaining access to your databases. The most common socket error looks like this:
Output
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
There are a few reasons why this error may occur, and a few potential ways to resolve it.
One common cause of this error is that the MySQL service is stopped or did not start to begin with, meaning that it was unable to create the socket file in the first place. To find out if this is the reason you’re seeing this error, try starting the service with systemctl:
sudo systemctl start mysql
Then try accessing the MySQL prompt again. If you still receive the socket error, double check the location where your MySQL installation is looking for the socket file. This information can be found in the mysqld.cnf file:
sudo nano /etc/mysql/mysql.conf.d/mysql.cnf
Look for the socket parameter in the [mysqld] section of this file. It will look like this:
/etc/mysql/mysql.conf.d/mysqld.cnf
. . .
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
. . .
Close this file, then ensure that the mysqld.sock file exists by running an ls command on the directory where MySQL expects to find it:
If the socket file exists, you will see it in this command's output:
Output
. .. mysqld.pid mysqld.sock mysqld.sock.lock
If the file does not exist, the reason may be that MySQL is trying to create it, but does not have adequate permissions to do so. You can ensure that the correct permissions are in place by changing the directory's ownership to the mysql user and group:
sudo chown mysql:mysql /var/run/mysqld/
Then ensure that the mysql user has the appropriate permissions over the directory. Setting these to 775 will work in most cases:
sudo chmod -R 755 /var/run/mysqld/
Finally, restart the MySQL service so it can attempt to create the socket file again:
sudo systemctl restart mysql
Then try accessing the MySQL prompt once again. If you still encounter the socket error, there's likely a deeper issue with your MySQL instance, in which case you should review the error log to see if it can provide any clues.
The most common cause of crashes in MySQL is that it stopped or failed to start due to insufficient memory. To check this, you will need to review the MySQL error log after a crash.
First, attempt to start the MySQL server by typing:
sudo systemctl start mysql
Then review the error logs to see what’s causing MySQL to crash. You can use less to review your logs, one page at a time:
sudo less /var/log/mysql/error.log
Some common messages that would indicate an insufficient amount of memory are Out of memory or mmap can't allocate.
Potential solutions to an inadequate amount of memory are:
Optimizing your MySQL configuration. A great open-source tool for this is MySQLtuner. Running the MySQLtuner script will output a set of recommended adjustments to your MySQL configuration file (mysqld.cnf). Note that the longer your server has been running before using MySQLTuner, the more accurate its suggestions will be. To get a memory usage estimate of both your current settings and those proposed by MySQLTimer, use this MySQL Calculator.
Reducing your web application’s reliance on MySQL for page loads. This can usually be done by adding static caching to your application. Examples for this include Joomla, which has caching as a built-in feature that can be enabled, and WP Super Cache, a WordPress plugin that adds this kind of functionality.
Upgrading to a larger VPS. At minimum, we recommend a server with at least 1GB of RAM for any server using a MySQL database, but the size and type of your data can significantly affect memory requirements.
Take note that even though upgrading your server is a potential solution, it’s only recommended after you investigate and weigh all of your other options. An upgraded server with more resources will likewise cost more money, so you should only go through with resizing if it truly ends up being your best option. Also note that the MySQL documentation includes a number of other suggestions for diagnosing and preventing crashes.
Use promo code DOCS10 for $10 credit on a new account.
Puppet is a configuration management system that helps simplify the use and deployment of different types of software, making system administration more reliable and replicable. In this guide, we use Puppet to manage an installation of MySQL, a popular relational database used for applications such as WordPress, Ruby on Rails, and others. Hiera is a method of defining configuration values that Puppet will use to simplify MySQL configuration.
In this guide, you’ll use Puppet to deploy modules on your server. At the end, you will have MySQL installed, configured, and ready to use for a variety of applications that require a database backend.
Note
This guide is written for a non-root user. Commands that require elevated privileges are prefixed with sudo. If you’re not familiar with the sudo command, see the Users and Groups guide.
Before You Begin
A Linode 1GB plan should be sufficient to run MySQL. Consider using a larger plan if you plan to use MySQL heavily, or for more than just a simple personal website.
Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.
This guide will use sudo wherever possible. Complete the sections of our Securing Your Server to create a standard user account, harden SSH access and remove unnecessary network services.
Update your system:
sudo apt-get update && sudo apt-get upgrade
Install and Configure Puppet
Follow these steps to set up Puppet for single-host, local-only deployment. If you need to configure more than one server or to deploy a Puppet master, follow our multi-server Puppet guide.
Install the Puppet Package
Install the puppetlabs-release-bionic repository to add the Puppet packages:
Update the apt package index to make the Puppet Labs repository packages available, then install Puppet. This will install the puppet-agent package, which provides the puppet executable within in a compatible Ruby environment:
sudo apt update && sudo apt install puppet-agent
Confirm the version of Puppet installed:
puppet --version
At the time of writing, the Puppet version is 6.1.0.
Install the Puppet MySQL Module
Puppet Forge is a collection of modules that aid in the installation of different types of software. The MySQL module handles the installation and configuration of MySQL without you needing to manage various configuration files and services by hand.
This will install the mysql module into the default path: /etc/puppetlabs/code/environments/production/modules/.
Puppet MySQL Manifest
This guide uses a Puppet manifest to provide Puppet with installation and configuration instructions. Alternatively, you can configure a Puppet master.
While the entirety of a Puppet manifest can contain the desired configuration for a host, values for Puppet classes or types can also be defined in a Hiera configuration file to simplify writing Puppet manifests in most cases. In this example, the mysql::server class parameters will be defined in Hiera, but the class must first be applied to the host.
To apply the mysql::server class to all hosts by default, create the following Puppet manifest:
Note that site.pp is the default manifest file. Without a qualifying node { .. } line, this applies the class to any host applying the manifest. Puppet now knows to apply the mysql::server class, but still needs values for resources like databases, users, and other settings. Configure Hiera to provide these values in the next section.
Install and Configure Puppet Hiera
To understand how Hiera works, consider this excerpt from the default hiera.yaml file:
This Hiera configuration instructs Puppet to accept variable values from nodes/%{::trusted.certname}.yaml. If your Linode’s hostname is examplehostname, define a file called nodes/examplehostname.yaml). Any variables found in YAML files higher in the hierarchy are preferred, while any variable names that do not exist in those files will fall-through to files lower in the hierarchy (in this example, common.yaml).
The following configuration will define Puppet variables in common.yaml to inject variables into the mysql::server class.
Initial Hiera Configuration
Hiera configuration files are formatted as yaml, with keys defining the Puppet parameters to inject their associated values. To get started, set the MySQL root password. The following example of a Puppet manifest is one way to control this password:
example.pp
1
2
3
class {'::mysql::server':
root_password=> 'examplepassword',
}
We can also define the root password with the following Hiera configuration file. Create the following YAML file and note how the root_password parameter is defined as Hiera yaml:
Using Hiera, we can define the rest of the MySQL configuration entirely in yaml. The following steps will create a database and user for use in a WordPress installation.
Create a pre-hashed MySQL password. Replace the password wordpresspassword in this example, and when prompted for a the root MySQL password, use the first root password chosen in the previous section to authenticate. Note the string starting with a * that the command returns for Step 2:
mysql -u root -p -NBe 'select password("wordpresspassword")'
*E62D3F829F44A91CC231C76347712772B3B9DABC
With the MySQL password hash ready, we can define Hiera values. The following YAML defines parameters to create a database called wordpress and a user named wpuser that has permission to connect from localhost. The YAML also defines a GRANT allowing wpuser to operate on the wordpress database with ALL permissions:
The wpuser should now be able to connect to the wordpress database. To verify, connect to the MySQL daemon as the user wpuser to the wordpress database:
mysql -u wpuser -p wordpress
After you enter the password for wpuser, exit the MySQL prompt:
exit
Add Hierarchies for Specific Environments
Additional configurations can be added that will only be applied to specific environments. For example, backup jobs may only be applied for hosts in a certain region, or specific databases can be created in a particular deployment.
In the following example, Puppet will configure the MySQL server with one additional database, but only if that server’s distribution is Debian-based.
---version:5hierarchy:-name:"Per OS Family"path:"os/%{facts.os.family}.yaml"-name:"Other YAML hierarchy levels"paths:-"common.yaml"
This change instructs Hiera to look for Puppet parameters first in "os/%{facts.os.family}.yaml" and then in common.yaml. The first, fact-based element of the hierarchy is dynamic, and dependent upon the host that Puppet and Hiera control. In this Ubuntu-based example, Hiera will look for Debian.yaml in the os folder, while on a distribution such as CentOS, the file RedHat.yaml will automatically be referenced instead.
Though similar to the common.yaml file defined in previous steps, this file will add the ubuntu-backup database only on Debian-based hosts (like Ubuntu). In addition, the lookup_options setting ensures that the mysql::server:databases parameter is merged between Debian.yaml and common.yaml so that all databases are managed. Without lookup_options set to deeply merge these hashes, only the most specific hierarchy file will be applied to the host, in this case, Debian.yaml.
Alternatively, because our Puppet manifest is short, we can test the same command using the -e flag to apply an inline manifest:
Congratulations! You can now control your Puppet configuration via highly configurable Hiera definitions.
More Information
You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.
Find answers, ask questions, and help others.
This guide is published under a CC BY-ND 4.0 license.