'분류 전체보기'에 해당되는 글 381건

# Adding multiple columns AFTER a specific column in MySQL (mysql 5.7.19)

after 를 사용하게되면 algorithm = copy로 처리하게되서 테이블의 용량에 따라 컬럼 추가시에는 성능이슈가 발생할 수도 있다.

예를 들어 테이블 TMP_LOAD_20201203에 x0 컬럼 뒤에 컬럼 5개를 추가한다고하면

#잘못된 예)
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x1 VARCHAR(8) DEFAULT NULL AFTER x0; 
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x2 VARCHAR(1) DEFAULT NULL AFTER x1; 
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x3 VARCHAR(1) DEFAULT NULL AFTER x2; 
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x4 VARCHAR(1) DEFAULT NULL AFTER x3; 
ALTER TABLE TESTDB.TMP_LOAD_20201203 ADD COLUMN x5 VARCHAR(1) DEFAULT NULL AFTER x4; 
// 컬럼 하나 추가할때마다 임시테이블을 만드는 작업을 하고 있어 느리다.

#수정 예)
ALTER TABLE TESTDB.TMP_LOAD_20201203 
ADD COLUMN x1 VARCHAR(8) DEFAULT NULL AFTER x0, 
ADD COLUMN x2 VARCHAR(1) DEFAULT NULL AFTER x1, 
ADD COLUMN x3 VARCHAR(1) DEFAULT NULL AFTER x2, 
ADD COLUMN x4 VARCHAR(1) DEFAULT NULL AFTER x3, 
ADD COLUMN x5 VARCHAR(1) DEFAULT NULL AFTER x4; 
// 임시테이블 한번만 만들고 나머지는 컬럼모두 추가~ 아래꺼가 훨씬 빠르다.

after column 을 쓰게되면 algorithm = inplace 를 사용할 수 없다. mysql 8에서도 맨뒤에 컬럼을 추가하는 경우를

제외하고는 instant를 쓸 수 없는 것으로 알고 있다.

컬럼 순서 재정렬 이슈.. 이번에 정리해두자.

끝~

참조 : stackoverflow.com/questions/17541312/adding-multiple-columns-after-a-specific-column-in-mysql

mysql 메모리 캐싱하는 부분과 테이블캐시, 스레드캐시등 메모리 사용하는 부분을 좀 더 알고 싶어서 샀는데

와...욕심이었나? 겁나 어렵다;; 1권 보고 다시 와야겠다

Mysql과 MariaDB가 서로 각각 갈길을 가고 있다.

어차피 둘다 BSD 하면서 가면서 굳이 왜 이러나 싶기도 하다.

이제는 RDB 두 개를 공부하게 생겼으니 귀찮아지게 생겼다.

github에 있는 히스토리뷰를 수시로 확인하면서 추후 어떻게 가는지 지켜보자.

- percona-server은 mysql과 동일하게 가는거 같은데... 카카오뱅크에서 쓴다는 기사만 봤지

따로 보진 않았는데 thread-pool을 무료로 쓸수 있다는 얘기가 있어 성능테스트를 조만간 한번해봐야겟다.

1) Mysql 히스토리뷰

참조 : https://github.com/dveeden/mysql-history-graph

2) Mysql 8.0 VS MariaDB 10.3 비교

Please watch Percona’s Chief Evangelist, Colin Charles as he presents as he presents MariaDB 10.3 vs. MySQL 8.0.
- Percona의 수석 에반젤리스트인 Colin Charles가 MariaDB 10.3 대 MySQL 8.0을 제시하면서 발표하는 모습을 지켜봐 달라.

Are they syntactically similar? Where do these two languages differ? Why would I use one over the other?
- 그들은 구문론적으로 비슷한가? 이 두 언어는 어디에서 다른가? 왜 내가 하나를 다른 하나보다 더 쓰겠어?

MariaDB 10.3 is on the path of gradually diverging from MySQL 8.0.
One obvious example is the internal data dictionary currently under development for MySQL 8.0.
This is a major change to the way metadata is stored and used within the server, and MariaDB doesn’t have an equivalent feature.
Implementing this feature could mark the end of datafile-level compatibility between MySQL and MariaDB.
- MariaDB 10.3은 MySQL 8.0에서 점차 멀어지는 경로에 있다.
한 가지 분명한 예가 현재 MySQL 8.0에 대해 개발 중인 내부 데이터 사전이다. 이는 서버 내에서 메타데이터를 저장하고 사용하는 방식을 크게 변화시킨 것으로 마리아DB에는 동등한 기능이 없다.
이 기능을 구현하면 MySQL과 MariaDB 간의 데이터 파일 레벨 호환성이 종료될 수 있다.

ex) 예를들면 MariaDB 를 mysql 로 이관한다면 예전에는 스키마 통째  mysqldump 로 이관이 가능했지만

이제는 불가능하고 테이블의 데이터만 이관가능하는 얘기가 된다.

There are also non-technical differences between MySQL 8.0 and MariaDB 10.3, including:
- MySQL 8.0과 MariaDB 10.3 사이에는 다음과 같은 비기술적인 차이점도 있다.

Licensing: MySQL offers their code as open-source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise.
MariaDB can only use the GPL, because their work is derived from the MySQL source code under the terms of that license.
- 라이센스: MySQL은 그들의 코드를 GPL에 따라 오픈 소스로 제공하고, MySQL Enterprise의 형태로 비 GPL 상업 배포 옵션을 제공한다.
MariaDB는 GPL만 사용할 수 있다. GPL의 작업은 해당 라이센스의 조건에 따른 MySQL 소스 코드에서 파생되기 때문이다.

Support services: Oracle provides technical support, training, certification and consulting for MySQL, while MariaDB has their own support services.
Some people will prefer working with smaller companies, as traditionally it affords them more leverage as a customer.
- 지원 서비스: Oracle은 MySQL에 대한 기술 지원, 교육, 인증 및 컨설팅을 제공하며, MariaDB는 자체 지원 서비스를 보유하고 있다. 전통적으로 고객으로서 더 많은 지렛대를 제공하기 때문에 일부 사람들은 중소기업과 일하는 것을 선호할 것이다.

