Friday, June 30, 2017

Mariadb 10.2.6 installation and playing

mariadb

installation

apt-get install software-properties-common
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

root@mariadbn:~# cat /etc/apt/sources.list.d/mariadb.list
deb [arch=amd64,i386] http://mirror.jmu.edu/pub/mariadb/repo/10.2/ubuntu trusty main
deb-src http://mirror.jmu.edu/pub/mariadb/repo/10.2/ubuntu trusty main


apt-get update
apt install mariadb-server

Edit Config

/etc/mysql/my.cnf

[mysqld]
.
bind-address            = 0.0.0.0
.
.

and

[galera]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="gggggcluster"
wsrep_cluster_address="gcomm://172.16.155.182,172.16.155.183?pc.wait_prim=no"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="172.16.155.182"
wsrep_node_name="mariadbn"
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

you might edit different attribute on different node

wsrep_node_address="host ip"
wsrep_node_name="hostname"

Modify remote access

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

Modify debian-sys-maint

root@mariadbn:~# cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = root
password = password
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = password
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Start Clustering

in first node

service mysql start --wsrep-new-cluster

in sendong node

service mysql start

Check result

mysql -uroot -ppassword

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.00 sec)

Add database

create database aa;

You might check the new created database in another node.

Set up Bootstrap

It Seems Maraidb solve the boot sequence problem temporarily

Adding ?pc.wait_prim=no to wsrep_cluster_address to each node.

wsrep_cluster_address="gcomm://172.16.155.182,172.16.155.183?pc.wait_prim=no"

And connect to one of node and into mariadb with the following command.

set global wsrep_provider_options="pc.bootstrap=true";

It works well, while system reboot. If we set pc.bootstrap=true to node1,

Reboot Problem

It still have some problem in version 10.2.6 but it's better than old version. Sometimes it works, but sometimes it failed.

If you cannot restart mariadb, at any node

 root@mariadbnn:~# cat /var/lib/mysql/grastate.dat
# GALERA saved state
version: 2.1
uuid:    e1b56403-5d49-11e7-8b80-06dac255d854
seqno:   -1
safe_to_bootstrap: 0

change safe_to_bootstrap: 1 and restart it with

service mysql start --wsrep-new-cluster

Reboot test

Trouble Shooting

Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Method1

/etc/mysql/debian.cnf

[client]
host     = localhost
user     = debian-sys-maint
password = 5aULtSThxY8iKQf2
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = 5aULtSThxY8iKQf2
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '[debian-sys-maint password]';

Method2

replace /etc/mysql/debian.cnf

[client]
host     = localhost
user     = root
password = [password]
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = root
password = [password]
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Change [password] to root password that is accessing to db's password.

Master wrong sequence

Nov  5 16:12:04 mariadb3 mysqld: } joined {
Nov  5 16:12:04 mariadb3 mysqld: } left {
Nov  5 16:12:04 mariadb3 mysqld: } partitioned {
Nov  5 16:12:04 mariadb3 mysqld: })
Nov  5 16:12:04 mariadb3 mysqld: 161105 16:12:04 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 3
Nov  5 16:12:04 mariadb3 mysqld: 161105 16:12:04 [Note] WSREP: Flow-control interval: [28, 28]
Nov  5 16:12:04 mariadb3 mysqld: 161105 16:12:04 [Note] WSREP: Received NON-PRIMARY.
Nov  5 16:12:04 mariadb3 mysqld: 161105 16:12:04 [Note] WSREP: New cluster view: global state: c3069ad9-a23e-11e6-a5ff-cbf732092ea0:6, view# -1: non-Primary, number of nodes: 3, my index: 0, protocol version -1
Nov  5 16:12:04 mariadb3 mysqld: 161105 16:12:04 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Nov  5 16:12:06 mariadb3 mysqld: 161105 16:12:06 [Note] WSREP: (30fec36f, 'tcp://0.0.0.0:4567') turning message relay requesting off
Nov  5 16:43:24 mariadb3 ntpdate[840]: step time server 91.189.94.4 offset 1877.225237 sec
Nov  5 16:43:39 mariadb3 ntpdate[1735]: adjust time server 91.189.94.4 offset -0.046589 sec
Nov  5 16:43:48 mariadb3 /etc/init.d/mysql[1912]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Nov  5 16:43:48 mariadb3 /etc/init.d/mysql[1912]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
Nov  5 16:43:48 mariadb3 /etc/init.d/mysql[1912]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111 "Connection refused")'
Nov  5 16:43:48 mariadb3 /etc/init.d/mysql[1912]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Nov  5 16:43:48 mariadb3 /etc/init.d/mysql[1912]:
Nov  5 16:43:49 mariadb3 kernel: [   38.395295] init: plymouth-upstart-bridge main process ended, respawning

Slave Wrong Sequence

ov  5 16:43:21 mariadb2 mysqld: } joined {
Nov  5 16:43:21 mariadb2 mysqld: } left {
Nov  5 16:43:21 mariadb2 mysqld: } partitioned {
Nov  5 16:43:21 mariadb2 mysqld: })
Nov  5 16:43:21 mariadb2 mysqld: 161105 16:43:21 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 1, memb_num = 3
Nov  5 16:43:21 mariadb2 mysqld: 161105 16:43:21 [Note] WSREP: Flow-control interval: [28, 28]
Nov  5 16:43:21 mariadb2 mysqld: 161105 16:43:21 [Note] WSREP: Received NON-PRIMARY.
Nov  5 16:43:21 mariadb2 mysqld: 161105 16:43:21 [Note] WSREP: New cluster view: global state: c3069ad9-a23e-11e6-a5ff-cbf732092ea0:6, view# -1: non-Primary, number of nodes: 3, my index: 1, protocol version -1
Nov  5 16:43:21 mariadb2 mysqld: 161105 16:43:21 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Nov  5 16:43:23 mariadb2 mysqld: 161105 16:43:23 [Note] WSREP: (52092468, 'tcp://0.0.0.0:4567') turning message relay requesting off

Slave Wrong Sequence

Nov  5 16:43:21 mariadb1 mysqld: } joined {
Nov  5 16:43:21 mariadb1 mysqld: } left {
Nov  5 16:43:21 mariadb1 mysqld: } partitioned {
Nov  5 16:43:21 mariadb1 mysqld: })
Nov  5 16:43:21 mariadb1 mysqld: 161105 16:43:21 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 2, memb_num = 3
Nov  5 16:43:21 mariadb1 mysqld: 161105 16:43:21 [Note] WSREP: Flow-control interval: [28, 28]
Nov  5 16:43:21 mariadb1 mysqld: 161105 16:43:21 [Note] WSREP: Received NON-PRIMARY.
Nov  5 16:43:21 mariadb1 mysqld: 161105 16:43:21 [Note] WSREP: New cluster view: global state: c3069ad9-a23e-11e6-a5ff-cbf732092ea0:6, view# -1: non-Primary, number of nodes: 3, my index: 2, protocol version -1
Nov  5 16:43:21 mariadb1 mysqld: 161105 16:43:21 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Nov  5 16:43:23 mariadb1 mysqld: 161105 16:43:23 [Note] WSREP: (666a866f, 'tcp://0.0.0.0:4567') turning message relay requesting off

No comments:

Post a Comment