MySQL replication

mysql_repl.md

Create users for replication

On master (source):

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

Obtain dump on source

On master (source) for one database db:

mysqldump --databases db --master-data --single-transaction --quick  | gzip --best > dump.gz

--master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

If you do not use --master-data, then it is necessary to lock all tables in a separate session manually. It is also possible to set up a replica by dumping an existing replica of the source, using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

--single-transaction option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail. To dump large tables, combine the --single-transaction option with the --quick option.

--quick option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-howto-mysqldump.html https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data

Dump from source (master)

mysqldump --default-character-set=utf8mb4 --databases db --master-data --single-transaction --quick > master.dump

--default-character-set=utf8mb4 for old MySQL 5.7 with legacy utf8 server coding.

Slave

mysqld.conf:

read-only=1

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only

Check replica status

MySQL 5.x

On slave:

SHOW SLAVE STATUS\G

On master:

SHOW PROCESSLIST \G;

SHOW SLAVE HOSTS;

https://dev.mysql.com/doc/mysql-replication-excerpt/5.6/en/replication-administration-status.html

MySQL 8.x

On slave:

SHOW REPLICA STATUS\G

On master:

SHOW PROCESSLIST \G;

SHOW REPLICAS;

https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-administration-status.html