Docker compose Mariadb (Master-Slave) 로 구성 중 Last_IO_Errno is = 1236 에러

1. Error 발생

Docker-compose로 띄운 Mariadb 두 서버를 Master-slave 로 자동으로 구성하는 .sh 파일 작성 중 "show slave status\G" 쿼리 결과값에서 오류가 검출됐다.

• 스크립트 파일 구성 전에 직접 하나하나씩 쿼리를 날리며 확인했을 때는 아래와 같이 오류가 없었다

Last_Errno = 0
Last_IO_Errno = 0

---

2. Docker-compose

Docker-compose.yml

• Docker-compose.yml

version: "3.7"

services:
  db_master:
    image: mariadb:10.7.1
    container_name: db_master
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mariadb_root_password
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/config/:/etc/mysql/conf.d
      - ./master/mysql-init-files/:/docker-entrypoint-initdb.d/
    ports:
      - "33306:3306"
    secrets:
      - mariadb_root_password

  db_slave:
    image: mariadb:10.7.1
    container_name: db_slave
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mariadb_root_password
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/config/:/etc/mysql/conf.d
      - ./slave/mysql-init-files/:/docker-entrypoint-initdb.d/
    ports:
      - "43306:3306"
    secrets:
      - mariadb_root_password
    depends_on:
      - db_master
    links:
      - db_master

secrets:
  mariadb_root_password:
    file: ./mariadb_root_password.txt

---

3. Shell-Script File

• msdb.sh

#!/bin/bash

docker-compose -f ./docker-compose.yml up -d

sleep 7

docker exec db_master mysql -u root -proot -e "CREATE DATABASE testdb;"
echo "create database Done."

sleep 1

docker exec db_master mysql -u root -proot -e "grant all privileges on testdb.* to user@'%' identified by 'user';"
echo "grant privileges to slave user Done."

sleep 1

docker exec db_master mysql -u root -proot -e "grant replication slave on *.* to 'user'@'%';"
echo "grant replication slave done"

sleep 1

docker exec db_master mysql -u root -proot -e "flush privileges;"
echo "flush privileges done"

sleep 1
docker exec db_master mysql -u root -proot -e "use testdb; create table testtable ( no int(8), primary key (no) ); "
echo "create table done"

sleep 1

#docker exec db_master mysql -u root -proot -e "exit"
#echo "exit mysql(db_master)"
#docker exec db_master mysql -u root -proot -e "exit"
#echo "exit container(slave)"

sleep 1

docker exec db_master mysqldump -uroot -proot testdb > dump.sql
echo "send mysql dump to local "

sleep 2

docker cp dump.sql db_slave:.
echo "copy dump.sql to db-slave"

sleep 2

docker exec db_slave mysql -u root -proot -e "CREATE DATABASE testdb;"
echo "start sync done"

sleep 1

docker exec db_slave mysql -u root -proot -e "exit"
echo "exit mysql(db_slave)"

sleep 1

docker exec db_slave mysql -u root -proot -e "exit"
echo "exit container(slave)"

sleep 1

docker exec db_slave mysql -u root -proot testdb < dump.sql
echo "slave get dump.sql"
echo "before change master to master query"

sleep 2

docker exec db_master mysql -u root -proot -e "show master status\G" > master_status.txt
echo "master_status file save finished"

sleep 2

search_string="mysql-bin"
file_name="master_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
    master_log_File=$(grep "$search_string" "$file_name" | awk '{print $2}')
    echo "success!"
    echo "master_log_File is = " $master_log_File
else
    echo "문자열을 찾을 수 없삼"
fi

search_string="Position"
file_name="master_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
    master_log_Position=$(grep "$search_string" "$file_name" | awk '{print $2}')
    echo "success!"
    echo "master_log_Position is = " $master_log_Position
else
    echo "문자열을 찾을 수 없삼"
fi

sleep 2

docker exec db_slave mysql -u root -proot -e "CHANGE MASTER TO MASTER_HOST='db-master',
MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='${master_log_File}', MASTER_LOG_POS='${master_log_Position}';"
echo "Finished connected"

