Useful shortcuts for vi editor

Showing posts with label mariadb. Show all posts
Showing posts with label mariadb. Show all posts

Wednesday 20 November 2019

How to reset mariadb root password on using Galera Cluster system?

e.g. reset root password
two machines in a cluster

1st step:

-> Activate "safe_to_bootstrap" parameter on first machine
# vi /var/lib/mysql/grastate.dat
safe_to_bootstrap: 1

2nd step on first/second machine:
# systemctl stop mariadb

3rd step:

-> Run database in safe mode on first machine
# /usr/bin/mysqld_safe --skip-grant-tables --wsrep-new-cluster "${@:2}" > /dev/null 2>&1 & 

4th step:

-> Note the process id of mysql service
# ps -ef | grep mysql

5th step:

-> Connect mysql terminal then run following commands on first machine
# mysql -u root
 use mysql;
 update user SET PASSWORD=PASSWORD("password") WHERE USER='root';
 flush privileges;
 exit

6th step:

-> Try to connect with new password on first machine
# mysql -u root -ppassword

7th step:
-> kill the odd service on first machine
# kill -9 1111

8th step:
-> Start new cluster on first machine
# galera_new_cluster

9th step:

-> Start mariadb on second machine
systemctl start mariadb

Done...

Ref:

https://robbinespu.github.io/eng/2018/03/29/Reset_mariadb_root_password.html
https://blog.toonormal.com/2015/11/23/mariadb-cluster-install-notes/

Wednesday 31 October 2018

WSREP "Failed to open channel" solution

Error
ERROR WSREP "Failed to open channel 'my_mariadb_cluster'"

Detailed Error
WSREP: gcs/src/gcs.cpp:gcs_open():1458: Failed to open channel 'my_mariadb_cluster' at 'gcomm://10.10.10.10,10.10.10.11': -110 (Connection timed out)

Solution
On the first node: (e.g. node-1)
-> Stop mariadb service if it hangs
# systemctl stop mariadb.service

-> Make sure "safe_to_bootstrap" value is "1"
# cat /var/lib/mysql/grastate.dat
safe_to_bootstrap: 1

-> Run following command for starting cluster as new
# galera_new_cluster

-> mariadb service should be started automatically
# systemctl status mariadb.service

-> note wsrep_last_committed number by looking system table
# mysql -u root -p
> show status like 'wsrep_%';

-> note uuid number
# cat /var/lib/mysql/grastate.dat

On the second node: (e.g. node-2)
-> Stop mariadb service if it hangs
# systemctl stop mariadb.service

-> uuid and seqno (wsrep_last_committed) should be same with node-1
# vi /var/lib/mysql/grastate.dat
uuid: 0000
seqno: 100

-> start mariadb service
# systemctl start mariadb.service