Community contributions: MariaDB touts the fact that they accept more community contributions than Oracle.
Part of the reason for this disparity is that developers like to contribute features,
bug fixes and other code without a lot of paperwork overhead (and they complain about the Oracle Contributor Agreement).
However, MariaDB has its own MariaDB Contributor Agreement -- which more or less serves the same purpose.
- 지역사회 공헌: 마리아DB는 그들이 오라클보다 지역사회 공헌을 더 많이 받아들인다는 사실을 강조한다. 이러한 불균형의 이유 중 일부는 개발자들이 많은 문서 업무 부담 없이 기능, 버그 수정 및 기타 코드를 제공하기를 좋아하기 때문이다(그리고 그들은 Oracle 기부자 계약에 대해 불평한다).
그러나 마리아DB는 마리아DB 기부자 협정이 있는데, 이것은 거의 같은 목적을 위해 사용되고 있다.

Colin will take a look at some of the differences between MariaDB 10.3 and MySQL 8.0 and help answer some of the common questions our Database Performance Experts get about the two databases.
- Colin은 MariaDB 10.3과 MySQL 8.0의 차이점을 살펴보고, 두 데이터베이스에 대해 데이터베이스 성능 전문가들이 얻는 일반적인 질문에 답하는 데 도움을 줄 것이다.

참조 : https://www.percona.com/resources/webinars/mariadb-103-vs-mysql-80

1. 업그레이드 준비

#마리아DB 10.4의 변화와 개선점
https://mariadb.com/kb/en/changes-improvements-in-mariadb-104/

1.인증에서 달라진 점

1) mysql.host 테이블이 더 이상 생성되지 않음
2) mysql.user 테이블이 폐기되고, 사용자 계정 및 글로벌 권한이 mysql.global_priv 테이블에 저장됨

2.엔진(InnoDB) 에서 달라진 점

1) Online DDL 알고리즘 algorithm=INSTANT 지원범위 추가됨 (MDEV-15562)
- Drop column, changing of the order of columns

2) 테이블 ROW_FORMAT=COMPACT, DYNAMIC or COMPRESSED 로 압축된 row형식이라도 제한적으로 instant 지원 (MDEV-15563)
- MariaDB 10.4.3 이상에서 InnoDB는 또한 ROW_FORMAT 테이블 옵션을 CONCOMPANT, DYNAMIC 또는 CONPRED로 설정한 경우에도 algorithm=INSTANT로 설정하면 VARCHAR 열의 길이를 더 제한적으로 늘릴 수 있도록 지원한다.
- 열의 원래 길이가 127바이트 이하, 열의 새 길이가 256바이트 이상이면 알고리즘을 INSTANT로 설정하면 길이를 늘릴 수 있다.
- 열의 원래 길이가 255바이트 이하인 경우, 열의 새 길이는 여전히 255바이트 이하인 경우 알고리즘을 INSTANT로 설정하면 길이를 늘릴 수 있다.
- 열의 원래 길이가 256바이트 이상이고, 열의 새 길이가 여전히 256바이트 이상이면 알고리즘을 INSTANT로 설정하면 길이를 늘릴 수 있다.
- 원래 길이가 128바이트에서 255바이트 사이였고, 새로운 길이가 256바이트 이상이면 알고리즘을 INSTANT로 설정한 상태에서 길이를 늘릴 수 없다.
- 자세한 내용은 MDEV-15563을 참조한다.

3) 인덱스 없는 컬럼에 instant로 정렬 or 문자셋 변경 (MDEV-15564)
4) undo tablespace를 초기화 하기위한 redo log 볼륨을 줄임 (MDEV-17138)
5) MariaDB 10.2.19 이전 truncate table 에 대한 crash-upgrade 지원을 제거 (MDEV-13564)
6) innodb_encrypt_log에 대한 key rotation 추가 (MDEV-12041)
7) innodb_checksum_algorithm=full_crc32 개선 (MDEV-12026)

3. 옵티마이져 달라진 점

1) Optimizer 트레이스 구현, 시스템 변수 Optimizer_trace를 활성화하여 Optimizer 트레이스를 활성화할 수 있음 (MDEV-6111)
2) 엔진 독립 테이블 통계는 이제 기본적으로 활성화되며, 새로운 default값은 use_stat_tables=PREFERABLY_FOR_QUERIES and optimizer_use_condition_selectivity=4 이다. (MDEV-15253)

통계는 mysql.table_stats, mysql.column_stats, mysql.index_stats의 세 가지 테이블에 저장된다.
위 테이블의 데이터 사용 또는 업데이트는 use_stat_tables 변수에 의해 제어된다. 가능한 값은 다음과 같다.

https://mariadb.com/kb/en/engine-independent-table-statistics/

히스토그램은 현재 기본적으로 수집된다 (MDEV-18608)
analyze_sample_percentage 변수가 추가됨. 기본값은 100(Analyze가 전체 테이블을 사용함)이지만, 통계수집에 테이블 데이터의 샘플만 사용하도록 분석을 설정할 수도 있다.

3) 조건 푸시다운 최적화의 적용 범위가 확대됨
- materialized in 서브쿼리에 조건을 적용할 수 있음 (MDEV-12387)
- HAVING 절의 조건은 WHERE로 푸시될 수 있다. 이 동작은 최적기 스위치 플래그를 통해 제어된다.
condition_pushdown_from_having.

4) 옵티마이져 스위치 플래그 optimize_join_buffer_size가 이제 기본적으로 ON으로 설정됨(default) (MDEV-17903)
5) Rowid 필터링 최적화 추가 (MDEV-16188). 옵티마이저 스위치 플래그 rowid_filter를 통해 제어된다.

