HoguInside & ㅁ ㅇ ㄷ nail

[ database 에러 ]

[root@localhost01:33:26:~]# tail -f /usr/local/mysql/data/localhost.localdomain.err
2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Fetch of persistent statistics requested for table "SY_NOTI"."DMND_ORGNZ2" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2018-10-26 13:24:11 7f92494d6700 InnoDB: Error: Fetch of persistent statistics requested for table "SY_NOTI"."PROD_CLAS_CODE" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

2018-10-26 13:24:14 7f924af31700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2018-10-26 13:24:14 7f924af31700 InnoDB: Recalculation of persistent statistics requested for table "SY_SESSION"."SPRING_SESSION" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

[ 해결 ]

i. 위 언급한 mysql database에서 5개 테이블 제거

[root@localhost01:25:56:/usr/local/mysql/data]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52789
Server version: 5.6.10-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

mysql> drop table innodb_index_stats ; drop table innodb_table_stats; drop table slave_master_info; drop table slave_relay_log_info; drop table slave_worker_info;
ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats'
ERROR 1051 (42S02): Unknown table 'mysql.innodb_table_stats'
ERROR 1051 (42S02): Unknown table 'mysql.slave_master_info'
ERROR 1051 (42S02): Unknown table 'mysql.slave_relay_log_info'
ERROR 1051 (42S02): Unknown table 'mysql.slave_worker_info'
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

mysql> quit

* table drop 시 에러메시지는  위에서 force recorvery 로 강제 복구시 정도가 제거 된듯;;
  확인 결과 정상적으로 테이블 제거

ii. ****.rbd 파일 제거 및 mysql 재구동

[root@localhost01:27:21:/usr/local/mysql/data]# /etc/init.d/mysqld stop
Shutting down MySQL....                                    [  OK  ]


[root@localhost01:27:21:/usr/local/mysql/data]#cd mysql
[root@localhost01:27:36:/usr/local/mysql/data/mysql]# rm -rf innodb_index_stats.ibd innodb_table_stats.ibd slave_master_info.ibd slave_relay_log_info.ibd  slave_worker_info.ibd

[root@localhost01:28:20:/usr/local/mysql/data/mysql]# /etc/init.d/mysqld start
Starting MySQL...                                          [  OK  ]

