'MySQL, MariaDB'에 해당되는 글 22건

1. brew install mariadb

ellord@Chanui-MacBookPro ~ % brew install mariadb
==> Downloading https://formulae.brew.sh/api/formula.jws.json
==> Downloading https://formulae.brew.sh/api/cask.jws.json
==> Fetching downloads for: mariadb
==> Downloading https://ghcr.io/v2/homebrew/core/mariadb/manifests/11.8.2
########################################################################################## 100.0%
==> Fetching dependencies for mariadb: mecab, mecab-ipadic, msgpack, ca-certificates, openssl@3 and groonga
==> Downloading https://ghcr.io/v2/homebrew/core/mecab/manifests/0.996-4
########################################################################################## 100.0%
==> Fetching mecab
==> Downloading https://ghcr.io/v2/homebrew/core/mecab/blobs/sha256:d91a5e1bd7fdea15cfc0469705b33
########################################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/mecab-ipadic/manifests/2.7.0-20070801-1
########################################################################################## 100.0%
==> Fetching mecab-ipadic
==> Downloading https://ghcr.io/v2/homebrew/core/mecab-ipadic/blobs/sha256:12212f7bc769338a7747d2
########################################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/msgpack/manifests/6.1.0
########################################################################################## 100.0%
==> Fetching msgpack
==> Downloading https://ghcr.io/v2/homebrew/core/msgpack/blobs/sha256:f8a10c653f0e071f725866c790e
########################################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/ca-certificates/manifests/2025-07-15
########################################################################################## 100.0%
==> Fetching ca-certificates
==> Downloading https://ghcr.io/v2/homebrew/core/ca-certificates/blobs/sha256:84e089e758e75d61228
########################################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/openssl/3/manifests/3.5.1
########################################################################################## 100.0%
==> Fetching openssl@3
==> Downloading https://ghcr.io/v2/homebrew/core/openssl/3/blobs/sha256:752c0242eaad67ab63c47448f
########################################################################################## 100.0%
==> Downloading https://ghcr.io/v2/homebrew/core/groonga/manifests/15.1.3
########################################################################################## 100.0%
==> Fetching groonga
==> Downloading https://ghcr.io/v2/homebrew/core/groonga/blobs/sha256:346e2596561348e7aa5bf2d7f3a
########################################################################################## 100.0%
==> Fetching mariadb
==> Downloading https://ghcr.io/v2/homebrew/core/mariadb/blobs/sha256:fede2f4bad07d091fc504197718
########################################################################################## 100.0%
==> Installing dependencies for mariadb: mecab, mecab-ipadic, msgpack, ca-certificates, openssl@3 and groonga
==> Installing mariadb dependency: mecab
==> Downloading https://ghcr.io/v2/homebrew/core/mecab/manifests/0.996-4
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/c3b1ce288999bf20be4e6f931edd01876367bd1046d2d7979b68eb3e5f2315f9--mecab-0.996-4.bottle_manifest.json
==> Pouring mecab--0.996.sonoma.bottle.4.tar.gz
🍺  /usr/local/Cellar/mecab/0.996: 19 files, 3.9MB
==> Installing mariadb dependency: mecab-ipadic
==> Downloading https://ghcr.io/v2/homebrew/core/mecab-ipadic/manifests/2.7.0-20070801-1
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/daa8ae338ff2bd6c9d5e70d7b50f6571f1dd831848ea35ad632cd3ceb14917f6--mecab-ipadic-2.7.0-20070801-1.bottle_manifest.json
==> Pouring mecab-ipadic--2.7.0-20070801.sonoma.bottle.1.tar.gz
🍺  /usr/local/Cellar/mecab-ipadic/2.7.0-20070801: 15 files, 50.6MB
==> Installing mariadb dependency: msgpack
==> Downloading https://ghcr.io/v2/homebrew/core/msgpack/manifests/6.1.0
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/64deb06ebbeaaf491c089ce032ab47d3a2fd7017a0d42f92620a8feed75d9e28--msgpack-6.1.0.bottle_manifest.json
==> Pouring msgpack--6.1.0.sonoma.bottle.tar.gz
🍺  /usr/local/Cellar/msgpack/6.1.0: 39 files, 183.8KB
==> Installing mariadb dependency: ca-certificates
==> Downloading https://ghcr.io/v2/homebrew/core/ca-certificates/manifests/2025-07-15
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/1b8ec93adfe410583ff46788783b030707eec1898c7ded356e25694e4c29aa32--ca-certificates-2025-07-15.bottle_manifest.json
==> Pouring ca-certificates--2025-07-15.all.bottle.tar.gz
==> Regenerating CA certificate bundle from keychain, this may take a while...
🍺  /usr/local/Cellar/ca-certificates/2025-07-15: 4 files, 225.8KB
==> Installing mariadb dependency: openssl@3
==> Downloading https://ghcr.io/v2/homebrew/core/openssl/3/manifests/3.5.1
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/9d8c8da104539ae29f198f366fe5794ac377d96a92458ae62557e3f83b03a351--openssl@3-3.5.1.bottle_manifest.json
==> Pouring openssl@3--3.5.1.sequoia.bottle.tar.gz
🍺  /usr/local/Cellar/openssl@3/3.5.1: 7,563 files, 35.9MB
==> Installing mariadb dependency: groonga
==> Downloading https://ghcr.io/v2/homebrew/core/groonga/manifests/15.1.3
Already downloaded: /Users/ellord/Library/Caches/Homebrew/downloads/e320072797b1d2ea631c2a7f2d0675e061ec80105edc6cb031072ac9a236c709--groonga-15.1.3.bottle_manifest.json
==> Pouring groonga--15.1.3.sonoma.bottle.tar.gz
🍺  /usr/local/Cellar/groonga/15.1.3: 907 files, 94.4MB
==> Installing mariadb
==> Pouring mariadb--11.8.2.sonoma.bottle.tar.gz
==> /usr/local/Cellar/mariadb/11.8.2/bin/mysql_install_db --verbose --user=ellord --basedir=/usr/
==> Caveats
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mariadbd-safe --datadir\=/usr/local/var/mysql
==> Summary
🍺  /usr/local/Cellar/mariadb/11.8.2: 966 files, 235.2MB
==> Running `brew cleanup mariadb`...
Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP.
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`).
==> Caveats
==> mariadb
A "/etc/my.cnf" from another install may interfere with a Homebrew-built
server starting up correctly.

MySQL is configured to only allow connections from localhost by default

To start mariadb now and restart at login:
  brew services start mariadb
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mariadb/bin/mariadbd-safe --datadir\=/usr/local/var/mysql

2. mariadb 시작

ellord@Chanui-MacBookPro ~ % brew services start mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

3. maraidb 접속

ellord@Chanui-MacBookPro ~ % mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 11.8.2-MariaDB Homebrew

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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.001 sec)

4. mysql DB선택

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

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

5. root 비번 설정

MariaDB [mysql]> select host,user,password from user;
+-------------------------+-------------+----------+
| Host                    | User        | Password |
+-------------------------+-------------+----------+
| localhost               | mariadb.sys |          |
| localhost               | root        | invalid  |
| localhost               | ellord      | invalid  |
|                         | PUBLIC      |          |
| localhost               |             |          |
| chanui-macbookpro.local |             |          |
+-------------------------+-------------+----------+
6 rows in set (0.001 sec)

MariaDB [mysql]> set password for 'root'@'localhost'=password('didcksals00A%*%A');
Query OK, 0 rows affected (0.009 sec)

6. 설정 값 적용

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> select host,user,password from user;
+-------------------------+-------------+-------------------------------------------+
| Host                    | User        | Password                                  |
+-------------------------+-------------+-------------------------------------------+
| localhost               | mariadb.sys |                                           |
| localhost               | root        | *AFB14E2338F689442E9555CDCB6E0EEFD1896B04 |
| localhost               | ellord      | invalid                                   |
|                         | PUBLIC      |                                           |
| localhost               |             |                                           |
| chanui-macbookpro.local |             |                                           |
+-------------------------+-------------+-------------------------------------------+
6 rows in set (0.002 sec)

7. 새로운 비밀번호로 접속

ellord@Chanui-MacBookPro ~ % mysql -u root -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 11.8.2-MariaDB Homebrew

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 [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.001 sec)

1. event_scheduler 설정 확인

MariaDB [mysql]> show variables like'%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.001 sec)

2. event_scheduler 를 ON 으로 설정

MariaDB [mysql]> set global event_scheduler = ON;
ERROR 1408 (HY000): Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

에러가 난다.

3. Mariadb 서버를 빠져나와  mysql_upgrade 를 실행

mysql_upgrade 파일은 /usr/bin 에 있다.

[root@localhost my.cnf.d]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
[root@localhost my.cnf.d]# cd /usr/bin
[root@localhost bin]# pwd
/usr/bin
[root@localhost bin]# ls mysql* -ls
 4308 -rwxr-xr-x. 1 root root  4409824 Aug 10  2022 mysql
  112 -rwxr-xr-x. 1 root root   111950 Aug 10  2022 mysqlaccess
 3828 -rwxr-xr-x. 1 root root  3917696 Aug 10  2022 mysqladmin
 4076 -rwxr-xr-x. 1 root root  4171992 Aug 10  2022 mysqlbinlog
 3820 -rwxr-xr-x. 1 root root  3910760 Aug 10  2022 mysqlcheck
    8 -rwxr-xr-x. 1 root root     4217 Aug 10  2022 mysql_convert_table_format
   28 -rwxr-xr-x. 1 root root    27326 Aug 10  2022 mysqld_multi
   32 -rwxr-xr-x. 1 root root    30778 Aug 10  2022 mysqld_safe
 3464 -rwxr-xr-x. 1 root root  3543896 Aug 10  2022 mysqld_safe_helper
 3920 -rwxr-xr-x. 1 root root  4010816 Aug 10  2022 mysqldump
   12 -rwxr-xr-x. 1 root root     8242 Aug 10  2022 mysqldumpslow
20876 -rwxr-xr-x. 1 root root 21373048 Aug 10  2022 mysql_embedded
    4 -rwxr-xr-x. 1 root root     3290 Aug 10  2022 mysql_find_rows
    4 -rwxr-xr-x. 1 root root     1250 Aug 10  2022 mysql_fix_extensions
   36 -rwxr-xr-x. 1 root root    34963 Aug 10  2022 mysqlhotcopy
 3816 -rwxr-xr-x. 1 root root  3905592 Aug 10  2022 mysqlimport
   20 -rwxr-xr-x. 1 root root    20092 Aug 10  2022 mysql_install_db
 3504 -rwxr-xr-x. 1 root root  3587144 Aug 10  2022 mysql_plugin
   16 -rwxr-xr-x. 1 root root    12527 Aug 10  2022 mysql_secure_installation
   20 -rwxr-xr-x. 1 root root    17542 Aug 10  2022 mysql_setpermission
 3812 -rwxr-xr-x. 1 root root  3899944 Aug 10  2022 mysqlshow
 3832 -rwxr-xr-x. 1 root root  3923520 Aug 10  2022 mysqlslap
 3500 -rwxr-xr-x. 1 root root  3582424 Aug 10  2022 mysql_tzinfo_to_sql
 3596 -rwxr-xr-x. 1 root root  3678784 Aug 10  2022 mysql_upgrade
 3492 -rwxr-xr-x. 1 root root  3573856 Aug 10  2022 mysql_waitpid

4. mysql_upgrade 를 수행해 본다.

[root@localhost bin]# ./mysql_upgrade 
This installation of MariaDB is already upgraded to 10.11.8-MariaDB.
There is no need to run mysql_upgrade again for 10.11.8-MariaDB.
You can use --force if you still want to run mysql_upgrade

이미 업그레이드가 되어 있어서 할 필요가 없다 하므로 강제로 다시 시도한다.

[root@localhost bin]# ./mysql_upgrade -f
Phase 1/8: 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.global_priv                                  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.transaction_registry                         OK
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
Phase 4/8: Fixing views

...

중략

...

performance_schema
sys
sys.sys_config                                     OK
test
Phase 7/8: uninstalling plugins
Phase 8/8: Running 'FLUSH PRIVILEGES'
OK

5. my.cnf 파일에도 추가한다.

[root@localhost bin]# vi /etc/my.cnf.d/server.cnf

[mysqld]

event_scheduler = on

6. Mariadb 서버를 재시작한다.

[root@localhost bin]# systemctl restart mariadb

7. Mariadb 서버에 접속하여 event_scheduler 설정상태를 확인해 본다.

MariaDB [mysql]> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.001 sec)

MariaDB [mysql]>

event_scheduler 를 ON 으로 세팅한다.

MariaDB [mysql]> set global event_scheduler = ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> show variables like '%event_scheduler%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.002 sec)

MariaDB [mysql]>

1. Yum repo 등록

# cd /etc/yum.repos.d
# vi MariaDB.repo

# MariaDB 10.7 CentOS repository list - created 2022-04-16 21:02 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
baseurl = https://mirror.yongbok.net/mariadb/yum/10.7/centos7-amd64
gpgkey=https://mirror.yongbok.net/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

2. 설치

# yum install MariaDB-server MariaDB-client

3. 서버시작

# systemctl start mariadb

4. root password 설정

# mysqladmin -u root password

5. 시작프로그램에 등록

systemctl enable mariadb

6. 버전확인

# mysql -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.7.3-MariaDB MariaDB Server

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

Reading history-file /root/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

끝.

MariaDB [mysql]> stop slave; 

한 뒤,

MariaDB [mysql]> SET GLOBAL sql_slave_skip_counter = 1;

하고

MariaDB [mysql]> start slave;

한다.

에러가 계속 발생하면,

위 단계를 반복한다.

start slave 시 아래와 같은 메시지가 나온다.

MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> MariaDB [mysql]> start slave;

ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 

MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 

MariaDB [mysql]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
MariaDB [mysql]> 

MariaDB [mysql]> start slave;

ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log

MariaDB [mysql]> 

해결방법 : slave를 reset한 후 start 한다.

MariaDB [mysql]> reset slave;

Query OK, 0 rows affected (0.014 sec)

MariaDB [mysql]> start slave;

Query OK, 0 rows affected (0.015 sec)

replication을 유지하면서 mysql-bin 로그 삭제

[마스터에서 수행]

1. 로그 목록 확인 [마스터에서 수행]

MariaDB [mysql]> show binary logs;

+------------------+------------+

| Log_name         | File_size  |

+------------------+------------+

| mysql-bin.000001 | 1073742334 |

| mysql-bin.000002 | 1073742066 |

| mysql-bin.000003 | 1073742021 |

| mysql-bin.000004 | 1073742090 |

| mysql-bin.000005 | 1074091311 |

| mysql-bin.000006 | 1074186164 |

| mysql-bin.000007 | 1074181534 |

| mysql-bin.000008 | 1073741968 |

| mysql-bin.000009 | 1073744583 |

| mysql-bin.000010 | 1073741939 |

| mysql-bin.000011 | 1000547863 |

+------------------+------------+

11 rows in set (0.00 sec)

1. 로그전체 삭제 --> replication 이 깨지므로 slave에서 replication을 다시 시작해줘야 한다.

MariaDB [mysql]>RESET MASTER;

2. replication유지하며 과거 로그 삭제

아래 명령어로 현재 slave가 사용중인 로그 확인[slave에서 수행]

MariaDB [mysql]> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.1

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000011

          Read_Master_Log_Pos: 1002605164

               Relay_Log_File: mysql-relay-bin.000034

                Relay_Log_Pos: 185226372

        Relay_Master_Log_File: mysql-bin.000011

             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: 1002605164

              Relay_Log_Space: 1002606071

              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: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

                   Using_Gtid: No

                  Gtid_IO_Pos: 

      Replicate_Do_Domain_Ids: 

  Replicate_Ignore_Domain_Ids: 

                Parallel_Mode: conservative

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [mysql]> 

현재 slave가 사용중인 master 의 로그 파일은 mysql-bin.000011 이다.

그러므로 이것 이전 로그파일은  삭제해도 replication이 깨지지 않는다.

로그를 삭제하기 위해 PURGE BINARY LOGS 명령어를 사용한다.[마스터에서 수행]

첫번째 방법 :  PURGE BINARY LOGS TO 'mysql-bin.00003';

mysql-bin.00003 보다 낮은 번호의 로그들이 한꺼번에 삭제된다.

(mysql-bin.000001, mysql-bin.000002이 삭제됨)

두번째 방법 : PURGE BINARY LOGS BEFORE '2019-01-15 00:00:00';

2019년 1월 15일 0시 이전에 생성된 로그 파일들이 모두 삭제된다.

끝.

1. A의 모든 DB를 B로 복제

A : 

mysqldump -u root -p --all-databases > all.sql

B : 

mysql -u root -p < all.sql

2. 마스터와 슬레이브 설정

A. 마스터 설정

설정파일  : /etc/my.cnf.d/server.cnf

서버아이디는 1로 설정

로그파일은 mysql-bin.000001 과 같은 형태로 생성됨

[mysqld]

server_id = 1

log-bin=mysql-bin

expire_logs_days=7

replication 사용자 등록

마스터 호스트 : 192.168.0.1

사용자 : repl

비밀번호 : 12345

MariaDB [mysql]> grant select, reload, lock tables, replication slave, replication client on *.* to 'repl@'192.168.0.1' identified by '12345';ant select, reload, lock tables, replication slave, replication client on *.* to 'id'@'slave ip address' identified by 'password';

MariaDB [mysql]> flush privileges;

B. 슬레이브 설정

설정파일 : /etc/my.cnf.d/server.cnf

서버아이디 : 2 로 설정

로그파일은  mysql-relay-bin.000001 과 같은 형식으로 생성됨

[mysqld]

server_id = 2

relay_log = mysql-relay-bin

3. replication

마스터 정보 확인

A : 

MariaDB [mysql]> show master status \G;

*************************** 1. row ***************************

            File: mysql-bin.000001

        Position: 104060378

    Binlog_Do_DB: 

Binlog_Ignore_DB: 

1 row in set (0.00 sec)

ERROR: No query specified

마스터의 로그파일 : mysql-bin.000001

로그 포지션 : 104060378

B : 설정

MariaDB [mysql]> stop slave;

MariaDB [mysql]> change master to 

master_host = '192.168.0.1',

master_user = 'repl',

master_password = '12345',

master_log_file = 'mysql-bin.000001',

master_log_pos = 104060378

;

MariaDB [mysql]> start slave;

슬레이브 상태 확인

MariaDB [mysql]> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.1

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 662570599

               Relay_Log_File: mysql-relay-bin.000001

                Relay_Log_Pos: 42800785

        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: 43002550

              Relay_Log_Space: 662370967

              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: 416430

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

                   Using_Gtid: No

                  Gtid_IO_Pos: 

      Replicate_Do_Domain_Ids: 

  Replicate_Ignore_Domain_Ids: 

                Parallel_Mode: conservative

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: updating

1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [mysql]> 

mysql 디비 디렉토리에 가 보면,

아래와 같이 로그파일이 생성되어 있다.

-rw-rw----. 1 mysql mysql  47888824  6월 26 10:03 mysql-bin.000001

-rw-rw----. 1 mysql mysql        19  6월  7 11:22 mysql-bin.index

-rw-rw----. 1 mysql mysql       309  6월 21 09:02 mysql-relay-bin.000001

-rw-rw----. 1 mysql mysql       150  6월 26 10:03 mysql-relay-bin.index

끝.

1. nginx 설치

[root@localhost yum.repos.d]# vi /etc/yum.repos.d/nginx.repo 

[nginx]

name=nginx repo

baseurl=http://nginx.org/packages/centos/7/$basearch/

gpgcheck=0

enabled=1

~                                                                                                                                         

[root@localhost yum.repos.d]# yum install nginx

Loaded plugins: fastestmirror, replace

http://nginx.org/packages/OS/OSRELEASE/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found

Trying other mirror.

To address this issue please refer to the below knowledge base article 

https://access.redhat.com/articles/1320623

If above article doesn't help to resolve this issue please create a bug on https://bugs.centos.org/

 One of the configured repositories failed (nginx repo),

 and yum doesn't have enough cached data to continue. At this point the only

 safe thing yum can do is fail. There are a few ways to work "fix" this:

     1. Contact the upstream for the repository and get them to fix the problem.

     2. Reconfigure the baseurl/etc. for the repository, to point to a working

        upstream. This is most often useful if you are using a newer

        distribution release than is supported by the repository (and the

        packages for the previous distribution release still work).

     3. Run the command with the repository temporarily disabled

            yum --disablerepo=nginx ...

     4. Disable the repository permanently, so yum won't use it by default. Yum

        will then just ignore the repository until you permanently enable it

        again or use --enablerepo for temporary usage:

            yum-config-manager --disable nginx

        or

            subscription-manager repos --disable=nginx

     5. Configure the failing repository to be skipped, if it is unavailable.

        Note that yum will try to contact the repo. when it runs most commands,

        so will have to try and fail each time (and thus. yum will be be much

        slower). If it is a very temporary problem though, this is often a nice

        compromise:

            yum-config-manager --save --setopt=nginx.skip_if_unavailable=true

failure: repodata/repomd.xml from nginx: [Errno 256] No more mirrors to try.

http://nginx.org/packages/OS/OSRELEASE/x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found

[root@localhost yum.repos.d]# vi /etc/yum.repos.d/nginx.repo

[root@localhost yum.repos.d]# yum install nginx

Loaded plugins: fastestmirror, replace

base                                                                                                               | 3.6 kB  00:00:00     

epel/x86_64/metalink                                                                                               | 6.2 kB  00:00:00     

extras                                                                                                             | 3.4 kB  00:00:00     

nginx                                                                                                              | 2.9 kB  00:00:00     

updates                                                                                                            | 3.4 kB  00:00:00     

webtatic                                                                                                           | 3.6 kB  00:00:00     

nginx/x86_64/primary_db                                                                                            |  18 kB  00:00:00     

Loading mirror speeds from cached hostfile

 * base: mirror.oasis.onnetcorp.com

 * epel: mirror.premi.st

 * extras: mirror.oasis.onnetcorp.com

 * updates: mirror.oasis.onnetcorp.com

 * webtatic: sp.repo.webtatic.com

Resolving Dependencies

--> Running transaction check

---> Package nginx.x86_64 1:1.10.2-1.el7.ngx will be installed

--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================================

 Package                      Arch                          Version                                    Repository                    Size

==========================================================================================================================================

Installing:

 nginx                        x86_64                        1:1.10.2-1.el7.ngx                         nginx                        643 k

Transaction Summary

==========================================================================================================================================

Install  1 Package

Total download size: 643 k

Installed size: 2.2 M

Is this ok [y/d/N]: y

Downloading packages:

nginx-1.10.2-1.el7.ngx.x86_64.rpm                                                                                  | 643 kB  00:00:02     

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

  Installing : 1:nginx-1.10.2-1.el7.ngx.x86_64                                                                                        1/1 

----------------------------------------------------------------------

Thanks for using nginx!

Please find the official documentation for nginx here:

* http://nginx.org/en/docs/

Commercial subscriptions for nginx are available on:

* http://nginx.com/products/

----------------------------------------------------------------------

  Verifying  : 1:nginx-1.10.2-1.el7.ngx.x86_64                                                                                        1/1 

Installed:

  nginx.x86_64 1:1.10.2-1.el7.ngx                                                                                                         

Complete!

[root@localhost yum.repos.d]# service nginx start

Redirecting to /bin/systemctl start  nginx.service

2. MariaDb 10 설치

[root@localhost yum.repos.d]# vi /etc/yum.repo.d/MariaDB.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/rhel7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

[root@localhost yum.repos.d]# yum install -y mariadb mariadb-server

[root@localhost etc]# systemctl enable mariadb

[root@localhost etc]# systemctl start mariadb

설정파일 변경

2.1 /etc/my.cnf.d/mysql-clients.cnf

[mysql] 아래에

default-character-set=utf8 추가

[root@localhost etc]# vi /etc/my.cnf.d/mysql-clients.cnf 

[mysql]

default-character-set=utf8

2. /etc/my.cnf.d/server.cnf

[mysqld] 아래에 아래와 같이 추가하고

[mysqld_safe] 를 만들고 아래와 같이 추가

[root@localhost etc]# vi /etc/my.cnf.d/server.cnf 

[mysqld]

max_connections = 512

wait_timeout = 30

character-set-server = utf8

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

3. 기본 보안 설정 

root 비밀번호 설정 등

Enter current password for root (enter for none): 그냥엔터(아직 root 비번을 설정하지 않았으므로) 

새로운 루트비밀번호 설정

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!

?>

아래와 같이 나온다.

SELECT 
concat(table_schema,'.',table_name), 
concat(round(table_rows/1000000,2),'M') rows, 
concat(round(data_length/(1024*1024*1024),2),'G') DATA, 
concat(round(index_length/(1024*1024*1024),2),'G') idx, 
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size, 
round(index_length/data_length,2) idxfrac 
FROM information_schema.TABLES
where table_schema='데이터베이스명'
order by data desc;

1. 디비툴이 있다면 쿼리편집기에서 아래 명령어를 쿼리날린다.

  SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

  FROM INFORMATION_SCHEMA.TABLES 

  WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

  AND TABLE_SCHEMA = '데이터베이스명';

테이블이 많다면 아래와 같이 결과가 나온다.

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

DROP TABLE 데이터베이스명.prefix_테이블명xx ;

검색결과를 복사해서 다시한번 쿼리 편집기에 넣어 쿼리를 날린다.

2. 쿼리결과를 파일로 저장해야할 경우

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

FROM INFORMATION_SCHEMA.TABLES 

WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

AND TABLE_SCHEMA = '데이터베이스명' 

INTO OUTFILE '/tmp/저장할파일명.sql';

아래와 같이,

MariaDB [mysql]> SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') 

    -> FROM INFORMATION_SCHEMA.TABLES 

    -> WHERE TABLE_NAME LIKE 'prefix_테이블명%' 

    -> AND TABLE_SCHEMA = '테이터베이스명' 

    -> INTO OUTFILE '/tmp/저장할파일명.sql';

그 후,

MariaDB [mysql]> source /tmp/저장할파일명.sql

삭제가 될 것이다.