• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1179
  • Last Modified:

Rebuild master from slave ( MASTER MASTER ACTIVE ACTIVE Replication)

HI
i had Master master Active active replication

then Master 1 , died, so it was running on Master 2

now today i fixed the Master1, and now have to replication all data from Master2

so what i did is:
i made the broken Mastser1 as slave first
and my.cnf for master1 :
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

master-host     = xxxxxxxxx
master-user     =noslave
master-password = xxxxxx
master-port     = 3306
#auto_increment_increment= 2
#auto_increment_offset   = 2
log_slave_updates

and when i take slave status\G;


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxx
                  Master_User: xxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 4080379
               Relay_Log_File: mail-relay-bin.000006
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1049
                   Last_Error: Error 'Unknown database 'fosiul'' on query. Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 4082090
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1049
               Last_SQL_Error: Error 'Unknown database 'fosiul'' on query. Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'
1 row in set (0.00 sec)

ERROR:
No query specified



why its failing to create database from Master2 ?
0
fosiul01
Asked:
fosiul01
  • 13
  • 9
1 Solution
 
arnoldCommented:
You must restore the database on the master1 first.
On master2 clear all binary logs. perform a backup of the database.
Copy the backup file to master1.
Restore the database from the file on master1
Reestablish replication relationship.  And you should be set.

make sure your auto-increment* parameters are set correct or you would run into errors because of an ID column collision between the two active/active servers.
0
 
fosiul01Author Commented:
While copy, do i have to stop mysql server from Master2 ???
0
 
fosiul01Author Commented:
what you meant by clear binary logs ?? clear logs in Master1 or MAster 2??
you meant delete ?? if i delete binary logs, then if i have a slave server( i dont have, just saying) the how that slave server will keep the update??

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
arnoldCommented:
You do not need to stop mysql on Master2 while you are restoring the database on Master1. From the point of the backup the binary logs will be accumulating.
Once the database is restored on Master1 and the replication is reestablished, master1 will start using the binary logs from master2 that have accumulated to bring the database up-to-date.
If you do a backup while mysql is not running, you would clear the binary logs after the backup is complete.
The idea is that the binary logs on Master2 will have the information/transactions to bring data in the backup file up-to-date.
0
 
arnoldCommented:
I think my prior post answered your question, but just in case.
Currently Master2 has the database with 20,000 records  To manage space, master2 maintains 20,30 binary log files.
Master1 has os/application installed but is missing the database.
If you restore the database on master1 and reestablish replication, it will start processing all 20,30 transactions that could/would corrupt your data.  I.e. an update in the earliest binary log file could affect a row that was not present at the time the update was issued.  Inserts would either cause duplicate entries or generate error messages that such a record already exists. The delete directive would/could delete an entry that was not present at the time.
effectively what you are doing is:
getting a current snapshot of the database and you only want to binary logs from this point on to be provided to the master1 server when database restore is completed and the replication is reestablished.
You can/should move the binary logs on Master2 to a different location for your backup/dr plan until the backups/binary logs on master2 get to your backup/dr plan settings.  i.e. 20,30 binary logs from the last full backup.
0
 
fosiul01Author Commented:
hmmmm

i am confused

Ok

1.I copyed all the database from Master2 to master 2.
2. then i started mysql server.

Reports:

show slave status:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 2xxxx
                  Master_User: xxxxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 4080379
               Relay_Log_File: mail-relay-bin.000006
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1005
                   Last_Error: Error 'Can't create table 'testweb' (errno: 13)'                                                                              on query. Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 4082813
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1005
               Last_SQL_Error: Error 'Can't create table 'testweb' (errno: 13)'                                                                              on query. Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'

Master status :

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |  4080379 | fosiul,hesk  | mysql,test       |
+------------------+----------+--------------+------------------+


if you see, mysql-bin and position is same for both master and slave

still why the problem>??

0
 
fosiul01Author Commented:
Ok
there was a permisison issue, so i fixed that, now problem is, Slave is trying to create table from master...

but it should not ,r ight ?? becasue i copied all the database from master to slave


here is error now

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxxxx
                  Master_User: xxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 4080379
               Relay_Log_File: mail-relay-bin.000006
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'testweb' already exists' on query.                                                                              Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 4083536
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'testweb' already exists' on query.                                                                              Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'
1 row in set (0.00 sec)

ERROR:
No query specified


what to do now ?? i think you said, some thing about delete binlog, but i did not understand that