- Instant add column 이란?
기존 INPLACE 등의 방식에서 대용량 테이블에 add column default value 수행은
full scan 등을 유발하여 처리가 오래걸릴 뿐만 아니라 Master <-> slave 간 replication LAG 이 발생하기 때문에 데이터 동기화에도 문제가 생김
이를 해결하고자 나온 것이 INSTANT algorithm 으로 mysql 8.0.12 / MariaDB 10.3.7 버전에서 default 로 사용됨
원리는 ORACLE에서 LOB 데이터를 테이블 segment 처럼 따로 저장하는 것과 같이
instant add column으로 생성된 컬럼을 테이블과 따로 저장하고 meta data 만 연결시켜주는 방식
https://sarc.io/index.php/mariadb/1459-mariadb-instant-add-column

2. DB 버전 업그레이드

1) MariaDB 가이드안 대로 10.2 to 10.3 로 올리고 10.3 to 10.4로 순차적으로 업그레이드한다.
https://mariadb.com/kb/en/upgrading/

2) Xtrabackup 으로 기존 DB 백업

/usr/bin/innobackupex \
--host=127.0.0.1 \
--user=root \
--password='비번' \
--ibbackup=xtrabackup \
--no-lock \
--stream=tar ./ | pigz -p 6 > DEV_20201123.tar.gz

--> pigz는 기존 tar 압축을 parallel로 지원한다. -p 옵션은 cpu core를 얼마나 쓸것인가 입력하는 값으로
예를들어 하이퍼스레딩 포함해서 24 core라면 상용 서버라면 4 정도 , 개발 서버는 6 정도가 적당한 듯하다. 그이상 할당해도 top 으로 확인해보면 600% 이상은 잘 안쓴다

# 압축해제
# tar -xizf DEV_20201123.tar.gz

3) MariaDB 10.2 binary 폴더백업 후 10.3.27 설치

downloads.mariadb.com/MariaDB/mariadb-10.3.27/bintar-linux-x86_64/

[root@localhost backup]# service mysqld stop
Shutting down MariaDB...                                   [  OK  ]

mv mariadb_data mariadb_data_bak
mkdir mariadb_data
chown -Rf mysql:mysql mariadb_data

mv mariadb-10.3.27-linux-x86_64 /svc/mariadb_engine/mariadb-10.3.27

[root@localhost svc]# rm mariadb
rm: remove 심볼릭 링크 `mariadb'? y'

ln -s /svc/mariadb_engine/mariadb-10.3.27 mariadb

chown -Rf mysql:mysql mariadb
 
rm -rf mariadb_logs
mkdir mariadb_logs
mkdir {binary,error,relay,slow}

mariadb-10.3.27버전 설치 방법[su]
[root@localhost scripts]# ./mysql_install_db --defaults-file=/etc/my.cnf --basedir=/svc/mariadb --datadir=/svc/mariadb_data --user=mysql
Installing MariaDB/MySQL system tables in '/svc/mariadb_data' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:

'/svc/mariadb/bin/mysqladmin' -u root password 'new-password'
'/svc/mariadb/bin/mysqladmin' -u root -h 127.0.0.1
127.0.0.1 password 'new-password'

Alternatively you can run:
'/svc/mariadb/bin/mysql_secure_installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/svc/mariadb' ; /svc/mariadb/bin/mysqld_safe --datadir='/svc/mariadb_data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/svc/mariadb/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:'
https://mariadb.org/get-involved/

[root@localhost scripts]# 

[root@localhost support-files]# ls
binary-configure  magic  mysql-log-rotate  mysql.server  mysqld_multi.server  policy  wsrep.cnf  wsrep_notify
[root@localhost support-files]# cp -dpr mysql.server ../bin/

[root@localhost svc]# service mysqld start
Starting MariaDB.201112 17:49:51 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201112 17:49:51 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
.                                                          [  OK  ]

[root@localhost bin]# ./mysql_secure_installation --basedir=/svc/mariadb --socket=/tmp/mysql.sock
print: /svc/mariadb/bin/my_print_defaults

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost bin]# 

[root@localhost bin]# service mysqld stop
Shutting down MariaDB..                                    [  OK  ]
[root@localhost bin]# service mysqld start
Starting MariaDB.201112 17:52:18 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201112 17:52:18 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
.                                                          [  OK  ]
[root@localhost bin]# 
// 재시작 문제 없다.

cp my_print_defaults mysql mysql_config mysqladmin mysqlbinlog mysqlcheck mysqldump mysqlimport mysqlshow mysqlslap /usr/bin

cd /usr/bin
[root@mvno-db bin]# chown -Rf root.root my_print_defaults mysql mysql_config mysqladmin mysqlbinlog mysqlcheck mysqldump mysqlimport mysqlshow mysqlslap

[root@localhost svc]# service mysqld stop

[root@localhost svc]# mv mariadb_data mariadb_data_tmp
[root@localhost svc]# mv mariadb_data_bak mariadb_data
//현재 설치된 10.3.x버전 mariadb_data를 tmp로 mv 시키고 이전 10.2 버전의 실제 데이터 폴더를 mariadb_data로 변경한다.

[root@localhost svc]# service mysqld start
Starting MariaDB.201112 17:55:39 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201112 17:55:39 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
.                                                          [  OK  ]

[root@localhost svc]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.27-MariaDB-log 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)]> exit
Bye
// 10.3.27로 바뀐거 확인

4) MariaDB 10.3.27 데이터 업그레이드

[root@localhost bin]# ./mysql_upgrade -uroot -p
Enter password: 

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
....
information_schema
performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

// root로 쉘접속해서 정상적으로 뜨는지 다시 확인
[root@localhost bin]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.3.27-MariaDB-log 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)]> show processlist;
+----+-------------+----------------------+-------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host                 | db    | Command | Time | State                    | Info             | Progress |
+----+-------------+----------------------+-------+---------+------+--------------------------+------------------+----------+
|  1 | system user |                      | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |                      | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |                      | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |                      | NULL  | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  5 | system user |                      | NULL  | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 24 | xxxxxx      | 192.168.10.100:56510 | MDLDB | Sleep   |   12 |                          | NULL             |    0.000 |
| 25 | xxxxxx      | 192.168.10.100:56511 | MDLDB | Sleep   |   12 |                          | NULL             |    0.000 |
| 26 | xxxxxx      | 192.168.10.100:56512 | MDLDB | Sleep   |   12 |                          | NULL             |    0.000 |
| 27 | root        | localhost            | NULL  | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+----------------------+-------+---------+------+--------------------------+------------------+----------+
9 rows in set (0.000 sec)

5) MariaDB 10.3 binary 폴더백업 후 10.4.17 설치

<--백업도 xtrabackup에서 Mariabackup으로 변경된다.

service mysqld stop

mv mariadb_data mariadb_data_bak
mkdir mariadb_data
chown -Rf mysql:mysql mariadb_data

mv mariadb-10.4.17-linux-x86_64 /svc/mariadb_engine/mariadb-10.4.17

[root@localhost svc]# rm mariadb
rm: remove 심볼릭 링크 `mariadb'? y'

