question about backing up two databases

Discussion in 'Installation/Configuration' started by cruz, Nov 17, 2007.

  1. cruz

    cruz New Member

    I am setting up How To Set Up Database Replication In MySQL on debian perfet setup. I have entered binlog-do-db=exampledb twice, once for each database I want to backup. When I get to the setting up of the mysql, I am not sure what the corect entry is for two databases.
    HTML:
    USE exampledb;
    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;
    
    
    How can I enter both databases that I want backed up in here? Also on this part, what is the corect way to enter for two databases?
    HTML:
    mysqldump -u root -p<password> --opt exampledb > exampledb.sql 
    Also in the slave as well. can I enter both databases on here as well?
    HTML:
    mysql -u root -p
    Enter password:
    CREATE DATABASE exampledb;
    quit;
    
    
    HTML:
    mysql -u root -p<password> exampledb < /path/to/exampledb.sql 
    On this I would just add the last line twice with the other database?
    HTML:
    server-id=2master-host=192.168.0.100master-user=slave_usermaster-password=secretmaster-connect-retry=60replicate-do-db=exampledb
    Dose this not pertain to my setup if I chose the first setup? (dump) and not (load data from master)?
    HTML:
    If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:
    
    mysql -u root -p
    Enter password:
    LOAD DATA FROM MASTER;
    quit;
    
    If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb. 
    
    
    --------------------------------------------------------------------------------
    
    Finally, we must do this:
    
    mysql -u root -p
    Enter password:
    SLAVE STOP;
    
    In the next command (still on the MySQL shell) you have to replace the values appropriately:
    
    CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183; 
    
    MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100). 
    MASTER_USER is the user we granted replication privileges on the master. 
    MASTER_PASSWORD is the password of MASTER_USER on the master. 
    MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master. 
    MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master. 
    Now all that is left to do is start the slave. Still on the MySQL shell we run
    
    START SLAVE;
    quit;
    
    That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it! 
    
    
    
    
    
    Thank you for explaining this to me.
     

Share This Page