| 版本 | ip | 主機名 | 身份 |
|---|---|---|---|
| centos8 | 192.168.136.239 | master01 | 主庫 |
| centos8 | 192.168.136.219 | master02 | 主庫 |
| centos8 | 192.168.136.230 | slave | 從庫 |
[root@master01 ~]# systemctl stop firewalld [root@master01 ~]# systemctl disable firewalld [root@master02 ~]# systemctl stop firewalld [root@master02 ~]# systemctl disable firewalld [root@slave ~]# systemctl stop firewalld [root@slave ~]# systemctl disable firewalld
mysql> grant replication slave on *.* to 'user'@'192.168.136.%' identified by 'user';
[root@slave ~]# mysql -uuser -p'user' -h192.168.136.239 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.33 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'app'@'192.168.136.%' identified by 'app'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
[root@slave ~]# mysql -uapp -papp -h192.168.136.219 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.33 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
#master01主機：
[root@master01 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
skip-grant-tables
log-bin = master_bin
server-id = 10
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
#master02主機
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
#replication config
log-bin = master_bin
server-id = 11
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
#slave主機
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /opt/data/mysql.pid
skip-name-resolve
log-bin = slave_bin
server-id = 13
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
[root@master01 ~]# systemctl restart mysqld [root@master02 ~]# systemctl restart mysqld [root@slave ~]# systemctl restart mysqld
channel(頻道)：每一個channel都是一個獨立的slave服務，都有一個IO_THREAD和SQL_THREAD，原理和普通復制一樣，只是需要在change master to語句后面使用FOR Channel來進行區分slave
在使用channel時需要將從庫的master-info-repository、relay-log-info-repository設置為table，否則會報錯。
將信息存儲庫設置為table格式
方式一（mysql內設置）:
set global master_info_repository='table';
set global relay_log_info_repository='table';
方式二（/etc/my.cnf內設置）:
3.在my.cnf中設置
master_info_repository = TABLE
relay_log_info_repository = TABLE
#檢查是否更改成功
mysql> show variables where variable_name in ('relay_log_info_repository','master_info_repository');
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
#slave從庫上配置倆個主庫GTID復制
mysql> change master to
-> master_host='192.168.136.219', #mysql02主庫ip
-> master_user='app', #mysql02主庫授權的普通用戶
-> master_password='app', #mysql02主庫授權的普通用戶密碼
-> master_port=3306, #主庫端口
-> master_auto_position=1 for channel 'master01'; #位置從1開始同步,并且第一個slave取名master01
mysql> change master to
-> master_host='192.168.136.239', #mysql01主庫ip
-> master_user='user',
-> master_password='user',
-> master_port=3306, #主庫端口
-> master_auto_position=1 for channel 'master02'; #位置從1開始同步,并且第一個slave取名master01
#查看倆個slave狀態
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.136.219
Master_User: app
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: slave02-relay-bin-master1.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No #都是關閉的
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_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:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
d68b404d-0a35-11ec-9df1-000c29581959:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State:
Master_Host: 192.168.136.239
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: slave02-relay-bin-master2.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 0
Relay_Log_Space: 154
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_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:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: b4326a77-0a31-11ec-a991-000c298d3571:1-2,
d68b404d-0a35-11ec-9df1-000c29581959:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master2
Master_TLS_Version:
2 rows in set (0.00 sec)
#開啟倆個slave
mysql> start slave;
#再次查看狀態
#master01主庫創建一個test數據庫 mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) #master02主庫上查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | #沒有內容 +--------------------+ 4 rows in set (0.00 sec) #slave從庫查看 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | #已經同步了test庫 +--------------------+ 5 rows in set (0.00 sec) #mysql02主庫創建一個RHCA數據庫 mysql> create database RHCA; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | RHCA | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) #slave從庫 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | RHCA | | mysql | | performance_schema | | sys | #有了mysql01主庫的test庫和mysql02的RHCA的庫 | test | +--------------------+ 6 rows in set (0.00 sec)
show slave status； //查看全部slave狀態
show slave status for channel ‘naem'; //查看單個slave狀態
reset slave; #重置全部slave
reset slave for channel ‘master1'; #重置單個slave
stop slave for channel ‘master1'; #暫停單個slave
start slave for channel ‘master1'; #開啟單個slave
雖然我在做的過程沒有遇到錯誤，但是下面這個是最最容易出現的錯誤
配置完開啟slave出現報錯
mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
由于mysql.slave_relay_log_info表中保留了以前的復制信息，導致新從庫啟動時無法找到對應文件，那么我們清理掉該表中的記錄即可
mysql> reset slave; Query OK, 0 rows affected (0.00 sec)
以上就是MySQL示例DTID主從原理解析的詳細內容，更多關于MySQL示例DTID主從原理的資料請關注腳本之家其它相關文章！
下一篇：MySQL 基礎常用命令總結