[Maria-discuss] Problem with Master/Slave <=> Master/Slave <=> Master/Slave Replication
Hi All! I am having a Problem with getting mariadb works flawless using a Master/Slave[1] <=> Master/Slave[2] <=> Master/Slave[3] The First Time i was able to insert a row on the Master[1] Server and the data was reproduced/replicated to all servers. At this Time i thinked everything is fine now. But then when i tryed to deleted the new Created Row on Master[1] the Data was only deleted on the Master[1] Server while the others Servers [2] and [3] still had the data! I expected that the deleted row should be also deleted on all master/slave servers but it did not! How can i solve this Problem? And what is actually the Problem with MySQL/MariaDB? I am getting the impression that MariaDB is really bad when it comes to Replication/Sync of Data in a Distributed Architecture. As a Example let say on server has to reboot because of a Cloud maintenance. From what i see this MaraiDB server will be forever corrupted after the restart and never sync again as first it has to manually be adjusted to the master bin log position and second it has also be told to work as a slave after this. Not really what i would expect from a autonomous replicating Master Slave Data Base Server after a restart! Hope somebody can tell me how to resolve all this Sync Problems so finally MariaDB Master/Slave Database Server work flawless without to worry that sooner or later a node or a few of them will get useless and corrupted! Thanks in advance for any helpful Tip and Answer! A example of my my.cnf Master/Slave[2] Server changes! server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 8 auto-increment-offset = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index # not fab for performance, but safer sync_binlog = 1 expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = logedin_users binlog-format = ROW # slaves relay_log = /var/log/mysql/relay-bin relay_log_index = /var/log/mysql/relay-bin.index relay_log_info_file = /var/log/mysql/relay-bin.info log_slave_updates = 1 #read_only binlog-checksum = 1 master_verify_checksum = 1 relay_log_recovery = 1 slave_compressed_protocol = 1 slave_sql_verify_checksum = 1 sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1
Hi, On Tue, Oct 25, 2016 at 5:51 AM LinuXperia <linuxperia@gmx.ch> wrote:
Hi All!
I am having a Problem with getting mariadb works flawless using a Master/Slave[1] <=> Master/Slave[2] <=> Master/Slave[3]
If you want to have synchronous replication across all 3 servers I would NOT recommend using this kind of setup. Please use Galera Cluster instead.
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluste... The First Time i was able to insert a row on the Master[1] Server
and the data was reproduced/replicated to all servers.
At this Time i thinked everything is fine now.
But then when i tryed to deleted the new Created Row on Master[1] the Data was only deleted on the Master[1] Server while the others Servers [2] and [3] still had the data!
Probably something in your settings is wrong and/or servers are not replicating. For example you have: binlog_do_db = logedin_users This means that statements will only replicate if you are connected to this DB.
I expected that the deleted row should be also deleted on all master/slave servers but it did not!
How can i solve this Problem? And what is actually the Problem with MySQL/MariaDB? I am getting the impression that MariaDB is really bad when it comes to Replication/Sync of Data in a Distributed Architecture.
I recommend you read some more documentation and refer to the MariaDB KB about use cases and replication configuration.
As a Example let say on server has to reboot because of a Cloud maintenance. From what i see this MaraiDB server will be forever corrupted after the restart and never sync again as first it has to manually be adjusted to the master bin log position and second it has also be told to work as a slave after this.
You shouldn't have to do this if you've configured your servers and replication correctly. As mentioned before if you need a three server cluster setup I recommend using MariaDB Galera Cluster. It is easy to set up in MariaDB 10.1. Best regards GL
Not really what i would expect from a autonomous replicating Master
Slave Data Base Server after a restart!
Hope somebody can tell me how to resolve all this Sync Problems so finally MariaDB Master/Slave Database Server work flawless without to worry that sooner or later a node or a few of them will get useless and corrupted!
Thanks in advance for any helpful Tip and Answer!
A example of my my.cnf Master/Slave[2] Server changes!
server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 8 auto-increment-offset = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index # not fab for performance, but safer sync_binlog = 1 expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = logedin_users binlog-format = ROW # slaves relay_log = /var/log/mysql/relay-bin relay_log_index = /var/log/mysql/relay-bin.index relay_log_info_file = /var/log/mysql/relay-bin.info log_slave_updates = 1 #read_only
binlog-checksum = 1 master_verify_checksum = 1 relay_log_recovery = 1 slave_compressed_protocol = 1 slave_sql_verify_checksum = 1 sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
Am 25.10.2016 um 05:51 schrieb LinuXperia:
Hi Guillaume Lefranc!
If you want to have synchronous replication across all 3 servers I would NOT recommend using this kind of setup. Please use Galera Cluster instead.
https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluste...
Probably something in your settings is wrong and/or servers are not replicating. For example you have: binlog_do_db = > logedin_users This means that statements will only replicate if you are connected to this DB.
Thanks a Lot for your helpful Suggestions Guillaume. I have managed now finally to make a Hybrid Cluster where all the DB Servers on the Cloud are in a Galera Cluster and for feeding the Cluster with Data i have turned it into a Hybrid Master/Slave <=> [Master/Slave Galera] Cluster. After lot of Failures and Trying i finally got it to run Smoothly and Flawlessly. The Documentation in MariaDB Website is not really much helpful as even the Default Values MariaDB use prevent a smooth less Syncing of the Data. With the Default Values especially the Network Values used by MariaDB getting Out of Sync when using a Cluster is guaranteed! * After getting it finally to work everything like it should I donated now also 100.00 USD just right now to the MaraiDB Foundation for the Work and Engagement that was done till yet.* If everything works well and play out as planned i am willing to donate further and on a regular basis, heck maybe even do a sponsorship. Because of the not very helpful documentation i was on the way to install MemSQL. The only thing that prevented me to Install it after the Download was that a 32 Bit version do not exist and i could not run it and ditch MariaDB. After this a gave MariaDB another try an finally got it running like it should. I recommend the People at MariaDB to provide at least Donators access to Best Use and Configuration Settings that works best in Production. That way people like me that are willing to pay dont waste their time on the Internet to find out what the problem with MariaDB is when it comes to Clustering and Out Of Sync Problems. Thanks for everything. Till next Time. Greetings
I am having a Problem with getting mariadb works flawless using a Master/Slave[1] <=> Master/Slave[2] <=> Master/Slave[3]
The First Time i was able to insert a row on the Master[1] Server and the data was reproduced/replicated to all servers.
At this Time i thinked everything is fine now.
But then when i tryed to deleted the new Created Row on Master[1] the Data was only deleted on the Master[1] Server while the others Servers [2] and [3] still had the data!
I expected that the deleted row should be also deleted on all master/slave servers but it did not!
Thanks in advance for any helpful Tip and Answer!
Hello, Could you please explain what you meant by the following:
The Documentation in MariaDB Website is not really much helpful as even the Default Values MariaDB use prevent a smooth less Syncing of the Data. With the Default Values especially the Network Values used by MariaDB getting Out of Sync when using a Cluster is guaranteed!
I have never met such issues with the default settings, can you please let me know what is the problem and which settings you changed? I am happy to help improving defaults in future versions if needed.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Guillaume Lefranc Remote DBA Services Manager MariaDB Corporation
participants (2)
-
Guillaume Lefranc -
LinuXperia