One place for hosting & domains

      MySQL

      How To Troubleshoot Socket Errors in MySQL


      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.



      Source link

      How To Address Crashes in MySQL


      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.



      Source link

      Install and Manage MySQL Databases with Puppet Hiera on Ubuntu 18.04


      Updated by Linode Contributed by Linode

      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

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

      2. Familiarize yourself with our Getting Started guide and complete the steps for setting your Linode’s hostname and timezone.

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

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

      1. Install the puppetlabs-release-bionic repository to add the Puppet packages:

        wget https://apt.puppetlabs.com/puppet-release-bionic.deb
        sudo dpkg -i puppet-release-bionic.deb
        
      2. 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
        
      3. 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.

      1. Install the MySQL module:

        sudo puppet module install puppetlabs-mysql --version 7.0.0
        

        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:

      /etc/puppetlabs/code/environments/production/manifests/site.pp
      1
      
      include ::mysql::server

      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:

      /etc/puppetlabs/code/environments/production/hiera.yaml
      1
      2
      3
      4
      5
      6
      7
      
      ---
      version: 5
      hierarchy:
        - name: "Per-node data"
          path: "nodes/%{::trusted.certname}.yaml"
        - name: "Common data"
          path: "common.yaml"

      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:

      /etc/puppetlabs/code/environments/production/data/common.yaml
      1
      
      mysql::server::root_password: examplepassword

      Replace examplepassword with the secure password of your choice. Run Puppet to set up MySQL with default settings and the chosen root password:

      sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
      

      Puppet will output its progress before completing. To confirm MySQL has been configured properly, run a command:

      mysql -u root -p -e 'select version();'
      

      Enter the password and MySQL returns its version:

      +-------------------------+
      | version()               |
      +-------------------------+
      | 5.7.24-0ubuntu0.18.04.1 |
      +-------------------------+
      

      Define MySQL Resources

      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.

      1. 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
        
      2. 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:

        /etc/puppetlabs/code/environments/production/data/common.yaml
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        
        mysql::server::root_password: examplepassword
        mysql::server::databases:
          wordpress:
            ensure: present
        mysql::server::users:
          wpuser@localhost:
            ensure: present
            password_hash: '*E62D3F829F44A91CC231C76347712772B3B9DABC'
        mysql::server::grants:
          wpuser@localhost/wordpress.*:
            ensure: present
            privileges: ALL
            table: wordpress.*
            user: wpuser@localhost
      3. Re-run Puppet:

        sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
        
      4. 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.

      1. Modify hiera.yaml to contain the following:

        /etc/puppetlabs/code/environments/production/hiera.yaml
        1
        2
        3
        4
        5
        6
        7
        8
        
        ---
        version: 5
        hierarchy:
          - 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.

      2. Create the following YAML file:

        /etc/puppetlabs/code/environments/production/data/os/Debian.yaml
        1
        2
        3
        4
        5
        6
        7
        
        lookup_options:
          mysql::server::databases:
            merge: deep
        
        mysql::server::databases:
          ubuntu-backup:
            ensure: present

        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:

          sudo -i puppet apply -e 'include ::mysql::server'
          
      3. Run Puppet and observe the changes:

        sudo -i puppet apply /etc/puppetlabs/code/environments/production/manifests/site.pp
        
      4. Verify that the new database exists:

        mysql -u root -p -e 'show databases;'
        

        This includes the new ubuntu-backup database:

        +---------------------+
        | Database            |
        +---------------------+
        | information_schema  |
        | mysql               |
        | performance_schema  |
        | sys                 |
        | ubuntu-backup       |
        | wordpress           |
        +---------------------+
        

      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.



      Source link