window mysql my.ini 파일 찾기
서비스 열기 -> mysql 서비스 열기
실행파일경로 :
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 요놈이 경로다.
window mysql my.ini 파일 찾기
서비스 열기 -> mysql 서비스 열기
실행파일경로 :
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56
C:\ProgramData\MySQL\MySQL Server 5.6\my.ini 요놈이 경로다.
mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax ...
near 'interval (begin INT, end INT)'
쿼더 기호를 이용해서 다음과 같이 사용 할 수도 있다.
mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)
예약어들은 5.7 기준
CESSIBLE (R) | ACCOUNT [a] | ACTION |
ADD (R) | AFTER | AGAINST |
AGGREGATE | ALGORITHM | ALL (R) |
ALTER (R) | ALWAYS [b] | ANALYSE |
ANALYZE (R) | AND (R) | ANY |
AS (R) | ASC (R) | ASCII |
ASENSITIVE (R) | AT | AUTOEXTEND_SIZE |
AUTO_INCREMENT | AVG | AVG_ROW_LENGTH |
BACKUP | BEFORE (R) | BEGIN |
BETWEEN (R) | BIGINT (R) | BINARY (R) |
BINLOG | BIT | BLOB (R) |
BLOCK | BOOL | BOOLEAN |
BOTH (R) | BTREE | BY (R) |
BYTE | CACHE | CALL (R) |
CASCADE (R) | CASCADED | CASE (R) |
CATALOG_NAME | CHAIN | CHANGE (R) |
CHANGED | CHANNEL [c] | CHAR (R) |
CHARACTER (R) | CHARSET | CHECK (R) |
CHECKSUM | CIPHER | CLASS_ORIGIN |
CLIENT | CLOSE | COALESCE |
CODE | COLLATE (R) | COLLATION |
COLUMN (R) | COLUMNS | COLUMN_FORMAT |
COLUMN_NAME | COMMENT | COMMIT |
COMMITTED | COMPACT | COMPLETION |
COMPRESSED | COMPRESSION [d] | CONCURRENT |
CONDITION (R) | CONNECTION | CONSISTENT |
CONSTRAINT (R) | CONSTRAINT_CATALOG | CONSTRAINT_NAME |
CONSTRAINT_SCHEMA | CONTAINS | CONTEXT |
CONTINUE (R) | CONVERT (R) | CPU |
CREATE (R) | CROSS (R) | CUBE |
CURRENT | CURRENT_DATE (R) | CURRENT_TIME (R) |
CURRENT_TIMESTAMP (R) | CURRENT_USER (R) | CURSOR (R) |
CURSOR_NAME | DATA | DATABASE (R) |
DATABASES (R) | DATAFILE | DATE |
DATETIME | DAY | DAY_HOUR (R) |
DAY_MICROSECOND (R) | DAY_MINUTE (R) | DAY_SECOND (R) |
DEALLOCATE | DEC (R) | DECIMAL (R) |
DECLARE (R) | DEFAULT (R) | DEFAULT_AUTH |
DEFINER | DELAYED (R) | DELAY_KEY_WRITE |
DELETE (R) | DESC (R) | DESCRIBE (R) |
DES_KEY_FILE | DETERMINISTIC (R) | DIAGNOSTICS |
DIRECTORY | DISABLE | DISCARD |
DISK | DISTINCT (R) | DISTINCTROW (R) |
DIV (R) | DO | DOUBLE (R) |
DROP (R) | DUAL (R) | DUMPFILE |
DUPLICATE | DYNAMIC | EACH (R) |
ELSE (R) | ELSEIF (R) | ENABLE |
ENCLOSED (R) | END | ENDS |
ENGINE | ENGINES | ENUM |
ERROR | ERRORS | ESCAPE |
ESCAPED (R) | EVENT | EVENTS |
EVERY | EXCHANGE | EXECUTE |
EXISTS (R) | EXIT (R) | EXPANSION |
EXPIRE | EXPLAIN (R) | EXPORT |
EXTENDED | EXTENT_SIZE | FALSE (R) |
FAST | FAULTS | FETCH (R) |
FIELDS | FILE | FILE_BLOCK_SIZE [e] |
FILTER [f] | FIRST | FIXED |
FLOAT (R) | FLOAT4 (R) | FLOAT8 (R) |
FLUSH | FOLLOWS [g] | FOR (R) |
FORCE (R) | FOREIGN (R) | FORMAT |
FOUND | FROM (R) | FULL |
FULLTEXT (R) | FUNCTION | GENERAL |
GENERATED [h] (R) | GEOMETRY | GEOMETRYCOLLECTION |
GET (R) | GET_FORMAT | GLOBAL |
GRANT (R) | GRANTS | GROUP (R) |
GROUP_REPLICATION [i] | HANDLER | HASH |
HAVING (R) | HELP | HIGH_PRIORITY (R) |
HOST | HOSTS | HOUR |
HOUR_MICROSECOND (R) | HOUR_MINUTE (R) | HOUR_SECOND (R) |
IDENTIFIED | IF (R) | IGNORE (R) |
IGNORE_SERVER_IDS | IMPORT | IN (R) |
INDEX (R) | INDEXES | INFILE (R) |
INITIAL_SIZE | INNER (R) | INOUT (R) |
INSENSITIVE (R) | INSERT (R) | INSERT_METHOD |
INSTALL | INT (R) | INT1 (R) |
INT2 (R) | INT3 (R) | INT4 (R) |
INT8 (R) | INTEGER (R) | INTERVAL (R) |
INTO (R) | INVOKER | IO |
IO_AFTER_GTIDS (R) | IO_BEFORE_GTIDS (R) | IO_THREAD |
IPC | IS (R) | ISOLATION |
ISSUER | ITERATE (R) | JOIN (R) |
JSON [j] | KEY (R) | KEYS (R) |
KEY_BLOCK_SIZE | KILL (R) | LANGUAGE |
LAST | LEADING (R) | LEAVE (R) |
LEAVES | LEFT (R) | LESS |
LEVEL | LIKE (R) | LIMIT (R) |
LINEAR (R) | LINES (R) | LINESTRING |
LIST | LOAD (R) | LOCAL |
LOCALTIME (R) | LOCALTIMESTAMP (R) | LOCK (R) |
LOCKS | LOGFILE | LOGS |
LONG (R) | LONGBLOB (R) | LONGTEXT (R) |
LOOP (R) | LOW_PRIORITY (R) | MASTER |
MASTER_AUTO_POSITION | MASTER_BIND (R) | MASTER_CONNECT_RETRY |
MASTER_DELAY | MASTER_HEARTBEAT_PERIOD | MASTER_HOST |
MASTER_LOG_FILE | MASTER_LOG_POS | MASTER_PASSWORD |
MASTER_PORT | MASTER_RETRY_COUNT | MASTER_SERVER_ID |
MASTER_SSL | MASTER_SSL_CA | MASTER_SSL_CAPATH |
MASTER_SSL_CERT | MASTER_SSL_CIPHER | MASTER_SSL_CRL |
MASTER_SSL_CRLPATH | MASTER_SSL_KEY | MASTER_SSL_VERIFY_SERVER_CERT (R) |
MASTER_TLS_VERSION [k] | MASTER_USER | MATCH (R) |
MAXVALUE (R) | MAX_CONNECTIONS_PER_HOUR | MAX_QUERIES_PER_HOUR |
MAX_ROWS | MAX_SIZE | MAX_STATEMENT_TIME [l] |
MAX_UPDATES_PER_HOUR | MAX_USER_CONNECTIONS | MEDIUM |
MEDIUMBLOB (R) | MEDIUMINT (R) | MEDIUMTEXT (R) |
MEMORY | MERGE | MESSAGE_TEXT |
MICROSECOND | MIDDLEINT (R) | MIGRATE |
MINUTE | MINUTE_MICROSECOND (R) | MINUTE_SECOND (R) |
MIN_ROWS | MOD (R) | MODE |
MODIFIES (R) | MODIFY | MONTH |
MULTILINESTRING | MULTIPOINT | MULTIPOLYGON |
MUTEX | MYSQL_ERRNO | NAME |
NAMES | NATIONAL | NATURAL (R) |
NCHAR | NDB | NDBCLUSTER |
NEVER [m] | NEW | NEXT |
NO | NODEGROUP | NONBLOCKING [n] |
NONE | NOT (R) | NO_WAIT |
NO_WRITE_TO_BINLOG (R) | NULL (R) | NUMBER |
NUMERIC (R) | NVARCHAR | OFFSET |
OLD_PASSWORD [o] | ON (R) | ONE |
ONLY | OPEN | OPTIMIZE (R) |
OPTIMIZER_COSTS [p] (R) | OPTION (R) | OPTIONALLY (R) |
OPTIONS | OR (R) | ORDER (R) |
OUT (R) | OUTER (R) | OUTFILE (R) |
OWNER | PACK_KEYS | PAGE |
PARSER | PARSE_GCOL_EXPR [q] | PARTIAL |
PARTITION (R) | PARTITIONING | PARTITIONS |
PASSWORD | PHASE | PLUGIN |
PLUGINS | PLUGIN_DIR | POINT |
POLYGON | PORT | PRECEDES [r] |
PRECISION (R) | PREPARE | PRESERVE |
PREV | PRIMARY (R) | PRIVILEGES |
PROCEDURE (R) | PROCESSLIST | PROFILE |
PROFILES | PROXY | PURGE (R) |
QUARTER | QUERY | QUICK |
RANGE (R) | READ (R) | READS (R) |
READ_ONLY | READ_WRITE (R) | REAL (R) |
REBUILD | RECOVER | REDOFILE |
REDO_BUFFER_SIZE | REDUNDANT | REFERENCES (R) |
REGEXP (R) | RELAY | RELAYLOG |
RELAY_LOG_FILE | RELAY_LOG_POS | RELAY_THREAD |
RELEASE (R) | RELOAD | REMOVE |
RENAME (R) | REORGANIZE | REPAIR |
REPEAT (R) | REPEATABLE | REPLACE (R) |
REPLICATE_DO_DB [s] | REPLICATE_DO_TABLE [t] | REPLICATE_IGNORE_DB [u] |
REPLICATE_IGNORE_TABLE [v] | REPLICATE_REWRITE_DB [w] | REPLICATE_WILD_DO_TABLE [x] |
REPLICATE_WILD_IGNORE_TABLE [y] | REPLICATION | REQUIRE (R) |
RESET | RESIGNAL (R) | RESTORE |
RESTRICT (R) | RESUME | RETURN (R) |
RETURNED_SQLSTATE | RETURNS | REVERSE |
REVOKE (R) | RIGHT (R) | RLIKE (R) |
ROLLBACK | ROLLUP | ROUTINE |
ROW | ROWS | ROW_COUNT |
ROW_FORMAT | RTREE | SAVEPOINT |
SCHEDULE | SCHEMA (R) | SCHEMAS (R) |
SCHEMA_NAME | SECOND | SECOND_MICROSECOND (R) |
SECURITY | SELECT (R) | SENSITIVE (R) |
SEPARATOR (R) | SERIAL | SERIALIZABLE |
SERVER | SESSION | SET (R) |
SHARE | SHOW (R) | SHUTDOWN |
SIGNAL (R) | SIGNED | SIMPLE |
SLAVE | SLOW | SMALLINT (R) |
SNAPSHOT | SOCKET | SOME |
SONAME | SOUNDS | SOURCE |
SPATIAL (R) | SPECIFIC (R) | SQL (R) |
SQLEXCEPTION (R) | SQLSTATE (R) | SQLWARNING (R) |
SQL_AFTER_GTIDS | SQL_AFTER_MTS_GAPS | SQL_BEFORE_GTIDS |
SQL_BIG_RESULT (R) | SQL_BUFFER_RESULT | SQL_CACHE |
SQL_CALC_FOUND_ROWS (R) | SQL_NO_CACHE | SQL_SMALL_RESULT (R) |
SQL_THREAD | SQL_TSI_DAY | SQL_TSI_HOUR |
SQL_TSI_MINUTE | SQL_TSI_MONTH | SQL_TSI_QUARTER |
SQL_TSI_SECOND | SQL_TSI_WEEK | SQL_TSI_YEAR |
SSL (R) | STACKED | START |
STARTING (R) | STARTS | STATS_AUTO_RECALC |
STATS_PERSISTENT | STATS_SAMPLE_PAGES | STATUS |
STOP | STORAGE | STORED [z] (R) |
STRAIGHT_JOIN (R) | STRING | SUBCLASS_ORIGIN |
SUBJECT | SUBPARTITION | SUBPARTITIONS |
SUPER | SUSPEND | SWAPS |
SWITCHES | TABLE (R) | TABLES |
TABLESPACE | TABLE_CHECKSUM | TABLE_NAME |
TEMPORARY | TEMPTABLE | TERMINATED (R) |
TEXT | THAN | THEN (R) |
TIME | TIMESTAMP | TIMESTAMPADD |
TIMESTAMPDIFF | TINYBLOB (R) | TINYINT (R) |
TINYTEXT (R) | TO (R) | TRAILING (R) |
TRANSACTION | TRIGGER (R) | TRIGGERS |
TRUE (R) | TRUNCATE | TYPE |
TYPES | UNCOMMITTED | UNDEFINED |
UNDO (R) | UNDOFILE | UNDO_BUFFER_SIZE |
UNICODE | UNINSTALL | UNION (R) |
UNIQUE (R) | UNKNOWN | UNLOCK (R) |
UNSIGNED (R) | UNTIL | UPDATE (R) |
UPGRADE | USAGE (R) | USE (R) |
USER | USER_RESOURCES | USE_FRM |
USING (R) | UTC_DATE (R) | UTC_TIME (R) |
UTC_TIMESTAMP (R) | VALIDATION [aa] | VALUE |
VALUES (R) | VARBINARY (R) | VARCHAR (R) |
VARCHARACTER (R) | VARIABLES | VARYING (R) |
VIEW | VIRTUAL [ab] (R) | WAIT |
WARNINGS | WEEK | WEIGHT_STRING |
WHEN (R) | WHERE (R) | WHILE (R) |
WITH (R) | WITHOUT [ac] | WORK |
WRAPPER | WRITE (R) | X509 |
XA | XID [ad] | XML |
XOR (R) | YEAR | YEAR_MONTH (R) |
ZEROFILL (R) |
create database jejuwater;
grant all privileges on jejuwater.* to jejuwater@'%' identified by 'jejuwater';
flush privileges;
데이터베이스 생성
mysql> create database 데이터베이스명;
권한 추가
mysql> grant all privileges on DB명.* to DB유저id@'%' identified by 'DB유저패스워드' with grant option;
주기적으로(최소한 하루 한 번) MySQL database 를 백업하기 위해 cron job 을 사용하여 매일 마다 백업 후, ftp 를 통해 특정 서버에 덤프파일을 업로드하도록 설정합니다.
커맨드라인 ftp 툴인 lftp 패키지를 사용하기 위해서 우선 lftp 를 설치합니다.
쉘 스크립트는 아래의 코드와 같습니다. 이를 통해서 /home/username/mysqldump/ 폴더에 특정 db 를 제외한 모든 데이터베이스의 덤프를 기록하고 FTP 서버에 업로드 됩니다. 사용자명과 비밀번호는 스크립트를 사용하기 전에 설정해야 합니다.
/home/username/mysql.backup.sh 로 저장한 후에, 실행 권한을 줍니다.
최초 백업을 위해서 쉘 스크립트를 실행합니다.
cron job 에 작업을 등록하여 매일 정해진 시간에 위의 작업이 이루어질 수 있도록 합니다.
crontab 편집기를 실행하여 아래와 같이 설정합니다.
이로써 MySQL database Server 의 데이터베이스를 매일 백업해서 압축된 덤프파일을 특정 FTP 서버로 업로드 되도록 하는 작업이 완료되었습니다.
이후, 특정 FTP 서버로 전송된 백업 파일은 외부 저장소에 주기적으로 2차 백업을 하여 데이터베이스 보존에 심혈을 기울여야할 것 입니다.
su -
service mysql stop
rm -fr /var/lib/mysql/*
service mysql start
mysqladmin -u root password 'rawr'
show function status;
테이블을 다시 만들어야 하는 변경 내용을 사용자가 저장할 수 없게 만듭니다. 다음 동작을 수행하려면 테이블을 다시 만들어야 할 수 있습니다.