Slave DB 복구 테스트를 진행하겠습니다.
이 부분은 구성하는데 필수 부분이 아니기 때문에 넘어가셔도 좋습니다~
▶ Test data insert (master서버)
## 넣으려는 sakila 샘플 데이터가 MyISAM 스토리지 엔진을 사용. 안에 내용을 innodb로 수정
[root@jh-my001 ~]# sed -i 's/MyISAM/InnoDB/g' sakila-mv-schema.sql
[root@jh-my001 ~]# mysql -uroot -p < sakila-mv-schema.sql
Enter password:
[root@jh-my001 ~]# sed -i 's/MyISAM/InnoDB/g' sakila-mv-data.sql
[root@jh-my001 ~]# mysql -uroot -p < sakila-mv-data.sql
Enter password:
mysql> show tables in sakila;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)
▶ 복구 test (Clone 이용)
3번 mysql kill
[root@jh-my003 ~]# ps -ef | grep mysql
root 3200 2543 0 08:39 pts/0 00:00:00 grep --color=auto mysql
mysql 26772 1 0 May09 ? 00:05:29 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/log/mysqld.err --pid-file=jh-my003.pid --socket=/tmp/mysql.sock --port=3306
[root@jh-my003 ~]# kill -9 26772
## cluster status
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().status();
{
"clusterName": "testcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.100.40:3306",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.",
"topology": {
"192.168.100.40:3306": {
"address": "192.168.100.40:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.29"
},
"192.168.100.41:3306": {
"address": "192.168.100.41:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.29"
},
"192.168.100.42:3306": {
"address": "192.168.100.42:3306",
"memberRole": "SECONDARY",
"mode": "n/a",
"readReplicas": {},
"role": "HA",
"shellConnectError": "MySQL Error 2003: Could not open connection to '192.168.100.42:3306': Can't connect to MySQL server on '192.168.100.42:3306' (111)",
"status": "(MISSING)"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.168.100.40:3306"
}
## 3번 DB 데이터파일 삭제 및 재생성
[root@jh-my003 data]# rm -rf ./*
[root@jh-my003 log]# mysqld --defaults-file=/etc/my.cnf --initialize
[root@jh-my003 data]# mysqld_safe --user=mysql &
## icadmin 유저는 그냥 sql문으로 생성
mysql> show grants for icadmin@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for icadmin@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT CLONE_ADMIN,CONNECTION_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `icadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `icadmin`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
## seed 인스턴스에서 3번 DB rejoin
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().rescan()
Rescanning the cluster...
Result of the rescanning operation for the 'testcluster' cluster:
{
"name": "testcluster",
"newTopologyMode": null,
"newlyDiscoveredInstances": [],
"unavailableInstances": [
{
"host": "192.168.100.42:3306",
"label": "192.168.100.42:3306",
"member_id": "1fe1bcfd-cf6a-11ec-b5ad-f220cd849d34"
}
],
"updatedInstances": []
}
The instance '192.168.100.42:3306' is no longer part of the cluster.
The instance is either offline or left the HA group. You can try to add it to the cluster again with the cluster.rejoinInstance('192.168.100.42:3306') command or you can remove it from the cluster configuration.
Would you like to remove it from the cluster metadata? [Y/n]: Y
Removing instance from the cluster metadata...
The instance '192.168.100.42:3306' was successfully removed from the cluster metadata.
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().addInstance('icadmin@192.168.100.42:3306', {ipWhitelist:'192.168.100.0/24'});
WARNING: The ipWhitelist option is deprecated in favor of ipAllowlist. ipAllowlist will be set instead.
NOTE: The target instance '192.168.100.42:3306' has not been pre-provisioned (GTID set is empty). The Shell is unable to decide whether incremental state recovery can correctly provision it.
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '192.168.100.42:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone):
Validating instance configuration at 192.168.100.42:3306...
This instance reports its own address as 192.168.100.42:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using '192.168.100.42:33061'. Use the localAddress option to override.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.
Adding instance to the cluster...
Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Clone based state recovery is now in progress.
NOTE: A server restart is expected to happen as part of the clone process. If the
server does not support the RESTART command or does not come back after a
while, you may need to manually start it back.
* Waiting for clone to finish...
NOTE: 192.168.100.42:3306 is being cloned from 192.168.100.40:3306
** Stage DROP DATA: Completed
** Clone Transfer
FILE COPY ############################################################ 100% Completed
PAGE COPY ############################################################ 100% Completed
REDO COPY ############################################################ 100% Completed
NOTE: 192.168.100.42:3306 is shutting down...
* Waiting for server restart... ready
* 192.168.100.42:3306 has restarted, waiting for clone to finish...
** Stage RESTART: Completed
* Clone process has finished: 72.61 MB transferred in about 1 second (~72.61 MB/s)
State recovery already finished for '192.168.100.42:3306'
The instance '192.168.100.42:3306' was successfully added to the cluster.
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().status();
{
"clusterName": "testcluster",
"defaultReplicaSet": {
"name": "default",
"primary": "192.168.100.40:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"192.168.100.40:3306": {
"address": "192.168.100.40:3306",
"memberRole": "PRIMARY",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.29"
},
"192.168.100.41:3306": {
"address": "192.168.100.41:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.29"
},
"192.168.100.42:3306": {
"address": "192.168.100.42:3306",
"memberRole": "SECONDARY",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.29"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "192.168.100.40:3306"
}
## 3번 DB 데이터 확인
mysql> show tables in sakila;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.01 sec)
▶ 복구 테스트2 (MEB이용)
mysql> create user kim@'%' identified by 'kim';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to kim@'%';
Query OK, 0 rows affected (0.01 sec)
[root@jh-my001 backup]# mysqlbackup --user=kim --password=kim --host=localhost --port=3306 --backup-dir=/backup/full backup
[root@jh-my001 backup]# cd /backup/full/
[root@jh-my001 full]# ll
total 36
-rw-r--r-- 1 root root 313 May 10 09:30 backup-my.cnf
drwxr-x--- 8 root root 4096 May 10 09:30 datadir
drwxr-x--- 2 root root 154 May 10 09:30 meta
-rw-r----- 1 root root 22841 May 10 09:30 server-all.cnf
-rw-r----- 1 root root 1903 May 10 09:30 server-my.cnf
## Backup후 Master에 추가 Data Insert --> 3번에 Restore하고 나서 cluster join시 이 Data까지 따라오나 Test
mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table cluster_test(c1 int primary key); --> Primary key를 안해주면 Insert 할때 Error가 난다.
mysql> insert into cluster_test values(1);
mysql> insert into cluster_test values(2);
mysql> insert into cluster_test values(3);
## DB 3번 kill & datafile 삭제
[root@jh-my003 data]# ps -ef | grep mysql
mysql 6098 1 11 09:34 pts/0 00:00:01 /data/mysql/bin/mysqld --basedir=/data/mysql --datadir=/data/data --plugin-dir=/data/mysql/lib/plugin --user=mysql --log-error=/data/log/mysqld.err --pid-file=jh-my003.pid --socket=/tmp/mysql.sock --port=3306
root 6166 2543 0 09:34 pts/0 00:00:00 grep --color=auto mysql
[1]+ Killed mysqld_safe --user=mysql
[root@jh-my003 data]# kill -9 6098
[root@jh-my003 data]# rm -rf ./*
## Master DB에서 backup file 전송
[root@jh-my001 backup]# scp -rp full/ root@192.168.100.42:/backup
## 3번에서 restore 및 재기동
[root@jh-my003 backup]# cd /backup/
[root@jh-my003 backup]# mysqlbackup --backup-dir=/backup --datadir=/data/data --log-bin=/data/log/mysql-bin --relay-log=/data/relay-log/relay-bin copy-back-and-apply-log
...생략
220510 09:41:34 PCR1 INFO: Setting 'ib_logfile1' file size to 50331648
220510 09:41:35 PCR1 INFO: Log file header:
format = 5
pad1 = 0
start lsn = 31364608
checkpoint lsn = 31364722
checksum = 3548644360
creator = MEB 8.0.29
220510 09:41:35 PCR1 INFO: We were able to parse ibbackup_logfile up to lsn 31444209.
220510 09:41:35 PCR1 INFO: Last MySQL binlog file position 0 6475, file name mysql-bin.000006
220510 09:41:35 PCR1 INFO: The first data file is '/data/data/ibdata1'
and the new created log files are at '/data/data'
220510 09:41:35 MAIN INFO: Apply-log operation completed successfully.
220510 09:41:35 MAIN INFO: Full Backup has been restored successfully.
mysqlbackup completed OK! with 4 warnings
[root@jh-my003 ~]# chown -R mysql.mysql /data/data/
[root@jh-my003 ~]# mysqld_safe &
## Seed에서 다시 addinstance
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().rescan()
MySQL 192.168.100.40:3306 ssl JS > dba.getCluster().addInstance('icadmin@192.168.100.42:3306', {ipWhitelist:'192.168.100.0/24'});
## DB 3번에서 Data확인
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| cluster_test | ---> Backup후 생성했던 Table이 보임
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
24 rows in set (0.01 sec)'DataBase > MySQL & MariaDB' 카테고리의 다른 글
| [MariaDB to MySQL 데이터 이관] use. mysqldump (0) | 2022.07.15 |
|---|---|
| [MySQL - InnoDB cluster] part 4 Router설치 (0) | 2022.05.14 |
| [MySQL - InnoDB cluster] part 2 cluster생성/인스턴스 추가 (0) | 2022.05.14 |
| [MySQL - InnoDB cluster] part 1 Mysql 설치 & shell설치 (0) | 2022.05.14 |
| [Mysql - Performance parameter] (0) | 2022.03.24 |