Running Multiple MySQL Instances on One Machine

WIP

Resources;

https://dev.mysql.com/doc/refman/8.0/en/multiple-servers.html

https://mariadb.com/kb/en/running-multiple-mariadb-server-processes/#systemd

https://www.percona.com/blog/2014/08/26/mysqld_multi-how-to-run-multiple-instances-of-mysql/

Master-Slave

https://www.toptal.com/mysql/mysql-master-slave-replication-tutorial this one rocks

https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

See also https://stackoverflow.com/a/2896718

and my previous post here

For an overview of Replication specific Filters, see here https://mydbops.wordpress.com/2021/09/24/replication-filters-in-mysql-an-overview/

 

See here for copies of my 3 cnf files from a working configuration on a Slackware box. 

See also [removed] for a wrapper type script called sql and [removed] for script to automate the configuration of setting up mysql instances, username's, passwords, master, slave replication etc.

Master-Master

https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

Initial Setup

Note: if we are using mysqld_multi, then we should disable the system default, which can be done by;

systemctl disable mysql

 

TODO find rc.local alternative to start our new instances.

 

Backup existing 50-server.cnf file and create cnf files or file with [mysqld*] groups

In my example, there will be a my.cnf file, master.cnf and slave.cnf

The my.cnf file will have an !include master and !include slave.cnf entries.

Ensure there are no other !includes that allow other cnf files to run with [mysqld] groups.

The master and slave will have [mysqld*] groups, with basic settings for both being shown below;

#master

server-id           = 1

port                = 3306

socket              = /var/run/mysqld/mysqld.sock

pid-file            = /var/run/mysqld/mysqld.pid

datadir             = /var/lib/mysql

log_error           = /var/log/mysql/error_slave.log
log_bin                     = /var/log/mysql/mysql-bin.log

#slave

server-id           = 2

log-slave-updates

port                = 3307

socket              = /var/run/mysqld/mysqld_slave.sock

pid-file            = /var/run/mysqld/mysqld_slave.pid

datadir             = /var/lib/mysql_slave

log_error           = /var/log/mysql_slave/error_slave.log

relay-log           = /var/log/mysql_slave/relay-bin

#relay-log-index     = /var/log/mysql_slave/relay-bin.index

#master-info-file    = /var/log/mysql_slave/master.info

#relay-log-info-file = /var/log/mysql_slave/relay-log.info

read_only           = 1

The master and slave must be have different values for;

socket

pid-file

datadir

log_error

slave will also need (as shown above);

relay-log

relay-log-index

master-info-file

relay-log-info-file

Note: If we wish the Slave to also create binary logs (which we will need if we wish to use it as a Master one day) [source] then we need to add log-slave-updates

We also need to add the following to the my.cnf file or add a third file and add a !include to the my.cnf file.

