1. 개요
2. 사전작업
mysql 모듈을 설치한다.
$ npm install mysql
이 이메일 주소가 스팸봇으로부터 보호됩니다. 확인하려면 자바스크립트 활성화가 필요합니다..0 node_modules/mysql
├── safe-buffer@5.1.2
├── sqlstring@2.3.1
├── bignumber.js@4.1.0
└── readable-stream@2.3.6 (process-nextick-args@2.0.0, inherits@2.0.3, string_decoder@1.1.1, util-deprecate@1.0.2, core-util-is@1.0.2, isarray@1.0.0)
설치 여부 확인.
$ ls -l node_modules total 4 drwxrwxr-x 4 ec2-user ec2-user 4096 Nov 29 14:44 mysql
3. 파일 및 디렉토리 구조
테스트를 위해 임의의 디렉토리 구조를 생성하였다.
- db 디렉토리
- sql 디렉토리
- execute.js 파일
4. db 디렉토리 하위
4-1. info.js
module.exports = (function() {
return {
host: "DB연결정보(URL)",
user: "계정이름",
password: "패스워드",
database: "DB이름"
}
})();
4-2. connect.js
var mysql = require('mysql');
module.exports = function () {
var config = require('../db/info');
var pool = mysql.createPool({
host: config.host,
user: config.user,
password: config.password,
database: config.database
});
return {
getConnection: function (callback) {
pool.getConnection(callback);
},
end: function(callback){
pool.end(callback);
}
}
}();
5. sql
5-1. select.js
var pool = require('../db/connect');
module.exports = function () {
return {
select: function(callback){
pool.getConnection(function(err, con){
var sql = 'SELECT table_name FROM information_schema.tables';
con.query(sql, function (err, result, fields) {
con.release();
if (err) return callback(err);
callback(null, result);
});
});
},
pool: pool
}
};
6. execute.js
var sql = require('./sql/select')();
console.log('** Started');
sql.select(function(err, data){
if (err) console.log(err);
else console.log(data);
sql.pool.end(function(err){
if (err) console.log(err);
else {
console.log('** Finished');
}
});
});
7. 실행 결과 (스압...)
$ node execute.js
** Started
[ RowDataPacket { table_name: 'ALL_PLUGINS' },
RowDataPacket { table_name: 'APPLICABLE_ROLES' },
RowDataPacket { table_name: 'CHARACTER_SETS' },
RowDataPacket { table_name: 'COLLATIONS' },
RowDataPacket { table_name: 'COLLATION_CHARACTER_SET_APPLICABILITY' },
RowDataPacket { table_name: 'COLUMNS' },
RowDataPacket { table_name: 'COLUMN_PRIVILEGES' },
RowDataPacket { table_name: 'ENABLED_ROLES' },
RowDataPacket { table_name: 'ENGINES' },
RowDataPacket { table_name: 'EVENTS' },
RowDataPacket { table_name: 'FILES' },
RowDataPacket { table_name: 'GLOBAL_STATUS' },
RowDataPacket { table_name: 'GLOBAL_VARIABLES' },
RowDataPacket { table_name: 'KEY_CACHES' },
RowDataPacket { table_name: 'KEY_COLUMN_USAGE' },
RowDataPacket { table_name: 'PARAMETERS' },
RowDataPacket { table_name: 'PARTITIONS' },
RowDataPacket { table_name: 'PLUGINS' },
RowDataPacket { table_name: 'PROCESSLIST' },
RowDataPacket { table_name: 'PROFILING' },
RowDataPacket { table_name: 'REFERENTIAL_CONSTRAINTS' },
RowDataPacket { table_name: 'ROUTINES' },
RowDataPacket { table_name: 'SCHEMATA' },
RowDataPacket { table_name: 'SCHEMA_PRIVILEGES' },
RowDataPacket { table_name: 'SESSION_STATUS' },
RowDataPacket { table_name: 'SESSION_VARIABLES' },
RowDataPacket { table_name: 'STATISTICS' },
RowDataPacket { table_name: 'SYSTEM_VARIABLES' },
RowDataPacket { table_name: 'TABLES' },
RowDataPacket { table_name: 'TABLESPACES' },
RowDataPacket { table_name: 'TABLE_CONSTRAINTS' },
RowDataPacket { table_name: 'TABLE_PRIVILEGES' },
RowDataPacket { table_name: 'TRIGGERS' },
RowDataPacket { table_name: 'USER_PRIVILEGES' },
RowDataPacket { table_name: 'VIEWS' },
RowDataPacket { table_name: 'GEOMETRY_COLUMNS' },
RowDataPacket { table_name: 'SPATIAL_REF_SYS' },
RowDataPacket { table_name: 'CLIENT_STATISTICS' },
RowDataPacket { table_name: 'INDEX_STATISTICS' },
RowDataPacket { table_name: 'INNODB_SYS_DATAFILES' },
RowDataPacket { table_name: 'TABLE_STATISTICS' },
RowDataPacket { table_name: 'INNODB_SYS_TABLESTATS' },
RowDataPacket { table_name: 'USER_STATISTICS' },
RowDataPacket { table_name: 'INNODB_SYS_INDEXES' },
RowDataPacket { table_name: 'XTRADB_RSEG' },
RowDataPacket { table_name: 'INNODB_CMP_PER_INDEX' },
RowDataPacket { table_name: 'INNODB_TRX' },
RowDataPacket { table_name: 'CHANGED_PAGE_BITMAPS' },
RowDataPacket { table_name: 'INNODB_FT_BEING_DELETED' },
RowDataPacket { table_name: 'INNODB_LOCK_WAITS' },
RowDataPacket { table_name: 'INNODB_LOCKS' },
RowDataPacket { table_name: 'INNODB_TABLESPACES_ENCRYPTION' },
RowDataPacket { table_name: 'XTRADB_INTERNAL_HASH_TABLES' },
RowDataPacket { table_name: 'INNODB_SYS_FIELDS' },
RowDataPacket { table_name: 'INNODB_CMPMEM_RESET' },
RowDataPacket { table_name: 'INNODB_CMP' },
RowDataPacket { table_name: 'INNODB_FT_INDEX_TABLE' },
RowDataPacket { table_name: 'INNODB_SYS_TABLESPACES' },
RowDataPacket { table_name: 'INNODB_MUTEXES' },
RowDataPacket { table_name: 'INNODB_BUFFER_PAGE_LRU' },
RowDataPacket { table_name: 'INNODB_SYS_FOREIGN_COLS' },
RowDataPacket { table_name: 'INNODB_CMP_RESET' },
RowDataPacket { table_name: 'INNODB_BUFFER_POOL_STATS' },
RowDataPacket { table_name: 'INNODB_FT_INDEX_CACHE' },
RowDataPacket { table_name: 'INNODB_SYS_FOREIGN' },
RowDataPacket { table_name: 'INNODB_METRICS' },
RowDataPacket { table_name: 'INNODB_FT_DEFAULT_STOPWORD' },
RowDataPacket { table_name: 'INNODB_CMPMEM' },
RowDataPacket { table_name: 'INNODB_SYS_TABLES' },
RowDataPacket { table_name: 'INNODB_SYS_COLUMNS' },
RowDataPacket { table_name: 'INNODB_FT_CONFIG' },
RowDataPacket { table_name: 'INNODB_BUFFER_PAGE' },
RowDataPacket { table_name: 'INNODB_CMP_PER_INDEX_RESET' },
RowDataPacket { table_name: 'XTRADB_READ_VIEW' },
RowDataPacket { table_name: 'INNODB_SYS_SEMAPHORE_WAITS' },
RowDataPacket { table_name: 'INNODB_CHANGED_PAGES' },
RowDataPacket { table_name: 'INNODB_FT_DELETED' },
RowDataPacket { table_name: 'INNODB_TABLESPACES_SCRUBBING' },
RowDataPacket { table_name: 'column_stats' },
RowDataPacket { table_name: 'columns_priv' },
RowDataPacket { table_name: 'db' },
RowDataPacket { table_name: 'event' },
RowDataPacket { table_name: 'func' },
RowDataPacket { table_name: 'general_log' },
RowDataPacket { table_name: 'general_log_template' },
RowDataPacket { table_name: 'gtid_slave_pos' },
RowDataPacket { table_name: 'help_category' },
RowDataPacket { table_name: 'help_keyword' },
RowDataPacket { table_name: 'help_relation' },
RowDataPacket { table_name: 'help_topic' },
RowDataPacket { table_name: 'host' },
RowDataPacket { table_name: 'index_stats' },
RowDataPacket { table_name: 'innodb_index_stats' },
RowDataPacket { table_name: 'innodb_table_stats' },
RowDataPacket { table_name: 'plugin' },
RowDataPacket { table_name: 'proc' },
RowDataPacket { table_name: 'procs_priv' },
RowDataPacket { table_name: 'proxies_priv' },
RowDataPacket { table_name: 'rds_configuration' },
RowDataPacket { table_name: 'rds_global_status_history' },
RowDataPacket { table_name: 'rds_global_status_history_old' },
RowDataPacket { table_name: 'rds_heartbeat2' },
RowDataPacket { table_name: 'rds_history' },
RowDataPacket { table_name: 'rds_replication_status' },
RowDataPacket { table_name: 'rds_sysinfo' },
RowDataPacket { table_name: 'roles_mapping' },
RowDataPacket { table_name: 'servers' },
RowDataPacket { table_name: 'slow_log' },
RowDataPacket { table_name: 'slow_log_template' },
RowDataPacket { table_name: 'table_stats' },
RowDataPacket { table_name: 'tables_priv' },
RowDataPacket { table_name: 'time_zone' },
RowDataPacket { table_name: 'time_zone_leap_second' },
RowDataPacket { table_name: 'time_zone_name' },
RowDataPacket { table_name: 'time_zone_transition' },
RowDataPacket { table_name: 'time_zone_transition_type' },
RowDataPacket { table_name: 'user' },
RowDataPacket { table_name: 'accounts' },
RowDataPacket { table_name: 'cond_instances' },
RowDataPacket { table_name: 'events_stages_current' },
RowDataPacket { table_name: 'events_stages_history' },
RowDataPacket { table_name: 'events_stages_history_long' },
RowDataPacket {
table_name: 'events_stages_summary_by_account_by_event_name' },
RowDataPacket { table_name: 'events_stages_summary_by_host_by_event_name' },
RowDataPacket { table_name: 'events_stages_summary_by_thread_by_event_name' },
RowDataPacket { table_name: 'events_stages_summary_by_user_by_event_name' },
RowDataPacket { table_name: 'events_stages_summary_global_by_event_name' },
RowDataPacket { table_name: 'events_statements_current' },
RowDataPacket { table_name: 'events_statements_history' },
RowDataPacket { table_name: 'events_statements_history_long' },
RowDataPacket {
table_name: 'events_statements_summary_by_account_by_event_name' },
RowDataPacket { table_name: 'events_statements_summary_by_digest' },
RowDataPacket {
table_name: 'events_statements_summary_by_host_by_event_name' },
RowDataPacket {
table_name: 'events_statements_summary_by_thread_by_event_name' },
RowDataPacket {
table_name: 'events_statements_summary_by_user_by_event_name' },
RowDataPacket {
table_name: 'events_statements_summary_global_by_event_name' },
RowDataPacket { table_name: 'events_waits_current' },
RowDataPacket { table_name: 'events_waits_history' },
RowDataPacket { table_name: 'events_waits_history_long' },
RowDataPacket { table_name: 'events_waits_summary_by_account_by_event_name' },
RowDataPacket { table_name: 'events_waits_summary_by_host_by_event_name' },
RowDataPacket { table_name: 'events_waits_summary_by_instance' },
RowDataPacket { table_name: 'events_waits_summary_by_thread_by_event_name' },
RowDataPacket { table_name: 'events_waits_summary_by_user_by_event_name' },
RowDataPacket { table_name: 'events_waits_summary_global_by_event_name' },
RowDataPacket { table_name: 'file_instances' },
RowDataPacket { table_name: 'file_summary_by_event_name' },
RowDataPacket { table_name: 'file_summary_by_instance' },
RowDataPacket { table_name: 'host_cache' },
RowDataPacket { table_name: 'hosts' },
RowDataPacket { table_name: 'mutex_instances' },
RowDataPacket { table_name: 'objects_summary_global_by_type' },
RowDataPacket { table_name: 'performance_timers' },
RowDataPacket { table_name: 'rwlock_instances' },
RowDataPacket { table_name: 'session_account_connect_attrs' },
RowDataPacket { table_name: 'session_connect_attrs' },
RowDataPacket { table_name: 'setup_actors' },
RowDataPacket { table_name: 'setup_consumers' },
RowDataPacket { table_name: 'setup_instruments' },
RowDataPacket { table_name: 'setup_objects' },
RowDataPacket { table_name: 'setup_timers' },
RowDataPacket { table_name: 'socket_instances' },
RowDataPacket { table_name: 'socket_summary_by_event_name' },
RowDataPacket { table_name: 'socket_summary_by_instance' },
RowDataPacket { table_name: 'table_io_waits_summary_by_index_usage' },
RowDataPacket { table_name: 'table_io_waits_summary_by_table' },
RowDataPacket { table_name: 'table_lock_waits_summary_by_table' },
RowDataPacket { table_name: 'threads' },
RowDataPacket { table_name: 'users' } ]
** Finished