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
## 解决方法三：
重新配置主从