ln -s /svc/mariadb_engine/mariadb-10.4.17 mariadb

chown -Rf mysql:mysql mariadb

mariadb-10.4.17버전 설치 방법[su]
[root@localhost scripts]# ./mysql_install_db --defaults-file=/etc/my.cnf --basedir=/svc/mariadb --datadir=/svc/mariadb_data --user=mysql
Installing MariaDB/MySQL system tables in '/svc/mariadb_data' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/svc/mariadb' ; /svc/mariadb/bin/mysqld_safe --datadir='/svc/mariadb_data'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/svc/mariadb/mysql-test' ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:'
https://mariadb.org/get-involved/

[root@localhost support-files]# cp -dpr mysql.server ../bin/

[root@localhost svc]# service mysqld start
Starting MariaDB.201113 13:50:40 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201113 13:50:40 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
                                                           [  OK  ]

[root@localhost bin]# ./mysql_secure_installation --basedir=/svc/mariadb --socket=/tmp/mysql.sock
print: /svc/mariadb/bin/my_print_defaults

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Sorry, passwords do not match.

New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost bin]# 


[root@localhost bin]# service mysqld stop
Shutting down MariaDB..                                    [  OK  ]

cp my_print_defaults mysql mysql_config mysqladmin mysqlbinlog mysqlcheck mysqldump mysqlimport mysqlshow mysqlslap /usr/bin

cd /usr/bin
[root@mvno-db bin]# chown -Rf root.root my_print_defaults mysql mysql_config mysqladmin mysqlbinlog mysqlcheck mysqldump mysqlimport mysqlshow mysqlslap
[root@localhost svc]# mv mariadb_data mariadb_data_tmp
[root@localhost svc]# mv mariadb_data_bak mariadb_data

[root@localhost svc]# service mysqld start
Starting MariaDB.201113 13:57:38 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201113 13:57:38 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
                                                           [  OK  ]

[root@localhost svc]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.17-MariaDB-log 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)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
|  9 | root        | localhost | NULL | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.000 sec)

MariaDB [(none)]> exit
Bye
// 10.4.17로 바뀐거 확인

6) MariaDB 10.4.17  데이터 업그레이드

[root@localhost bin]# ./mysql_upgrade -uroot -p
Enter password: 
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
....
information_schema
performance_schema
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

// root로 쉘접속해서 정상적으로 뜨는지 다시 확인
[root@localhost svc]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.4.17-MariaDB-log 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)]> show processlist;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host            | db   | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
|  1 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  3 | system user |                 | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |                 | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  5 | system user |                 | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 17 | root        | 127.0.0.1:38998 | NULL | Sleep   |   70 |                          | NULL             |    0.000 |
| 19 | root        | localhost       | NULL | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
7 rows in set (0.000 sec)

문제 없다 10.4 업그레이드 끝~

6) MariaDB 10.4 to 10.5 업그레이드?
하지 않는다. 오픈소스 특성상 최신버전은 예기치 못한 버그가 있을 수 있다.

가장 최신에 한단계 낮은 Stable 버전으로 설치한다.

#파티션 테이블 이관 참조
https://purumae.tistory.com/210

파티션 관련 문의가 있어 정리해 놓는다.

아래와 같은 테이블 파티셔닝을 구성한다면 (RANGE는 보통 날짜를 많이 사용한다)

CREATE TABLE `TB_TEST` (
`SEQ` int(11) NOT NULL AUTO_INCREMENT,
`xxx_DATE` datetime NOT NULL,
`xxx_NO` bigint(15) unsigned NOT NULL,
`xxx_ID` varchar(20) NOT NULL,
`xxx__CD` varchar(2) DEFAULT NULL,
`xx_DATE` datetime DEFAULT NULL,
PRIMARY KEY (`SEQ`,`xxx_DATE`),
KEY `IDX_TEST_01` (`xxx_DATE`,`xxx_CD`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(`xxx_DATE`))
(PARTITION `p201909` VALUES LESS THAN (737698) ENGINE = InnoDB,
PARTITION `p201910` VALUES LESS THAN (737729) ENGINE = InnoDB,
PARTITION `p201911` VALUES LESS THAN (737759) ENGINE = InnoDB,
PARTITION `p201912` VALUES LESS THAN (737790) ENGINE = InnoDB,
PARTITION `pExt` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
;

위처럼 파티셔닝을 사용할 컬럼은 반드시 PK로 구성되어 있어야 한다.

그럼 테이블 자체에 PK가 없으면 파티션 구성이 안되나?

-> 아니다 파티셔닝을 할 수는 있다. MariaDB든 Mysql이든 pk가 없으면 옵티마이져가 후보군 컬럼중에 하나를 pk처럼 사용하기 때문이다. 하지만 제대로 성능이 나오지 않는다.

1) 파티션 추가

p201912 이후 신규로 파티션을 추가한다면 pExt를 삭제하고 p202001을 추가한 후 다시 pExt를 생성해야한다.

이것을 한번에 할수 있는 명령어는 REORGANIZE !

/* 파티션 추가 */
ALTER TABLE TB_TEST REORGANIZE PARTITION pExt INTO (
PARTITION p202001 VALUES LESS THAN (TO_DAYS('2020-02-01')),
PARTITION pExt VALUES LESS THAN MAXVALUE
);

파티션 추가는 기존파티션 사이에 넣을 수는 없고 가장 이후 파티션 뒤로 생성이 가능하다.

이런 점 때문에 혹시 파티션을 생성했는데.. range 범위를 잘못 생성했을 경우 삭제하고 재구성해야할 수도 있다.

2) 파티션 삭제