[root@localhost01:28:29:/usr/local/mysql/data/mysql]# /usr/local/mysql/bin/mysql_upgrade --force -uroot -p
Enter password: 
Looking for 'mysql' as: /usr/local/mysql/bin/mysql
Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' 
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock' 
Warning: Using a password on the command line interface can be insecure.
SY_ANLS.AUTO_ANLS                                  OK
SY_ANLS.AUTO_ANLS_GUGAN                            OK
SY_ANLS.AUTO_ANLS_LOG                              OK
SY_ANLS.AUTO_ANLS_PRF                              OK
SY_ANLS.AUTO_ANLS_RESULT                           OK
SY_ANLS.AUTO_ANLS_STAT_CD                          OK
SY_ANLS.QUARTILE                                   OK
SY_APP.CUST_APP                                    OK
SY_APP.CUST_APP_BIZ                                OK
SY_APP.SY_APP_MASTER                               OK
SY_BILCLT.BILCLT                                   OK
SY_BILCLT.BILCLT_AGR                               OK
SY_BILCLT.BILCLT_EXCCLC_RATE                       OK
SY_BILCLT.BILCLT_HIST                              OK
SY_BILCLT.BILCLT_HIST_BACK                         OK
SY_BILCLT.RCPTMN                                   OK
SY_BILCLT.RCPTMN_HIST                              OK
SY_BILCLT.TAXBL                                    OK
SY_CUST.ANLS_CHRGR_CHNG_HIST                       OK
SY_CUST.CUST                                       OK
SY_CUST.CUST_ACRSLT_AP_ST                          OK
SY_CUST.CUST_APP_CFG                               OK
SY_CUST.CUST_BIZ                                   OK
SY_CUST.CUST_BIZ_HIST                              OK
SY_CUST.CUST_CFG                                   OK
SY_CUST.CUST_CNCN_NOTI                             OK
SY_CUST.CUST_CNTR_MNGT                             OK
SY_CUST.CUST_COMSN_CNDTN                           OK
SY_CUST.CUST_CONSL                                 OK
SY_CUST.CUST_CONSL_KIND                            OK
SY_CUST.CUST_CRGRS                                 OK
SY_CUST.CUST_HIST                                  OK
SY_CUST.CUST_LICNS                                 OK
SY_CUST.CUST_LICNS_DEL_HIST                        OK
SY_CUST.CUST_LICNS_HIST                            OK
SY_CUST.CUST_LOGIN_HIST                            OK
SY_CUST.CUST_MEMO_HIST                             OK
SY_CUST.CUST_MXD_LICNS                             OK
SY_CUST.CUST_MXD_PRVNCL                            OK
SY_CUST.CUST_NOTI                                  OK
SY_CUST.CUST_NOTI_HIST                             OK
SY_CUST.CUST_OMTP_ECPT_NOTI                        OK
SY_CUST.CUST_PRFSH                                 OK
SY_CUST.CUST_PRTC_AMT                              OK
SY_CUST.CUST_PRTC_AMT_HIST                         OK
SY_CUST.CUST_SBID                                  OK
SY_CUST.CUST_SVC                                   OK
SY_CUST.CUST_TELNO                                 OK
SY_CUST.CUST_TKN                                   OK
SY_CUST.CUST_UTLZ_CLAUSE                           OK
SY_CUST.CUST_UTLZ_CLAUSE_HIST                      OK
SY_CUST.KAKAOTALK_USER                             OK
SY_CUST.NCST                                       OK
SY_CUST.NCST_BSN                                   OK
SY_CUST.NCST_CONSL                                 OK
SY_CUST.NCST_LICNS                                 OK
SY_CUST.NCST_MNGT_FLDR                             OK
SY_CUST.NCST_MNGT_FLDR_JNOW                        OK
SY_CUST.NCST_MNGT_FLDR_TARGT                       OK
SY_CUST.NCST_RECPTN_REJECT                         OK
SY_CUST.NCST_TELNO                                 OK
SY_CUST.NCST_TELNO_HIST                            OK
SY_CUST.TRANS                                      OK
SY_CUST.TRANS_TMPL                                 OK
SY_EMP.ACRSLT_NOTI                                 OK
SY_EMP.ACRSLT_ST_MNGT                              OK
SY_EMP.ATTEND_LVFFC                                OK
SY_EMP.AUTH                                        OK
SY_EMP.BDDPR_ASIST_MNGT                            OK
SY_EMP.EMP                                         OK
SY_EMP.EMP_ALAM                                    OK
SY_EMP.EMP_AUTHZ_REQ                               OK
SY_EMP.EMP_CNCN_NOTI                               OK
SY_EMP.EMP_GBS_LICNS                               OK
SY_EMP.EMP_HIST                                    OK
SY_EMP.EMP_LOGIN_HIST                              OK
SY_EMP.EMP_LONG_OFFDAY                             OK
SY_EMP.EMP_MEMO                                    OK
SY_EMP.EMP_MENU                                    OK
SY_EMP.EMP_MENU_AUTH                               OK
SY_EMP.EMP_MENU_SKIL                               OK
SY_EMP.EMP_NTBD                                    OK
SY_EMP.EMP_NTBD_JNOW                               OK
SY_EMP.EMP_PC_REGST                                OK
SY_EMP.EMP_REC                                     OK
SY_EMP.POSIT_TEAM                                  OK
SY_EMP.POSIT_TEAM_AUTH                             OK
SY_ETC.ETCLINK                                     OK
SY_ETC.SCHEDULED_RUN                               OK
SY_ETC.migrationCheck107                           OK
SY_NOTI.AREA_CODE                                  OK
SY_NOTI.BATCH_JOB_EXECUTION                        OK
SY_NOTI.BATCH_JOB_EXECUTION_CONTEXT                OK
SY_NOTI.BATCH_JOB_EXECUTION_PARAMS                 OK
SY_NOTI.BATCH_JOB_EXECUTION_SEQ                    OK
SY_NOTI.BATCH_JOB_HIST                             OK
SY_NOTI.BATCH_JOB_INSTANCE                         OK
SY_NOTI.BATCH_JOB_SEQ                              OK
SY_NOTI.BATCH_MASTER                               OK
SY_NOTI.BATCH_STEP_EXECUTION                       OK
SY_NOTI.BATCH_STEP_EXECUTION_CONTEXT               OK
SY_NOTI.BATCH_STEP_EXECUTION_SEQ                   OK
SY_NOTI.CFMT_UNDWRT_ACRSLT_MLTP                    OK
SY_NOTI.CFMT_UNDWRT_AVG_RATE                       OK
SY_NOTI.CFMT_UNDWRT_CDLT_DISTMK                    OK
SY_NOTI.CFMT_UNDWRT_CDLT_GROUP                     OK
SY_NOTI.CFMT_UNDWRT_CDLT_ST                        OK
SY_NOTI.CFMT_UNDWRT_ST                             OK
SY_NOTI.CFMT_UNDWRT_ST_DISTMK                      OK
SY_NOTI.CNTR_MTHD                                  OK
SY_NOTI.COMN_CODE                                  OK
SY_NOTI.COMN_CODE_GROUP                            OK
SY_NOTI.DMND_ORGNZ                                 OK
SY_NOTI.DMND_ORGNZ2                                OK
SY_NOTI.KEP_REQ_HDR                                OK
SY_NOTI.LICNS_CODE                                 OK
SY_NOTI.LICNS_NEED_TCNXPRT                         OK
SY_NOTI.NOTI                                       OK
SY_NOTI.NOTI_AREA                                  OK
SY_NOTI.NOTI_AREA_HIST                             OK
SY_NOTI.NOTI_ATCHMNFL                              OK
SY_NOTI.NOTI_BDDPR_PRTC_CMP                        OK
SY_NOTI.NOTI_COMPNO_PRDPRC                         OK
SY_NOTI.NOTI_HIST                                  OK
SY_NOTI.NOTI_LICNS                                 OK
SY_NOTI.NOTI_LICNS_HIST                            OK
SY_NOTI.NOTI_ORIGN                                 OK
SY_NOTI.NOTI_SBID_PRARG_CMP                        OK
SY_NOTI.PROD_CLAS_CODE                             OK
SY_NOTI.RPSNT_ORGNZ                                OK
SY_NOTI_TEST.AREA_CODE                             OK
SY_NOTI_TEST.BATCH_JOB_EXECUTION                   OK
SY_NOTI_TEST.BATCH_JOB_EXECUTION_CONTEXT           OK
SY_NOTI_TEST.BATCH_JOB_EXECUTION_PARAMS            OK
SY_NOTI_TEST.BATCH_JOB_EXECUTION_SEQ               OK
SY_NOTI_TEST.BATCH_JOB_HIST                        OK
SY_NOTI_TEST.BATCH_JOB_INSTANCE                    OK
SY_NOTI_TEST.BATCH_JOB_SEQ                         OK
SY_NOTI_TEST.BATCH_MASTER                          OK
SY_NOTI_TEST.BATCH_STEP_EXECUTION                  OK
SY_NOTI_TEST.BATCH_STEP_EXECUTION_CONTEXT          OK
SY_NOTI_TEST.BATCH_STEP_EXECUTION_SEQ              OK
SY_NOTI_TEST.CFMT_UNDWRT_ACRSLT_MLTP               OK
SY_NOTI_TEST.CFMT_UNDWRT_AVG_RATE                  OK
SY_NOTI_TEST.CFMT_UNDWRT_CDLT_DISTMK               OK
SY_NOTI_TEST.CFMT_UNDWRT_CDLT_GROUP                OK
SY_NOTI_TEST.CFMT_UNDWRT_CDLT_ST                   OK
SY_NOTI_TEST.CFMT_UNDWRT_ST                        OK
SY_NOTI_TEST.CFMT_UNDWRT_ST_DISTMK                 OK
SY_NOTI_TEST.CNTR_MTHD                             OK
SY_NOTI_TEST.COMN_CODE                             OK
SY_NOTI_TEST.COMN_CODE_GROUP                       OK
SY_NOTI_TEST.DMND_ORGNZ                            OK
SY_NOTI_TEST.DMND_ORGNZ2                           OK
SY_NOTI_TEST.LICNS_CODE                            OK
SY_NOTI_TEST.NOTI                                  OK
SY_NOTI_TEST.NOTI_AREA                             OK
SY_NOTI_TEST.NOTI_AREA_HIST                        OK
SY_NOTI_TEST.NOTI_ATCHMNFL                         OK
SY_NOTI_TEST.NOTI_BDDPR_PRTC_CMP                   OK
SY_NOTI_TEST.NOTI_COMPNO_PRDPRC                    OK
SY_NOTI_TEST.NOTI_HIST                             OK
SY_NOTI_TEST.NOTI_LICNS                            OK
SY_NOTI_TEST.NOTI_LICNS_HIST                       OK
SY_NOTI_TEST.NOTI_ORIGN                            OK
SY_NOTI_TEST.NOTI_SBID_PRARG_CMP                   OK
SY_NOTI_TEST.RPSNT_ORGNZ                           OK
SY_SESSION.SPRING_SESSION                          OK
SY_SESSION.SPRING_SESSION_ATTRIBUTES               OK
SY_TRSMIS.TRSMIS                                   OK
SY_TRSMIS.TRSMIS_ALAM                              OK
SY_TRSMIS.TRSMIS_ALAM_OBJPS                        OK
SY_TRSMIS.TRSMIS_ATCHMNFL                          OK
SY_TRSMIS.TRSMIS_SCLAS                             OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.slow_log                                     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.user                                         OK
mysql_20181026.columns_priv                        OK
mysql_20181026.db                                  OK
mysql_20181026.event                               OK
mysql_20181026.func                                OK
mysql_20181026.general_log                         OK
mysql_20181026.help_category                       OK
mysql_20181026.help_keyword                        OK
mysql_20181026.help_relation                       OK
mysql_20181026.help_topic                          OK
mysql_20181026.innodb_index_stats
Error    : Table 'mysql_20181026.innodb_index_stats' doesn't exist
status   : Operation failed
mysql_20181026.innodb_table_stats
Error    : Table 'mysql_20181026.innodb_table_stats' doesn't exist
status   : Operation failed
mysql_20181026.ndb_binlog_index                    OK
mysql_20181026.plugin                              OK
mysql_20181026.proc                                OK
mysql_20181026.procs_priv                          OK
mysql_20181026.proxies_priv                        OK
mysql_20181026.servers                             OK
mysql_20181026.slave_master_info
Error    : Table 'mysql_20181026.slave_master_info' doesn't exist
status   : Operation failed
mysql_20181026.slave_relay_log_info
Error    : Table 'mysql_20181026.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql_20181026.slave_worker_info
Error    : Table 'mysql_20181026.slave_worker_info' doesn't exist
status   : Operation failed
mysql_20181026.slow_log                            OK
mysql_20181026.tables_priv                         OK
mysql_20181026.time_zone                           OK
mysql_20181026.time_zone_leap_second               OK
mysql_20181026.time_zone_name                      OK
mysql_20181026.time_zone_transition                OK
mysql_20181026.time_zone_transition_type           OK
mysql_20181026.user                                OK
mysql_back.columns_priv                            OK
mysql_back.db                                      OK
mysql_back.event                                   OK
mysql_back.func                                    OK
mysql_back.general_log                             OK
mysql_back.help_category                           OK
mysql_back.help_keyword                            OK
mysql_back.help_relation                           OK
mysql_back.help_topic                              OK
mysql_back.innodb_index_stats
Error    : Table 'mysql_back.innodb_index_stats' doesn't exist
status   : Operation failed
mysql_back.innodb_table_stats
Error    : Table 'mysql_back.innodb_table_stats' doesn't exist
status   : Operation failed
mysql_back.ndb_binlog_index                        OK
mysql_back.plugin                                  OK
mysql_back.proc                                    OK
mysql_back.procs_priv                              OK
mysql_back.proxies_priv                            OK
mysql_back.servers                                 OK
mysql_back.slave_master_info
Error    : Table 'mysql_back.slave_master_info' doesn't exist
status   : Operation failed
mysql_back.slave_relay_log_info
Error    : Table 'mysql_back.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql_back.slave_worker_info
Error    : Table 'mysql_back.slave_worker_info' doesn't exist
status   : Operation failed
mysql_back.slow_log                                OK
mysql_back.tables_priv                             OK
mysql_back.time_zone                               OK
mysql_back.time_zone_leap_second                   OK
mysql_back.time_zone_name                          OK
mysql_back.time_zone_transition                    OK
mysql_back.time_zone_transition_type               OK
mysql_back.user                                    OK
test.board                                         OK
test.board_reply                                   OK
test.test                                          OK
test.users                                         OK

