3

I've been asked to setup replication between master and slave database. The aim is not to lock any tables while dumping database.

I've never setup replication but I have found this guide:

https://plusbryan.com/mysql-replication-without-downtime He is using these settings.

On master:

server-id=1
binlog-format   = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1

On slave:

server-id               = 101
binlog-format       = mixed
log_bin                 = mysql-bin
relay-log               = mysql-relay-bin
log-slave-updates = 1
read-only               = 1

Could you please guys advise me if what following settings mean and what impact they will have on whole settings (database is around 35GB)

One more thing. This replication needs to be 'row based' so instead mixed I shall use row option in master settings

innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-slave-updates = 1
read-only               = 1

In don't want to suffer any performance issue or high I/O because of constant disk writes. As I mentioned I have no experience on settings this up so I'm kindly asking you for help. Many Thanks

5
  • I'm surprised that that link did not pipe do/from gzip. Instead he is tripling the number of disk hits.
    – Rick James
    Feb 21, 2017 at 23:23
  • Note that the link works properly with InnoDB, but not MyISAM.
    – Rick James
    Feb 21, 2017 at 23:24
  • How can i check what is setup for database InnoDB or MyISAM? And could you say something more about your previous comment
    – LukeJ
    Feb 21, 2017 at 23:27
  • This is scary. If you know nothing about MySQL, it is premature to ask you to do something as potentially complex/dangerous as setting up replication. SHOW TABLE STATUS or SHOW CREATE TABLE.
    – Rick James
    Feb 21, 2017 at 23:31
  • I've checked and We have MyISAM tables no InnoDB.
    – LukeJ
    Feb 22, 2017 at 11:46

1 Answer 1

2

this is the meaning of the parameters you pointed:

  • server-id: its mean the server has unique ID in-order to communicate with other servers, the ID range should be 1-2^32, and if not specified its mean the server ID will be 0 (stand alone) and will not communicate with other servers.
  • binlog-format: its a binary log format, it has three values, STATEMENT causes logging to be statement based, ROW causes logging to be row based, and MIXED causes logging to use mixed format.
  • log-bin: this will enable binary logging (https://dev.mysql.com/doc/refman/5.7/en/replication-options.html)
  • datadir: where you will store your data.
  • innodb_flush_log_at_trx_commit: Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash. (Is it safe to use innodb_flush_log_at_trx_commit = 2)
  • sync_binlog: this parameter is use to sync binlog from one server to another.
  • relay-log: The relay log, like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.(https://dev.mysql.com/doc/refman/5.7/en/slave-logs-relaylog.html)
  • log-slave-updates: this option needs to be enabled in order for the auto-positioning mechanism to be active.
  • read-only: this option will make slave is used for read-only no update (other than the ones come from master server) is allowed, to make sure there will be no conflict

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.