Questions[master-master](server)

I am trying to setup multi source replication. In my slave I have defined following two properties in its configuration file.

master-info-repository=table  
relay-log-info-repository=table

These entries are needed because I am defining channels in command "CHANGE MASTER TO" as below:

change master to master_host="127.0.0.1", master_port=20000, master_user="replication",master_password="password1" for channel="master1";

Above command is showing error:

ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. Yo u must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.

SERVER ID is clearly defined in config file.

If I don't define those two properties (shown on top) in config file and start slave without "for channel" the it works fine. Only when I try to define multi source it is showing this error.

Error log file contain following entries:

2017-01-01T12:41:54.446764Z 0 [ERROR] Error in checking mysql.slave_master_info repository info type of TABLE.
2017-01-01T12:41:54.446764Z 0 [ERROR] Error creating master info: Error checking repositories. 2017-01-01T12:41:54.446764Z 0 [ERROR] Failed to create or recover replication info repository.
2017-01-01T12:41:54.446764Z 0 [ERROR] Failed to create or recover replication info repositories.

How do I fix this issue?

EDIT
One thing I came to know that this is related to 5 tables innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info, slave_worker_info. Even if I delete those tables and then create again then it still shows same error. By the way I am using multiple instance on a single machine.

I am trying to implement a master-master replication between 3 MySQL Databases running on 3 different servers.

I implemented master-master replication for 2 servers, but can't figure out how can I implement if for 3 different MySQL Servers.

Does anyone know how to accomplish this?

I set up MySQL master-master replication on my 2 Ubuntu VM. Configuration and connection done correctly according to this guide. Here is my problem.

1. I print the master status on master 1 and it gives position XXX, then I jump to Master 2 and print slave status it is also prints its position as XXX.
2. I change any configuration beside the must-replicated db position changes. (As usual)
3. Problem is this when I insert any record to must-replicated db position does not change. Also record does not replicated to Master 2 must-replicated DB.

In logs also it does not give any info about replication.

Here is my 1st master-slave configuration in my.conf files:

Master:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=REPLICATE_DB
server-id=1

Slave:

server-id=2
master-host=10.1.1.171
master-user=slave_user
master-password=your_password
master-connect-retry=60
replicate-do-db=REPLICATE_DB

I have configured two MySQL servers (MySQL-1, MySQL-2) in master-master replication in the same datacenter using a local backend connection with the following options:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

We use a load balancer to round robin MySQL requests back and forth equally between the two MySQL servers. This works great, but I am concerned about replication lag. For example, if user A inserts a row into MySQL-1, then user A selects from MySQL-2, the data may have not been successfully replicated.

Basically, my question is, how much lag should be expected (milliseconds, seconds)? Are their anymore MySQL options to set to prevent/reduce lag?

I have implemented a HA solution for mysql based on master-master replication. There is a mechanism on the front end part which guarantees that only one db will be read/written to at a given time (i.e. we only use replication for HA).

I have confirmed replication works as expected, but I am wondering about failure scenario and recovery. In particular, I worry about what happens when one master fails in an unrecoverable state, and needs to be recreated from the other master:

• As the other master is live and most likely used, I cannot lock it and create dumps from mysqldump (our databases are moderately large, and mysqldump can easily take hours after a few months of usage).
• Even assuming I have a dump, it is crucial that the binlog position as shown by SHOW MASTER STATUS correspond to the dump being done after the database has been locked.

The simple solution to the first problem is to use a third database acting as a backup, from which I can do the mysqldump. But then how do I make sure the recreated master can start replication from the running master in a consistent way?