Repairing tables
mysql_20181026.innodb_index_stats
Error    : Table 'mysql_20181026.innodb_index_stats' doesn't exist
status   : Operation failed
mysql_20181026.innodb_table_stats
Error    : Table 'mysql_20181026.innodb_table_stats' doesn't exist
status   : Operation failed
mysql_20181026.slave_master_info
Error    : Table 'mysql_20181026.slave_master_info' doesn't exist
status   : Operation failed
mysql_20181026.slave_relay_log_info
Error    : Table 'mysql_20181026.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql_20181026.slave_worker_info
Error    : Table 'mysql_20181026.slave_worker_info' doesn't exist
status   : Operation failed
mysql_back.innodb_index_stats
Error    : Table 'mysql_back.innodb_index_stats' doesn't exist
status   : Operation failed
mysql_back.innodb_table_stats
Error    : Table 'mysql_back.innodb_table_stats' doesn't exist
status   : Operation failed
mysql_back.slave_master_info
Error    : Table 'mysql_back.slave_master_info' doesn't exist
status   : Operation failed
mysql_back.slave_relay_log_info
Error    : Table 'mysql_back.slave_relay_log_info' doesn't exist
status   : Operation failed
mysql_back.slave_worker_info
Error    : Table 'mysql_back.slave_worker_info' doesn't exist
status   : Operation failed
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
OK