/* 파티션 삭제 */
ALTER TABLE TB_TEST DROP PARTITION pExt;

맨아래서부터 순서대로 삭제하고 잘못구성한 파티션까지 삭제후 재구성한다.

만약 p202001을 TO_DAY('2020-03-01')로 잘못구성했다고 가정하면 pExt, p202001 순으로 순차적으로 삭제하면된다.

이럴 경우 파티션 여러 개 한번에 삭제하려면?

<-- 서버에 한번에 삭제하도록 구성하고 싶다는 문의가 있었고 아래와 같이 명령어를 사용하면 적용가능했다.

ALTER TABLE TB_TEST DROP PARTITION pExt, p202001;

3) 파티션 생성

ALTER TABLE TB_TEST add PARTITION (
PARTITION p202001 VALUES LESS THAN (TO_DAYS('2020-02-01')),
PARTITION pExt VALUES LESS THAN MAXVALUE
);

MAXVAULE 는 사용하는 것이 좋다. 어떤 이유에서든 테이블에 데이터가 스키마구성에 의해 에러가 나서 쌓이지 않는 경우는 예방해야하기 때문이다.

xtrabackup 을 사용하면 보통 innobackupex 명령어를 쓰게된다.
근데 최근 MariaDB 를 버전업하면서 백업한 것을 재생성하려는데

ib_logfile0 이 0이라고 복원시 리두로그가 깨지고 DB가 start되지 않는 이슈가 발생했다.

확인해보니 MariaDB 10.3부터는 xtrabackup이 아니라 mariabackup이라는 것을 사용해야 한다고 한다.

이것에 대해 알아본다.

1. xtrabackup에서 mariabackup으로 변경해야하는 이유

xtrabackup 에서 mariadb에서 변경된 redo log를 인식하지 못하기 때문에 mariadbackup을 사용해야 한다.

#참조:  mariadb.com/kb/en/percona-xtrabackup-overview/

In MariaDB 10.3, Percona XtraBackup is not supported. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.
In MariaDB 10.2 and MariaDB 10.1, Percona XtraBackup is only partially supported. See Percona XtraBackup Overview: Compatibility with MariaDB for more information.

2. Percona XtraBackup과의 차이점

• Percona XtraBackup은 InnoDB 리두 로그 파일을 xtrabackup_logfile 파일로 복사하는 반면,

Mariabackup은 ib_logfile0 파일을 사용합니다.

• Percona XtraBackup의 libgcrypt 기반 백업 암호화는 Mariabackup에서 지원되지 않습니다.

• innobackupex 과 같이 innobackupex 에서 mariabackup 로의 심볼릭 링크는 없습니다.

대신 mariabackup 에는 innobackupex 호환 옵션을 활성화하는 --innobackupex 명령 줄 옵션이 있습니다.

• --compact 및 --rebuild_indexes 옵션은 지원되지 않습니다.

• MariaDB 10.1.24의 Mariabackup에서 --stream=tar 에 대한 지원이 제거되었습니다.

• xbstream 유틸리티 이름이 mbstream 으로 변경되었습니다. 그러나 백업을 생성 할 때이 출력 형식을 선택하기 위해 Mariabackup의 --stream 옵션은 여전히 xbstream 값을 예상합니다.
• Mariabackup은 잠금없는 binlog를 지원하지 않습니다.

3. 이제 xtrabackup은 사용못하나?

--innobackupex 옵션을 추가하면 당분간은 사용가능하다.

하지만 10.4부터는 이마저도 지원하지 않는다고 하니 Mariabackup으로 변경하는 게 낫겠다.

4. Mariabackup 스크립트 변경 및 테스트 (DB 버전 : Mariadb 10.4.15)

4-1) Mariabackup

[root@localhost backup]# mariabackup \
 --backup \
 --no-lock \
 --host=127.0.0.1 \
 --port=3306 \
 --user=root \
 --password='비번' \
 --target-dir=/backup/innobackup/20201028_17
 
 //스크립트 종료후 파일확인
 [root@localhost 20201028_17]# ls -lh
drwx------. 2 root root 4.0K 2020-10-28 16:49 TESTDB
-rw-r-----. 1 root root  72K 2020-10-28 16:49 aria_log.00000001
-rw-r-----. 1 root root   52 2020-10-28 16:49 aria_log_control
-rw-r-----. 1 root root  366 2020-10-28 16:49 backup-my.cnf
-rw-r-----. 1 root root  25K 2020-10-28 16:49 ib_buffer_pool
-rw-r-----. 1 root root 215M 2020-10-28 16:49 ib_logfile0 <--- 트렌젝션 데이터 쌓임
-rw-r-----. 1 root root 500M 2020-10-28 16:41 ibdata1
-rw-r-----. 1 root root 500M 2020-10-28 16:41 ibdata2
-rw-r-----. 1 root root 500M 2020-10-28 16:41 ibdata3
-rw-r-----. 1 root root 100M 2020-10-28 16:41 ibdata4
drwx------. 2 root root 4.0K 2020-10-28 16:49 mysql
drwx------. 2 root root 4.0K 2020-10-28 16:49 performance_schema
-rw-r-----. 1 root root   85 2020-10-28 16:49 xtrabackup_checkpoints
-rw-r-----. 1 root root  535 2020-10-28 16:49 xtrabackup_info

4-2) redo 로그파일 합치기(=xtrabackup에서는 apply log 적용)

