The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

MariaDB Federated FederatedX

https://stackoverflow.com/questions/49089641/mariadb-federated-federatedx

I have a question to know what is the best solution I should choose.

I have two MariaDB databases on different machines on the same gigabit network, both running MariaDB 10.1.8 on centos 7

One is a Web database and the other is a FreeRadius database.

Web Database is around 8GB and with workbench I can see around 18 InnoDB writes per second. Web Database machine is 50GB disc, 8Gb Ram, 4xCPU On PRTG the Web Database select sensor delays around 140ms-203ms

Radius Database is around 20GB and with workbench I can see around 28 InnoDB writes per second. Radius Database machine is 100GB disc, 16GB Ram, 6xCPU On PRTG the Web Database select sensor delays around 140ms-300ms

I think the Radius Database is usually more used than web database.

Now the question is that i need to create a table with users visits by day.

I need this table to be shared by 2 databases and to be able to insert data from both machines.

Sometimes web Server will insert first and sometimes will be radius server that will insert them. Radius Server will do most of the inserts on that table. This is the table that I need to populate

CREATE TABLE `visitas` ( `idUsuario` int(10) unsigned NOT NULL, `idInstalacion` int(10) unsigned NOT NULL, `fVisita` date DEFAULT NULL, `tAcceso` varchar(25) DEFAULT NULL, `nUpdates` int(10) unsigned NOT NULL, UNIQUE KEY `Visitas` (`idUsuario`,`idInstalacion`,`fVisita`), KEY `idUsuario` (`idUsuario`), KEY `idInstalacion` (`idInstalacion`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; So what I've created is a TRIGGER on radius server to insert on visits (with on duplicated key update). That works perfect.

DELIMITER $$; create TRIGGER UpdateVisitas AFTER INSERT ON radacct FOR EACH ROW BEGIN INSERT into visitas ( select u.department as idUsuario,u.company as idInstalacion,DATE(r.acctstarttime) as fVisita,'Radius' as tAcceso,0 as nUpdates from radacct r,userinfo u where r.username=u.username and r.radacctid=NEW.radacctid ) ON DUPLICATE KEY UPDATE visitas.tAcceso='Radius',nUpdates=(1+nUpdates) ; END$$ Now the question i need to FEDERATEDX this table to be able to insert from radius server and web server and to be joined with other tables from web server.

Insert will be done mostly from Radius and select with joins for statistics will be done mostly from Web.

So according to all this information what is best to create the Visit table on Radius Server and federated it on Web Server o the other way.

Let me know if you need more information.

thanks a lot !.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.