Spring Boot와 MariaDB/MySQL 연결하기: H2에서 실전 DB로 레벨업

🗄️ 들어가며

"개발할 때는 잘 되는데, 배포하니까 데이터가 다 사라졌어요!"

H2 인메모리 데이터베이스로 개발하다가 겪는 흔한 문제입니다. 이제는 실제 운영 환경에서 사용할 수 있는 MariaDB/MySQL로 전환할 때입니다.

이 글에서는 Spring Boot 프로젝트를 H2에서 MariaDB/MySQL로 마이그레이션하는 완벽 가이드를 제공합니다. Docker를 활용한 설치부터 트러블슈팅까지, 실무에서 마주칠 모든 상황을 다룹니다.

📑 목차

1. MariaDB vs MySQL 선택 가이드
2. 설치 방법 3가지
3. Spring Boot 연결 설정
4. 데이터베이스 설정 최적화
5. JPA 설정과 Dialect
6. 마이그레이션 전략
7. 트러블슈팅
8. 운영 환경 베스트 프랙티스

MariaDB vs MySQL 선택 가이드

🤔 뭘 선택해야 할까?

구분MariaDBMySQL

라이선스	GPL v2 (완전 오픈소스)	GPL + 상업 라이선스 (Oracle)
성능	일반적으로 더 빠름	대규모 엔터프라이즈 최적화
기능	더 많은 스토리지 엔진	Oracle 지원
호환성	MySQL 5.5까지 100% 호환	원조
커뮤니티	활발한 오픈소스 커뮤니티	Oracle 주도

💡 결론

• 스타트업/중소기업: MariaDB 추천 (무료, 빠름, 기능 풍부)
• 대기업/레거시: MySQL 추천 (Oracle 지원, 안정성)
• Spring Boot: 둘 다 완벽 지원! 설정만 약간 다름

설치 방법 3가지

방법 1: Docker 🐳 (추천!)

MariaDB Docker 설치

 

yaml

# docker-compose.yml
version: '3.8'

services:
  mariadb:
    image: mariadb:11.2
    container_name: my-mariadb
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: root1234!
      MYSQL_DATABASE: myapp
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypass1234!
      TZ: Asia/Seoul
    ports:
      - "3306:3306"
    volumes:
      - mariadb_data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d  # 초기화 SQL
    command:
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      - --skip-character-set-client-handshake
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 3

volumes:
  mariadb_data:
    driver: local

MySQL Docker 설치

 

yaml

# docker-compose.yml
version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: my-mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: root1234!
      MYSQL_DATABASE: myapp
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypass1234!
      TZ: Asia/Seoul
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./init:/docker-entrypoint-initdb.d
    command:
      - --default-authentication-plugin=mysql_native_password
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_unicode_ci
      - --skip-character-set-client-handshake
      - --sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

volumes:
  mysql_data:
    driver: local

실행 명령어

 

bash

# 시작
docker-compose up -d

# 로그 확인
docker-compose logs -f

# 접속 테스트
docker exec -it my-mariadb mysql -u root -p

# 종료
docker-compose down

# 데이터까지 삭제
docker-compose down -v

방법 2: 로컬 설치

Windows

 

powershell

# Chocolatey 사용
choco install mariadb

# 또는 공식 사이트에서 다운로드
# https://mariadb.org/download/

macOS

 

bash

# Homebrew 사용
brew install mariadb

# 서비스 시작
brew services start mariadb

# 보안 설정
mysql_secure_installation

Ubuntu/Debian

 

bash

# 패키지 업데이트
sudo apt update

# MariaDB 설치
sudo apt install mariadb-server mariadb-client

# 보안 설정
sudo mysql_secure_installation

# 서비스 상태 확인
sudo systemctl status mariadb

방법 3: 클라우드 서비스

AWS RDS

 

terraform

resource "aws_db_instance" "mariadb" {
  identifier     = "myapp-mariadb"
  engine         = "mariadb"
  engine_version = "10.11"
  instance_class = "db.t3.micro"
  
  allocated_storage = 20
  storage_type      = "gp3"
  
  db_name  = "myapp"
  username = "admin"
  password = var.db_password
  
  vpc_security_group_ids = [aws_security_group.rds.id]
  db_subnet_group_name   = aws_db_subnet_group.main.name
  
  skip_final_snapshot = true
  
  tags = {
    Name = "MyApp MariaDB"
  }
}

Spring Boot 연결 설정

1. 의존성 추가

 

xml

<!-- pom.xml -->
<dependencies>
    <!-- MariaDB -->
    <dependency>
        <groupId>org.mariadb.jdbc</groupId>
        <artifactId>mariadb-java-client</artifactId>
        <version>3.3.3</version>
    </dependency>
    
    <!-- 또는 MySQL -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    
    <!-- Connection Pool (HikariCP는 기본 포함) -->
    <!-- 추가 설정만 필요 -->
