Run MySQL Cluster Multi Masters For High Availability

Hello Folks, it’s has been awhile I didn’t write new articles,

it’s has been awhile I didn’t write new articles, so time to give back to the community, I will describe how to implement MySQL cluster for high-availability and disturbed workload

MySQL Cluster Architect comes with new process  ndbd and ndb_mgmd

ndb is used to handle all data and table using the NDB Cluster Engine

ndbmtd multi threaded data  handler in NDB Cluster Engine

ndb_mgmd is the Cluster Management Server Daemon responsible for distribute the configuration and log around the cluster

in this setup we will use 4 server to distribute MySQL service cluster process

Group Beta

  • 172.31.24.183  ndbd
  • 172.31.23.137 ndbd

Group Alpha

  • 172.31.16.43 mysqld & ndb_mgmd
  • 172.31.16.34 mysql & ndb_mgmd

our applications will communicate to load balance that distribute the workload  to Alpha Group

to begin setup we have to download mysql cluster manager package from Oracle website https://edelivery.oracle.com/osdc/faces/Home.jspx feel free to create your account

pickup your platform and download the package for i use the Cluster+Generic Linux x86 (64bit) version

download and extract the package  in all nodes

inside the mcm1.4.3 folder  is a bin folder  has 2 files mcm and mcmd the client and the demon for the cluster manager

we need to run mcmd in all nodes so they can communicate to each others

lets setup out cluster first we need create site ( all nodes we need to group it in a site )

now lets run the client add the site  MySQL Cluster Manager Interface

mcm client

 

second we need to load the cluster package in the site we created

 

now lets define the roles for the node who plays what

172.31.23.137  & 172.31.24.183 plays data role

172.31.16.43 & 172.31.16.34  mysqld & cluster management

now run the cluster and check the services status

confirm the roles for each host upon your design

now we want to communicate with out lovely cluster

as we built 2 servers with mysqld they are up and running under node id 51,52

by default mysql cluster will not sync the mysql.user table as it running in MyISM and for that we need to enable a mysql routine that sync the users over the cluster

to do that first login to mysqld nodes as a root  and import distribute mysql privileges routine .sql

now create your remote account and it will be synced over the cluster

Now feel free to scale up alpha or beta to any number you want also you can create nodes mixed of alpha and beta.

and keep your eyes on the nodes

to add a new node to our cluster we load the mcm package in the node and the demon

mcm> add package -b /home/ec2-user/mcm/cluster -h 172.31.20.215 7_6_8;

 

as it clear our new node 172.31.20.215 is in the sites but didn’t joined the cluster yet because he has no rule to add it  and after that we need to start the role inside the cluster so nodes can collaborate and sync

now adding extra node and load more database data store on 172.31.20.215

checking our cluster status

to run added processes  on target cluster we use start process -a mycluster

now everything should be steady for our cluster

now lets change all ndbd to multi threaded version

all our data engine now running multi threaded version

 

Happy Hits 😀

Docker Persistent Storage for MySQL Server and SELinux

hello everyone today we will make

MySQL Docker Container with Shared Storage

first lets pull latest mysql  version of docker

after we done download the latest image

this image come handy with some awesome parameters

  1. MYSQL_ROOT_PASSWORD
  2. MYSQL_DATABASE

with this  parameters we can create database and set root password for mysql

now let’s create a folder in our host so we can use it instead of /var/lib/mysql (let’s keep the mysql data in the host not inside a container)

remember it should be numeric formate

then we chanage the folder context to for selinux to  treat this as virtualized sandbox

here we created a database called unixawy and root password un1x4wyp4ssw0rd

inspect your docker and connect to it ip

now each container you run with this command will share the same database data

check database content in your node storage via ls /var/mysql_data_store

cheers

Script : MySQL Create Database UTF-8 with user and password

we do  create many  databases every day and i love UTF-8 data formate so i decided to make  something simple and save my time

here is the syntax to create database called unixawy in utf8

to add user for unixawy with password unixawysecret

also  i made simple script to save my timemysql_utf8

here is the link on github

https://github.com/n1xf1/misc/blob/master/mysql_db_user_pass_gen.py

 

Rest MySQL root password

we all hate this ERROR 1045 (28000):

the problem starts with  “you can’t access and u will not be able to change the mysql/mariadb password while the service is running u have to disable it and run mysqld_safe which will allow u to update the user table inside mysql datebase with no password then u will be able to run mysql/mariadb again with  the new password”

First Solution

1 – service mysqld stop

2 – mysqld_safe –skip-grant-tables

3 – run mysql command it will let u in without password

4 – now u will be able to  update the user table

apply this code inside mysql shell

the new root password will be UNIXAWY

5 – killall mysqld

6- service mysqld start

Second Solution

1- create file restmysqlpwd.txt

2 – insert this code inside

 

3 – service mysqld stop

4 – mysqld_safe –init-file restmysqlpwd.txt

5- killall mysqld

6 – service mysqld start