{"id":353,"date":"2010-07-20T05:48:00","date_gmt":"2010-07-20T11:48:00","guid":{"rendered":"http:\/\/blog.supportpro.com\/?p=353"},"modified":"2019-10-30T06:11:26","modified_gmt":"2019-10-30T12:11:26","slug":"one-way-replication-of-mysql-database","status":"publish","type":"post","link":"https:\/\/www.supportpro.com\/blog\/one-way-replication-of-mysql-database\/","title":{"rendered":"One way replication of mysql database:"},"content":{"rendered":"

Mysql replication help us in keeping the data replicated to one or more sites reliably with binary logs. Apart from good amount of advantages Mysql replication doesnt help with data corruption, since the corrupted data is replicated in all slaves. It is good to have periodic backup of database apart from replication.<\/p>\n

<\/p>\n

Replication Steps :<\/p>\n

    \n
  1. Create database with same name on all servers:> mysql -u db_user -p -e “CREATE DATABASE db_name”<\/li>\n
  2. Create database user with replication privilege on master
    \n> GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@’replication_clients” IDENTIFIED BY ‘replication_password’This can be also supplied with particular database name with ;db_name.* instead of *.*<\/li>\n
  3. Edit Mysql master configuration (my.cnf) to allow replication
    \n[mysqld]
    \nserver-id = 1 # Important with replication
    \nlog-slave-updates
    \nlog-bin = \/var\/lib\/mysql\/mysql-bin
    \nlog-bin-index = \/var\/lib\/mysql\/mysql-bin.index
    \nreplicate-do-db = db_name # specify the dbs to replicate
    \nlog-warnings
    \ninnodb_flush_log_at_trx_commit=1
    \nsync-binlog=1
    \ninnodb_safe_binlog<\/li>\n
  4. Take dump of master db and put them on all replicas
    \nuse db_name;
    \nFLUSH TABLES WITH READ LOCK;<\/li>\n<\/ol>\n

    $ mysqldump -u dbuser -p db_name > db_dump.sql
    \ninstall on slaves
    \n$ mysql -u dbuser -p db_name > db_dump.sql<\/p>\n

    use db_name;
    \nUNLOCK TABLES;<\/p>\n

    5. Edit mysql configuration on replicas with master credentials
    \n[mysqld]
    \nold_passwords=1
    \nserver-id=2
    \ninnodb_file_per_table
    \nlog-slave-updates
    \nmaster-host = master_hostname
    \nmaster-port = master_port
    \nmaster-user = master_user
    \nmaster-password = master_password
    \nlog-bin = \/var\/lib\/mysql\/mysql-bin
    \nlog-bin-index = \/var\/lib\/mysql\/mysql-bin.index<\/p>\n

    Restart the Mysql daemon on all servers and check the replication status:
    \nMaster: > show master status;
    \nReplicas: > show slave status;<\/p>\n

    If you require help, contact SupportPRO Server Admin<\/a><\/p>\n

    \"Server<\/a><\/span>