Kategori arşivi: DB

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;

install mysql

mysql 5.7

sudo apt-get install software-properties-common
sudo add-apt-repository -y ppa:ondrej/mysql-5.7
sudo apt-get update
sudo apt-get install mysql-server

mysql 5.6
sudo apt-get install software-properties-common
sudo add-apt-repository -y ppa:ondrej/mysql-5.6
sudo apt-get update
sudo apt-get install mysql-server

mysql completely remove

sudo apt-get remove --purge mysql* 
sudo apt-get autoremove 
sudo apt-get autoclean


service mysql stop
killall -KILL mysql mysqld_safe mysqld
apt-get --yes purge mysql-server mysql-client
apt-get --yes autoremove --purge
apt-get autoclean
deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean

after this, if you are having issues with re installing, Try to remove Mysql files in :

sudo rm -rf /var/lib/mysql


sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql