mariadb

[Galera Cluster] Starting MariaDB database server mysqld [Fail] After All Node Down

cat /var/log/syslog

Aug 16 10:02:22 ubuntu mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Aug 16 10:02:22 ubuntu mysqld_safe: WSREP: Running position recovery with –log_error=’/var/lib/mysql/wsrep_recovery.PySORv’ –pid-file=’/var/lib/mysql/ubuntu-recover.pid’
Aug 16 10:02:22 ubuntu mysqld: 2017-08-16 10:02:22 140084985264000 [Note] /usr/sbin/mysqld (mysqld 10.2.7-MariaDB-10.2.7+maria~trusty-log) starting as process 4821 …
Aug 16 10:02:28 ubuntu mysqld_safe: WSREP: Recovered position e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] /usr/sbin/mysqld (mysqld 10.2.7-MariaDB-10.2.7+maria~trusty-log) starting as process 4904 …
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Read nil XID from storage engines, skipping position init
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/galera/libgalera_smm.so’
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_load(): Galera 25.3.20(r3703) by Codership Oy <info@codership.com> loaded successfully.
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: CRC-32C: using hardware acceleration.
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Found saved state: e0cf993b-8175-11e7-bd60-2326e7fcbf42:-1, safe_to_bootsrap: 0
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.8.18; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 256M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announc
Aug 16 10:02:28 ubuntu mysqld: e_timeout = PT3S; pc.checksum = false; pc.i
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: GCache history reset: old(e0cf993b-8175-11e7-bd60-2326e7fcbf42:0) -> new(e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399)
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Assign initial position for certification: 4399, protocol version: -1
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_sst_grab()
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Start replication
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: ‘wsrep-new-cluster’ option used, bootstrapping the cluster
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Setting initial position to e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [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 .
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [ERROR] WSREP: wsrep::connect(gcomm://192.168.8.18,192.168.8.19,192.168.8.20) failed: 7
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [ERROR] Aborting
Aug 16 10:02:28 ubuntu mysqld:
Aug 16 10:02:29 ubuntu mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: 0 processes alive and ‘/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf ping’ resulted in
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: #007/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)’
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]:

The red fonts is the key

So, we must see tha node that safe_to_bootstrap is 1, and then sudo service mysql start –wsrep-new-cluster

mariadb

Galera Cluster with MariaDB 10.2 (3 Nodes) on Ubuntu 14.04 / 16.04 Servers

Note:

node_1, IP : 192.168.212.1

node_2, IP : 192.168.212.2

node_3, IP : 192.168.212.3

1.  Install MariaDB 10.2

If you have MariaDB 10.1 or later before, please make sure that upgrade or see https://askubuntu.com/questions/703123/mariadb-10-1-server-wont-start-after-update

Here for more detail installing mariaDB 10.2; https://downloads.mariadb.org/mariadb/repositories/#mirror=Beritagar&distro=Ubuntu&distro_release=trusty–ubuntu_trusty&version=10.2

sudo apt-get install software-properties-common 
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db 
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://sumberterbuka.beritagar.id/mariadb/repo/10.2/ubuntu trusty main'

sudo apt-get update sudo apt-get install mariadb-server

2. Configure local hostname

In all node

sudo nano /etc/hosts

Paste :

192.168.212.1   node_1
192.168.212.2   node_2
192.168.212.3   node_3

sudo service networking restart

3. [mysql] * Basic Settings

Uncomment in /etc/mysql/my.cnf node_1, node_2 and node_3

#bind-address           = 127.0.0.1
#default_storage_engine = InnoDB
#query_cache_limit              = 128K
#query_cache_size               = 64M

4. [galera] * Galera-related settings

a. node_1

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.1"
wsrep_node_name="node_1"
wsrep_slave_threads=16
wsrep_sst_method=rsync
bind-address=0.0.0.0

b. node_1

wsrep_on=ON
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.2"
wsrep_node_name="node_2"
bind-address=0.0.0.0

c. node_1

wsrep_on=ON
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.3"
wsrep_node_name="node_3"
wsrep_slave_threads=16
wsrep_sst_method=rsync
bind-address=0.0.0.0

5. Stop MariaDB Service on all node

Sudo service mysql stop

6. Start MariaDB Service

a. node_1

sudo service mysql start –wsrep-new-cluster

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

b. node_2

Service mysql start

If you see an ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES), copy /etc/mysql/debian.cnf from the first node (node_1) and then restart mysql in node_2 : service mysql restart

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

c. node_3

Service mysql start

If you see an ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES), copy /etc/mysql/debian.cnf from the first node (node_1) and then restart mysql in node_3 : service mysql restart

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

7. Set Firewall (IPTABLES)

-A INPUT -s 192.168.212.0/24 -p tcp -m tcp –dport 22 -j ACCEPT
-A INPUT -s 192.168.212.0/24 -p tcp -m multiport –dports 3306,4567,4568,4444 -j ACCEPT
-A INPUT -p udp -m udp –dport 4567 -j ACCEPT

