centos 8 - galera + mariadb 마이그레이션

 

* 노후 장비 제거하고 새 장비에 DB를 운영할때(down time은 가져야 함)

* server3의 DB를 백업 후 server 1에 migration 하고 server1과 server2를 galera로 동기화 한다.

* 동기화 DB는 test_db, table은 test_table, 데이터는 50002개

 

 

####################

##### SERVER 3 ######

###################

[root@server3 ~]# mkdir /backup

[root@server3 ~]# mysqldump -u root -p --routines --single-transaction --all-databases | gzip -c > /backup/all_db_20210108_1.sql.gz

 

[root@server3 ~]# ls -als /backup

total 248

  0 drwxr-xr-x.  2 root root     68 Jan  8 10:23 .

  0 dr-xr-xr-x. 18 root root    238 Jan  8 09:56 ..

244 -rw-r--r--.  1 root root 248350 Jan  8 10:23 all_db_20210108_1.sql.gz

 

[root@server3 ~]# scp /backup/all_db_20210108_1.sql.gz  root@server1.example.com:/backup

 

[root@server3 ~]# shutdown -h 0

####################

##### SERVER 4 ######

###################

[root@server4 ~]# shutdown -h 0 

 

####################

##### SERVER 2 ######

###################

[root@server2 ~]# dnf -y install mariadb-server-galera

[root@server2 ~]# firewall-cmd --permanent --zone=public --add-service=mysql
[root@server2 ~]#  firewall-cmd --permanent --zone=public --add-port={4567,4568,4444}/tcp
[root@server2 ~]# firewall-cmd --reload

 

[root@server2 ~]# cd /etc/my.cnf.d/

[root@server2 my.cnf.d]# vi mariadb-server.cnf

[galera]

# Mandatory settings

wsrep_on=ON

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.10.200,192.168.10.210

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

 

[root@server2 my.cnf.d]# setenforce 0

[root@server2 my.cnf.d]# systemctl start mariadb

[root@server2 my.cnf.d]# systemctl enable mariadb

 

####################

##### SERVER 1 ######

###################

 

[root@server1 ~]# mkdir /backup

[root@server1 backup]# ls -als

total 244

  0 drwxr-xr-x.  2 root root     38 Jan  8 10:25 .

  0 dr-xr-xr-x. 18 root root    238 Jan  8 10:02 ..

244 -rw-r--r--.  1 root root 248350 Jan  8 10:24 all_db_20210108_1.sql.gz

 

[root@server1 ~]# dnf -y install mariadb-server-galera

[root@server1 ~]# firewall-cmd --permanent --add-service=mysql
[root@server1 ~]#  firewall-cmd --permanent --add-port={4567,4568,4444}/tcp
[root@server1 ~]# firewall-cmd --reload

[root@server1 ~]# setenforce 0

[root@server1 ~]# cd /etc/my.cnf.d/

[root@server1 my.cnf.d]# vi mariadb-server.cnf

[galera]

# Mandatory settings

wsrep_on=ON

wsrep_provider=/usr/lib64/galera/libgalera_smm.so

wsrep_cluster_address=gcomm://192.168.10.200,192.168.10.210

binlog_format=row

default_storage_engine=InnoDB

innodb_autoinc_lock_mode=2

 

[root@server1 my.cnf.d]# systemctl start mariadb

[root@server1 my.cnf.d]# systemctl enable mariadb

 

[root@server1 backup]# mysqladmin -u root password
New password:
Confirm new password:

 

[root@server1 backup]# gunzip  all_db_20210108_1.sql.gz

[root@server1 backup]# mysql -u root -p < /backup/all_db_20210108_1.sql

Enter password:

 

[root@server1 backup]# mysql -u root -p
Enter password: 

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.001 sec)

 

MariaDB [(none)]> use test_db;

MariaDB [test_db]> select count(*) from test_table;
+----------+
| count(*) |
+----------+
|    50002 |
+----------+
1 row in set (0.017 sec)

 

[root@server1 my.cnf.d]# systemctl stop mariadb   //아래 명령어에서 자동으로 start 됨, 안하면 자동 동기화가 안됨

[root@server1 backup]# galera_new_cluster

 

[root@server1 backup]# mysql -u root -p
Enter password:

MariaDB [(none)]> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.001 sec)

 

##########################

##### SERVER 2 test 확인######

##########################

server2에서 확인하면

 

[root@server2 my.cnf.d]# mysql -u root -p

Enter password:

 

MariaDB [(none)]> show global status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+
1 row in set (0.003 sec)

 

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.001 sec)

 

 

참조 : m.blog.naver.com/theswice/221726407896

offbyone.tistory.com/228