mysql学习笔记三之主从复制及遇到的报错总结

mysql学习笔记一之数据库安装.
mysql学习笔记二之阿里云Mysql8.0平滑升级.

环境：采用一主一从的形式
主数据库：阿里云服务器8.0.21版本
从服务器：docker中安装的mysql8.0.25

1、主库配置：

# 打开数据库my.cnf
vi /etc/my.cnf

# 增加配置
log-bin=mysql-bin
server-id=1
# 只备份ticket一个数据库
binlog-do-db=ticket

2、添加用于同步的数据库账号

# 进入mysql -uroot -p 进入mysql交互界面后
use mysql;
create user 'pipi'@'%' identified by 'mysql';
grant replication slave on *.*  to 'pipi'@'%';
flush privileges;

3、查看主库的相关配置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      449 | ticket       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 这里的File和Position后续要用到

4、从库配置：

# 我本人用的是docker里面的mysql
apple@appledeMBP-2 ~ % docker exec -it Dmysql bash
# 安装vim，默认没有vim
apt-get install vim
# 打开mysql配置文件
cd /etc/mysql
vim my.cnf

# 增加
server-id=2

5、从库创建账号

# 同样得进入mysql
use mysql;
create user 'pipi'@'%' identified by 'mysql';
grant replication slave on *.*  to 'pipi'@'%';
flush privileges;

6、在从库中配置主从同步， 注意master_log_file和master_log_pos使用主库通过show master status查到的内容

# 同样得进入mysql
change master to master_host='8.129.90.10',
master_port=3306,
master_user='pipi',
master_password='mysql',
master_log_file='mysql-bin.000003',
master_log_pos=449;
start slave;

7、执行show slave status\G查看主从同步情况，Slave_IO_Running: Yes和Slave_SQL_Running: Yes说明成功了

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 8.129.90.10
                  Master_User: pipi
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 449
               Relay_Log_File: 79465241113d-relay-bin.000003
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

8、建表，插入数据查看从库是否与主库数据一致。

执行show slave status\G报错

# 从库报错一
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or REPLICATION_SLAVE_ADMIN privilege(s) for this operation
# 解决方法
grant replication slave on *.* to 'pipi'@'mysql' identified by 'root';
flush privileges;


# 从库报错二
error connecting to master 'pipi@8.129.90.10:3306' - retry-time: 60 retries: 6 message: Access denied for user 'rep1'@'218.17.86.227' (using password: YES)
# 解决方法
使用复制账号在从库登录一次

# 从库报错三
error connecting to master 'pipi@8.129.90.10:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
#错误原因
密码加密方式不支持，
# 解决方法
在主服务器执行以下命令：
ALTER USER 'pipi'@'%' IDENTIFIED WITH mysql_native_password BY 'mysql';
FLUSH PRIVILEGES;
在从库中执行STOP slave；
然后执行start slave;

# 从库报错四
报错:
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.;
#原因
从库修改配置主从同步
change master to
master_host='8.129.90.10',
master_port=3306,
master_user='pipi',
master_password='mysql',
master_log_file='mysql-bin.000003',
master_log_pos=449;
start slave;
# 解决方法
进入slave服务器mysql执行命令：STOP slave;
然后执行start slave;

# 从库报错五
Error 'Table 'test' already exists' on query. Default database: 'ticket'. Query: 'CREATE TABLE `ticket`.`test`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  PRIMARY KEY (`id`)
)'
# 原因
在主从配置完成以后，我才在主服务器新建了一张test表，然后在从服务器新建和主库一样的数据库，执行数据同步后把主库的test表也同步到从库，从而导致这个问题发生。
# 解决方法
只需要把从库这张表删除即可