from-sh

SQL Error [1227] [42000]: (conn=601379) Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation

CREATE DEFINER=`test`@`%` PROCEDURE

-> CREATE PROCEDURE

DEFINER 삭제하고 명령어 수행 시 가능 

-- purge 명령어로 binlog 삭제 

purge binary logs to 'mysql-bin.000025';

PURGE BINARY LOGS BEFORE '2025-02-01 00:00:00';

-- binlog 보관주기 확인
show global variables like 'expire_logs_days';
set global expire_logs_days=3;
flush privileges;

-- 삭제 안 될시 warnings 확인 
MariaDB [(none)]> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1375
Message: Binary log 'mysql-bin.000001' is not purged because it is the current active binlog
1 row in set (0.000 sec)

-- slave_connections_needed_for_purge 설정 확인 
show global variables like 'slave_connections_needed_for_purge';
set global slave_connections_needed_for_purge=0;

simple_password_check_digits : value 값 이상의 숫자 포함    
simple_password_check_letters_same_case : value 값 이상의 대문자 포함  
simple_password_check_minimal_length : value 값 이상의 패스워드 길이    
simple_password_check_other_characters  : value 값 이상의 특수문자 포함

-- 계정 비밀번호 복잡도 조회 쿼리
MariaDB [(none)]> show global variables like 'simple_password%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| simple_password_check_digits            | 1     |
| simple_password_check_letters_same_case | 1     |
| simple_password_check_minimal_length    | 8     |
| simple_password_check_other_characters  | 1     |
+-----------------------------------------+-------+
4 rows in set (0.001 sec)

-- 설정값 변경 명령어 
MariaDB [(none)]> set global simple_password_check_letters_same_case = 1;

-- 재기동 후에도 변경이 안 되게 하려면 my.cnf파일에도 설정해야함!!

--  테이블 통계 확인 쿼리

MariaDB []> select * from mysql.innodb_table_stats;

--  인덱스 통계 확인 쿼리
MariaDB []> select * from mysql.innodb_index_stats;

-- 전체 데이터베이스 통계수집
mysqlcheck -a --all-databases

-- 특정 테이블 통계 수집
optimize table table_name;

select view_definition from information_schema.view
where table_name = 'view_name';

SELECT T.*, EVENTS.EVENT_CNT, ROUTINES.ROUTINE_CNT, TRIGGERS.TRIGGER_CNT
FROM 
(
SELECT TABLE_SCHEMA, COUNT(TABLE_NAME) AS TABLE_CNT
FROM INFORMATION_SCHEMA.TABLES
GROUP BY TABLE_SCHEMA
) T
LEFT JOIN
(
SELECT TABLE_SCHEMA, COUNT(TRIGGER_NAME) AS TRIGGER_CNT
FROM INFORMATION_SCHEMA.TRIGGERS
GROUP BY TABLE_SCHEMA
) TRIGGERS ON T.TABLE_SCHEMA = TRIGGERS.TRIGGER_NAME
LEFT JOIN(
SELECT EVENT_SCHEMA, COUNT(EVENT_SCHEMA) AS EVENT_CNT
FROM INFORMATION_SCHEMA.EVENTS
GROUP BY EVENT_SCHEMA
) EVENTS ON T.TABLE_SCHEMA = EVENTS.EVENT_SCHEMA
LEFT JOIN(
SELECT ROUTINE_SCHEMA, ROUTINES(ROUTINE_NAME) AS ROUTINE_CNT
FROM INFORMATION_SCHEMA.EVENTS
GROUP BY ROUTINE_SCHEMA
) ROUTINES ON T.TABLE_SCHEMA = ROUTINES.ROUTINE_SCHEMA
WHERE T.TABLE_SCHEMA NOT IN 
(
'INFORMATION_SCHEMA','MYSQL','PERFORMANCE_schema','sys'
);

mysql 데이터 복구 하다 해당 에러 발생 

걍 백업 파일이 깨진거 다시 백업 후 복구!

ERROR 1064 (42000) at line 8823: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''<TABLE \\\"FONT-IZE: 12px\\\" cellSpacing=0 borderColorDark=white width=' at line 1

# ./mysqldump -uroot -p test > /data/test.sql
Enter password:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `info`': SELECT command denied to user ''@'%' for column 'info1' in table 'status' (1143)

# ./mysql -uroot -p

mysql> SHOW FIELDS FROM info;
ERROR 1143 (42000): SELECT command denied to user ''@'%' for column ' info1' in table 'status'

mysql> select * from test.info limit 5;
ERROR 1449 (HY000): The user specified as a definer ('test'@'%') does not exist

-- 계정, host 생성 
CREATE USER 'test'@'%' IDENTIFIED BY 'test';
GRANT ALL PRIVILEGES ON *.* to 'test'@'%';

FLUSH PRIVILEGES;

# mysqldump -uroot -p test> /var/lib/mysql/test.sql
Enter password:
mysqldump: Error 1194: Table 'log' is marked as crashed and should be repaired when dumping table `log` at row: 8521321

# mysql -uroot -p
mysql> check table log;
+---------------+-------+----------+----------------------------------------------------------+
| Table         | Op    | Msg_type | Msg_text                                                 |
+---------------+-------+----------+----------------------------------------------------------+
| test.log | check | warning  | 3 clients are using or haven't closed the table properly |
| test.log  | check | error    | Found wrong packed record at 1049886676                  |
| test.log  | check | error    | Corrupt                                                  |
+---------------+-------+----------+----------------------------------------------------------+
3 rows in set (38.94 sec)

mysql> repair table log;
+---------------+--------+----------+-------------------------------------------+
| Table         | Op     | Msg_type | Msg_text                                  |
+---------------+--------+----------+-------------------------------------------+
| test.log  | repair | info     | Found wrong packed record at 1049886676   |
| test.log  | repair | warning  | Number of rows changed from 0 to 10540321 |
| test.log  | repair | status   | OK                                        |
+---------------+--------+----------+-------------------------------------------+
3 rows in set (6 min 45.35 sec)

mysql> check table log;
+---------------+-------+----------+----------+
| Table         | Op    | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| test.log   | check | status   | OK       |
+---------------+-------+----------+----------+
1 row in set (36.82 sec)

MariaDB [(none)]> show global variables like '%expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000077 | 1073742455 |
| mysql-bin.000078 | 1073742613 |
| mysql-bin.000079 | 1073742043 |
| mysql-bin.000080 |  714294090 |
+------------------+------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> purge binary logs to 'mysql-bin.000079';
Query OK, 0 rows affected (0.634 sec)

MariaDB [(none)]> show binary logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000079 | 1073742043 |
| mysql-bin.000080 |  714294090 |
+------------------+------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> exit
Bye

[maria@db binary]$ ll
total 1750224
-rw-rw---- 1 maria maria 1073742043 Jun  9 10:03 mysql-bin.000079
-rw-rw---- 1 maria maria  714703482 Jun 17 10:00 mysql-bin.000080
-rw-rw---- 1 maria maria         70 Jun 17 10:00 mysql-bin.index