主节点：
在my.cnf配置文件中增加配置如下：
[mysqld]
#主从复制配置 主实例
log-bin=mysql-bin
server-id=1
innodb-file-per-table=ON
skip_name_resolve=ON
设置完毕后重启mysql
systemctl restart mysqld
进入mysql 查看二进制日志是否开启成功
show global variables like '%log%';
查看主节点二进制日志列表
show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 155 | No |
+------------------+-----------+-----------+
查看主节点的server id
show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | utf8mb4 |
| collation_server | utf8mb4_0900_ai_ci |
| innodb_dedicated_server | OFF |
| innodb_ft_server_stopword_table | |
| server_id | 1 |
| server_id_bits | 32 |
| server_uuid | 1633904d-6597-11ea-895a-00163e084251 |
+---------------------------------+--------------------------------------+
创建用于数据同步的账户
CREATE USER 'repl'@'192.168.102.11' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.102.11';
FLUSH PRIVILEGES;
查看master状态
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
- 如果数据库有数据持续写入的话，主库上锁表，然后备份数据库
flush table with read lock;
锁表后查看主库状态，需要记录在案，待会复制主库就是从这个位置开始的。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从数据库配置
- 首先要设置server-id并关闭binlog功能
show variables like '%log_bin%';
log_bin 如果显示为ON,标识binlog为开启状态，需要关闭
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
关闭方法为在my.cnf的[mysqld]下添加skip-log-bin
8.0以下版本注释掉配置即可
#log-bin=mysql-bin
#binlog_format=mixed
#server-id = 1
#expire_logs_days = 10
由于我装的是最新版本，关闭方法不一样
[mysqld]
skip-log-bin #8.0版本关闭binlog
修改配置后重启mysql，再次查询log_bin状态则为关闭状态
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.02 sec)
然后增加从库的配置，注意从库的server-id不能和主库一样，我的主库设置为1，从库就设置2：
[mysqld]
#主从复制设置 本机为副本实例
server-id=2
relay-log=relay-log
relay-log-index=relay-log.index
innodb_file_per_table=ON
skip_name_resolve=ON
编辑保存，重启数据库
从库配置复制参数如下（这个操作实际上就是把用户、密码以及主库的其他信息写入到从库的master.info文件去）：
CHANGE MASTER TO
MASTER_HOST='47.99.87.3',
MASTER_PORT=3306,
MASTER_USER='luminqiang_back',
MASTER_PASSWORD='Jay@:19951027123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=155;
- 从库上开启同步开关
start slave;
我这里出现了如下异常：
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
去docker查看日志，会发现原因是找不到对应的中继日志，导致启动slave报错，由于mysql.slave_relay_log_info表中保留了以前的复制信息，导致新从库启动时无法找到对应文件，需要清理掉该表中的记录
清理命令，执行后mysql会进行如下操作
reset slave
1、删除slave_master_info ，slave_relay_log_info两个表中数据
2、删除所有relay log文件，并重新创建新的relay log文件；
3、不会改变gtid_executed 或者 gtid_purged的值
然后重新启动
start slave;
出现如下错误需要先停止slave
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
测试：
- 在从节点查找二进制日志信息，并查看mydb数据库是否复制成功
SHOW SLAVE STATUS\G;
我发现我的同步不成功，看到有一条错误信息如下：
error connecting to master 'luminqiang_back@47.99.87.3:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
就是主库下用来同步的账号的验证方式问题了，主库查询下用户的验证方式：
mysql> select user,host,plugin from user;
+------------------+---------------+-----------------------+
| user | host | plugin |
+------------------+---------------+-----------------------+
| lumq | % | mysql_native_password |
| luminqiang_back | 47.*.48.* | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| root | localhost | mysql_native_password |
+------------------+---------------+-----------------------+
我用的是这个账号 luminqiang_back，验证方式为caching_sha2_password，需要改成mysql_native_password，修改命令如下：
ALTER USER luminqiang_back@47.*.48.* IDENTIFIED WITH mysql_native_password BY '111111';
同步指定数据库可以在主库配置增加如下配置，然后重启mysql
binlog-do-db=ZhiFouMovie
建议最好是指定数据库同步，那些mysql自带的系统库是不需要同步的
这时在主库增加一条记录，去从库查看就是成功的了
slave命令相关：
start slave;
stop slave;
reset slave;
总结：参考了很多博客，有些写的很不靠谱，还是需要自己多实践，多记录
- https://blog.csdn.net/daicooper/article/details/79905660