sleep 2

docker exec db_slave mysql -u root -proot -e "start slave;"
echo "success!"
echo "start slave!"

sleep 2

docker exec db_slave mysql -u root -proot -e "show slave status\G" > slave_status.txt

sleep 2

search_string="Last_Errno"
file_name="slave_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
    last_errno=$(grep "$search_string" "$file_name" | awk '{print $2}')
    echo "success!"
    echo "Last_Errno is = " $last_errno
    echo "0 이면 master_slave 성공입니다."
else
    echo "Failed Maset-slave"
fi

search_string="Last_IO_Errno"
file_name="slave_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
    last_io_errno=$(grep "$search_string" "$file_name" | awk '{print $2}')
    echo "success!"
    echo "Last_IO_Errno is = " $last_io_errno
    echo "0 이면 master_slave 성공입니다."
else
    echo "Failed Maset-slave"
fi

---

4. Error 케이스 생각

Error 1

• 자꾸 syntx 오류가 발생했다

• 혹시 아래의 구문에서 정수인 Position 값이 문자열로 인식되어서 생긴 문제일까?

  • MASTER_LOG_POS='${master_log_Position}' 이것이 문자열로 변수에 입력돼서 그런 것인가?

search_string="Position"
file_name="master_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
    master_log_Position=$(grep "$search_string" "$file_name" | awk '{print $2}')
    echo "success!"
    echo "master_log_Position is = " $master_log_Position ### 요 부분?
else
    echo "문자열을 찾을 수 없삼"
fi
************

docker exec db_slave mysql -u root -proot -e "CHANGE MASTER TO MASTER_HOST='db-master',
MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='${master_log_File}', MASTER_LOG_POS='${master_log_Position}';"
echo "Finished connected"

• 정수형으로 변경을 하는 것이 중요한게 아니었다.

docker exec db_slave mysql -u root -proot -e "CHANGE MASTER TO MASTER_HOST='${ip_address}',
MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='${master_log_File}', MASTER_LOG_POS='${master_log_Position}';"

• 이 구문에서 MASTER_LOG_POS='${master_log_Position}' 의 '' 부분을 없애줘야 하는 것이였다!

수정된 구문

docker exec db_slave mysql -u root -proot -e "CHANGE MASTER TO MASTER_HOST='${ip_address}',
MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='${master_log_File}', MASTER_LOG_POS=${master_log_Position};"

---

Error 2

• 또한 "CHANGE MASTER TO MASTER_HOST='db-master', 이 구문에서도 에러가 발생했다

• 분명 도커 컴포즈 파일에 --links 를 db_master 와 연결해줬는데 오류가 발생했다
  그래서 아예 컨테이너 IP를 따와서 MASTER_HOST 에 값을 전달하기로 했다

docker exec db_master ip addr show eth0 > ip_output.txt
ip_address=$(cat output.txt | awk '/inet / {print $2}' | cut -f1 -d'/')
echo $ip_address

결과
172.29.0.2

---

• Master-Slave 구성 완료!!

---

5. 리눅스 로그 경로

• 루트권한 접속
• cat /var/log/messages

messages 파일이 보이지 않는다면

• vi /etc/rsyslog.d/50-default.conf 편집하여 주석 해제

# Some "catch-all" log files.
#
#*.=debug;\
#       auth,authpriv.none;\
#       news.none;mail.none     -/var/log/debug
*.=info;*.=notice;*.=warn;\
        auth,authpriv.none;\
        cron,daemon.none;\
        mail,news.none          -/var/log/messages

• 데몬 restart

service rsyslog restart

root@user1:/var/log# ls
alternatives.log  cloud-init.log         faillog    lastlog               ubuntu-advantage-timer.log
apt               cloud-init-output.log  installer  messages              unattended-upgrades
auth.log          dist-upgrade           journal    private               vmware-vmsvc-root.log
bootstrap.log     dmesg                  kern.log   syslog                vmware-vmtoolsd-root.log
btmp              dpkg.log               landscape  ubuntu-advantage.log  wtmp

---