https://www.adminschool.net/wiki/ 2026-04-22T09:15:29+00:00 https://www.adminschool.net/wiki/ https://www.adminschool.net/wiki/lib/exe/fetch.php?media=wiki:dokuwiki.svg text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:accountcreate&rev=1759498250&do=diff MySQL MySQL User/Database Create Scripts * 아래는 User/DB 생성 스크립트이다. # cat admin_user.sql use mysql; grant usage on *.* to admin@localhost; update user set password=password('password') where user='admin'; -- drop database admindb; create database admindb; grant all on admindb.* to admin; flush privileges; text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:admin&rev=1759498250&do=diff MySQL Administrator Tip & Tech mysqldump : table crashed and locked * mysqldump 명령으로 백업을 받을때, 아래와 같은 메세지가 나오면서 진행이 되지 않을 경우. (mytablename은 해당 테이블명이다) mysqldump: Got error: 144: Table 'mytablename' is marked as crashed and last (automatic?) repair failed when using LOCK TABLES text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:backuprestore&rev=1759498250&do=diff MySQL backup and restore backup view/procedure/function/trigger 백업방법 * mysqldump 를 이용한 백업은 기본(default)값이 view/procedure/function/trigger는 백업을 하지 않습니다. * --routines 옵션을 이용하면 백업을 할 수 있습니다. (default는 false) text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:comment&rev=1759498250&do=diff MySQL comment 설정 및 조회 Table 정보조회 (with comment) * 명령어를 통한 테이블정보 조회 SHOW TABLE STATUS; * Query를 통한 테이블정보 조회 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name'; text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:mysqlclientconnect&rev=1759498250&do=diff MySQL Connect MySQL Server with MySQL client # mysql -u 아이디 -p비밀번호 디비이름 text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:mysqlutf8&rev=1759498250&do=diff MySQL Create DB, Account (UTF-8) * Character set 을 UTF-8로 DB를 생성하고, 사용자 계정을 생성하는 방법은 다음과 같다. mysql> CREATE DATABASE 디비이름 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> GRANT ALL PRIVILEGES ON 디비이름.* TO '아이디'@'localhost' IDENTIFIED BY '비밀번호' WITH GRANT OPTION; mysql> flush privileges; text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:procedure&rev=1759498250&do=diff MySQL Procedure text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:rootpasswdchange&rev=1759498250&do=diff MySQL Change root password MySQL을 설치한뒤에 root의 비밀번호가 없는 상태이므로 반드시 비밀번호를 설정해야만 한다. # mysqladmin -u root password "비밀번호" text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:security&rev=1759498250&do=diff MySQL 암복호화 encode 양방향 암호화 decode 양방향 복호화 password 단방향 암호화 예제 select AdminNames ,encode(AdminNames,'securekey') as encode ,decode(encode(AdminNames,'securekey'),'securekey') as decode ,password(AdminNames) as password from admin text/html 2025-10-03T13:30:50+00:00 Anonymous (anonymous@undisclosed.example.com) https://www.adminschool.net/wiki/doku.php?id=dbms:mysql:sql&rev=1759498250&do=diff MySQL Query * 프로시저,함수내용에 들어가 있는 문자열 검색 SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM information_schema.routines WHERE LOWER(ROUTINE_DEFINITION) LIKE '%class%';