mariabackup \
    --prepare \
	--user=root \
	--password='비번' \
	--target-dir=/backup/innobackup/20201028_17
    
//prepare 로그 확인
mariabackup based on MariaDB server 10.4.15-MariaDB Linux (x86_64)
[00] 2020-10-28 17:00:04 cd to /backup/innobackup/20201028_17/
[00] 2020-10-28 17:00:04 This target seems to be not prepared yet.
[00] 2020-10-28 17:00:04 mariabackup: using the following InnoDB configuration for recovery:
[00] 2020-10-28 17:00:04 innodb_data_home_dir = .
[00] 2020-10-28 17:00:04 innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
[00] 2020-10-28 17:00:04 innodb_log_group_home_dir = .
[00] 2020-10-28 17:00:04 InnoDB: Using Linux native AIO
[00] 2020-10-28 17:00:04 Starting InnoDB instance for recovery.
[00] 2020-10-28 17:00:04 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2020-10-28 17:00:04 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-28 17:00:04 0 [Note] InnoDB: Uses event mutexes
2020-10-28 17:00:04 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-28 17:00:04 0 [Note] InnoDB: Number of pools: 1
2020-10-28 17:00:04 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-28 17:00:04 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
2020-10-28 17:00:04 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-28 17:00:04 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2020-10-28 17:00:04 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=14172663378
2020-10-28 17:00:12 0 [Note] InnoDB: Starting a batch to recover 1918 pages from redo log. <-- ib_logfile0에서 트랜젝션 파일 읽어서 합치기 작업중
2020-10-28 17:00:14 0 [Note] InnoDB: Starting a batch to recover 2135 pages from redo log.
2020-10-28 17:00:15 0 [Note] InnoDB: Starting a batch to recover 2551 pages from redo log.
2020-10-28 17:00:19 0 [Note] InnoDB: Read redo log up to LSN=14282567680
2020-10-28 17:00:20 0 [Note] InnoDB: Starting a batch to recover 2577 pages from redo log.
2020-10-28 17:00:24 0 [Note] InnoDB: Starting a batch to recover 3005 pages from redo log.
2020-10-28 17:00:28 0 [Note] InnoDB: Starting a batch to recover 3070 pages from redo log.
2020-10-28 17:00:33 0 [Note] InnoDB: Starting final batch to recover 1742 pages from redo log.
[00] 2020-10-28 17:00:33 Last binlog file , position 0
[00] 2020-10-28 17:00:34 completed OK!

//작업후 ib_logfile0 상태 확인
[root@localhost 20201028_17]# ls -lh
drwx------. 2 root root 4.0K 2020-10-28 16:49 TESTDB
-rw-r-----. 1 root root  72K 2020-10-28 16:49 aria_log.00000001
-rw-r-----. 1 root root   52 2020-10-28 16:49 aria_log_control
-rw-r-----. 1 root root  366 2020-10-28 16:49 backup-my.cnf
-rw-r-----. 1 root root  25K 2020-10-28 16:49 ib_buffer_pool
-rw-r-----. 1 root root    0 2020-10-28 17:00 ib_logfile0 <--- 트렌젝션 데이터 merge 끝
-rw-r-----. 1 root root 500M 2020-10-28 17:00 ibdata1
-rw-r-----. 1 root root 500M 2020-10-28 16:41 ibdata2
-rw-r-----. 1 root root 500M 2020-10-28 16:41 ibdata3
-rw-r-----. 1 root root 100M 2020-10-28 16:41 ibdata4
drwx------. 2 root root 4.0K 2020-10-28 16:49 mysql
drwx------. 2 root root 4.0K 2020-10-28 16:49 performance_schema
-rw-r-----. 1 root root   83 2020-10-28 17:00 xtrabackup_checkpoints
-rw-r-----. 1 root root  535 2020-10-28 16:49 xtrabackup_info

백업은 정상적으로 끝난 것을 확인했다. 이제 재생성 테스트를 해본다.

4-3) restore

#재생성
[mysql@localhost ~]$ service mysqld stop
Shutting down MariaDB..                                    [  OK  ]


[root@localhost dbtest]# mv mariadb_data mariadb_data_bak

[root@localhost dbtest]# mkdir mariadb_data
	
//copy-back or move-back 둘중 아무거나 하면 된다.

mariabackup \
	--move-back \
	--target-dir=/backup/innobackup/20201028_17 \
	--user=root \
	--password='비번' \
	--datadir=/dbtest/mariadb_data
    
// restore 로그
mariabackup based on MariaDB server 10.4.15-MariaDB Linux (x86_64)
[01] 2020-10-28 17:56:29 Moving ibdata1 to /dbtest/mariadb_data/ibdata1
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ibdata2 to /dbtest/mariadb_data/ibdata2
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ibdata3 to /dbtest/mariadb_data/ibdata3
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ibdata4 to /dbtest/mariadb_data/ibdata4
....
[01] 2020-10-28 17:56:29 Moving ./mysql/proc.MAD to /dbtest/mariadb_data/mysql/proc.MAD
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./mysql/proxies_priv.MAI to /dbtest/mariadb_data/mysql/proxies_priv.MAI
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./mysql/general_log.CSV to /dbtest/mariadb_data/mysql/general_log.CSV
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./mysql/func.frm to /dbtest/mariadb_data/mysql/func.frm
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./mysql/proc.MAI to /dbtest/mariadb_data/mysql/proc.MAI
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./aria_log_control to /dbtest/mariadb_data/aria_log_control
[01] 2020-10-28 17:56:29         ...done
[01] 2020-10-28 17:56:29 Moving ./xtrabackup_info to /dbtest/mariadb_data/xtrabackup_info
[01] 2020-10-28 17:56:29         ...done
[00] 2020-10-28 17:56:29 completed OK!

