笔记：Mysql 主从搭建

主库

创建用户并授权

create user slave identified with mysql_native_password by '123456'
GRANT REPLICATION SLAVE ON *.* to 'slave'@'%'; 
FLUSH PRIVILEGES;

主库配置文件

/etc/my.cnf
#日志路径及文件名，目录要是mysql有权限写入
log-bin=/var/lib/mysql/binlog 
#master端的id号
server-id=1 
#指定同步的数据库，如果不写，默认是同步所有数据库
binlog-do-db = 数据库
#指定不同步的数据库　　
binlog-ignore-db=mysql
#binlog日志的保留时间
expire_logs_days=7
#指定binlog日志的格式
binlog_format=ROW 

主库日志及pos

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.004624 |      403 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库

从库配置文件

/etc/my.cnf

server-id=99
#日志路径及文件名，目录要是mysql有权限写入
log-bin=mysql-bin
#复制哪个库可以不用填写
replicate-do-db=webkit
replicate-do-db=dgp
replicate-do-db=dses_etldb
replicate-do-db=dses_metadb
replicate-do-db=dcf
replicate-do-db=etl
replicate-do-db=metadata
#不复制哪个库
replicate-ignore-db=mysql,information_schema,performance_schema
#这两个是启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止，保证数据写入的一致性
master_info_repository=table
relay_log_info_repository=table

配置主从

change master to master_host='10.68.15.37',
master_user='backup',
master_password='Fr@bjev.20320',
master_log_file='mysql-bin.000016',
master_log_pos=576474136; 

##MASTER_LOG_FILE和MASTER_LOG_POS的值为master节点中执行show master status查询到的信息

启动从库

start slave;

查看从库状态

show slave status\G

如果下面两项值为YES，则表示配置正确：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

备份及恢复

主库备份

mysqldump -uroot -p123456  --single-transaction --master-data=2 --all-databases > backup_all_databases.sql

从库恢复

删除数据库
drop database database_name;
恢复
mysql -uroot -p123456 < backup_all_databases.sql

binlog-format

binlog-format：指定二进制日志的格式，可以是STATEMENT、ROW或MIXED。

STATEMENT：MySQL 会将每个执行的 SQL 语句记录到二进制日志中，二进制日志只包含执行的 SQL 语句，而不会记录具体的行级变更，适用于大多数情况，但在某些情况下可能会导致主从同步的一致性问题
 ROW：MySQL 会将每个修改的行的变更信息记录到二进制日志中，二进制日志将包含实际的行级变更信息，而不仅仅是 SQL 语句，可以确保主从同步的精确一致性，但会产生更大的二进制日志文件
MIXED：MySQL 可以根据具体情况自动选择记录 SQL 语句或行级变更信息，对于大多数情况，MySQL 会使用 STATEMENT 格式记录 SQL 语句，但对于某些无法使用 SQL 语句准确重放的情况，MySQL 会使用 ROW 格式记录行级变更信息，所以这种格式结合了 STATEMENT 和 ROW 的优点，可以在不同情况下选择最合适的记录方式。

show master status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.004624 |      403 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

File：表示当前正在写入的二进制日志文件的名称。
Position：表示当前正在写入的二进制日志文件中的位置（字节偏移量）。
Binlog_Do_DB：表示为主服务器启用二进制日志记录的数据库列表，只记录指定的数据库操作。
Binlog_Ignore_DB：表示为主服务器启用二进制日志记录时要忽略的数据库列表，不记录指定的数据库操作。
Executed_Gtid_Set：表示已经在主服务器上执行的 GTID（Global Transaction Identifier）集合。GTID 是用于跟踪分布式事务的标识符。

参数说明

server-id：为每个MySQL服务器设置一个唯一的整数ID
relay-log：指定了从服务器（Slave）中用于存储中继日志的文件名前缀
relay-log-index：指定了从服务器（Slave）中用于存储中继日志索引的文件名
log-bin：启用二进制日志，并指定日志文件的名称。
binlog-format：指定了从服务器是否将复制事件写入其自己的二进制日志，当设置为 1 时，从服务器会将接收到的主服务器的二进制日志事件写入自己的二进制日志中，即在中继日志和二进制日志中都会记录复制事件，该选项通常在主从服务器之间使用基于链式复制的配置中，用于实现级联复制和多级复制
log_bin-index：二进制日志文件索引文件的名称