[root@localhost01:28:35:/usr/local/mysql/data/mysql]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 333
Server version: 5.6.10-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
28 rows in set (0.00 sec)

mysql> quit

* 해당 작업 후 에러 제거되고 정상 메시지만 출력. 근데 이 서버는 리플리케이션 제거 되고 단독으로 도는 서버.. mysql_upgrade 명령어를 하고 아래 에러 들이 출력 된것 보니 리플리케이션 관련 내용들이 없어서 뜬것... 

[ Master DB server ]

i) mycnf 설정?
[root@DB_master ~]# vi /etc/my.cnf

server-id       = 2

[root@DB_master ~]# /etc/init.d/mysqld restart

* server-id 는 어떤 번호도 노상관 입니다. slave서버와 다르게만 하면 되고, 통상적으로 master 1 slave 2 ~ 이런식으로 설정 하지만 저는 실수로 반대로 해버렸습니다.

* 현재 master 2  , slave  1 입니다. 
* 가장 중요한건 데이터 덤프시 데이터가 쌓이지 않는게 베스트지만 어쩔수 없다면 lock을 걸고 진행합니다.( 그냥 웹 데몬 내리고 진행하세요 제일 마음 편합니다.)

ii) replication 권한추가
* replication 구설할때 replication 계정을 따로 만들지만. 저는 기존 디비 계정으로 진행했습니다.
  통상적으로는 replication 계정을 따로 만들어서 권한 설정을 해줍니다.

