[Maria] Maria DB Upgrade

오늘은 MariaDB Version을 Upgrade 하는 것에 대해 포스팅할 예정이다.

https://mariadb.com/kb/en/upgrading-from-mariadb-101-to-mariadb-102/

Upgrading from MariaDB 10.1 to MariaDB 10.2

 

 

기존 DB Version : 10.1.45-MariaDB 

업그레이드 DB Version : 10.2.12-MariaDB 

 

■현재 DB 상태

- Sakila Database를 넣어 두었다. 

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1       |
|      | lee       |
| root | lee       |
|      | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)

 

■ DB Shutdown & Database Backup

- 공식문서에는 업그레이드 하기전에 Database 백업을 추천하고 Mariabackup을 추천하고 있다. Backup 하고 있는 동안은 DB에 접근하는 Session이 없어야 혹시 모를 Restore상황에서 Data의 정합성에 문제가 없다.

- mysqladmin 명령어를 이용해서 DB를 Down 시켜준다.

[root@lee sakila]  mariabackup --backup --target-dir=/data/mariabackup --user=root --password=root
Info: Using unique option prefix 'backup' is error-prone and can break in the future. Please use the full name 'backup_encrypted' instead.
211008 17:05:00 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 10.1.45-MariaDB
mariabackup based on MariaDB server 10.1.45-MariaDB Linux (x86_64) 
mariabackup: uses posix_fadvise().
mariabackup: cd to /data/mysql/
mariabackup: open files limit requested 0, set to 1024
mariabackup: using the following InnoDB configuration:
mariabackup:   innodb_data_home_dir = 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
mariabackup:   innodb_log_group_home_dir = ./
mariabackup:   innodb_log_files_in_group = 2
mariabackup:   innodb_log_file_size = 50331648
211008 17:05:00 >> log scanned up to (8940121)
mariabackup: Generating a list of tablespaces
211008 17:05:00 [01] Copying ibdata1 to /data/mariabackup/ibdata1
211008 17:05:00 [01]        ...done
(생략)
211008 17:05:02 Executing UNLOCK TABLES
211008 17:05:02 All tables unlocked
211008 17:05:02 Backup created in directory '/data/mariabackup/'
211008 17:05:02 [00] Writing backup-my.cnf
211008 17:05:02 [00]        ...done
211008 17:05:02 [00] Writing xtrabackup_info
211008 17:05:02 [00]        ...done
mariabackup: Transaction log of lsn (8940121) to (8940121) was copied.
211008 17:05:02 completed OK!


[root@lee sakila] mysqladmin -uroot -proot shutdown
[1]+  Done                    mysqld_safe --user=mysql  (wd: /data/mariadb-10.1.45-linux-x86_64/bin)
(wd now: ~/test_db/sakila)

 

■ 새로운 Database Version으로 Basedir 변경

- 기존 Basedir를 제거하고 새로운 10.2.12 Version의 Binary 파일을 Basedir로 변경한다.

- 10.2.12 Basedir의 mysqld_safe로 Database를 올린다.

[root@lee data]# tar -zxvf mariadb-10.2.12-linux-x86_64.tar.gz


[root@lee data]# ll
total 16
drwxr-xr-x  2 mysql  mysql    24 Oct  8 17:06 log
drwx------  6 root   root   4096 Oct  8 17:05 mariabackup
drwxrwxr-x 12 nbpmon nbpmon 4096 May  9  2020 mariadb-10.1.45-linux-x86_64
drwxrwxr-x 12   1021   1004 4096 Jan  4  2018 mariadb-10.2.12-linux-x86_64
drwxr-xr-x  6 mysql  mysql  4096 Oct  8 17:06 mysql


[root@lee data]# cd mariadb-10.2.12-linux-x86_64/bin/

[root@lee bin]# ./mysqld_safe --user=mysql &
[1] 4523
[root@lee bin]# 211008 17:10:16 mysqld_safe Logging to '/data/log/mariadb.log'.
211008 17:10:16 mysqld_safe Starting mysqld daemon with databases from /data/mysql

 

■ mysql_upgrade 명령어 실행

- mysql 데이터 베이스의 시스템 테이블 이 새 버전과 완전히 호환되는지 확인합니다.

- 모든 테이블을 매우 빠르게 확인하고 새 버전의 MariaDB와 호환되는 것으로 표시합니다.

[root@lee bin]# ./mysql_upgrade -uroot -proot
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
sakila.actor_info                                  OK
sakila.customer_list                               OK
sakila.film_list                                   OK
sakila.nicer_but_slower_film_list                  OK
sakila.sales_by_film_category                      OK
sakila.sales_by_store                              OK
sakila.staff_list                                  OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
performance_schema
sakila
sakila.actor                                       OK
sakila.address                                     OK
sakila.category                                    OK
sakila.city                                        OK
sakila.country                                     OK
sakila.customer                                    OK
sakila.film                                        OK
sakila.film_actor                                  OK
sakila.film_category                               OK
sakila.film_text                                   OK
sakila.inventory                                   OK
sakila.language                                    OK
sakila.payment                                     OK
sakila.rental                                      OK
sakila.staff                                       OK
sakila.store                                       OK
test
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

 

■ DB 재부팅

-DB 재부팅 이후 Version 및 Database의 Data가 제대로 되어있는지 확인해보았다.

[root@lee bin]# pwd
/data/mariadb-10.2.12-linux-x86_64/bin

[root@lee bin]# ./mysqladmin -uroot -proot shutdown
[1]+  Done                    ./mysqld_safe --user=mysql

[root@lee bin]# ./mysqld_safe --user=mysql &
[1] 4864
[root@lee bin]# 211008 17:14:47 mysqld_safe Logging to '/data/log/mariadb.log'.
211008 17:14:47 mysqld_safe Starting mysqld daemon with databases from /data/mysql

[root@lee bin]# mysql -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.12-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.12-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
+--------------------+
5 rows in set (0.01 sec)


MariaDB [sakila]> select * from actor;
+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK        | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED          | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER    | DAVIS        | 2006-02-15 04:34:33 |
(생략)
|      197 | REESE       | WEST         | 2006-02-15 04:34:33 |
|      198 | MARY        | KEITEL       | 2006-02-15 04:34:33 |
|      199 | JULIA       | FAWCETT      | 2006-02-15 04:34:33 |
|      200 | THORA       | TEMPLE       | 2006-02-15 04:34:33 |
+----------+-------------+--------------+---------------------+
200 rows in set (0.00 sec)