DMYSQL Replication

Database Replication

To manage Database on both server should setup Replication. Debian

• On Master Server:

Edit the conf file /etc/mysql/my.cnf

Comment bind-address line:

       # bind-address          = 127.0.0.1

Uncomment the following and change the server-id if required:

       server-id = 1
       log_bin                 = /var/log/mysql/mysql-bin.log

Restart MySQL

   /etc/init.d/mysql restart
   Stopping MySQL database server: mysqld.
   Starting MySQL database server: mysqld.
   Checking for corrupt, not cleanly closed and upgrade needing tables..

Enter MySQL shell to create a save user account and password.

   mysql -u root -ppassw0rd

Create a slave user account in MySQL Shell

   CREATE USER 'rep1'@'%' IDENTIFIED BY 'slavepassword';
   GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'%' IDENTIFIED BY 'slavepassword';
   FLUSH PRIVILEGES;

Check the master status in MySQL shell

   SHOW MASTER STATUS;

You should then be presented with something similar to this:

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+

On Slave Server

Edit the file /etc/mysql/my.cnf and uncomment and modify the following to suite:

   server-id = 2

Restart MySQL

   /etc/init.d/mysql restart

Enter MYSQL shell to create a connection to the master:

   mysql -u root -ppassw0rd

Create a connection with the master database in MySQL Shell.

   CHANGE MASTER TO MASTER_HOST='X.X.X.X',
   MASTER_USER='rep1',
   MASTER_PASSWORD='slavepassword',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=106;

Note: Replace X.X.X.X with Master's IP address, mysql-bin.000001 with Master's log file name and 106 with Master's log file position.

“Query OK, 0 rows affected” should be displayed at the end of the command.

• Issue further commands in MySQL shell to start the slave.

   SLAVE START;
   SHOW SLAVE STATUS \G

The status should then be displayed and if all went well we should have the following contained within that output.

 Slave_IO_Running: Yes 
 Slave_SQL_Running: Yes

The last thing you should do is to dump main database, and import it into backup database.

Source: http://home.exetel.com.au/widget/blog/tag/debian-mysql-database-replication/

Note: Slave_SQL_Running will show No if any (I think) error occurs in Master. This document may help you:

http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/

CentOS

To enable Replication :

• On Master Server

Edit /etc/my.cnf and change these lines :

       [mysqld]
       bind-address = 0.0.0.0
       server-id=1
       log-bin=/var/run/mysqld/mysqld-bin

Restart mysql server

 /etc/init.d/mysqld restart

open mysql console and run commands

       mysql->CREATE USER 'slave_user' IDENTIFIED BY 'Hdfk@2@2js@h';" 
       mysql->GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY 'Hdfk@2@2js@h' ;
       mysql> use asterisk ;
       Database changed
       mysql> SHOW MASTER STATUS ;
       +-------------------+----------+--------------+------------------+
       | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
       +-------------------+----------+--------------+------------------+
       | mysqld-bin.000001 |     2335 |              |                  | 
       +-------------------+----------+--------------+------------------+
       1 row in set (0.00 sec)

• On Slave Server

Edit /etc/my.cnf and change this lines

       [mysqld]
       master-host=192.168.1.17
       master-user=slave_user
       master-password=Hdfk@2@2js@h
       relay-log=/var/run/mysqld/mysqld-relay-bin
       server-id=2

Restart mysql server

       /etc/init.d/mysqld restart

Restore database which you want to Replication Open mysql console and run these commands :

       mysql> use asterisk ;
       Database changed
       mysql> SHOW SLAVE STATUS\G
       mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000001' , MASTER_LOG_POS=2335 ;