Database replication without locking tables

Question

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

Answer 1

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