0

In case of title is not clear, I would like to explain myself further. I have 6 MySQL instances in my server. Let's call them, M1, M2, .., M6.

The reason I have 6 MySQL instances here, I have also other 6 servers which is hosting other websites. This server (according to my plan) will be acting as a Disaster Recovery Server. What my plan is;

  • Create replication from each other server to instances in DR Server.
  • Each instance will be slaves of other servers MySQL replication.
  • Phpmyadmin as GUI for my manager.

What I couldn't figure out is to make instances to be separate from them.

Anytime I import a DB like this;

mysql -u root -p xxxx -S /var/run/mysqld/xxxx.sock --host xxx.xxx.xx.xxx < /home/user/xxxx.sql

Which I edited my.cnf file and created instances, which they're all up and running, I also checked it with netstat -telpn, and ports are listening.

After I use above command and import dump to database, all other MySQL instances also can see this database in PhpMyAdmin.

I already setup phpmyadmin to allow me to check all databases in one url, buy adding $cfg.['server'] and port etc.

But when I import MySQL dump to specific instance, how can other instances have access to that database?

PS: Each instances has their own data paths;

  • M1 > /var/lib/M1
  • M2 > /var/lib/M2

edit for clarification:

My question is, how can I setup a multiple MySQL instances in one machine, and let those MySQL's be a replica member of another servers master. While doing this, by accessing those databases via phpmyadmin, any instances shouldn't access to other data.

M1 replication can access > M1 master's replication M2 can access > M2 master's.

enter image description here

2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Oct 1, 2021 at 12:07
  • The title says "dumps to". Perhaps you mean "replicates to"?
    – Rick James
    Oct 1, 2021 at 16:37

1 Answer 1

0

You have 2 servers; 12 MySQL instances?

On each of the 2 servers, do one or the other of:

Plan A: Multiple MySQL instances, each using a different my.cnf, Port, and base directory.

Plan B: Multiple Dockers, each with one MySQL instance. Configure Docker config maps incoming different port numbers to 3306. You should probably use a "volume" so that the base directory can persist rather than vanish when the Docker vanishes.

With either Plan, be sure to lower innodb_buffer_pool_size to account for having less RAM available. (Other settings may need lowering, too.)

Each Replica points the the Primary server, but with a different port number.

You might use Port 13306 for M1 and S1; 23306 for M2 and S2; etc.

6
  • Thanks for your input! I totally understand your logic, and will try to do it. But I'd be also extra happy if you can tell me why my current plan doesn't store databases to different locations. In mysql config files, I configured them to use separate ones and ports. But all of them just uses the same data directory.
    – emir ko
    Oct 5, 2021 at 13:08
  • What path are they in? Was /var/lib/M1 specified in the [mysqld] section?
    – Rick James
    Oct 5, 2021 at 18:39
  • Excuse my late reply, Paths are specified in config file as; ``` [mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld1.pid socket = /var/run/mysqld/mysqld1.sock port = 10001 datadir = /data/mysql/mysql1 ``` ``` [mysqld1] user = mysql pid-file = /var/run/mysqld/mysqld2.pid socket = /var/run/mysqld/mysqld2.sock port = 10002 datadir = /data/mysql/mysql2 ``` but anytime I connect to mysql by declaring socket file, all mysql instances can see other databases created in different instances.
    – emir ko
    Oct 6, 2021 at 6:28
  • Don't use the socket -- there is only one socket, and they will fight over it.
    – Rick James
    Oct 6, 2021 at 6:30
  • So I should connect them by specifying ports or data directories instead of socket.
    – emir ko
    Oct 6, 2021 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.