过滤复制

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.

标签：##,--,过滤,Master,复制,mysql,data,wz	

From： https://www.cnblogs.com/xiutai/p/17750624.html