[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user       = multi_admin

password   = multipass

We need to create the directories;

mkdir -p /var/lib/mysql_slave

chmod --reference /var/lib/mysql /var/lib/mysql_slave

chown --reference /var/lib/mysql /var/lib/mysql_slave

 

mkdir -p /var/log/mysql_slave

chmod --reference /var/log/mysql /var/log/mysql_slave

chown --reference /var/log/mysql /var/log/mysql_slave

Make the data dir;

mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

Start both instances (or just the slave)

mysqld_multi start 2

mysqladmin --host=127.0.0.1 --port=3307 -u root password mypassword

Now quit and connect to the first instance (stopping the slave and starting the master if you prefer)

mysql --host=127.0.0.1 --port=3306 -uroot -p

or if only the master is running;

mysql -u root -p 

When connected, run;

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

FLUSH PRIVILEGES;

Exit from the MySQL client, and connect to the slave instance:

mysql --host=127.0.0.1 --port=3307 -uroot -p

When connected, again run;

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';

FLUSH PRIVILEGES;

Now to setup the replication

In our master.cnf file, add or uncomment the following;

log_bin                     = /var/log/mysql/mysql-bin.log

innodb_flush_log_at_trx_commit  = 1

sync_binlog                 = 1

binlog-format               = ROW

Then restart the master instance.

mysqld_multi stop 1

mysqld_multi start 1

We now need to connect to the master and create a replication user;

mysql -uroot -p --host=127.0.0.1 --port=3306

or if only the master is running;

mysql -u root -p 

mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

We now need to create a dump of the master database structure;

mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

or if only the master is running;

mysqldump -u root -p --all-databases --master-data=2 > replicationdump.sql

I'm not exactly sure if --all-databases is a good idea, in the sense that I don't know if we actually want to replicate the mysql database for example. So we can always do;

 

mysqldump -u root -p --databases system test --master-data=2 > replicationdump.sql

 

where we want to replicate system and test.

 

Regardless we then need to do the following;

cat replicationdump.sql | less

and look out for;

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=349

Make a note of the values, we'll need them in a bit.

 

While the above is required to copy the database structure, the above information can also be found by logging into the master instance and doing;

 

SHOW MASTER STATUS;

Start the slave and import the dump as follows;

mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

 

We now need to connect to the slave;

mysql -uroot -p --host=127.0.0.1 --port=3307

and execute the following;

CHANGE MASTER TO

MASTER_HOST='127.0.0.1',

MASTER_USER='replication',

MASTER_PASSWORD='replication',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=349;

NOTE: the above changes where the SLAVE will look for it's MASTER and at what point it will start getting data from.

Then to start the slave;

START SLAVE;

Finally, to verify the replication is up and running;

SHOW SLAVE STATUS \G;

Additional Information

mysql_multi usage;
mysqld_multi report
mysqld_multi start *
mysqld_multi stop *

the following will read a single cnf file ONLY;

 

mysqld_multi --defaults-file=my2.cnf report

 

while the following will read an additional cnf file;

mysqld_multi --defaults-extra-file=my2.cnf report 

 

and of course report can of course be replaced by start/stop etc, referencing any mysqld groups in any read cnf files, including the one passed.

 

The --no-log option can be quite helpful as is --password=

 

If we want to check our cnf files and read an option group within a my.cnf config file, then we can do so as follows;

my_print_defaults --config-file=/etc/mysql/my.cnf --mysqld0 

where mysqld0 is the group.

 

It's worth noting though, that I have seen instances whereby the above will show no issues, while mysqld_multi will not ready additional files denoted by the !include directive.

So if we have a file my.cnf which contains !include to additional files and these other files contains groups, such as mysqld0 and mysqld1 while the my_print_defaults will ready the file and also the included files, mysqld_multi may not. (this was on older versions of Debian and mysql)

A note of phpMyAdmin;

When setting up 2 instances as above, it has been noted that I was unable to connect using phpmyadmin and I found that I was able to resolve this by making a change to the config.inc.php file.

Changing the host value from localhost to 127.0.0.1

A note on connecting to each instance;

I also found that I was unable to login to the default instance as per;

 

mysql -u root -p

 

I found I had to use;

 

mysql --host=127.0.0.1 --port=3306 -u root -p

 

or whichever port value we are using.

 

I believe this can be resolved by adding the socket in the mysql.cnf file. (untested). This isn't the case where the default socket is used in the master.cnf file for example. so in cases where we are using the default data directory, pid file and socket file, this wont be a problem.

In my opinion, this isnt actually an issue, a bit of a minor inconvenience, granted but it does ensure that you are connecting to the correct instance by using the host and port.

 

Extract relating to binlog_do_db and replicate_do_db from ChatGPT

On the slave server, replicate-do-db should be used to control which databases are replicated from the master server, while on the master server, binlog-do-db should be used to control which databases are written to binary logs.

The binlog-do-db parameter tells the master server to write binary logs only for the specified databases, and the replicate-do-db parameter tells the slave server to replicate only the specified databases from the binary logs generated by the master server.

It's important to note that these parameters work independently of each other. Configuring binlog-do-db on the master server does not automatically configure replication for those databases on the slave server, and configuring replicate-do-db on the slave server does not automatically configure binary logging on the master server.

Therefore, you need to configure both parameters appropriately on both the master and the slave server to ensure that only the desired databases are written to binary logs and replicated to the slave server.