</dependencies>

Gradle:

 

gradle

dependencies {
    // MariaDB
    implementation 'org.mariadb.jdbc:mariadb-java-client:3.3.3'
    
    // 또는 MySQL
    implementation 'mysql:mysql-connector-java:8.0.33'
    
    // Spring Data JPA
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
}

2. application.yml 설정 (추천)

 

yaml

spring:
  # 프로파일별 설정
  profiles:
    active: local

---
# 로컬 개발 환경
spring:
  config:
    activate:
      on-profile: local
  
  datasource:
    url: jdbc:mariadb://localhost:3306/myapp?useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
    username: myuser
    password: mypass1234!
    driver-class-name: org.mariadb.jdbc.Driver
    
    # HikariCP 설정
    hikari:
      pool-name: MyAppHikariPool
      maximum-pool-size: 10
      minimum-idle: 5
      idle-timeout: 300000
      connection-timeout: 20000
      max-lifetime: 1200000
      auto-commit: true
      connection-test-query: SELECT 1
      
  jpa:
    hibernate:
      ddl-auto: update  # 개발: update, 운영: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MariaDBDialect
        format_sql: true
        use_sql_comments: true
        default_batch_fetch_size: 100
        jdbc:
          batch_size: 25
          batch_versioned_data: true
        order_inserts: true
        order_updates: true
    show-sql: true
    open-in-view: false  # 성능 최적화

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql.BasicBinder: TRACE
    com.zaxxer.hikari: DEBUG

---
# 운영 환경
spring:
  config:
    activate:
      on-profile: prod
      
  datasource:
    url: jdbc:mariadb://${DB_HOST}:${DB_PORT}/${DB_NAME}?useSSL=true&requireSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    driver-class-name: org.mariadb.jdbc.Driver
    
    hikari:
      pool-name: ProdHikariPool
      maximum-pool-size: 30
      minimum-idle: 10
      idle-timeout: 600000
      connection-timeout: 30000
      max-lifetime: 1800000
      leak-detection-threshold: 60000
      
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MariaDBDialect
        jdbc:
          batch_size: 50
    show-sql: false
    
logging:
  level:
    org.hibernate: WARN
    com.zaxxer.hikari: INFO

3. application.properties 설정

 

properties

# MariaDB 설정
spring.datasource.url=jdbc:mariadb://localhost:3306/myapp?createDatabaseIfNotExist=true&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
spring.datasource.username=myuser
spring.datasource.password=mypass1234!
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver

# MySQL 설정 (대체)
#spring.datasource.url=jdbc:mysql://localhost:3306/myapp?createDatabaseIfNotExist=true&useSSL=false&allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Seoul
#spring.datasource.username=myuser
#spring.datasource.password=mypass1234!
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# JPA 설정
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect
#spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

# HikariCP 설정
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000

데이터베이스 설정 최적화

1. 초기 데이터베이스 생성

 

sql

-- init.sql
-- 데이터베이스 생성
CREATE DATABASE IF NOT EXISTS myapp 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 사용자 생성 및 권한 부여
CREATE USER IF NOT EXISTS 'myuser'@'%' IDENTIFIED BY 'mypass1234!';
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'%';
FLUSH PRIVILEGES;

-- 테이블 생성 예시
USE myapp;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 인덱스 생성
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

2. 성능 최적화 설정

 

sql

-- MariaDB/MySQL 튜닝 (my.cnf)
[mysqld]
# 기본 설정
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

# InnoDB 설정
innodb_buffer_pool_size=1G  # RAM의 50-70%
innodb_log_file_size=256M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_file_per_table=1

# 쿼리 캐시 (MySQL 8.0부터 제거됨)
query_cache_type=1  # MariaDB만
query_cache_size=128M

# 연결 설정
max_connections=200
connect_timeout=10
wait_timeout=600
interactive_timeout=600

# 로그 설정
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2

# 기타 최적화
join_buffer_size=256K
sort_buffer_size=256K
read_buffer_size=256K
read_rnd_buffer_size=256K

JPA 설정과 Dialect

1. Hibernate Dialect 선택

 

java

// Hibernate 6.x 기준
// MariaDB
org.hibernate.dialect.MariaDBDialect         // 자동 버전 감지
org.hibernate.dialect.MariaDB103Dialect      // 10.3+
org.hibernate.dialect.MariaDB106Dialect      // 10.6+