[root@DB_master ~]#  mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 98024
Server version: 5.5.29-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> GRANT REPLICATION SLAVE ON {db명}.* TO '{계정명}'@'{slave_host_ip}' IDENTIFIED BY '{password}';

mysql>  flush privileges;


iii-1) 웹서버 중단 후  DB dump
[root@DB_master ~]# mysqldump -u root -p --master-data=2 --databases --no-autocommit=1 --single-transaction=1 --extended-insert=1 {DB명} > DB_master.sql

또는

iii-2) 웹서버 무중단 DB dump
mysql> FLUSH TABLES WITH READ LOCK;

[root@DB_master ~]# mysqldump -u root -p --master-data=2 --databases --no-autocommit=1 --single-transaction=1 --extended-insert=1 {DB명} > DB_master.sql

mysql> UNLOCK TABLES;


iv) dump file 복사
* scp, rsync 등으로 이동

[root@DB_master ~]# scp DB_marster.sql  192.168.10.10:/{저장경로}

 [  기존 데이터 디렉토리 백업 및 생성(데이터 베이스생성) ]

[root@localhost]# /etc/init.d/mysqld stop

[root@localhost]# mv /usr/local/mysql/data /usr/local/mysql/data_bak

[root@localhost]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql

[root@localhost]# /etc/init.d/mysqld start

[root@localhost ]# mysql -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.5-MariaDB-log Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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


MariaDB [(none)]> create database xxxxxxx;
Query OK, 1 row affected (0.03 sec)
MariaDB [(none)]> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
| xxxxxxxxx          | 
+--------------------+
3 rows in set (0.07 sec)

[ 테이블 구조 복원 작업 및 기존 ibd 데이터 제거 ]

