HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Installation/Configuration (http://www.howtoforge.com/forums/forumdisplay.php?f=4)
-   -   question about backing up two databases (http://www.howtoforge.com/forums/showthread.php?t=17421)

cruz 17th November 2007 21:50

question about backing up two databases
 
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 Code:

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 Code:

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 Code:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;

HTML Code:

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 Code:

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 Code:

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.


All times are GMT +2. The time now is 03:11.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.