Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a complete backup of one database from master to slave. Now I want to back up two databases from master to slave. But use the same ways it doesn't work. What should I do?

Master's conf

[mysqld]
#master configure  
server-id = 1  
log-bin=mysql-bin  
binlog-do-db=webdb,just_test 
binlog-ignore-db=mysql  
#master configure 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave conf

[mysqld]
#configure master-slave  
server-id=2  
replicate-ignore-db=mysql  
replicate-do-db=webdb,just_test
#configure master-slave  
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

And the backup method is like this:

In master I do this

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@192.168.74.236 IDENTIFIED BY 'hello';  
Query OK, 0 rows affected (0.00 sec)  

mysql> GRANT FILE,SELECT,REPLICATION SLAVE  ON *.* TO slave@192.168.74.236 IDENTIFIED BY 'hello';  
Query OK, 0 rows affected (0.00 sec)  

mysql> FLUSH TABLES WITH READ LOCK;  
Query OK, 0 rows affected (0.00 sec) 

Then cd /var/lib/mysql and tar -cvf data.tar web_db just_test (web_db & just_test is the data fold which I want to back up). Then scp data.tar to slave and tar -xvf and ....

Back to master and do

mysql> UNLOCK TABLES;

Then restart mysqld service both in master & slave. Then run the flowing on master:

mysql> SHOW SLAVE STATUS\G  
Empty set (0.00 sec)  

mysql> SHOW MAster STATUS\G  
*************************** 1. row ***************************  
            File: mysql-bin.000001  
        Position: 106  
    Binlog_Do_DB: webdb  
Binlog_Ignore_DB: mysql  
1 row in set (0.00 sec) 

Then run following in slave

[root@Slave mysql]# mysql -h 127.0.0.1 -u root -phello  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 2  
Server version: 5.1.69 Source distribution  

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.  

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  

mysql> CHANGE MASTER TO MASTER_HOST='192.168.74.235',MASTER_USER='slave',MASTER_PASSWORD='hello',MASTER_LOG_FILE='mysql-

bin.000001';  
Query OK, 0 rows affected (0.02 sec)  
 mysql> start slave

And this is the slave status

mysql> show SLAVE status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.74.235
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: webdb,just_test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 407
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec

And this is the error log:

130829 15:39:49 [Note] Error reading relay log event: slave SQL thread was killed
130829 15:39:49 [Note] Slave I/O thread killed while connecting to master
130829 15:39:49 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
130829 15:39:51 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.74.235', master_port='3306', 

master_log_file='', master_log_pos='4'. New state master_host='192.168.74.235', master_port='3306', master_log_file='mysql-

bin.000001', master_log_pos='4'.
130829 15:39:56 [ERROR] Slave I/O: error connecting to master 'slave@192.168.74.235:3306' - retry-time: 60  retries: 86400, 

Error_code: 2013
130829 15:39:56 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 4, relay log 

'./mysqld-relay-bin.000001' position: 4
130829 15:41:56 [Note] Slave I/O thread: connected to master 'slave@192.168.74.235:3306',replication started in log 'mysql-

bin.000001' at position 4
130829 15:42:20 [Note] Error reading relay log event: slave SQL thread was killed
130829 15:42:20 [Note] Slave I/O thread killed while reading event
130829 15:42:20 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 106
130829 15:42:23 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.74.235', master_port='3306', 

master_log_file='mysql-bin.000001', master_log_pos='106'. New state master_host='192.168.74.235', master_port='3306', 

master_log_file='mysql-bin.000001', master_log_pos='106'.
130829 15:42:27 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 106, relay log 

'./mysqld-relay-bin.000001' position: 4
130829 15:42:27 [Note] Slave I/O thread: connected to master 'slave@192.168.74.235:3306',replication started in log 'mysql-

bin.000001' at position 106

What should I do ?

share|improve this question

migrated from stackoverflow.com Sep 2 '13 at 17:04

This question came from our site for professional and enthusiast programmers.

    
We need some more details before we can help. Are the servers running replication or have you scheduled jobs which backup then transfer databases? If replicating, is it working properly? Are you replicating the whole server or just specific databases? Finally and most importantly, what have you tried already if anything? Have you read the manual? What problems have you encountered? –  DaveyBoy Sep 2 '13 at 14:50
    
Do you mean you want to set up replication to two slaves, with one back up? –  Tony Hopkinson Sep 2 '13 at 14:58

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.