startup procedure cluster mysql

startup primary node01

Turn on the MySQL primary server node01

When connecting node01 MySQL does not start. Try restarting the MySQL service

/etc/init.d/mysql restart

If the MySQL service does not start see the logs to see the startup error

systemctl status mariadb.service -l

[ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1380: Failed to open channel ‘galera’ at ‘gcomm://192.168.0.1,192.168.0.2’: -110 (Connection timed out)

The above error message indicates that it is not possible to connect to the secondary node. The node01 should be started as the primary node, then see if ports 3306 and 4567 are listening

galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1.

The error message above indicates that node01 was not the last one to leave the cluster, it will be necessary to force its initialization by changing the parameter safe_to_bootstrap to 1

vi /var/lib/mysql/grastate.dat
  
safe_to_bootstrap: 1

After changing the above file start the node as primary, the secondary node will re-synchronize with the primary, then see if ports 3306 and 4567 are listening

galera_new_cluster
netstat -nlp | grep mysqld

[ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -98 (Address already in use)

The above error indicates that one of the mysql services is running. Kill mysql services before start the mysql service. See if ports 3306 and 4567 are listening

/etc/init.d/mysql stop
pkill -9 mysqld
galera_new_cluster
netstat -nlp | grep mysqld

startup secondary node02

Restart MySQL, see if port 4567 is listening, after node02 synchronizes with node01 port 3306 is listening too

/etc/init.d/mysql restart
netstat -nlp | grep mysqld

*Upon restarting mysql on node02 the data will be synchronized with node01 using service running on port 4567 (rsync). Real-time synchronization can be seen in the /var/log/messages file

tail -f /var/log/messages

amount of concurrent connections mysql

show the current value of current connections on mysql

SHOW VARIABLES LIKE 'max_connections';

to change the value of concurrent mysql connections

SET GLOBAL max_connections=500;

to change permanently the value of concurrent mysql connections, under the [mysqld] section add the following line:

vi /etc/my.cnf.d/server.cnf

[mysqld]
max_connections = 500

configurar rede linux com comando ip

Listar interfaces de rede

ip addr

Listar IPs IPV4 das interfaces de rede

ip -4 addr

Configurar IP a interface de rede

ip addr add 192.168.0.7/24 dev eth0

Habilitar interface de rede

ip link set eth0 up

Desabilitar interface de rede

ip link set eth0 down

Remover IP de interface de rede

ip addr del 192.168.0.7/24 dev eth0

Ver tabela de roteamento

ip route

Adicionar default gateway

ip route add default via 192.168.0.254

Adicionar rota de rede

ip route add 10.10.3.0/24 via 192.168.5.100 dev eth0

Remover rota de rede

ip route del 10.10.3.0/24

Dominios Virtuais Apache2

Crie o arquivo de configuração do dominio virtual

vi /etc/httpd/conf.d/wp.conf
<VirtualHost 192.168.0.3:80>
  DocumentRoot /var/www/html/wp/
  ServerName wp.nickollas.com
  ServerAlias wordpress.nickollas.com
  ServerAdmin contato@nickollas.com
  ErrorLog /var/log/httpd/wp.nickollas.com-error.log
  CustomLog /var/log/httpd/wp.nickollas.com-access.log combined
</VirtualHost>

Reinicie o apache

apachectl restart

Adicionar entrada no ndjbdns

1 – Entre no diretorio de configuracao do ndjbdns

cd /etc/ndjbdns/

2 – Adicione a nova entrada de DNS no fim do arquivo:

vi /etc/ndjbdns/data

=apache.dominio.com:192.168.100.149:86400

3 – Regere o binario com o a nova configuracao de DNS

tinydns-data

4 – Reinicie os servicos de DNS

service dnscache restart
service tinydns restart

Teste

nslookup apache.dominio.com

Cluster MariaDB/MySQL CentOS 7

GALERA não funciona com ENGINE MyISAM, por padrão usa InnoDB.

Listar engine de todas tabelas de todas dabases

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES;

Listar engine de todas tabelas de uma unica database

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='minhaDatabase';

Listar todas tabelas com engine MyISAM de todas databases

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE ENGINE='MyISAM';

Para alterar a engine de usa table use:

ALTER TABLE nomeTabela ENGINE=InnoDB;

1 – Crie o arquivo /etc/yum.repos.d/MariaDB.repo de acordo com o link oficial:

# MariaDB 10.1 CentOS repository list - created 2016-07-28 10:09 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2 – Instale o MariaDB e o pacote para o cluster “galera”

yum install MariaDB-server MariaDB-client galera

CONFIGURAÇÃO NODE 01

3 – Configure o MariaDB no node01 192.168.0.1, alterando o arquivo /etc/my.cnf.d/server.cnf conforme abaixo:

vi /etc/my.cnf.d/server.cnf

[mysqld]
max_connections = 500
skip-name-resolve = 1

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.0.1,192.168.0.2'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.0.1'
wsrep_node_name='mariadb-node01'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Allow server to accept connections on all interfaces.
bind-address=0.0.0.0

# Optional setting
innodb_flush_log_at_trx_commit=0

4 – Use o comando abaixo para iniciar o node01 como node primario somente da primeira vez. Se precisar iniciar o mysql no futuro use: /etc/init.d/mysql start veja se as portas 3306 e 4567 estao ouvindo

galera_new_cluster
netstat -nlp | grep mysqld

*Para ver o debug da inicialização do cluster

tail -f /var/log/messages

CONFIGURAÇÃO NODE 02

5 – Configure o MariaDB no node02 192.168.0.2, alterando o arquivo /etc/my.cnf.d/server.cnf conforme abaixo:

vi /etc/my.cnf.d/server.cnf

[mysqld]
max_connections = 500

[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://192.168.0.1,192.168.0.2'
wsrep_cluster_name='galera'
wsrep_node_address='192.168.0.2'
wsrep_node_name='mariadb-node02'
wsrep_sst_method=rsync
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

# Allow server to accept connections on all interfaces.
bind-address=0.0.0.0

# Optional setting
innodb_flush_log_at_trx_commit=0

6 – Reinicie o MySQL, veja se a porta 4567 está ouvindo, depois que o node02 sincronizar com node01 a porta 3306 estará ouvindo também

/etc/init.d/mysql restart 
netstat -nlp | grep mysqld

*Ao reiniciar o mysql no node02 os dados serão sincronizados com node01 utilizando serviço rodando na porta 4567(rsync). A sincronização em tempo real pode ser vista no arquivo /var/log/messages

tail -f /var/log/messages

**Para ver erros de inicialização do mysql:

systemctl status mariadb.service -l

***Caso o node-01 não seja o último a sair do cluster será preciso forçar sua inicializaço alterando o parametro safe_to_bootstrap para 1

vi /var/lib/mysql/grastate.dat 

safe_to_bootstrap: 1

Depois de alterar o arquivo acima inicie o node como primário, o node secundário irá fazer a re-sincronização com o primário

galera_new_cluster

Checar o status do cluster

SHOW STATUS WHERE Variable_name REGEXP 'wsrep_connected|wsrep_ready|wsrep_local_state_comment|wsrep_cluster_size|wsrep_last_committed';

Ver limite de conexões simultâneas permitidas pelo MySQL

SHOW VARIABLES LIKE 'max_connections';

Oficial Doc:
http://galeracluster.com/documentation-webpages

Link sobre inicialização de cluster primário e secundário
http://galeracluster.com/documentation-webpages/startingcluster.html