// MySQL  
org.hibernate.dialect.MySQLDialect           // 자동 버전 감지
org.hibernate.dialect.MySQL8Dialect          // 8.0+
org.hibernate.dialect.MySQL57Dialect         // 5.7

2. 커스텀 Dialect

 

java

package com.myapp.config;

import org.hibernate.dialect.MariaDBDialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class CustomMariaDBDialect extends MariaDBDialect {
    
    public CustomMariaDBDialect() {
        super();
        
        // 커스텀 함수 등록
        registerFunction("group_concat", 
            new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
        
        registerFunction("json_extract",
            new StandardSQLFunction("json_extract", StandardBasicTypes.STRING));
    }
    
    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }
}

3. Entity 설정 최적화

 

java

@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email"),
    @Index(name = "idx_created_at", columnList = "createdAt")
})
@EntityListeners(AuditingEntityListener.class)
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @Column(nullable = false, unique = true, length = 50)
    private String username;
    
    @Column(nullable = false, unique = true, length = 100)
    private String email;
    
    @Column(nullable = false)
    private String password;
    
    @CreatedDate
    @Column(nullable = false, updatable = false)
    private LocalDateTime createdAt;
    
    @LastModifiedDate
    private LocalDateTime updatedAt;
    
    // JSON 타입 사용 (MariaDB 10.3+)
    @Column(columnDefinition = "JSON")
    @Convert(converter = JsonConverter.class)
    private Map<String, Object> metadata;
    
    // Getters and Setters
}

마이그레이션 전략

1. H2 → MariaDB/MySQL 데이터 마이그레이션

 

java

@Component
@Profile("migration")
public class DataMigrationRunner implements CommandLineRunner {
    
    @Autowired
    @Qualifier("h2DataSource")
    private DataSource h2DataSource;
    
    @Autowired
    @Qualifier("mariadbDataSource")
    private DataSource mariadbDataSource;
    
    @Override
    public void run(String... args) throws Exception {
        // 1. H2에서 데이터 읽기
        List<Map<String, Object>> data = readFromH2();
        
        // 2. MariaDB로 데이터 쓰기
        writeToMariaDB(data);
        
        // 3. 검증
        validateMigration();
    }
    
    private List<Map<String, Object>> readFromH2() {
        JdbcTemplate h2Jdbc = new JdbcTemplate(h2DataSource);
        return h2Jdbc.queryForList("SELECT * FROM users");
    }
    
    private void writeToMariaDB(List<Map<String, Object>> data) {
        JdbcTemplate mariadbJdbc = new JdbcTemplate(mariadbDataSource);
        
        String sql = "INSERT INTO users (username, email, password, created_at) VALUES (?, ?, ?, ?)";
        
        mariadbJdbc.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> row = data.get(i);
                ps.setString(1, (String) row.get("username"));
                ps.setString(2, (String) row.get("email"));
                ps.setString(3, (String) row.get("password"));
                ps.setTimestamp(4, (Timestamp) row.get("created_at"));
            }
            
            @Override
            public int getBatchSize() {
                return data.size();
            }
        });
    }
}

2. Flyway를 이용한 마이그레이션

 

xml

<!-- pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

 

yaml

# application.yml
spring:
  flyway:
    enabled: true
    baseline-on-migrate: true
    locations: classpath:db/migration
    sql-migration-prefix: V
    sql-migration-suffixes: .sql
    validate-on-migrate: true

 

sql