// 파일확인 -> ib_logfile0 은 사라졌다.
[root@localhost mariadb_data]# ls -lh
drwx------. 2 root root 4.0K 2020-10-28 17:56 TESTDB
-rw-r-----. 1 root root  72K 2020-10-28 17:47 aria_log.00000001
-rw-r-----. 1 root root   52 2020-10-28 17:47 aria_log_control
-rw-r-----. 1 root root 1.1M 2020-10-28 17:47 ib_buffer_pool
-rw-r-----. 1 root root 500M 2020-10-28 17:51 ibdata1
-rw-r-----. 1 root root 500M 2020-10-28 17:39 ibdata2
-rw-r-----. 1 root root 500M 2020-10-28 17:39 ibdata3
-rw-r-----. 1 root root 100M 2020-10-28 17:39 ibdata4
drwx------. 2 root root 4.0K 2020-10-28 17:56 mysql
drwx------. 2 root root 4.0K 2020-10-28 17:56 performance_schema
-rw-r-----. 1 root root  535 2020-10-28 17:47 xtrabackup_info

[root@localhost mariadb_data]# chown -Rf mysql:mysql /dbtest/mariadb_data

[mysql@localhost ~]$ service mysqld start

재생성 제대로 되는 것을 확인했다.... mariabackup으로 쉘스크립트 수정하고 반영준비한다.

끝~

1. 설치중 문제점 발견

MariaDB 최근버전으로 성능테스트를 하려고 virtualbox에서 10.4.13 버전을 받아 DB를 설치하고 start까지는 성공하였다.

[mysql@localhost bin]$ service mysqld start
Starting MariaDB.201021 16:14:13 mysqld_safe Logging to '/svc/mariadb_logs/error/mysql.err'.
201021 16:14:13 mysqld_safe Starting mysqld daemon with databases from /svc/mariadb_data
                                                           [  OK  ]

별로 달라진 건 없네..라고 생각하고 stop을 해보았는데 .. 어떤 로그도 없이 그냥 안된다. ???

[mysql@localhost bin]$ service mysqld stop 

//멍때리고 아무것도 안됨;; mysql.error 로그도 없음

ps -ef로 프로세스를 확인해보니 아래와 같다.

mysql    30913     1  0 11:12 pts/0    00:00:00 /bin/sh /svc/mariadb/bin/mysqld_safe --datadir=/svc/mariadb_data --pid
mysql    31151 30913  0 11:12 pts/0    00:00:00 /svc/mariadb/bin/mysqld --basedir=/svc/mariadb --datadir=/svc/mariadb_
mysql    31220  1957  0 11:12 pts/0    00:00:00 /bin/sh /sbin/service mysqld stop
mysql    31227 31220  0 11:12 pts/0    00:00:00 /bin/sh /etc/init.d/mysqld stop
root     31393 31227  0 11:12 pts/0    00:00:00 su - mysql -s /bin/sh -c kill -0 31151 // 음..su권한으로 하는데 mysql계정권한으로 실행해서 안되는 거였다.

이렇게 되면 스크립트를 까봐야하는데 수정범위가 크면 버그잡는게 더 시간이 많이 뺏길거 같아서..

가장최근 버전인 10.4.15 을 가져다 설치했지만, 동일한 현상이 발생했다.
그럼 정책적으로 shutdown은 su권한에서만 가능하도록 스크립트를 업데이트했다는건지?

보안강화? 근데 대부분 이중화하고..mysql계정으로 접속해서 처리하는데..
그냥 시간이 좀 걸리더라도 mysql 계정으로 shutdown이 가능하도록 수정하기로 했다.

2. 스크립트 수정

vi로 mysql.server 스크립트에서 stop 부분확인

su_kill() {
  if test "$USER" = "$user"; then
    kill $* >/dev/null 2>&1
  else
    su - $user -s /bin/sh -c "kill $*" >/dev/null 2>&1
  fi
}


  'stop')
    # Stop daemon. We use a signal here to avoid having to know the
    # root password.

    if test -s "$mysqld_pid_file_path"
    then
      mysqld_pid=`cat "$mysqld_pid_file_path"`

      if su_kill -0 $mysqld_pid ; then
        echo $echo_n "Shutting down MariaDB"
        su_kill $mysqld_pid
        # mysqld should remove the pid file when it exits, so wait for it.
        wait_for_gone $mysqld_pid "$mysqld_pid_file_path"; return_value=$?
      else
        log_failure_msg "MariaDB server process #$mysqld_pid is not running!"
        rm "$mysqld_pid_file_path"
      fi

      # Delete lock for RedHat / SuSE
      if test -f "$lock_file_path"
      then
        rm -f "$lock_file_path"
      fi
      exit $return_value
    else
      log_failure_msg "MariaDB server PID file could not be found!"
    fi
    ;;

10.3.x버전과 비교해보니 10.4.x버전에는 su_kill() 라는 함수가 추가되어있는데

if test "$USER" = "$user"; then 부분에서 $user는 mysql로 박혀있고 $USER는

basedir 에서 가져오도록 되어야 하는데 제대로 값을 못가지고 온다.

이 부분에 문제가 있어서 su_kill()이 else로 빠져서 su 권한으로만 실행되도록 해서 불편함이 있다.

수정될 때까지 su_kill()을 사용하는 부분 다 찾아서 주석처리하고 kill로 바꾼다.

수정파일

3. 테스트

[mysql@localhost bin]$ service mysqld stop
Shutting down MariaDB..                                    [  OK  ]

잘된다. 끝~

where 조건절에 OR 연산을 사용한다면 반영 전에 실행계획을 반드시 확인해야한다.
보통은 거의 쓰지 않거나 최소한으로 쓰는 것이 좋고 대용량테이블을 건드린다면 성능이슈가 발생할 가능성이 크다.
이럴경우 해결할 수 있는 방법은 오라클은 힌트가 있고 mysql은 이전에는 union all로 쪼개서 병합하는 방식을 썼는데
mysql 5.1 이후부터(maridb는 5.0부터던가?..) 인덱스중에 느린 편이지만 index_merge라는 것을 쓸 수 있다.

