MySQL Database Cluster
Make sure you have MySQL Repository Added:
https://dev.mysql.com/get/mysql-apt-config_0.8.26-1_all.deb (mysql 8.0.34)
Step 1:
Install mysql-server:
apt install mysql-server
Step 2:
Install mysql-shell:
apt install mysql-shell
Step 3:
Create MySQL User:
mysql -uroot -e "CREATE USER 'clusteradmin'@'%' IDENTIFIED BY 'Password123';"
Step 4:
Grant Permissions to the MySQL User:
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;"
mysql -u root -e "FLUSH PRIVILEGES;"
mysql -u root -e "reset master;"
Step 5:
Verify the hostname and user that we created:
mysql -u root -e "SELECT @@HOSTNAME";
mysql -u root -e "SELECT user FROM mysql.user WHERE user='clusteradmin';"
Step 6:
Make sure MySQL is listening on required interfaces:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
user = mysql
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
key_buffer_size = 16M
max_allowed_packet = 64M
thread_stack = 256K
Step 7:
Connect to mysql shell:
mysqlsh
Step 7:
Check Instance Configuration of all servers:
dba.check_instance_configuration("clusteradmin@dbserver1:3306")
Step 8:
Configure all instances:
dba.configure_instance("clusteradmin@dbserver1:3306")
Step 9:
Connect to any MySQL Server:
\connect clusteradmin@dbserver1:3306
Step 10:
Create a Database Cluster:
var myCluster = dba.createCluster("dbcluster")
Step 11:
Now add the other instances:
myCluster.addInstance('clusteradmin@dbserver2:3306')
myCluster.addInstance('clusteradmin@dbserver3:3306')
Step 12:
Now check the status of the Cluster:
myCluster.status()
Step 13:
Create a Router:
1: First Add mySQL Repo:
2: Install mysql-router: apt install mysql-router
3: Connect to any mysql cluster node
4: Create 2 Router Accounts from mysqlsh:
dba.getCluster("lmc_dbcluster").setupRouterAccount("router-1")
5: Create 2 Router Accounts from mysqlsh:
dba.getCluster("lmc_dbcluster").setupRouterAccount("router-2")
mysqlrouter --bootstrap clusteradmin@dbserver1 --user=root -d myrouter_idc --
account=router_1
Second Router Instance: mysqlrouter --bootstrap clusteradmin@dbserver2 --user=root
Third Router Instance: mysqlrouter --bootstrap clusteradmin@dbserver3 --user=root
Step 14:
Add this configuration in: /etc/keepalived/keepalived.conf
#For Main Node
vrrp_instance VI_1 {
state MASTER
interface bond0.2
virtual_router_id 50
priority 102
advert_int 1
virtual_ipaddress {
10.0.0.50
}
}
#For Other Nodes
vrrp_instance VI_2 {
state BACKUP
interface enp1s0
virtual_router_id 51
priority 103
advert_int 1
virtual_ipaddress {
10.0.0.50
}
}
MySQL Cluster Recovery
1: Connect to any node that is active using mysqlsh
2: dba.getCluster().status()
3: dba.getCluster().forceQuorumUsingPartitionOf("clusteradmin@127.0.0.1:3306")
4: cluster.removeInstance("clusteradmin@dbserver2:3306", {force: true})
5: dba.getCluster().rescan()
6: dba.getCluster().status()
7: dba.getCluster().addInstance("clusteradmin@dbserver2:3306")
To Re-add an Instance
1: Connect using mysqlsh
2: dba.getCluster().rejoinInstance(“clusteradmin@dbserver2:3306”)
