We have two MySQL databases set up as a master - master set ups. We have had issues replicating data from host1 to host2. I am a database management beginner.
The error log is given below
host1:/$ cat /var/log/mysql/error.log
131115 13:51:06 [ERROR] Slave I/O: error reconnecting to master 'host1@host.company.com:3306' - retry-time: 60 retries: 86400, Error_code: 2003
The error keeps repeating. I have gone through this tutorial http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html and this http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html for help on 2003 error code.
I verified from our logs that mysqld was running all the time and that there was no downtime in mysql server.
My grants for the host2 that successfully replicates the data from host2 to host1 are given below
mysql> show grants;
-------------------------------+
| Grants for admin@localhost |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*53CB11958EE3FBA4B6C0CECA582369151A97FFA9' |
+-------------------------------------------------------------------------------------------
My grants on host1 that does not replicate successfully from host1 to host2 are given below.
mysql> show grants;
+-------------------------------------------------------------------------------------------
| Grants for admin@localhost |
+-------------------------------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD '*53CB11958EE3FBA4B6C0CECA582369151A97FFA9' |
| GRANT ALL PRIVILEGES ON `MyDatabase`.* TO 'admin'@'localhost' |
+-------------------------------------------------------------------------------------------
Master status: host2 (replicating correctly to host 1)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000235 | 12804977 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
host1 (not replicating correctly to host 2)
mysql> show master status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation
I am not quite sure what it is I am doing wrong. I looked at host2's my.cnf that does replicate successfully to host has the following delta. Both host1 and host2 have different server-id parameter configured. Both values are greater than 0.
server-id = 1
replicate-do-db = MyDatabase
innodb_buffer_pool_size = 2G
innodb_flush_method = O_DIRECT
read_buffer_size = 64M
sort_buffer_size = 64M
My host1 my.cnf delta
server-id = 3
I don't know what caused the replication to fail. I looked at this SO link answers before posting here but I don't quite know what I need to do.
Mysql Master Master Replication, MySQL replication - Error connecting to master and have not been able to figure out what I have done wrong. Both hosts on the db have the same username and password.
