过滤复制

1.先配置多实例

# 创建多实例目录
mkdir -p /data/330{7,8,9}/data

# 初始化多实例数据
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3308/data
mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3309/data

## 初始化带密码的是
mysqld --initialize --user=mysql --basedir=/app/mysql --datadir=/data/3307/data

# 配置文件
cat >/data/3307/my.cnf<< EOF
[mysqld]
port=3307
user=mysql
basedir=/app/mysql          #工作目录
datadir=/data/3307/data    #存放数据目录
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
EOF

cat >/data/3308/my.cnf<< EOF
[mysqld]
port=3308
user=mysql
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
EOF

cat >/data/3309/my.cnf<< EOF
[mysqld]
port=3309
user=mysql
basedir=/app/mysql         #工作目录
datadir=/data/3309/data    #存放数据目录
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
EOF

# 授权
[root@db02 scripts]# chown -R mysql.mysql /data

# 配置文件启动指定启动
mysqld --defaults-file=/data/3308/my.cnf

# 配置启动文件
----------------1.
cat >/usr/lib/systemd/system/mysql3307.service<< EOF
[Unit]
Description=mysqld

[Service]
#Type=notify
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --user=mysql
KillMode=process
Restart=on-failure
RestartSec=42s

[Install]
WantedBy=multi-user.target
EOF
----------------------2.
cat >/usr/lib/systemd/system/mysql3307.service<< EOF
[Unit]
Description=mysqld

[Service]
#Type=notify
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf --user=mysql
KillMode=process
Restart=on-failure
RestartSec=42s

[Install]
WantedBy=multi-user.target
EOF
--------------------------3.
cat >/usr/lib/systemd/system/mysql3307.service<< EOF
[Unit]
Description=mysqld

[Service]
#Type=notify
ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf --user=mysql
KillMode=process
Restart=on-failure
RestartSec=42s

[Install]
WantedBy=multi-user.target
EOF

# 重载配置文件
[root@db02 system]# systemctl daemon-reload

# 启动3307端口的mysql
[root@db02 system]# systemctl start mysql3307

# 多实例创建密码
mysqladmin password 123 -S /data/3307/mysql.sock
mysqladmin password 123 -S /data/3308/mysql.sock
mysqladmin password 123 -S /data/3309/mysql.sock

# 指定登录(S大写)
mysql -uroot -p123 -S /data/3307/mysql.sock

##登录方法2
[root@db02 system]# vim /usr/local/bin/mysql3307
mysql -uroot -p123 -S /data/3307/mysql.sock
###赋予执行权限
[root@db02 system]# chmod +x /usr/local/bin/mysql3307

###连接mysql
mysql3307

2.配置GTID

## 主库
# 创建主从用户
grant replication slave on *.* to rep@'%' identified by '123'

# 配置文件
/etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
gtid_mode=on
enforce_gtid_consistency

## 从库
# 配置文件
cat >/data/3307/my.cnf<< EOF
[mysqld]
port=3307
user=mysql
basedir=/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
server_id=2
gtid_mode=on
enforce_gtid_consistency
EOF

cat >/data/3308/my.cnf<< EOF
[mysqld]
port=3307
user=mysql
basedir=/app/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
server_id=2
gtid_mode=on
enforce_gtid_consistency
EOF

cat >/data/3309/my.cnf<< EOF
[mysqld]
port=3307
user=mysql
basedir=/app/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
server_id=2
gtid_mode=on
enforce_gtid_consistency
EOF

# 基于GTID主从复制
root@localhost [(none)] >change master to
master_host='172.16.1.54',
master_user='rep',
master_password='123',
master_port=3306,
master_auto_position=1;

# 启动从库
start slave;

配置过滤复制

## 添加位置在[mysqld]
cat >>/data/3307/my.cnf<< EOF
replicate-do-db=wz
EOF

cat >>/data/3308/my.cnf<< EOF
replicate-do-db=lol
EOF

cat >>/data/3309/my.cnf<< EOF
replicate-do-db=cf
EOF

# 从启数据库
systemctl restart mysql3307
systemctl restart mysql3308
systemctl restart mysql3309

获得结果

## 主库上创建库，从库上只复制自己白名单上面的库。
mysql> create database wz;
Query OK, 1 row affected (0.00 sec)

mysql> create database lol;
Query OK, 1 row affected (0.01 sec)

mysql> create database cf;
Query OK, 1 row affected (0.00 sec)

1.🐂----------------------
## 主库不进入wz库下创建表创建表
mysql> create table wz.t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables from wz;
+--------------+
| Tables_in_wz |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)
## wz从库的wz库下没有这个表
mysql> show tables from wz;
Empty set (0.00 sec)
1.🐎------------------------------

2.🐂-------------------------
## 出库进入wz库下创建
mysql> use wz;
mysql> create table t2(id int);
mysql> show tables;
+--------------+
| Tables_in_wz |
+--------------+
| t1           |
| t2           |
+--------------+
## wz从库复制了这个SQL语句
mysql> show tables from wz;
+--------------+
| Tables_in_wz |
+--------------+
| t2           |
+--------------+
2.🐎----------------------------

3.🐂----------------------
## 主库不在库下写入
mysql> insert into wz.t2 values(1);
Query OK, 1 row affected (0.01 sec)
## 从库也可以收到sql语句并执行
mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+

## 主库在t1表下写入内容，从库也没有

## 主库在库下写入也有
3.🐎------------------------------

## 当主库在wz.t1内插入内容，从库没有t1,会报错卡住
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.54
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2506
               Relay_Log_File: db04-relay-bin.000005
                Relay_Log_Pos: 1729
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: wz
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error executing row event: 'Table 'wz.t1' doesn't exist'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2010
              Relay_Log_Space: 2731
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error executing row event: 'Table 'wz.t1' doesn't exist'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: afea5dcf-3515-11ee-b219-000c29e3dd62
             Master_Info_File: /data/3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 230807 21:31:30
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: afea5dcf-3515-11ee-b219-000c29e3dd62:1-12
            Executed_Gtid_Set: afea5dcf-3515-11ee-b219-000c29e3dd62:1-10
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
## 解决办法
# 解决方案一：
#临时停止同步
mysql> stop slave;
#将同步指针向下移动一个（可重复操作）
mysql> set global sql_slave_skip_counter=1;
#开启同步
mysql> start slave;

## 解决方案二：
#编辑配置文件
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007,1146

## 解决方法三：
重新配置主从
posted @   普里莫  阅读(8)  评论(0)  编辑  收藏  举报
相关博文：
· Mysql主从
· 延时从库企业案例
· Mysql-主从复制
· MySQL主从- slave跳过错误
· mysql主从 一台服务器多实例mysql做主从复制
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！
点击右上角即可分享
微信分享提示