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:
- the backup script mainly do 3 commands [stop slave; mysqldump; start slave;]
- 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?
- 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,
