I looking for a way to transfer a SQL record to RabbitMQ.
What I need is whenever a new record is insert to the table I want to transfer this record (as JSON or any other format) to the RabbitMQ.
I read about executing an external script with sys_exec() (like Python or Java) but I would like to know how it should be done and if this is the best way.
ssimicro/lib_mysqludf_amqp: Publish messages via AMQP directly from MySQL seems to fit the bill.
Then you'd be able to do this (cut'n'paste from the README):
SET @AMQP_URL = 'amqp://guest:guest@localhost:5672';
SET @AMQP_EXCHANGE = 'udf';
DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Customer Accounts';
DELIMITER ;;
DROP TRIGGER IF EXISTS `after_insert_on_accounts`;
CREATE DEFINER=`root`@`localhost` TRIGGER `after_insert_on_accounts` AFTER INSERT ON `accounts` FOR EACH ROW BEGIN
SET @message_id = (SELECT lib_mysqludf_amqp_sendjson(@AMQP_URL, @AMQP_EXCHANGE, 'accounts.insert', json_object('id', NEW.id, 'username', NEW.username)));
END ;;
DROP TRIGGER IF EXISTS `after_update_on_accounts`;
CREATE DEFINER=`root`@`localhost` TRIGGER `after_update_on_accounts` AFTER UPDATE ON `accounts` FOR EACH ROW BEGIN
SET @message_id = (SELECT lib_mysqludf_amqp_sendjson(@AMQP_URL, @AMQP_EXCHANGE, 'accounts.update', json_object('id', NEW.id, 'username', NEW.username)));
END ;;
DROP TRIGGER IF EXISTS `after_delete_on_accounts`;
CREATE DEFINER=`root`@`localhost` TRIGGER `after_delete_on_accounts` AFTER DELETE ON `accounts` FOR EACH ROW BEGIN
SET @message_id = (SELECT lib_mysqludf_amqp_sendjson(@AMQP_URL, @AMQP_EXCHANGE, 'accounts.delete', json_object('id', OLD.id, 'username', OLD.username)));
END ;;
DELIMITER ;
INSERT INTO accounts (username) values ('jdoe');
UPDATE accounts SET username = 'jsmith';
DELETE FROM accounts WHERE id = last_insert_id();
I suspect though, that it opens a new connection to RabbitMQ and logs in for every message, which is likely going to yield bad performance. But I don't know for sure.
I'm currently looking into doing something similar, it seems MySQL triggers may be the key:
http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html
and using those to execute something which posts to RabbitMQ
