Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

DBI mysql router mysqlrouter cluster

by RedJeep (Sexton)
on Jun 30, 2022 at 19:07 UTC ( #11145207=perlquestion: print w/replies, xml ) Need Help??

RedJeep has asked for the wisdom of the Perl Monks concerning the following question:

Hello friends, I am exploring using MySQL cluster with MySQL router with Perl 5. I have not been able to find any information on how to modify my database interface in Perl to connect to a cluster. Have any of you done this? My objective is for both fail over and scaling. Here is my existing code for how I connect to MySQL. Currently, this is just a single instance of MySQL.
use DBI; use strict; use warnings; my $driver= "mysql"; my $dsn = "DBI:$driver:database=$database;host=$host"; my $dbh = DBI->connect($dsn, $user, $pw); $dbh->do('INSERT INTO test_table (fname, lname, email, comment) VALUES + (?, ?, ?, ?)', undef, $fname, $lname, $email, $comment);
The above works fine. My hope is that MySQL clustering magically lets me use DBI and everything just like above. However, in the literature it seems that I would point my application to a MySQL router (mysqlrouter) instead of directly to the MySQL database. If I should be taking an entirely different direction to clustering for fail over and scaling feel free to let me know. The only requirements for the project are that we stick with Perl 5 and MySQL. Thank you in advance!

Replies are listed 'Best First'.
Re: DBI mysql router mysqlrouter cluster
by dsheroh (Monsignor) on Jul 02, 2022 at 11:02 UTC
    n the literature it seems that I would point my application to a MySQL router (mysqlrouter) instead of directly to the MySQL database
    That is correct. Basically, as far as your application is concerned, mysqlrouter is the mysql server, so you connect to that and send requests to it as if if were a standalone database. mysqlrouter will then decide which backend server(s) each request should be sent to (based on availability, loading, type of query, etc.) without your application having to know or care about these details.
Re: DBI mysql router mysqlrouter cluster
by Anonymous Monk on Jul 01, 2022 at 13:57 UTC
    As far as I understand the documentation, MySQL Router is entirely transparent and speaks the same MySQL protocol as the database itself. It should be possible to just switch the address and the port. What have you tried? What didn't work? What happened instead?
      Yes. It looks like you are correct and I was over-complicating things. It seems that a single replication model of write to primary server, replicate to secondary server and query from secondary server will do what I need. Thanks!

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11145207]
Approved by johngg
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2022-08-19 08:55 GMT
Find Nodes?
    Voting Booth?

    No recent polls found