Kategori arşivi: PHPMyAdmin

CentOS Ubuntu mysql master slave replication

On Master CentOS

  1. Open: /etc/my.cnf
  2. Add under [mysqld]:
    bind-address = [server IP]
    server-id = [3419111]
    log_bin = mysql-bin.log
    log_error = /var/log/mysql/mysql-bin.err
    binlog_do_db = [master_databasename1]
    binlog_do_db = [master_databasename2]
    binlog_do_db = [master_databasename]

    #binlog_do_db = booking_engine #log only this db as a master, for slaves
    #replicate-do-db = yachting_charter #only replicate(sync) this db as a slave from master of this mysql server

  3. service mysql restart
  4. mysql -u root -p
  5. GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
  6. FLUSH PRIVILEGES;
  7. quit;
  8. Backup master_databasename

 

On Slave Ubuntu

  1. Copy master databases
  2. Open: /etc/my.cnf
  3. Add under [mysqld]:
    server-id = [345342532]
    log_bin = /var/log/mysql/mysql-bin.log
    binlog_do_db = [master_databasename1]
    binlog_do_db = [master_databasename2]
    binlog_do_db = [master_databasename…]
    #binlog_do_db = booking_engine #log only this db as a master, for slaves
    #replicate-do-db = yachting_charter #only replicate(sync) this db as a slave from master of this mysql server
  4. service mysql restart
  5. mysql -u root -p
  6. Learn MASTER_LOG_FILE, MASTER_LOG_POS from Master server phpmyadmin replication tab OR
    type on master: SHOW MASTER STATUS;
    then:
    CHANGE MASTER TO MASTER_HOST=’132.3.3.3‘, MASTER_USER=’slave_user‘, MASTER_PASSWORD=’password‘, MASTER_LOG_FILE=’mysql-bin.000001‘, MASTER_LOG_POS=  107;
  7. START SLAVE
  8. Check if it is working: SHOW SLAVE STATUS\G
  9. If there is error: SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
  10. quit;