Expat-IT Tech Bits

Home

Contact

Links

Search this site:

Categories:

/ (287)
  Admin/ (122)
    Apache/ (10)
      HTTPS-SSL/ (4)
      PHP/ (3)
      performance/ (2)
    Cherokee/ (1)
    LAN/ (4)
    LVM/ (6)
    Monitoring/ (2)
      munin/ (2)
    SSH/ (6)
    SSL/ (1)
    Samba/ (1)
    VPN-options/ (6)
      OpenVPN/ (1)
      SSH-Proxy/ (3)
      Tinc/ (1)
      sshuttle/ (1)
    backups/ (17)
      SpiderOak/ (1)
      backuppc/ (5)
      dirvish/ (1)
      misc/ (6)
      rdiff-backup/ (1)
      rsync/ (1)
      unison/ (2)
    commandLine/ (24)
      files/ (8)
      misc/ (10)
      network/ (6)
    crontab/ (1)
    databases/ (15)
      MSSQL/ (2)
      MySQL/ (8)
      Oracle/ (3)
      PostgreSQL/ (1)
    dynamicDNS/ (2)
    email/ (11)
      Dovecot/ (1)
      deliverability/ (1)
      misc/ (1)
      postfix/ (7)
      puppet/ (1)
    iptables/ (3)
    tripwire/ (1)
    virtualization/ (9)
      VMware/ (1)
      virtualBox/ (8)
  Coding/ (14)
    bash/ (1)
    gdb/ (1)
    git/ (3)
    php/ (5)
    python/ (4)
      Django/ (2)
  Education/ (1)
  Hosting/ (27)
    Amazon/ (18)
      EBS/ (3)
      EC2/ (10)
      S3/ (1)
      commandline/ (4)
    Godaddy/ (2)
    NearlyFreeSpeech/ (3)
    Rackspace/ (1)
    vpslink/ (3)
  Linux/ (30)
    Android/ (1)
    Awesome/ (3)
    CPUfreq/ (1)
    China/ (2)
    Debian/ (8)
      APT/ (3)
      WPA/ (1)
    audio/ (1)
    encryption/ (3)
    fonts/ (1)
    misc/ (6)
    remoteDesktop/ (1)
    router-bridge/ (3)
  SW/ (45)
    Micro$soft/ (1)
    browser/ (2)
      Chrome/ (1)
      Firefox/ (1)
    business/ (28)
      Drupal/ (9)
      KnowledgeTree/ (6)
      Redmine/ (2)
      SugarCRM/ (7)
      WebERP/ (2)
      WordPress/ (1)
      eGroupware/ (1)
    chat/ (1)
    email/ (1)
    fileSharing/ (2)
      btsync/ (1)
      mldonkey/ (1)
    graphics/ (2)
    research/ (2)
    website/ (6)
      blog/ (6)
        blosxom/ (3)
        rss2email/ (1)
        webgen/ (1)
  Security/ (15)
    IMchat/ (2)
    circumvention/ (2)
    cryptoCurrency/ (1)
    e-mail/ (4)
    greatFirewall/ (1)
    hacking/ (1)
    password/ (1)
    privacy/ (2)
    skype/ (1)
  Services/ (1)
    fileSharing/ (1)
  TechWriting/ (1)
  xHW/ (14)
    Lenovo/ (1)
    Motorola_A1200/ (2)
    Thinkpad_600e/ (1)
    Thinkpad_a21m/ (3)
    Thinkpad_i1300/ (1)
    Thinkpad_x24/ (1)
    USB_audio/ (1)
    scanner/ (1)
    wirelessCards/ (2)
  xLife/ (17)
    China/ (9)
      Beijing/ (5)
        OpenSource/ (3)
    Expatriation/ (1)
    Vietnam/ (7)