[root@localhost ]# mysql -p xxxxxxx < xxxxxxx.sql

[root@localhost ]# mysql -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.5-MariaDB-log Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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


MariaDB [(none)]> use xxxxxxx;
Database changed
MariaDB [xxxxxxx]> alter table {테이블명} discard tablespace;

* 위 명령어 진행 하면 해당 테이블의 tablespace 가 제거 되면서, .ibd 데이터 파일이 제거가 됩니다.

  

[ ibd 데이트 import 진행 ]

[root@localhost ]# \cp -arp /usr/local/mysql/data_bak/*.ibd /usr/local/mysql/data/

[root@localhost ]# mysql -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.0.5-MariaDB-log Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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


MariaDB [(none)]> use xxxxxxx;
Database changed
MariaDB [xxxxxxxx]> alter table {테이블명} import tablespace;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

MariaDB [xxxxxxx]> select * from {테이블명} limit 10;
+-----+----------------------------------------+-------+------------------+--------+-----------+--------+---------------------+
| idx | url                                    | adkey | visit_id         | nv_key | useragent | month  | reqdate             |
+-----+----------------------------------------+-------+------------------+--------+-----------+--------+---------------------+
|   1 |                                        |       | A140828000000047 |        |           | 201408 | 2014-08-28 10:31:34 |
|   2 | http://xxx.co.kr/test/logtest.php      |       | 2C=B             |        |           | 201408 | 2014-08-28 10:31:35 |
|   3 |                                        |       | A140828000000048 |        |           | 201408 | 2014-08-28 10:32:10 |
|   4 | http://xxx.co.kr/test/logtest.php      |       | A140828000000049 |        |           | 201408 | 2014-08-28 10:32:10 |
|   5 |                                        |       | A140828000000050 |        |           | 201408 | 2014-08-28 10:52:56 |
|   6 | http://xxx.co.kr/test/logtest.php      |       | A140828000000051 |        |           | 201408 | 2014-08-28 10:52:56 |
|   7 |                                        |       | A140828000000052 |        |           | 201408 | 2014-08-28 11:22:08 |
|   8 | http://xxx.co.kr/test/logtest.php      |       | A140828000000053 |        |           | 201408 | 2014-08-28 11:22:09 |
|   9 |                                        |       | A140828000000054 |        |           | 201408 | 2014-08-28 11:43:27 |
|  10 | http://xxx.co.kr/test/logtest.php      |       | A140828000000055 |        |           | 201408 | 2014-08-28 11:43:31 |
+-----+----------------------------------------+-------+------------------+--------+-----------+--------+---------------------+
10 rows in set (0.00 sec)

* 정상적으로 복구 되어 데이터가 확인됩니다. 하지만 해당 작업으로 모든 데이터가 100% 복구 되기는 어렵습니다. 이점 인지 바랍니다.

[ shell script ]

[root@localhost ]# vi ibd_recover.sh

=======================================================================================
#! /bin/bash
org_data="/{원본경로}"
new_data="/{새로운경로}"

###isam
ll $org_data/ *.MY* | awk '{print $8}' | awk -F. '{print $1}' | uniq -c > isam_list.txt
###ibd
ll $org_data/ *.ibd | awk '{print $8}' | awk -F. '{print $1}' > ibd_list.txt


##isam_copy
cat isam_list.txt | while read isam
do
        \cp -arp $org_data/{databases_name}/$isam.* $new_data/{databases_name}/
done

##ibd_copty & import
cat ibd_list.txt | while read ibd

do
        echo "use {databases_name}; alter table $ibd discard tablespace;" | /usr/local/mariadb/bin/mysql -u root -p{db_root_pass}
        \cp -arp $org_data/{databases_name}/$ibd.ibd $new_data/{databases_name}/
        echo "use {databases_name}; alter table $ibd import tablespace;" | /usr/local/mariadb/bin/mysql -u root -p{db_root_pass}
done
=======================================================================================

* myisam 관련 내용은 innodb 와 같이 사용하는 데이터도 있기 때문에 작성 되었습니다. 필요 없으면 주석하면 되겠습니다.

#! /bin/bash

echo " use mysql; update user set password=password('change_password') where user='root'; flush privileges;" | /usr/local/mysql/bin/mysql -pPASSWORD

# mv /usr/local/mysql/data /usr/local/mysql/data_back

# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data

