Percona-Ubuntu-16.04.md

Percona / Ubuntu 16.04

Installing Percona Server from Percona apt repository

Fetch the repository packages from Percona web:

wget -c https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb

Apt-Pinning the packages

In some cases you might need to “pin” the selected packages to avoid the upgrades from the distribution repositories. You’ll need to make a new file /etc/apt/preferences.d/00percona.pref

sudo vim /etc/apt/preferences.d/00percona.pref

and add the following lines in it:

Package: *
Pin: release o=Percona Development Team
Pin-Priority: 1001

Install the downloaded package with dpkg.

sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb

Install Prcona server and Percona tools

sudo apt-get update && sudo apt-get install percona-server-server-5.7 percona-toolkit percona-xtrabackup

During installation set the root password for MySQL.

Setting up MySQL

Secure MySQL installation

mysql_secure_installation

Enable percona extra features

mysql -uroot -p -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -uroot -p -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -uroot -p -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

Check MySQL variables

mysqladmin -uroot -p variables | less

Set max file limits

Edit /etc/security/limits.conf

sudo vim /etc/security/limits.conf

add the following lines to the file:

mysql soft nofile 65535
mysql hard nofile 65535

Edit /lib/systemd/system/mysql.service

sudo vim /lib/systemd/system/mysql.service

Add the following line to section [Service]

LimitNOFILE=65535

Restart services:

sudo systemctl daemon-reload
sudo systemctl restart mysql.service

Check limits:

mysql -u root -p
show variables like 'open_files_limit';

PerconaDB config

Create or replace files in /etc/mysql/percona-server.conf.d/

client.cnf

[client]

port                               = 3306
socket                             = /var/run/mysqld/mysqld.sock
default-character-set              = utf8mb4

mysql.cnf

[mysql]

port                               = 3306
socket                             = /var/run/mysqld/mysqld.sock
default-character-set              = utf8mb4

mysqld.cnf

[mysqld]

# GENERAL #
user                               = mysql
default_storage_engine             = InnoDB
default_tmp_storage_engine         = InnoDB
socket                             = /var/run/mysqld/mysqld.sock
pid_file                           = /var/run/mysqld/mysqld.pid
port                               = 3306
basedir                            = /usr
datadir                            = /var/lib/mysql
tmpdir                             = /tmp
lc_messages_dir                    = /usr/share/mysql
explicit_defaults_for_timestamp    = ON
skip_external_locking              = ON

# CHARACTER SETS
character_set_server               = utf8mb4
collation_server                   = utf8mb4_general_ci

# MyISAM #
key_buffer_size                    = 32M

# SAFETY #
max_allowed_packet                 = 16M
max_connect_errors                 = 100000
skip_name_resolve                  = ON
sql_mode                           = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE
sysdate-is-now
innodb                             = FORCE
innodb_strict_mode                 = ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links                     = 0

# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind_address                       = 127.0.0.1

# BINARY LOGGING #
server_id                          = 0
log_bin                            = /var/lib/mysql/mysql-bin
expire_logs_days                   = 2
sync_binlog                        = 1
max_binlog_files                   = 20
max_binlog_size                    = 128M

# CACHES AND LIMITS #
tmp_table_size                     = 32M
max_heap_table_size                = 32M
query_cache_type                   = 0
query_cache_size                   = 0
max_connections                    = 500
thread_cache_size                  = 50
open_files_limit                   = 65535
table_definition_cache             = 4096
table_open_cache                   = 4096

# INNODB #
innodb_flush_method                = O_DIRECT
innodb_log_files_in_group          = 2
innodb_log_file_size               = 128M
innodb_log_buffer_size             = 16MB
innodb_flush_log_at_trx_commit     = 1
innodb_file_per_table              = 1
innodb_buffer_pool_size            = 1G
# If you run into InnoDB tablespace corruption, setting this to a nonzero
# value will likely help you to dump your tables. Start from value 1 and
# increase it up to 6 until you're able to dump the table successfully.
#innodb_force_recovery=1

# LOGGING #
log_error                          = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes      = ON
slow_query_log                     = ON
slow_query_log_file                = /var/lib/mysql/mysql-slow
max_slowlog_files                  = 2
max_slowlog_size                   = 128M

mysqld_safe.cnf

[mysqld_safe]
pid-file                           = /var/run/mysqld/mysqld.pid
socket                             = /var/run/mysqld/mysqld.sock
nice                               = 0
default-character-set              = utf8mb4

Restart MySQL service

sudo systemctl restart mysql.service