The Demo Site

You have a mysql database. now you want to have a backup database which will contain all of the backup of your live database, that means all the live transection will be replicated on your backup database.

For business point of view this is very important!
Bellow are the some easy steps to made online backup database.


For example you have two database on different machine with different ip, namely
master 10.10.10.100
slave 10.10.10.111

ON MASTER:

1. create an user rep
2. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.10.10.111' IDENTIFIED BY 're
plpass';
3.Setting the Replication Master Configuration (my.cnf or my.ini file)
[mysqld]
log-bin=mysql-bin
server-id=1

ON SLAVE

4.Setting the Replication Slave Configuration
[mysqld]
server-id=2

again ON MASTER
5. Obtaining the Master Replication Information
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;

here you will find like following info
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=1123077;


6. Creating a Data Snapshot Using mysqldump
mysql> FLUSH TABLES WITH READ LOCK;

shell> mysqldump --all-databases --lock-all-tables >dbdump.db
"C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqldump.exe" -u root -pmypassword* --opt swa > swa.sql

ON SLAVE

7. Create same database with same name and up the damped file.

8.Setting Up Replication with New Master and Slaves
Execute the CHANGE MASTER command to set the master replication server configuration

CHANGE MASTER TO
MASTER_HOST='10.10.10.100',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000006',
MASTER_LOG_POS=1123077;

9.mysql> START SLAVE;


10. be happy :-)

please leave your comment....


More info in bangla

More information