# /etc/init.d/mysqld start

** BINLOG 는 복구 시점 날짜를 덤프 파일로 생성합니다.
# cd /usr/local/mysql/data_back
# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000780 > /{경로}/20171107_binlog_1.sql
# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000781 > /{경로}/20171107_binlog_2.sql

**  해당 날짜로 백업된 dump 파일로 1차 복원. (해당 파일은 금일 새벽 시간대 파일) 

# cd /backup/mysqldump/20171107
# gunzip -d xxxnews-2017-11-07_04.sql.gz
# gunzip -d xxxnews2-2017-11-07_04.sql.gz
# gunzip -d mysql-2017-11-07_04.sql.gz
# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1187029
Server version: 5.5.29-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create database xxxnews;
mysql> create database xxxnews2;
mysql> use xxxnews;
mysql> source xxxnews-2017-11-07_04.sql;
mysql> use xxxnews2;
mysql> source xxxnews2-2017-11-07_04.sql;
mysql> use mysql;
mysql> source mysql-2017-11-07_7.sql;

# /etc/init.d/mysql restart

재 시작 후 사이트 확인 결과 정상적으로 개방 됐고,  새벽 시간 백업 본 이후부터  DB가 깨지기 전 까지 데이터 복원을 진행 합니다.

# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1187029
Server version: 5.5.29-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> source /{경로}/20171107_binlog_1.sql
mysql> source /{경로}/20171107_binlog_2.sql

1. 새로운 계정 만들고 관리하기(DB 사용권한 주기)

* mysql 접속 : mysql -u계정ID -p비밀번호 데이터베이스명(DB명 생략가능)
* mysql 종료 : quit \q

i)  데이터 베이스명 만들기
create database DB명;

ii) user 테이블에 계정, 비밀번호 등록
insert into user(host, user, password) values(‘호스트명’. ‘계정’, password(‘계정비밀번호’));

iii) db테이블에 db사용권한 등록
insert into db valuse(‘호스트명’, ‘DB명’, ‘계정’, ‘Y 12개’…); ==> 모든권한 줄 필요 없으면 N 적절 사용

iv) user, db 테이블 변경 내용 적용
flush privileges;

===> 자세한 내용은 아래의 내용 참고하기
== 중요한 테이블 user, db 테이블 ==

v) 계정등록

ex1) desc user; (시스템에 존재하는 계정을 등록하는 테이블임, 계정, 비밀번호 등등 볼때)
-> select host, user, password from user; ==> 로 레크드 보기(뭐있나 ^^)

그리고 계정, 비밀번호 등록해 보기

-> insert into user(host, user, password) values(‘localhost’. ‘php5′, password(‘1234′));

참고 : password(‘1234′) : 비밀번호를 암호화 하여 관리자를 포함한 어느 누구도 비밀번호를 알아내지 못하도록 하기 위함/나중에 select로 확인해 보면 446ㅁ1210000c85644 뭐 이런식으로 암호와 되있을 것임 ^^

vi) 사용권한 등록(권한부여)
ex2) desc db;

-> insert into db valuse(‘localhost’, ‘php5_db’, ‘php5′, ‘Y’,’Y’, 10개Y…); ==> 모든권한 부여할려면 Y 12개

php5 계정에서 php5_db 사용할 수 있도록 권한 부여 ==> DB 테이블에 레코드 추가하면 OK~!

vii) 사용자 호스트 권한 제거
delete from user where user='chicnews' and host="%";


viii) 시스템 적용 (user, db 테이블의 변경된 내용 적용)

flush privileges;

or) mysql 빠져나온 상태에서는  c:\mysql\bin> mysqladmin reload

둘중 한가지로 시스템에 적용해주세요!!


== 참고

update 문을 이용한 비빌번호 변경

ex) 관리자계정(root) 비밀번호 변경해 보기 ^^

i) user mysql;  (사용자 계정관리 DB)

ii) update user(테이블) set password = password(‘1234′) where user=’root';

iii) select host, user, password form user;

iv) flush privileges;

==> 반드시 db, user 테이블 업데이트 했을시 반드시 mysql 시스템에 적용!

나와서 mysql -uroot -p1234로 접속해 보기 변경 되었는지~

2. 데이터베이스 관련 명령어

i) 데이터베이스 접속
mysql -u계정ID -p비밀번호 데이터베이스명(생략가능)