옵티마이져가 equal 연산(=AND)이 아니더라도 인덱스끼리 분리 추출해서 병합해서 return 해주는 방식인데 인덱스 성능중에 느린 축이라서 무시했는데..

-->이번에 대용량쿼리 튜닝하면서 MariaDB 10.3.8 의 index_merge 성능에 놀라 따로 정리하기로 한다.

1. index_merge 지원여부확인

- mysql or mariadb 버전에서 인덱스병합을 지원하는지 확인해본다.

[maria@localhost ~]$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.8-MariaDB-log MariaDB Server

MariaDB [(none)]> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on // 지원확인 O
				   ,index_merge_union=on 
				   ,index_merge_sort_union=on 
				   ,index_merge_intersection=on 
				   ,index_merge_sort_intersection=off
				   ,engine_condition_pushdown=off
				   ,index_condition_pushdown=on
				   ,derived_merge=on
				   ,derived_with_keys=on
				   ,firstmatch=on
				   ,loosescan=on
				   ,materialization=on
				   ,in_to_exists=on
				   ,semijoin=on
				   ,partial_match_rowid_merge=on
				   ,partial_match_table_scan=on
				   ,subquery_cache=on
				   ,mrr=off
				   ,mrr_cost_based=off
				   ,mrr_sort_keys=off
				   ,outer_join_with_cache=on
				   ,semijoin_with_cache=on
				   ,join_cache_incremental=on
				   ,join_cache_hashed=on
				   ,join_cache_bka=on
				   ,optimize_join_buffer_size=off
				   ,table_elimination=on
				   ,extended_keys=on
				   ,exists_to_in=on
				   ,orderby_uses_equalities=on
				   ,condition_pushdown_for_derived=on
				   ,split_materialized=on

2. index_merge란?

보통 인덱스 접근방법 = 타입(type) 은 하나의 인덱스만 사용한다.
이와 달리 index_merge 는 2개 이상의 인덱스를 이용해 각각의 검색결과를 만들어낸 후 그 결과를 병합하는 처리방식이다. 하지만 여러 번의 경험을 보면 이름만큼 그렇게 효율적으로 작동하는 것 같지는 않았다. index_merge 접근방식에는 다음과 같은 특징이 있다.

1) 여러 인덱스를 읽어야 하므로 일반적으로 range 접근방식보다 효율성이 떨어진다.
2) AND와 OR 연상이 복잡하게 연결된 쿼리에서는 제대로 최적화되지 못할 때가 많다. -> 데이터 분포도에 따라 옵티마이져가판단함
3) 전문 검색 인덱스(fulltext)를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
4) index_merge 접근방식으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

#실행계획 인덱스 타입 빠른 순서
system > const > eq_ref > ref > fulltext > ref_or_null > unique_subquery > index_subquery > range > index_merge > index > ALL
->all 빼놓곤 다 인덱스스캔이고 뒤로 갈수록 성능이 떨어진다.

#실행계획 extra 부분
1.Using intersect(...)
각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미이다.

2.Using union(...)
각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.

3.Using sort_union(...)
Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 상대적으로 대량의 range 조건들)이 방식으로 처리된다. Using sort_union 과 Using_union의 차이점은
Using sort_union은 PK만 먼저 읽어서 정렬하고 병합한 후에야 비로소 레코드를 읽어서 반환할 수 있다는 것이다.

Using union()과 Using sort_union()은 둘다 충분히 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우에 사용된다.
Using union()은 대체로 동등 비교(Equal)처럼 일치하는 레코드 건수가 많지 않을 때 사용되고, 각 조건이 크다 또는 작다와 같이 상대적으로 많은 레코드에 일치하는 조건이 사용되는 경우
Using sort_union()이 사용된다. 하지만 실제로는 레코드 건수에 거의 관계없이 각 WHERE 조건에 사용된 비교조건이 모두 동등조건이면 Using union()이 사용되며, 그렇지 않으면 Using_sort_union()이 사용된다.

#알고리즘 참고
 Mysql 내부적으로 이 둘의 차이는 정렬 알고리즘에서 싱클 패스 정렬 알고리즘과 투 패스 정렬 알고리즘의 차이와 같다. Using union()이 싱글패스 정렬 알고리즘을 사용한다면,
Using sort_union()은 투패스 정렬 알고리즘을 사용한다. (Real Mysql 334P)

3. 성능튜닝사례

1) where 조건절 확인

WHERE B.SEQ = '16'
AND A.xxx_YN != 'Y'
AND ( C.xx_KEY = '40' OR ( C.xxx_KEY = '518' AND C.xxx_CD != '01' ) )
AND ( ( A.xx_CD IN ( '1', '3' ) AND C.xxxx_CD NOT IN ( '88' ) AND C.xxx_KEY = '401' )
OR ( A.xxx_CD = '2' AND C.xxxx_CD IN ( '01', '09' ) AND C.xxx_KEY = '1518' )
OR ( CI.xxx_KEY = '48' AND A.xxxx_CD NOT IN ( '9' ) ) )

--> 다른팀에서 성능튜닝을 문의해서 확인해보니 굳이 실행계획 안봐도 성능안나오게 생겼다.

복잡한 or연산을 분리해서 테이블 rows건수 보고 union all로 풀려다가 데이터 분포도가 좋은 컬럼이 인덱스가 안되어있어 추가하고

or연산의 index_merge 성능을 한번 확인해보기로 했다.

2) 인덱스 추가후 실행계획 확인

풀스캔이 사라지면서 rows수는 50만건에서 2000여건으로 많이 줄였고, 수행시간은 3초에서 0.05초로 줄었다.

-> 와우 index_merge가 이렇게 빨랐었나?ㅎ

3. 결론

최고의 튜닝은 버전업! 단 버그만 없다면 (MariaDB 10.2 쓰는거 다 10.3이상으로 올려야겠다...;)

데이터 분포도가 좋은 컬럼은 or연산에서 느린 index_merge라도 엄첨 빠르다.

끝~

참조 : Real Mysql