MySQL Server configuration - ghomem/legacy_puppet_infrastructure GitHub Wiki

MySQL Server configuration

This class manages a MySQL server, specifically the MariaDB flavour that is available on a Ubuntu system. The server instance is configured with a root user that can only connect from localhost and two other users that can connect from any host if using SSL. These other users use the ed25519 authentication plugin, requiring an ed25519 password hash, rather than the password, to be used in the configuration.

The class offers a high degree of customization by allowing the user to specify generic MySQL server configurations and configurations that are specific to MariaDB, and also by generating or using pre-existing SSL certificates.

Parameters of puppet_infrastructure::mysql_server

  • $root_pw: The password for the MySQL root user. Required.
  • $rw_user: The username for the read-write user. Required.
  • $rw_hash: The ed25519 password hash for the read-write user. Required.
  • $ro_user: The username for the read-only user. Required.
  • $ro_hash: The ed25519 password hash for the read-only user. Required.
  • $custom_mysqld_configs: A hash of additional MySQL server configuration settings. Optional.
  • $custom_mariadb_configs: A hash of additional MariaDB server configuration settings. Optional.
  • $generate_certificates: A boolean flag to indicate whether SSL certificates should be generated. If false, it will use existing certificates. Default: true.

Getting started

The simplest node declaration you can have to just get started with a MySQL server is something like this:

node 'mysql-server01' {

  # This is an initial node declaration to get started with a MySQL dabase server

  # Basic declarations
  include puppet_infrastructure::node_base
  include passwd_common

  # To make the variable below works, add this line to
  # /etc/puppetlabs/code/environments/production/data/common.yaml:
  #
  # db::mysql::root_pw: 'Insert here a strong password for the root user'
  #
  # This will restrict the database access to the given user and password

  $root_pw = lookup('db::mysql::root_pw')

  # The MySQL database server
  class { 'puppet_infrastructure::mysql_server':
    root_pw => $root_pw,
  }

}

After that you will have a MySQL server only accesible in the localhost, from now on if you need it, you could use:

mysql -u root -p -e 'CREATE FUNCTION ed25519_password RETURNS STRING SONAME "auth_ed25519.so";' # And insert the password you set for the root user when prompted
mysql -u root -p -e 'SELECT ed25519_password("reallyGoodPassword");'

to generate the ed25519 hashes for passwords.

Usage

A simple node declaration with this class would look like this:

node 'mysql-server02' {

  # Basic declarations
  include puppet_infrastructure::node_base
  include passwd_common

  # To make the variable below works, add this line to
  # /etc/puppetlabs/code/environments/production/data/common.yaml:
  #
  # db::mysql::root_pw: 'Insert here a strong password for the root user'
  #
  # This will restrict the database access to the given user and password

  $root_pw = lookup('db::mysql::root_pw')

  # the users created here are MySQL server users not pre-assgined to any db
  # they are for the team to administer the server
  # the ed25519 hashes can be generated with this command
  # mysql -u root -p -e 'SELECT ed25519_password("reallyGoodPassword");'
  $my_ro_hash = 'this is the mariadb ed25519 hash of a read only operations user'
  $my_rw_hash = 'this is the mariadb ed25519 hash of a read write operations user'

  class { 'puppet_infrastructure::mysql_server': 
    root_pw => $root_pw,
    rw_user => 'userrw',
    rw_hash => $my_rw_hash,
    ro_user => 'userro',
    ro_hash => $my_ro_hash,
  }

}

And this would be a node declaration with this class specifying custom configurations:

node 'data' {

  # Basic declarations
  include puppet_infrastructure::node_base
  include passwd_common

  # To make the variable below works, add this line to
  # /etc/puppetlabs/code/environments/production/data/common.yaml:
  #
  # db::mysql::root_pw: 'Insert here a strong password for the root user'
  #
  # This will restrict the database access to the given user and password

  $root_pw = lookup('db::mysql::root_pw')

  # lower memory configs because this is a staging machine
  $custom_mysqld_configs = {
      'innodb_buffer_pool_size' => '1G',
      'tmp_table_size'          => '32M',
      'max_heap_table_size'     => '32M',
      'query_cache_type'        => '1',
      'query_cache_size'        => '32M',
      'datadir'                 => '/storage/mysql',
      'sql_mode'                => 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION',
  }

  # the users created here are MySQL server users not pre-assgined to any db
  # they are for the team to administer the server
  # the ed25519 hashes can be generated with this command
  # mysql -u root -p -e 'SELECT ed25519_password("reallyGoodPassword");'
  $my_ro_hash = 'this is the mariadb ed25519 hash of a read only operations user'
  $my_rw_hash = 'this is the mariadb ed25519 hash of a read write operations user'

  # the users created here are MySQL server users not pre-assgined to any db
  # they are for the team to administer the server
  class { 'puppet_infrastructure::mysql_server':
    root_pw               => $rootpw,
    rw_user               => 'userrw',
    rw_hash               => $my_rw_hash,
    ro_user               => 'userro',
    ro_hash               => $my_ro_hash,
    custom_mysqld_configs => $custom_mysqld_configs,
    generate_certificates => false,
  }

}