-- src/main/resources/db/migration/V1__init_schema.sql
CREATE TABLE IF NOT EXISTS users (
    id BIGINT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- src/main/resources/db/migration/V2__add_user_roles.sql
CREATE TABLE IF NOT EXISTS roles (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

트러블슈팅

1. 연결 문제

오류: Access denied for user

 

Access denied for user 'root'@'localhost' (using password: YES)

해결방법:

 

bash

# MariaDB/MySQL 접속
sudo mysql -u root

# 권한 재설정
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

# 또는 새 사용자 생성
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass1234!';
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

오류: Unknown database

 

Unknown database 'myapp'

해결방법:

 

properties

# URL에 createDatabaseIfNotExist 추가
spring.datasource.url=jdbc:mariadb://localhost:3306/myapp?createDatabaseIfNotExist=true

오류: Public Key Retrieval

 

Public Key Retrieval is not allowed

해결방법:

 

properties

# 개발 환경에서만 사용
spring.datasource.url=jdbc:mariadb://localhost:3306/myapp?allowPublicKeyRetrieval=true&useSSL=false

2. 문자 인코딩 문제

오류: Incorrect string value

 

Incorrect string value: '\xF0\x9F\x98\x80' for column 'content'

해결방법:

 

sql

-- 데이터베이스 인코딩 변경
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 테이블 인코딩 변경
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 컬럼 인코딩 변경
ALTER TABLE posts MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. 타임존 문제

오류: The server time zone value 'KST' is unrecognized

 

The server time zone value 'KST' is unrecognized

해결방법:

 

properties

# URL에 serverTimezone 추가
spring.datasource.url=jdbc:mariadb://localhost:3306/myapp?serverTimezone=Asia/Seoul

# 또는 UTC 사용
spring.datasource.url=jdbc:mariadb://localhost:3306/myapp?serverTimezone=UTC

4. Dialect 문제

오류: Unable to resolve name MariaDB103Dialect

 

Unable to resolve name [org.hibernate.dialect.MariaDB103Dialect] as strategy [org.hibernate.dialect.Dialect]

해결방법:

 

properties

# Hibernate 6.x용 Dialect 사용
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect

# 또는 MySQL
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect

5. 성능 문제

N+1 쿼리 문제

 

java

// 문제: Lazy Loading으로 인한 N+1
@Entity
public class Post {
    @ManyToOne(fetch = FetchType.LAZY)
    private User author;
    
    @OneToMany(mappedBy = "post", fetch = FetchType.LAZY)
    private List<Comment> comments;
}

// 해결: Fetch Join 사용
@Query("SELECT p FROM Post p LEFT JOIN FETCH p.author LEFT JOIN FETCH p.comments WHERE p.id = :id")
Optional<Post> findByIdWithAuthorAndComments(@Param("id") Long id);

// 또는 @EntityGraph 사용
@EntityGraph(attributePaths = {"author", "comments"})
Optional<Post> findById(Long id);

운영 환경 베스트 프랙티스

1. 환경 변수 사용

 

yaml

spring:
  datasource:
    url: jdbc:mariadb://${DB_HOST:localhost}:${DB_PORT:3306}/${DB_NAME:myapp}
    username: ${DB_USERNAME:myuser}
    password: ${DB_PASSWORD:mypass1234!}

2. 연결 풀 모니터링

 

java

@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    public HikariDataSource dataSource() {
        return DataSourceBuilder.create()
            .type(HikariDataSource.class)
            .build();
    }
    
    @Bean
    public MeterRegistry meterRegistry() {
        return new SimpleMeterRegistry();
    }
    
    @EventListener
    public void handleContextRefresh(ContextRefreshedEvent event) {
        HikariDataSource dataSource = event.getApplicationContext()
            .getBean(HikariDataSource.class);
        
        dataSource.setMetricRegistry(meterRegistry());
    }
}

3. 읽기/쓰기 분리

 

java

@Configuration
public class DataSourceConfig {
    
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.read")
    public DataSource readDataSource() {
        return DataSourceBuilder.create().build();
    }
    
    @Bean
    public DataSource routingDataSource(
            @Qualifier("writeDataSource") DataSource writeDataSource,
            @Qualifier("readDataSource") DataSource readDataSource) {
        
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("write", writeDataSource);
        dataSourceMap.put("read", readDataSource);
        
        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(writeDataSource);
        
        return routingDataSource;
    }
}

4. 백업 전략

 

bash

#!/bin/bash
# backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/mariadb"
DB_NAME="myapp"
DB_USER="backup_user"
DB_PASS="backup_pass"

# 백업 실행
mysqldump -u $DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --add-drop-database \
  --databases $DB_NAME | gzip > "$BACKUP_DIR/backup_$DATE.sql.gz"

# 30일 이상된 백업 삭제
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +30 -delete

5. 모니터링

 

sql

-- 느린 쿼리 확인
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 현재 실행 중인 쿼리
SHOW FULL PROCESSLIST;

-- 테이블 상태 확인
SHOW TABLE STATUS FROM myapp;

-- 인덱스 사용 통계
SELECT * FROM information_schema.index_statistics 
WHERE table_schema = 'myapp' 
ORDER BY rows_read DESC;

마무리

H2에서 MariaDB/MySQL로의 전환은 실제 운영 환경으로 나아가는 중요한 단계입니다. 이 가이드를 통해 안정적이고 확장 가능한 데이터베이스 환경을 구축할 수 있기를 바랍니다.

체크리스트

• ✅ Docker 또는 로컬 DB 설치
• ✅ Spring Boot 의존성 및 설정
• ✅ 문자 인코딩 (utf8mb4) 설정
• ✅ 타임존 설정
• ✅ 연결 풀 최적화
• ✅ 백업 전략 수립
• ✅ 모니터링 도구 설정

다음 단계

• Redis 캐싱 적용
• 읽기 전용 복제본 구성
• 파티셔닝 전략
• 샤딩 구현

---

태그: #SpringBoot #MariaDB #MySQL #Database #JPA #Docker