Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I was checking the mysql backup script that being designed by senior mysql admin (left long time ago), just in order to implement the same setup for different client. all the steps were clear and match the guides and instructions provided in mysql official references, until i found that the backup script is being executed/scheduled in the wrong machine (according to my knowledge) due to the following factors:

  1. the backup script mainly do 3 commands [stop slave; mysqldump; start slave;]
  2. the Backup script is scheduled to run on the master mysql server NOT on the slave although stop slave and start slave should be invoked from slave mysql server. RIGHT?
  3. to double check that, i found the master contain up-to-date files in format "hostname-relay-bin.NUMBERS" and that files generated by slave only.

so that ensure that the master mysql server is is being SELF-SLAVED!

i know that single mysql server can be master-slave at the same time but different dbs but our setup is very simple and straight forward FORM of replication in which slave fully replicate the master. and i'm 100% sure that the server i'm talking about is the master not the slave so i'm not mistaken.

I tried to find explanation for that but couldn't, so i think that senior guy was in hurry when he did the scheduling task.

UPDATE
Backup script in brief [its scheduled on master but i'm arguing it should be scheduled on slave machine]

mysqladmin.exe --user=%mysqluser% --password=%mysqlpwd% stop-slave  
mysqldump.exe --user=%mysqluser% --password=%mysqlpwd% --lock-all-tables --databases db1 db2 > %backupworkingdir%\backup-%backupname%_%mydate%.sql  
mysqladmin.exe --user=%mysqluser% --password=%mysqlpwd% start-slave  

show slave status\G; output on MASTER

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.xx.xx.xx
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000093
          Read_Master_Log_Pos: 6354
               Relay_Log_File: Host-DB-relay-bin.000002
                Relay_Log_Pos: 1681
        Relay_Master_Log_File: mysql-bin.000029
             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: 1062
                   Last_Error: Error 'Duplicate entry '192.xx.xx.xx-user'
 for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO mys
ql.user(User, Host, Password, ssl_cipher, x509_issuer, x509_subject) SELECT 'user', '192.xx.xx.xx', Password, ssl_cipher, x509_issuer, x509_subject FROM
mysql.user WHERE User='user' ORDER BY Host LIMIT 1'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2775
              Relay_Log_Space: 1824235
              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: 1062
               Last_SQL_Error: Error 'Duplicate entry '192.xx.xx.xx-user'
 for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO mys
ql.user(User, Host, Password, ssl_cipher, x509_issuer, x509_subject) SELECT 'user', '192.xx.xx.xx', Password, ssl_cipher, x509_issuer, x509_subject FROM
mysql.user WHERE User='user' ORDER BY Host LIMIT 1'
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

but on slave machine the slave status is showing no errors.

please advice and enlight me in case i'm mistaken,
thanks,

share|improve this question

1 Answer 1

  1. You can backup the MySQL from a remote server.

  2. Would you mind posting your backup script?

  3. Looks like the binary log files. It is enabled by log-bin option whether master or slave.

so that ensure that the master mysql server is is being SELF-SLAVED!

You can check with: mysql> show slave status\G

share|improve this answer
    
yes, [log-bin=mysql-bin] exist in both slave and master. but what does that got to do with scheduling the backup on the wrong machine! –  jadook Aug 21 '11 at 4:31
    
i added backup script above! –  jadook Aug 21 '11 at 4:34
    
Also append the mysql> show slave status\G output (on the master) to your original post. –  quanta Aug 21 '11 at 4:42
    
i do added the requested command! –  jadook Aug 21 '11 at 10:19
    
the 192.xx.xx.xx is the ip of the slave machine. again the setup shouldn't be master-master, our requirements were clear which is master-slave only! –  jadook Aug 21 '11 at 10:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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