ex) mysql -uphp5 -p1234 php5_db

만약 DB명을 생략하게 되면 나중에 use 데이터베이스명; 으로^^(ex use php5_db)

ii) 데이터베이스 만들기(생성) 
create database DB명;

ex) create database php5_db;

* DB목록 확인 : show databases;

iii) 데이터베이스 삭제
drop database DB명;

ex) drop database php5_db;

3.테이블 관련 명령어

i) 테이블 생성
create table 테이블명(필드명 타입,필드명 타입,PRIMARY KEY(필드명));

ex)
create table friend (

num int NOT NULL,

name char(10),

address char(80),

tel char(20),

PRIMARY KEY(num)

);

Tip>


ii) 테이블 구조보기
desc 테이블명;

iii) 테이블의 필드 변경하기
alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명];

ex)
alter table friend add age int;
desc friend;
alter table friend add email char(30) after address;

iv) 필드 삭제
alter table 테이블명 drop 삭제할필드명1, 삭제할필드명2;

ex)alter table friend drop email;

v) 필드 수정(필드이름 변경)
alter table 테이블명 change 이전필드명 새로운필드명 타입;

ex)
alter table friend change tel phone int;
desc friend;

vi) 필드의 타입수정

alter table 테이블명 modify 기존필드명 새로운 타입;
ex) alter table friend modify name int;

vii) 테이블 이름 변경하기
alter table 이전테이블명 rename 새테이블명;

ex) alter table friend rename student;
show tables;
desc student;

vii)  테이블 삭제하기
drop table 테이블명;

ex) drop table friend;

4. 테이블 조작명령어

i) 데이터 삽입하기(이미만들어진 테이블에 실제데이터 저장)
insert into 테이블명 (필드명1, 필드명2, … ) values (필드값1, 필드값2, ….);

참고) (필드명1, 필드명2, … ) 생략가능  ==> 모든 필드에 집어 넣겠다는 뜻

ex) insert into friend (name, name, address, tel) values (1, ‘배성진’, ‘서울 동작구 노량진동’, ‘234-8998′);
insert into friend values (2, ‘성지연’, ‘대전시 유성구 송강동’, ‘987-333′);



ii) 데이터 검색하기(select)
select 필드명1, 필드명2 from 테이블명;

참고) 전체데이터 보기  select * from 테이블명;

iii) 조건에 맞는 데이터 검색하기(select ~ where)
select 필드명1, 필드명2 from 테이블명 where 조건식;

ex) select id, name, address, tel, sex from mem where sex=’W';
select * from men where (age>=20 and age<30) or (age>=40 and age<50) and sex=’M'; 20대와 40대 남성 정보

iv) 특정 문자열을 가진 레코드 검색하기
_ : 임이의 한문자(1byte) __: 한글한글자 2byte

select name, address, tel from men where name like ‘김%'; 김으로 시작하는 모든 문자열
select naem, id from mem where name like ‘__용%'; 가운데이름이 용인사람의 이름, 아이디 보기

v) 검색된 데이터 정렬하기(order by)
select 필드명1,2 .. form 테이블명 order by 필드명;  (오름차순) 1,2,3 abc

내림차순 정렬시 : order by 필드명 desc; (desc만 뒤에 더 붙여주면 OK)

참고) 이름순으로 정렬이 잘 되지 않는 경우

한글이름의 ‘가나다..’순이 2진 한글코드의 순서와 일치하지 않기 때문

===> mysql 프로그램 설치시 한글이 적용되도록 해당 파라미터를 옵션으로 설정해 주어야 한다.

ex) 서울에 사는 사람들 나이 많은 순대로 정렬

select age, name, address from men where adress like ‘서울%’ order by age desc;

vi)  데이터 수정(update set where)

update 테이블명 set  필드명 = 필드값(변결할 값) [where 조건식];

ex) men 테이블에서 아이디가 hyun인 레코드의 전화번호를 ‘888-9999’로 변경

update men set tel=’888-9999′ where id=’hyun';

확인==> select id, name, tel from men where id=’hyun';

vii) 데이터삭제(레코드삭제)

delete from 테이블명 [where 조건식];

— delete from 테이블명; ===> 주의 : 테이블 전체 레코드 삭제

ex) men 테이블에서 이름이 홍길동인 레코드를 삭제하려면?

delete from men where name=’홍길동';

확인==> select * from men where name=’홍길동';