Mysql Replication

---------------How to replicate Mysql database------------------------Source: http://dev.mysql.com/doc/refman/5.0/en/replication.html http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-databasereplication/ http://www.hackosis.com/2008/06/09/how-to-setup-mysql-replication-in-10-minutes/ http://www.howtoforge.com/mysql_database_replication http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html # The architecture consists of two boxes, Master and Slave # Backup all databases on Master and Slave if they exist mysqldump -u root -psecret --all-databases > backup.sql # Backup the my.cnf files on both boxes cp -a /etc/my.cnf /etc/my.cnf.todays.date # On the Master box add the following lines under [mysqld] section in /etc/my.cnf # Replication Values log-bin=mysql-bin server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 # Create a replication user account on Master # On the master run the following commands as the root user on the mysql prompt GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password'; FLUSH PRIVILEGES; # Restart Mysql on Master /etc/rc.d/init.d/mysqld restart # On slave configure the following under the [mysqld] section in /etc/my.cnf # Replication Values server-id=2 relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index # On master do the folowing in mysql prompt as root user FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; # Record the values generated # backup the dbs on master using the following mysqldump -u root -p --all-databases --lock-all-tables > dbdump.db # On slave copy the data dump from master scp root@master:/root/dbdump.db /root # On Slave drop all the databases # Stop Mysql by running the following command /etc/rc.d/init.d/mysqld stop

# Start SQL with the --skip-slave switch on the slave box /etc/rc.d/init.d/mysqld start --skip-slave # Import the data using the following command on the slave box mysql -u root -p < dbdump.db # On the slave box ,run the following commands in the mysql prompt slave stop; CHANGE MASTER TO MASTER_HOST='master_host_ip', MASTER_USER='slave_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; slave start; # On the Master box, run the following commands in the mysql prompt unlock tables; #Incase of any errors run the following command RESET SLAVE