Archives:

  • 2016/07
  • 2016/05
  • 2016/02
  • 2016/01
  • 2015/12
  • 2015/11
  • 2015/06
  • 2015/01
  • 2014/12
  • 2014/11
  • 2014/10
  • 2014/09
  • 2014/07
  • 2014/04
  • 2014/02
  • 2014/01
  • 2013/12
  • 2013/10
  • 2013/08
  • 2013/07
  • 2013/06
  • 2013/05
  • 2013/04
  • 2013/02
  • 2013/01
  • 2012/12
  • 2012/10
  • 2012/09
  • 2012/08
  • 2012/07
  • 2012/06
  • 2012/05
  • 2012/04
  • 2012/03
  • 2012/01
  • 2011/12
  • 2011/11
  • 2011/10
  • 2011/09
  • 2011/08
  • 2011/07
  • 2011/06
  • 2011/05
  • 2011/04
  • 2011/02
  • 2010/12
  • 2010/11
  • 2010/10
  • 2010/09
  • 2010/08
  • 2010/07
  • 2010/06
  • 2010/05
  • 2010/04
  • 2010/03
  • 2010/02
  • 2010/01
  • 2009/12
  • 2009/11
  • 2009/10
  • 2009/09
  • 2009/08
  • 2009/07
  • 2009/06
  • 2009/05
  • 2009/04
  • 2009/03
  • 2009/02
  • 2009/01
  • 2008/12
  • 2008/11
  • 2008/10
  • 2008/09
  • Subscribe XML RSS Feed

    Creative Commons License
    This work is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
    PyBlosxom

    This site has no ads. To help with hosting, crypto donations are accepted:
    Bitcoin: 1JErV8ga9UY7wE8Bbf1KYsA5bkdh8n1Bxc
    Zcash: zcLYqtXYFEWHFtEfM6wg5eCV8frxWtZYkT8WyxvevzNC6SBgmqPS3tkg6nBarmzRzWYAurgs4ThkpkD5QgiSwxqoB7xrCxs

    Thu, 04 Feb 2016


    /Admin/databases/MySQL: Master-Slave Replication: First Steps

    MASTER CONFIGURATION:

    There are a lot of search engine hits for this subject, I liked Rackspace's contribution[1] the best personally. I am going to improvise off of that document. And I am going to assume there is an existing MySQL server that will be the master that we will replicate. First of all, the new master will need a 'slave' user:

    grant replication slave on *.* TO slave_user@'ip-address' identified by 'password';
    flush privileges;

    And on a Debian / Ubuntu server, make these changes to /etc/mysql/my.cnf:

    # bind-address = 127.0.0.1
    server-id = 1
    log_bin = /var/log/mysql/mysql-bin.log
    expire_logs_days = 15
    max_binlog_size = 200M
    binlog_ignore_db = mysql

    Then restart MySQL. We comment out bind-address to permit non-localhost connections to the MySQL master from the slave. Both master and slave need a defined server-id, and they need to be different. log_bin is where the master records transactions that the slave will later pickup. The rest should be self-explanatory, except to say that in my setup binlog_ignore_db seems to be ignored. I wish it was not, but so far no major consequences.

    COPY DATABASES:

    Before replication can be started, the databases on both ends need to be exactly the same. On the master:

    FLUSH TABLES WITH READ LOCK;
    SHOW MASTER STATUS;

    The first line puts all master databases into read-only mode, and the second line will print out the file name and position (an integer) at which the binlog's record of writes to the database stopped. It is very important to record these two values, as they will be needed later on the slave. Now dump all the databases except (optionally) mysql and (not optionally) information_schema and performance_schema (the latter two are internal MySQL things that do not replicate). First get a list of all databases:

    mysql -uroot -p -e 'show databases' --skip-column-names | tr '\n' ' '

    Edit the above list to remove mysql, information_schema and performance_schema, and then dump all databases:

    mysqldump -uroot -p --databases list-of-databases | gzip > alldbs.sql.gz

    Don't forget to release the read lock on the master and resume normal operation!!!:

    UNLOCK TABLES;

    Copy alldbs.sql.gz to the slave server.

    SLAVE SETUP:

    Install mysql-server on the slave, and make these changes to /etc/mysql/my.cnf:

    # bind-address = 127.0.0.1
    tmpdir = /var/tmp
    server-id = 2

    and restart MySQL. Allowing non-localhost connections on the slave is actually not necessary for replication, but will be necessary later for the tools we will use for integrity checking and repairs. The tmpdir must be preserved through reboots, so we have moved it from /tmp to /var/tmp (and installed tmpreaper to keep it clean). Now import the dump of databases from the master:

    zcat alldbs.sql.gz | mysql -uroot -p

    And start replication:

    CHANGE MASTER TO MASTER_HOST='master-ip-address', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='filename', MASTER_LOG_POS=123456;

    start slave;

    where MASTER_LOG_FILE and MASTER_LOG_POS are the values that you recorded on the master when you locked the databases and issued a "SHOW MASTER STATUS" command, and slave_user is the user you created earlier on the master. Now check replication status:

    show slave status\G

    The field of particular interest is Seconds_Behind_Master. If things are working properly that integer should become smaller quite rapidly as the slave catches up with the master. Eventually that integer should get down very close to zero, if not zero. I am almost always seeing zero with my setup.

    Something you will want to verify after master and slave are synced is a slave reboot. You should find that after a reboot Seconds_Behind_Master quickly returns to zero and replication continues uninterrupted.

    [1] http://www.rackspace.com/knowledge_center/article/set-up-mysql-master-slave-replication

    posted at: 00:48 | path: /Admin/databases/MySQL | permanent link to this entry