0
 
arnoldCommented:
login into the master1 mysql server and run use fosiul.

error 13 from prior posts means  access denied/permission denied.  After you restored the database did you make sure that the database files in /var/lib/mysql/data or where you have your mysql data files are owned by mysql and not root?

The position means that the replication is established.  The problem on master1 is that when it tries to run a transaction on a database it is either unable because the database does not exist or it can not write into the DB files.
0
 
fosiul01Author Commented:
error 13 from prior posts means  access denied/permission denied.  After you restored the database did you make sure that the database files in /var/lib/mysql/data or where you have your mysql data files are owned by mysql and not root?


 you are 1 post behind!!!

i fixed permission issue

now
 Last_Error: Error 'Table 'testweb' already exists' on query.    Default database: 'fosiul'. Query: 'create table testweb (
id INT,
data VARCHAR(100)
)'


check my ID:26082688

why ots trying to create table??
0
 
arnoldCommented:
Do You know Murphy's Law?  I think Murphy is your unwanted guest this past month.

When replication is established, the slave tries to get the master to send it all the data from the binlog files.
When the master server starts sending transaction from days,weeks, months ago depending on how many binlog files you keep, certain transactions in the old files will conflict with existing data.
I.e. trying to create a table that already exists or trying to delete a table that does not exist.

The type of error you have do not break/halt replication.  I.e. future transaction will still propagate from master2 to master1.  

The procedure to restore a failed master or slave is to clear the master's logs.
You can not start the restored system from scratch and have the replication process bring it up to date.  You have to bring the failed serer as close as possible to being up-to-date such that when the replication is reestablished only the recent transaction (since the backup was taken) will be sent to the subordinated server.
This is why I said you need to clear the binlog log files to avoid master1 going through transactions that are reflected in the backup from which it was restored.

Yes it processing the transactions from the binlog entries done yesterday, a week, a month, a year ago.
how far back do the binlog files on the Master2 go?
0
 
fosiul01Author Commented:
Do You know Murphy's Law?  I think Murphy is your unwanted guest this past month.  :: wht is murfys law??!!!

how far back do the binlog files on the Master2 go?: 4 days.

and how u clear log??
0
 
arnoldCommented:
Murphy's law if something can go wrong it will go wrong.
http://en.wikipedia.org/wiki/Murphy%27s_law

On the master you run reset master or you can run purge binary logs  to "a particular file".
i.e.
purge binary logs to "mysql-log.0010"
#the above will delete all files from .0000 to 0010
or
purge binary logs before '2009-12-18 00:00:00'
#the above will delete all the binlog files whose timestamps fall before the date

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

Note in the show slave status, the master server has mysql-bin.0006 while the slave mysql server is processing mysql-log.0005.

You might want to restart the process.  Break the replication on master1.
reset master,create backup on master2.
restore the backup on master1.
reestablish replication.
0
 
fosiul01Author Commented:
hahahahahaha
ok thats works!!!!

it was,

reset master in master 2

reset slave in master1
stop slave  in master1

start master in master1


now have to set this master1 as master
0
 
fosiul01Author Commented:
its start slave in master1
0
 
arnoldCommented:
I am not sure what you mean.
you dealt with master2 stopping to retrieve data from master1.
Persumably once you established/reconnected master1 to master2 as a slave and both now have the same information, you want to reestablish the master/slave relationship between master2 and master1 to complete your active/active setup.





 
0
 
fosiul01Author Commented:
i just restore master1 from master2. have to make master1 as master for master2

its master master active active replication
0
 
fosiul01Author Commented:
yapppppp both done

now its working as Master master ACtive Active..

thanks


0
 
arnoldCommented:
Yes, I understand that part.  
You reestablished replication on master1.
I think you would need to reset the slave on master2
i.e. check show slave status on master2 and see what file it is looking for if it is not in an error state.

The problem is that before you reestablish the replication from master1 to master2 make sure that master1 database is as close to identical to master2 or the corruption/erroneous entries from the logs master1 processed will make their way into your existing known good database on master2. Or reset master on master1 to purge its binary logs prior to reestablishing the master2 slave to master1 master replication.
0
 
fosiul01Author Commented:
LOL
you are behind 1 post again!!!



0
 
arnoldCommented:
locking the doors to keep "Murphy" out.
0
 
fosiul01Author Commented:
lol , how long will you keep murphy out!!

0
 
fosiul01Author Commented:
Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now