Allowing Remote Database Access

If you need to configure a database that can be accessed remotely by a client, you can achieve this by including a declaration similar to the following in your server node declaration:

  $dbuser = 'remote_username' # Specify the remote username
  $dbpass = 'remote_user_password' # Specify the password for the remote user
  $host   = 'y.y.y.y' # The IP address of the host that will be granted access to this database

  mysql::db { 'database_name': # Specify the name of the database
    user     => $dbuser, # The username for the database
    password => $dbpass, # The password for the database user
    host     => $host,
    grant    => [ 'SELECT', 'SHOW VIEW' ], # Specify the permissions to grant this user
    require  => Class['puppet_infrastructure::mysql_server'],
  }

  # Create a firewall rule for this host to allow incoming connections to the MySQL server
  firewall { '200 accept mysql client': proto  => 'tcp', dport  => 3306, action => 'accept', source => $host, }

SSL Certificates

The puppet_infrastructure::mysql_server class supports both the generation of new SSL certificates and the use of pre-existing ones. This is controlled by the $generate_certificates parameter.

Auto generating SSL Certificates

If the $generate_certificates variable is not specified or set to true, the puppet_infrastructure::mysql_server class will generate and self-sign the SSL certificates automatically. They will be generated at the first puppet agent execution and stored at /etc/mysql/certificates/.

Using Pre-existing SSL Certificates

If you have your own SSL certificates, you can set the $generate_certificates variable to false. However, you must ensure that the certificates are correctly located and named on your Puppet master server.

The certificates should be placed in the /etc/puppetlabs/puppet/extra_files/ssl/db directory. This directory should have the following structure:

/etc/puppetlabs/puppet/extra_files/ssl/db/server-cert.pem
/etc/puppetlabs/puppet/extra_files/ssl/db/server-key.pem
/etc/puppetlabs/puppet/extra_files/ssl/db/ca.pem

Ensure that the db directory has puppet:puppet ownership:

chown puppet:puppet /etc/puppetlabs/puppet/extra_files/ssl/db

Also, the SSL files inside the db directory must have the correct permissions, which is 0644. You can set the permissions using:

chmod 0644 /etc/puppetlabs/puppet/extra_files/ssl/db/*

Client configuration

Configuring an SSL client (ex: python application, metabase, etc) is out of the scope of this document but we note that the client will need the pre-existing or autogenerated server-cert.pem file, apart from a user that can access the specific database the client wants to connect to. For a test of this functionality, you can do the following:

  1. Deploy a sample client and, configure it as a node and write for it a node declaration like the following:
node 'mysql-client01' {

  include puppet_infrastructure::node_base
  include passwd_common

  package { 'mariadb-client':
    ensure => 'installed',
  }

}
  1. After running the puppet agent on the node, you must copy the file located at /etc/mysql/certificates/server-cert.pem on the server to the client (you can use scp or sftp), ideally under the /opt/puppet-infrastructure/etc directory and give it the right permissions and ownership:
sudo chown root:root /opt/puppet-infrastructure/etc/server-cert.pem
sudo chmod 644 /opt/puppet-infrastructure/etc/server-cert.pem
  1. Try connecting to the SQL server with the following command:
mysql -u remoteuser -h mysqlserver-host -p --ssl-ca=/opt/puppet-infrastructure/etc/server-cert.pem

when prompted insert the correct password for the chosen user and you should be connected to the database remotely.

Note: Please keep in mind that You will need to configure the master with a node declaration that has a firewall rule for the client host and that has the ed25519 users or a remote database declared with a remote user. Please refer to the examples above.

Useful references