MySQL Master && Slave

1 Binlog & 位点

1.1Master my.cnf配置

log-bin="binlog_mymaster"
server-id=1

# 若是 innodb, 且有用 transaction 的話, 需再加入下面兩行

> # If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.

innodb_flush_log_at_trx_commit=1

sync_binlog=1

配置文件更改后，MySQL需要restart

mysql restart

1.2 Master授权

创建一个用于同步的专用账号

CREATE USER 'repl'@'192.168.180.61' IDENTIFIED BY 'root';

grant replication slave on *.* to 'repl'@'%' IDENTIFIED BY 'root'；
FLUSH PRIVILEGES;

查询授权结果

SELECT  host,user,repl_slave_priv,repl_client_priv from mysql.user ;

1.3 Master查询STATUS

当前bin log的file，从这个文件的Position开始同步。

SHOW MASTER STATUS; 

1.4 导出前，锁定数据库

把Master库导出前，先锁定，让数据库处于只读状态，以确保导出的数据是最新的。

FLUSH TABLES WITH READ LOCK; 

1.5 导出Master初始化数据

$ mysqldump -u root -p DB > dbdump.sql
$ mysqldump --all-databases --lock-all-tables >dbdump.sql
$ mysqldump --all-databases --master-data >dbdump.sql # --master-data: 會自動將CHANGE MASTER 的語法帶在裡面

mysqldump -uroot -p master_test > master_test.sql

1.6 解锁

UNLOCK TABLES; # dump 完資料後, 進去 mysql 解除唯讀

1.7 Slave导入初始数据

source D:/DB/test/master_test.sql;

1.8 Slave 停止

stop slave;

1.8 Slave 设置

设置前，需要停止Slave

CHANGE MASTER TO 
master_host='192.168.180.60',
master_user='repl',
master_port=3306,
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=7835341;

1.9 Slave 启动slave

mysql>start slave;

1.10 Slave 查询slave信息

show slave STATUS

Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 说明启动成功。

1.11 是否要跳过错误，继续执行

mysql>stop slave;
mysql>set global sql_slave_skip_counter=1;

1.12 MTS for Multi-threaded Slaves

Slave 从数据库的 my.cnf 文件中配置。

# slave
 slave-parallel-type=LOGICAL_CLOCK
 slave-parallel-workers=8        #一般建议设置4-8，太多的线程会增加线程之间的同步开销
 master_info_repository=TABLE
 relay_log_info_repository=TABLE
 relay_log_recovery=ON

1.13 查询relay log

 show variables like '%relay%';

max_relay_log_size
标记 relay log 允许的最大值，如果该值为 0，则默认值为 max_binlog_size(1G)；如果不为 0，则max_relay_log_size 则为最大的 relay_log 文件大小。
relay_log_purge
是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_recovery
当 slave 从库宕机后，假如 relay log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay log，并且重新从 master 上获取日志，这样就保证了 relay log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时，可在 slave 从库上开启该功能，建议开启。
relay_log_space_limit
防止中继日志写满磁盘，这里设置中继日志最大限额。但此设置存在主库崩溃，从库中继日志不全的情况，不到万不得已，不推荐使用。
sync_relay_log
这个参数和 Binlog 中的 sync_binlog作用相同。当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 Binlog 日志都要写入系统缓冲区，然后刷入 relay log 中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。
当设置为 0 时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是 0，可动态修改，建议采用默认值。
sync_relay_log_info
当设置为 1 时，slave 的 I/O 线程每次接收到 master 发送过来的 Binlog 日志都要写入系统缓冲区，然后刷入 relay-log.info 里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量 I/O。当设置为 0 时，并不是马上就刷入 relay-log.info 里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘 I/O 操作。这个值默认是0，可动态修改，建议采用默认值。

1.14 重置slave

RESET SLAVE

①清除slave 复制时的master binlog的位置；
②清空master info, relay log info；
③删除所有的relay log文件，并创建一个新的relay log文件；
④重置复制延迟(CHANGE MASTER TO 的 MASTER_DELAY参数指定的)为0。
RESET SLAVE 不会改变gtid_executed or gtid_purged
RESET SLAVE 不会改变复制连接使用的参数，例如master host, master port, master user, or master password
如果要重置这些连接参数，需要使用命令，使用 CHANGE MASTER TO 重新指定复制连接参数。

RESET SLAVE ALL

2 Binlog & GTID