Percona XtraDB установка, настройка и миграция

1.Требования

• Мимум 3 ноды, так как если отпадет одна нода и подключиться назад 2-я нода заблокируется и будет скидывать реплику на восстановленную ноду, т.е. останется активна только одна нода
• Только Linux, только хардкор!!
• Перкона работает только с MySQL InnoDB движком;
• Перед установкой вам необходимо удалить все существующие пакеты Mysql mysql-client, mysql-server, postfix...
• Ip адреса node1 10.1.9.162, node2 10.1.9.164, node3 10.1.9.164, node 4 10.1.9.165 Будем использовать 4 ноды.

2 Installation Procedure – процедура установки

Perkona это всего лишь модифицированная Mysql для возможности мультимастер репликации.

Итак, приступим к установке. Кстати, официальная документация по установке на debian системы на оф.сайте. Заранее убедитесь что у Вас не установлено никаких пакетов mysql в системе:

sudo apt-get purge mysql* -y

2.1.Установка percona 5.6 на debian 8 jessie

echo "deb https://repo.percona.com/apt jessie testing" >> sudo nano /etc/apt/sources.list
echo "deb-src https://repo.percona.com/apt jessie testing" >> sudo nano /etc/apt/sources.list
sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
sudo apt-get update
sudo apt-get install percona-xtradb-cluster-full-56 -y

Во время устаноки устанавливаем пароли для Mysql

3. Настройка НОД

Открываем порты на всех нодах. Для этого я устанавливаю пакет.

apt-get install iptables-persistent -y

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 4567:4568 -j ACCEPT
/sbin/iptables -A INPUT -i eth0 -p udp --destination-port 4567:4568 -j ACCEPT
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -i eth0 -p udp --destination-port 3306 -j ACCEPT
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 4444-j ACCEPT
/sbin/iptables -A INPUT -i eth0 -p udp --destination-port 4444 -j ACCEPT
Сохраним их

iptables-save > /etc/iptables/rules.v4

Проверяем наличие пакета NTP для того, чтобы время на серверах было синхронно настроено
apt-get install ntp -y
Добавляем строку server ntp.mobatime.ru

echo "server ntp.mobatime.ru" >> /etc/ntp.conf

3.1 Настройка ноды 1(стартовой)

Подправим конфиг /etc/mysql/my.cnf

Мой конфиг на нагруженную систему. Обратите внимание ври первом запуске первой ноды список всех нод с параметром

wsrep_cluster_address=gcomm://

пустой. Это нужно для инициализации кластера. Данное значение

wsrep_cluster_address=gcomm://

мы поменяем на

wsrep_cluster_address=gcomm://10.1.9.162,10.1.9.163,10.1.9.164,165

после запуска других нод кластера и сделаем

/etc/init.d/mysql restart

