Every now and again problems crop up and what's more is necessary to write to the Mysql support.
Following you'll find the description of the problem and the request to the Mysql Support.
I'll let you know the solution.
See you soon
Byeee
Hi,
I've been working on the MySQL Fabric framework lately and what's more I've made a cluster up.
The cluster was made up of a Master (Node1), a Slave (Node2) and what's more a Fabric node.
While I was looking at various failover scenarion a problem cropped up.
Let me explain what the problem was.
STEP 1 : The master (Node1) and Slave (node2) are online. The Jmeter
client asks a connection to the Fabric node through the port 32274 and
it commits a row on the Master which is replicated on the Slave
afterwards.
STEP 2: The Master (Node1) falls down, the Fabric
node promotes the Slave (Node2) as a Master node and what's more marks
the Node1 as a faulty node.
Be careful : The Jmeter client is still online.
STEP 3: The Node1 , the previous Master, reboots and comes online
again. Since the Fabric node has marked it as a faulty node it is still
out of the cluster which is made up of one node (Node2) only.
STEP 4: Again the Jmeter client execute an insert with the same
connection string (
"jdbc:mysql:fabric://mysql.fabric.node:32274/DB?fabricUsername=admin&fabricPassword=xxxxx&fabricServerGroup=group_mob_pre-prod-2&fabricReportErrors=true")
and that command will end up inserting a new row onto the faulty node
(Node1).
RESULT: I lost a row and what's more the Master (Node2) wasn't sincronyzed anymore.
Do you happen to know if there is a way to work around that problem ?
I've already tried the option [TTL=0] of the Fabric node but it doesn't work.
If you get back on this to me, I'll appreciate it.
Cheers
Thursday, 28 May 2015
Wednesday, 6 May 2015
MySQL Fabric - Part 2
All these steps end up starting the fabric daemon with the command :
mysqlfabric manage start --daemon
By default some information will be written in the log file /var/log/fabric.log. However it is possible to change the level of logging with the parameter :
[logging]
level=debug
We have been able to get through the installation process of the fabric node now we can get down the configuration steps of Master and Slave node.
As I said before for the database of fabric node, I'm going to skip the installation of the databases.
I've been installing the replication module recently but always using the manual procedure: CHANGE MASTER ecc.
Using the fabric utility is a completely different stuff !
The CHANGE MASTER command is executed by the daemon directly.
At first glance it seems to be a lost of control instead it is an useful and handy stuff afterwards.
Let's go over the fabric utility, shall we ?
1) Add the following parameters to the Master and Slave database config file : /usr/my.cnf
mysqltest02 (master node)
server-id = 11
log_bin=/mysql/mysql5.6/logs/binlogs/mysqltest02-bin.log
expire_logs_days = 7
max_binlog_size = 512M
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=mysqltest02
report-port=3306
mysqltest03 (slave node)
server-id = 12
log_bin=/mysql/mysql5.6/logs/binlogs/mysqltest03-bin.log
expire_logs_days = 7
max_binlog_size = 512M
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=mysqltest03
report-port=3306
Bounce both of them.
2) Create the fabric user with the following privileges and what's more on the following nodes :
GRANT ALL ON *.* TO fabric@'mysqltest01' identified by 'xxxx';
GRANT ALL ON *.* TO fabric@'mysqltest02' identified by 'xxxx';
GRANT ALL ON *.* TO fabric@'mysqltest03' identified by 'xxxx';
Since the Master and Slave node could change the role, due to a failure, for example, it is necessary to declare the fabric user on all the nodes that are going to make up the cluster.
3) On the fabric Node execute the following command to create a failure group :
mysqlfabric group create group_id-1
4) Add nodes to the failure group :
mysqlfabric group add group_id-1 mysqltest02:3306
mysqlfabric group add group_id-1 mysqltest03:3306
Be carefull: Both nodes will be added as slave nodes.
Execute the following to look into the configuration.
mysqlfabric group lookup_servers group_id-1
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- --------- ------
6dbb68be-bcd2-11e4-9778-0800275bd79b mysqltest02:3306 SECONDARY READ_ONLY 1.0
f460bb27-bcd2-11e4-977b-0800277d50e3 mysqltest03:3306 SECONDARY READ_ONLY 1.0
Look out the column STATUS : SECONDARY
5) Now we are ready to go over the promote process, choosing one node and what's more promoting it as a Master node.
mysqlfabric group promote group_id-1 --slave_id 6dbb68be-bcd2-11e4-9778-0800275bd79b
Mysqltest02 turned out to be Master node.
Let's look into the new status, shall we ?
mysqlfabric group lookup_servers group_id-1
server_uuid address status mode weight
------------------------------------ ------------------- --------- --------- ------
6dbb68be-bcd2-11e4-9778-0800275bd79b mysqltest02:3306 RRIMARY READ_WRITE 1.0
f460bb27-bcd2-11e4-977b-0800277d50e3 mysqltest03:3306 SECONDARY READ_ONLY 1.0
At first glance we get through the installation of a Master and Slave node. To be sure we can execute the following command on the Master node :
mysql> show master status;
+------------------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+----------------------------------------+
| mysqltest02-bin.000001 | 396 | | | 6dbb68be-bcd2-11e4-9778-0800275bd79b:1 |
+------------------------------+----------+--------------+------------------+----------------------------------------+
Instead the following command on the Slave node :
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysqltest02
Master_User: fabric
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqltest02-bin.000001
Read_Master_Log_Pos: 396
Relay_Log_File: mysqltest03-relay-bin.000002
Relay_Log_Pos: 630
Relay_Master_Log_File: mysqltest02-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 396
Relay_Log_Space: 845
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 6dbb68be-bcd2-11e4-9778-0800275bd79b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 6dbb68be-bcd2-11e4-9778-0800275bd79b:1
Executed_Gtid_Set: 6dbb68be-bcd2-11e4-9778-0800275bd79b:1,
f460bb27-bcd2-11e4-977b-0800277d50e3:1
Now we are ready to create a table and what's more load a row on it.
On the master :
create table test(field1 char(10));
insert into test values('xxxxx');
mysql> select *from test;
+--------+
| field1 |
+--------+
| xxxxx |
+--------+
On the Slave :
mysql> select *from test;
+--------+
| field1 |
+--------+
| xxxxx |
+--------+
The new row has been replicated by mysql replication daemon from the Master node to the Slave node.
This has been a handy post and in the near future I hope to be able to come up with new posts.
See you soon.
Byeeee
mysqlfabric manage start --daemon
By default some information will be written in the log file /var/log/fabric.log. However it is possible to change the level of logging with the parameter :
[logging]
level=debug
We have been able to get through the installation process of the fabric node now we can get down the configuration steps of Master and Slave node.
As I said before for the database of fabric node, I'm going to skip the installation of the databases.
I've been installing the replication module recently but always using the manual procedure: CHANGE MASTER ecc.
Using the fabric utility is a completely different stuff !
The CHANGE MASTER command is executed by the daemon directly.
At first glance it seems to be a lost of control instead it is an useful and handy stuff afterwards.
Let's go over the fabric utility, shall we ?
1) Add the following parameters to the Master and Slave database config file : /usr/my.cnf
mysqltest02 (master node)
server-id = 11
log_bin=/mysql/mysql5.6/logs/binlogs/mysqltest02-bin.log
expire_logs_days = 7
max_binlog_size = 512M
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=mysqltest02
report-port=3306
mysqltest03 (slave node)
server-id = 12
log_bin=/mysql/mysql5.6/logs/binlogs/mysqltest03-bin.log
expire_logs_days = 7
max_binlog_size = 512M
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
port=3306
report-host=mysqltest03
report-port=3306
Bounce both of them.
2) Create the fabric user with the following privileges and what's more on the following nodes :
GRANT ALL ON *.* TO fabric@'mysqltest01' identified by 'xxxx';
GRANT ALL ON *.* TO fabric@'mysqltest02' identified by 'xxxx';
GRANT ALL ON *.* TO fabric@'mysqltest03' identified by 'xxxx';
Since the Master and Slave node could change the role, due to a failure, for example, it is necessary to declare the fabric user on all the nodes that are going to make up the cluster.
3) On the fabric Node execute the following command to create a failure group :
mysqlfabric group create group_id-1
4) Add nodes to the failure group :
mysqlfabric group add group_id-1 mysqltest02:3306
mysqlfabric group add group_id-1 mysqltest03:3306
Be carefull: Both nodes will be added as slave nodes.
Execute the following to look into the configuration.
mysqlfabric group lookup_servers group_id-1
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
server_uuid address status mode weight
------------------------------------ ------------------- --------- --------- ------
6dbb68be-bcd2-11e4-9778-0800275bd79b mysqltest02:3306 SECONDARY READ_ONLY 1.0
f460bb27-bcd2-11e4-977b-0800277d50e3 mysqltest03:3306 SECONDARY READ_ONLY 1.0
Look out the column STATUS : SECONDARY
5) Now we are ready to go over the promote process, choosing one node and what's more promoting it as a Master node.
mysqlfabric group promote group_id-1 --slave_id 6dbb68be-bcd2-11e4-9778-0800275bd79b
Mysqltest02 turned out to be Master node.
Let's look into the new status, shall we ?
mysqlfabric group lookup_servers group_id-1
server_uuid address status mode weight
------------------------------------ ------------------- --------- --------- ------
6dbb68be-bcd2-11e4-9778-0800275bd79b mysqltest02:3306 RRIMARY READ_WRITE 1.0
f460bb27-bcd2-11e4-977b-0800277d50e3 mysqltest03:3306 SECONDARY READ_ONLY 1.0
At first glance we get through the installation of a Master and Slave node. To be sure we can execute the following command on the Master node :
mysql> show master status;
+------------------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------------+----------+--------------+------------------+----------------------------------------+
| mysqltest02-bin.000001 | 396 | | | 6dbb68be-bcd2-11e4-9778-0800275bd79b:1 |
+------------------------------+----------+--------------+------------------+----------------------------------------+
Instead the following command on the Slave node :
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysqltest02
Master_User: fabric
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqltest02-bin.000001
Read_Master_Log_Pos: 396
Relay_Log_File: mysqltest03-relay-bin.000002
Relay_Log_Pos: 630
Relay_Master_Log_File: mysqltest02-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 396
Relay_Log_Space: 845
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: 6dbb68be-bcd2-11e4-9778-0800275bd79b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 6dbb68be-bcd2-11e4-9778-0800275bd79b:1
Executed_Gtid_Set: 6dbb68be-bcd2-11e4-9778-0800275bd79b:1,
f460bb27-bcd2-11e4-977b-0800277d50e3:1
Now we are ready to create a table and what's more load a row on it.
On the master :
create table test(field1 char(10));
insert into test values('xxxxx');
mysql> select *from test;
+--------+
| field1 |
+--------+
| xxxxx |
+--------+
On the Slave :
mysql> select *from test;
+--------+
| field1 |
+--------+
| xxxxx |
+--------+
The new row has been replicated by mysql replication daemon from the Master node to the Slave node.
This has been a handy post and in the near future I hope to be able to come up with new posts.
See you soon.
Byeeee
Subscribe to:
Comments (Atom)