Generals

Format Flash Disk on Ubuntu

root@ts:/home/ts# fdisk -l
Disk /dev/sda: 465,8 GiB, 500107862016 bytes, 976773168 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 4096 bytes
I/O size (minimum/optimal): 4096 bytes / 4096 bytes
Disklabel type: dos
Disk identifier: 0x721a166e

Device Boot Start End Sectors Size Id Type
/dev/sda1 * 2048 781461503 781459456 372,6G 83 Linux
/dev/sda2 781463550 791031807 9568258 4,6G 5 Extended
/dev/sda5 781463552 791031807 9568256 4,6G 82 Linux swap / Solaris

Partition 2 does not start on physical sector boundary.

Disk /dev/sdb: 14,6 GiB, 15664676864 bytes, 30595072 sectorsfdisk -l
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x199700bc

Device Boot Start End Sectors Size Id Type
/dev/sdb1 * 0 3312959 3312960 1,6G 0 Empty
/dev/sdb2 3241304 3246167 4864 2,4M ef EFI (FAT-12/16/32)

root@ts:/home/ts# umount /dev/sdb1

root@ts:/home/ts# mkfs.vfat /dev/sdb1
mkfs.fat 4.0 (2016-05-06)

 

Security

Iptables Rule Minimal for ‘Secure’ Server

Here is iptable rule minimal for ‘secure’ server,

iptables -A INPUT -m conntrack –ctstate RELATED,ESTABLISHED -j ACCEPT
iptables -A INPUT -s 212.212.212.x/32 -p tcp -m tcp –dport 202 -j ACCEPT
iptables -P INPUT DROP
iptables -A INPUT -i lo -j ACCEPT
iptables -A INPUT -p tcp -m tcp –dport 80 -j ACCEPT
iptables -A INPUT -p tcp -m tcp –dport 8080 -j ACCEPT
iptables -A INPUT -p tcp -m tcp –dport 443 -j ACCEPT
iptables -A INPUT -p tcp -m tcp –dport 80 -j ACCEPT
iptables -A INPUT -p tcp -m tcp –dport 8080 -j ACCEPT
iptables -A INPUT -s 212.212.212.x/32 -p icmp -m icmp –icmp-type 8 -j ACCEPT
iptables -A INPUT -s 192.168.1.0/24 -i eth0 -p tcp -m tcp –dport 3306 -j ACCEPT
iptables -A INPUT -s 212.212.212.x/32 -i eth1 -p udp -m udp –dport 10050 -j ACCEPT
iptables -A INPUT -s 212.212.212.x/32 -i eth1 -p tcp -m tcp –dport 10050 -j ACCEPT

 

 

Security

Iptables Rule to Allow Incoming FTP

Port 21 is used to establish the connection, couse ftp server needs a channel to transfer data. So, for data transfer possible, we must allow port 20.

To make sure passive ftp connections are not rejected, do following;

modprobe ip_conntrack_ftp

Allow FTP connections on port 21 incoming and outgoing

# iptables -A INPUT -p tcp -m tcp –dport 21 -m conntrack –ctstate ESTABLISHED,NEW -j ACCEPT -m comment –comment “Allow ftp connections on port 21”
# iptables -A OUTPUT -p tcp -m tcp –dport 21 -m conntrack –ctstate NEW,ESTABLISHED -j ACCEPT -m comment –comment “Allow ftp connections on port 21”

If listen_port another 21, such as 2001, use the following command;

# iptables -A INPUT -p tcp -m tcp –dport 2001 -m conntrack –ctstate ESTABLISHED,NEW -j ACCEPT -m comment –comment “Allow ftp connections on port 2001”
# iptables -A OUTPUT -p tcp -m tcp –dport 2001 -m conntrack –ctstate NEW,ESTABLISHED -j ACCEPT -m comment –comment “Allow ftp connections on port 2001”

Then allow FTP port 20 for active connections incoming and outgoing,

# iptables -A INPUT -p tcp -m tcp –dport 20 -m conntrack –ctstate ESTABLISHED,RELATED -j ACCEPT -m comment –comment “Allow ftp connections on port 20”
# iptables -A OUTPUT -p tcp -m tcp –dport 20 -m conntrack –ctstate ESTABLISHED -j ACCEPT -m comment –comment “Allow ftp connections on port 20”

Finally allow FTP passive inbound traffic

# iptables -A INPUT -p tcp -m tcp –sport 1024: –dport 1024: -m conntrack –ctstate ESTABLISHED -j ACCEPT -m comment –comment “Allow passive inbound connections”
# iptables -A OUTPUT -p tcp -m tcp –sport 1024: –dport 1024: -m conntrack –ctstate ESTABLISHED,RELATED -j ACCEPT -m comment –comment “Allow passive inbound connections”

For more on FTP and firewall problems see: http://slacksite.com/other/ftp.html#active