메모장

회사에서 장비 모니터링 중 Disk 사용량이 높아져서 확인해보니 mysql에서 로그가 너무 많이 쌓이고 있었다.

mysql-relay 로그가 지워지지 않고 계속 쌓이고 있어서 조치를 하였다.

방법은 아래에 정리

MySQL mysql-relay 로그가 많이 남는 경우 조치 방법

1. relay_log_purge 옵션 확인 및 ON으로 변경

mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log_purge';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| relay_log_purge | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL relay_log_purge=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log_purge';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| relay_log_purge | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

2. my.cnf 확인

# 0:OFF, 1:ON
relay_log_purge                 = 1

3. flush logs 명령어로 남아 있는 로그 삭제

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

- Replication이란?

# Server A
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

# Server B
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

# Server A
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

# Server B
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

# Server A
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
     Position: 106
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

# Server B
mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 106
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

ERROR:
No query specified

# Server A
mysql> change master to master_host='192.1.1.2', master_port=3306, master_user='root', master_password='root.123', master_log_file= 'mysql-bin.000001', master_log_pos=106;
Query OK, 0 rows affected (0.00 sec)

# Server B
mysql> change master to master_host='192.1.1.1', master_port=3306, master_user='root', master_password='root.123', master_log_file= 'mysql-bin.000001', master_log_pos=106;
Query OK, 0 rows affected (0.01 sec)

# Server A
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.1.1.2
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: OAMPKB2A-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

# Server B
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.1.1.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: OAMPKB2B-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No 
          Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

# Server A
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

# Server B
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

               Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

# Server A
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.1.1.2
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: OAMPKB2A-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 409
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

# Server B
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.1.1.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: OAMPKB2B-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 409
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

우선 원하는 MySQL 버전을 다운로드 받자

 - Product Version : 5.1.52

 - Operating System : Source Code

 - OS Version : Linux Generic (glibc 2.3)

https://downloads.mysql.com/archives/community

( 현재 회사에서 사용하는 MySQL이 5.1.52 버전이라 해당 버전으로 설치 진행 )

설치 서버는 redhat 6

설치 전에 /etc/my.cnf를 /etc/my.cnt_bk로 바꾸자

( mysql이 기본적으로 /etc/my.cnf를 보게 되어 있어 /usr/local/mysql/data/my.cnf를 수정해도 반영이 안됨 )

받은 패키지를 원하는 위치에 압축을 풀고

OAMPKB2A [ app{52} ~/DB/mysql ] tar xvf mysql-5.1.52-linux-x86_64-glibc23.tar.gz

root 계정에서 /usr/local/mysql 을 link 걸자

# cd /usr/local
# sudo ln -s /home/app/DB/mysql/mysql-5.1.52-linux-x86_64-glibc23 mysql
# ls -l
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 bin
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 etc
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 games
drwxr-xr-x. 3 root root 4096 2019-02-08 05:31 hp
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 include
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 lib
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 lib64
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 libexec
lrwxrwxrwx. 1 root root   52 2019-02-13 09:43 mysql -> /home/app/DB/mysql/mysql-5.1.52-linux-x86_64-glibc23
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 sbin
drwxr-xr-x. 5 root root 4096 2019-02-08 03:28 share
drwxr-xr-x. 2 root root 4096 2011-06-28 21:13 src

이후 /usr/local/mysql 위치에서 ./scripts/mysql_install_db 명령어 수행

# cd /usr/local/mysql
# ./scripts/mysql_install_db

WARNING: The host 'OAMPKB2A' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
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 MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h OAMPKB2A password 'new-password'

Alternatively you can run:
./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 manual for more instructions.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

Please report any problems with the ./bin/mysqlbug script!

mysqld_safe & 로 기동

# /usr/local/mysql/bin/mysqld_safe &
[1] 29641
190213 10:00:38 mysqld_safe Logging to '/usr/local/mysql/data/OAMPKB2A.err'.
190213 10:00:38 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

아래의 명령어로 root 비번을 원하는 걸로 바꾸자

# /usr/local/mysql/bin/mysqladmin -u root password root.123

접속 확인

# mysql -uroot -proot.123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.52-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>