ЭТО ОЧЕНЬ ВАЖНО!!!
Итак просмотрим весь конфиг.

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# https://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 0.0.0.0
default-storage-engine         = InnoDB
collation-server                           = utf8_general_ci
init-connect                   = 'SET NAMES utf8'
character-set-server           = utf8
wait_timeout            = 25
interactive_timeout     = 25
net_write_timeout       = 20
net_read_timeout        = 20
lock_wait_timeout       = 20
connect_timeout = 300
delayed_insert_timeout = 300
explicit_defaults_for_timestamp = 1
log-slave-updates=1
#CLUSTER SETTINGS
wsrep_provider=/usr/lib/libgalera_smm.so
#wsrep_cluster_address=gcomm://
wsrep_cluster_address=gcomm://10.1.9.221,10.1.9.222,10.1.9.223,10.1.9.224
wsrep_provider_options="gcache.size=256M"
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog = 1
wsrep_node_address=10.1.9.222
wsrep_sst_method=xtrabackup-v2
wsrep_cluster_name=testdb
wsrep_node_name=test2
wsrep_sst_auth="sstuser:testpass"
wsrep_auto_increment_control=OFF
wsrep_provider_options="gcache.size = 5G; gcache.name = /var/mysql_m/gcache"
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 32M
thread_stack		= 192K
thread_cache_size       = 16384
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 1500
#table_cache            = 64
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/mysql_m/log/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/mysql_m/log/error.log
#
# Here you can see queries with especially long duration
slow_query_log_file = /var/mysql_m/log/mysql-slow.log
#slow_query_log      = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
log_bin			= /var/mysql_m/bin/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 1024M
binlog_do_db		= test
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# 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
[mysqldump]
quick
quote-names
max_allowed_packet	= 500M
[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
[isamchk]
key_buffer		= 16M
# CACHES AND LIMITS #
tmp-table-size                 = 1G #32M
max-heap-table-size            = 1G #32M
query-cache-type               = OFF
query-cache-size               = 32M
query_cache_limit              = 200M
max-connections                = 1500
thread-cache-size              = 500
open-files-limit               = 900000
table-definition-cache         = 4096
table-open-cache               = 10000
max_tmp_tables                  = 10000
join_buffer_size                = 4M
wsrep_provider_options="gcache.size=256M"
# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 12G
innodb_buffer_pool_instances = 6
innodb_flush_log_at_trx_commit = 0
# LOGGING #
server-id = 222
binlog_format                   = ROW
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Создадим каталог для mysql – логов,bin и др(так как это у нас указано в конфиге).

mkdir -p /home/mysql/bin
mkdir -p /home/mysql/log
mkdir -p /home/mysql/relay
chown -R -v mysql:mysql /home/mysql
/etc/init.d/mysql bootstrap-pxc

Создадим пользователя и дадим ему пароль(в mysql ), назначим права;

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'Cbrt32456';
GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

или так

mysql -u root -e "CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'Cbrt32456';"
mysql -u root -e "GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';"

FLUSH PRIVILEGES;

Идем далее. Добавим экстра функции

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

Так же необходимо отключить скрипт автозапуска проверки таблиц в debian(НА ВСЕХ НОДАХ)

touch /etc/mysql/NO–DEBIAN–START

3.2 Нода 2

Редактируем /etc/mysql/my.cnf. Он очень похож на первый, различия в том, что

wsrep_cluster_address=gcomm://10.1.9.162,10.1.9.163,10.1.9.164,165

указан сразу с адресами всех нод!!! и в параметре

wsrep_node_address=10.1.9.163

указан ip нашей 2-ой ноды. В параметре

wsrep_node_name=xtradb2

xtradb будет 2, так как это наша вторая нода. В других нодах параметры меняем аналогичным образом(wsrep_node_address=равен ip ноды,wsrep_node_name=xtradb- равен порядковуму номеру ноды)

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# https://dev.mysql.com/doc/mysql/en/server-system-variables.html
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 0.0.0.0
#
### Galera library.
wsrep_provider=/usr/lib/libgalera_smm.so
log-slave-updates=1
### IP addresses of your two nodes.
wsrep_cluster_address=gcomm://10.1.9.162,10.1.9.163,10.1.9.164,165
### This will not work unless binlog is formatted to ROW.
binlog_format=ROW
### This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
### Node #1 address (The node you are ssh`d into now.)
wsrep_node_address=10.1.9.163
### SST Method
wsrep_sst_method=xtrabackup-v2
### Cluster name
wsrep_cluster_name=xtradb
### Node Name, in this case we will just call it xtradb1
wsrep_node_name=xtradb2
### Authentication, REMEMBER THIS.
wsrep_sst_auth="sstuser:Cbrt32456"
wsrep_provider_options="gcache.size = 5G; gcache.name = /var/mysql_m/gcache"
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 32M
thread_stack		= 192K
thread_cache_size       = 32M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 1500
#table_cache            = 64
thread_concurrency     = 48
#
# * Query Cache Configuration
#
query_cache_limit	= 32M
query_cache_size        = 200M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /home/mysql/log/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /home/mysql/log/error.log
#
# Here you can see queries with especially long duration
slow_query_log_file = /home/mysql/log/mysql-slow.log
#slow_query_log      = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
log_bin			= /home/mysql/bin/mysql-bin.log
expire_logs_days	= 3
max_binlog_size         = 1024M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# 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
[mysqldump]
quick
quote-names
max_allowed_packet	= 500M
[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
[isamchk]
key_buffer		= 16M
# CACHES AND LIMITS #
tmp-table-size                 = 1G #32M
max-heap-table-size            = 1G #32M
query-cache-type               = ON
query-cache-size               = 32M
query_cache_limit              = 200M
max-connections                = 1500
thread-cache-size              = 500
open-files-limit               = 900000
table-definition-cache         = 4096
table-open-cache               = 10000
max_tmp_tables                  = 10000
join_buffer_size                = 4M
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Сейв.

Так же создадим каталог для mysql – логов,bin и др(так как это у нас указано в конфиге).

mkdir -p /home/mysql/bin
mkdir -p /home/mysql/log
mkdir -p /home/mysql/relay
chown -R -v mysql:mysql /home/mysql
/etc/init.d/mysql start

5.3 Percona Database Configuration

These steps configure the database section of the gerrit config file and must be followed once you have completed the installation of the Percona XtraDB cluster with Gerrit.

Procedure

When installing Gerrit with Percona XTRADB using an ‘n-nodes’ configuration, you need to:

1. Create the reviewdb database only on one node (the other nodes will replicate this).
2. Install vanilla gerrit on that node or on a node that connects to that database node.
3. Proceed with the standard installation of GerritMS.
4. Usually in a GerritMS-Percona configuration, each Gerrit node connects to an individual Percona XtraDB node, sitting maybe on the same host as Gerrit. So in the gerrit.config property file, in the dabase section, you will find localhost as the hostname to connect to.
5. Then, if you want, you can maximise the database access speed from Gerrit to Percona XtraDB by using connection pooling. For this you need to:
   • edit the etc/gerrit.config file and
   • add or replace this piece of configuration in the database section:

     [database]
     type = mysql
     hostname = localhost
     database = reviewdb
     username = gerrit
     connectionPool = true
     poolLimit = 100
     poolMinIdle = 50
     	

     Depending on the load of the machine you can raise or lower the poolLimit or the poolMinIdle properties. Just keep in mind that, since, as usual, the default max number of connections for a MySQL server database is 151, you need to raise that number if you need to set the poolLimit to a value close or higher than 150. If you need to raise the max number of connection to MySQL (Percona) server, the you have to modify the my.cnf file and add something like:

     
     [mysqld]
     ...
     open_files_limit = 8192  # only if you need to raise the max number of connections to MySQL. Not needed otherwise
     max_connections = 1000   # only if you need to raise the max number of connections to MySQL. Not needed otherwise
     ...
     
     

6. The last step is to modify the Git MultiSite configuration file (/opt/wandisco/git-multisite/replicator/properties/application.properties) for each node that will access a local master Percona database. Replace the following properties, or add them to the bottom of the file:

   gerrit.db.slavemode.sleepTime=0
   gerrit.db.mastermaster.retryOnDeadLocks=true

Note: Since Percona XtraDB cluster is based on MySQL server, the configuration is the same as the one for MySQL server.

5.4 Миграция с Mysql на Percona XtraDB

Требования

• Нам надо будет создать дамп базы данных с помощью утилиты mysqldump
• Вам надо учесть один момент, если ваша база данных не и использует InnoDB, то вам вначале необходимо выполнить еще один шаг, который трансформирует вашу базу данных из “ENGINE=MyISAM”‘ в  “ENGINE=InnoDB”.

Процедура миграции

1.  Делаем дамп базы данных:

   $ mysqldump -u root -pXXXXXX newsb1 > newdb1.dmp

2.  Если вам необходимо модифицировать дамп базы данных, то сделайте backup до этого.
3.  Установите и настройте Percona XtraDb(до этого удалив mysql)
4. • Импортируйте старую базу данных на наш кластер Percona XtraDB

      mysql -u root -pXXXXXX newdb1 < newdb1.dmp

   •  На другие ноды автоматически произойдет репликация, и вам не надо будет производить процедуру импорта на каждую из них.

6. Решение проблем PERCONA CLUSTER:

Проблема1 WSREP_SST: [INFO] Waiting for SST streaming to complete! При подключении ноды донора и старте mysql

Решение. Удаляем папку с .sst –

sudo rm -r /var/lib/mysql/.sst

sudo /etc/init.d/mysql start

P.S. Может понадоиться повторный запуск, пока нода реплика не догонит донора.

Проблема2  MySQL PID not found, pid_file detected/guessed: /var/run/mysqld/mysqld.pid

Решение Создаем Pid файл и даем ему права mysql

touch /var/run/mysqld/mysqld.pid && chown mysql:mysql /var/run/mysqld/mysqld.pid

sudo /etc/init.d/mysql star

Проблема3 WSREP: SST failed: 1 (Operation not permitted)

Решение. Я решил данную проблему переустановкой percona cluster и

Первое. Я изменил в конфиге следующие строки:

wsrep_cluster_name=xtradb на wsrep_cluster_name=xtradb2
#wsrep_cluster_address=gcomm://10.1.9.162,10.1.9.163,10.1.9.164,165  - закоментировал.

Затем сделал старт.
Подождал пока запустится.
Затем вернул всё обратно и подождал пока нода догонит реплики(надо время). Всё – можно пользовать.

Оф доки Percona по миграции https://www.percona.com/doc/percona-server/5.6/upgrading_guide_55_56.html

Как ускорить импорт данных

Установка и настройка proxysql – балансировщика записи-чтения для percona xtradb cluster

apt-get install proxysql

Запуск proxysql

/usr/bin/proxysql-admin –proxysql-user=root –proxysql-password=GtnhCnjksgbyVbybcnh –proxysql-port=6032 –proxysql-host=10.1.9.205 –cluster-user=sstuser –cluster-password=Cbrt205 –cluster-port=3306 –cluster-host=10.1.9.205 –galera-check-interval=1000 –enable –mode=’loadbal’ –adduser