DB Library

설명

아래 값을 모두 변경하여도 SQL Server 카운트가 보이지 않는 경우 ... SQL Server를 재시작

SQL Server 재시작에도 보이지 않는 경우 OS 재시작

1.레지스트리에서 Disable Performance Counters  값 변경 : 1 -> 0

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\Disable Performance Counters

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance\Disable Performance Counters

2. services.msc에 Remote Registry가 시작되었는지 체크
 
3. unlodctr 와 lodctr 을 이용해서 SQL 관련 카운터를 다시 등록할것

1) cmd(Administrator권한으로)

2) SQL SERVER의 binn폴더로 이동

3)unlodctr을 이용해서 SQL counters를 unload한다.
    e.g. unlodctr MSSQLSERVER (for default instance)
    e.g. unlodctr SQLSERVERAGENT (for default SQL Agent)
    e.g. unlodctr MSSQL$TEST (for named instance)
    e.g. unlodctr SQLAGENT$TEST (for SQL agent)

4)lodctr을 이용해서 SQL counters를 다시 등록한다.
    e.g. lodctr perf-MSSQLSERVERsqlctr.ini (for default instance)
    e.g. lodctr perf-SQLSERVERAGENTsqlagtctr.ini (for default SQL Agent)
    e.g. lodctr perf-MSSQL$TESTsqlctr.ini (for named instance)
    e.g. lodctr perf-SQLAGENT$TESTsqlagtctr.ini (for SQL Agent)

5)Remote Registry service를 다시 시작한다.
    net stop "Remote Registry" 
    net start "Remote Registry"

6)필요할 경우 WMI와 WinPrivSE.exe 싱크를 다시 맞춘다.
    e.g. winmgmt /resyncperfctr "5660" 
   cf)5660은 WinPrivSE.exe 의 pid

4. performance counter를 재등록 한다.

lodctr /R --> 모든 
주의!!! 모든 performance counter registry 세팅을 재등록하게 된다.

참고

https://www.travisgan.com/2013/05/missing-sql-performance-counters.html

Version - 5.7

-- 계정 생성

insert into user(host, user, authentication_string, ssl_cipher, x509_issuer, x509_subject)
values('%', 'account_srv', password('StrongPassword'), '', '', '');

--권한
grant all privileges on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;
grant select, insert, update, delete, execute on *.* to 'account_srv'@'%' identified by 'StrongPassword' with grant option;

flush privileges;

--- 비밀 번호 변경
UPDATE mysql.user SET Password=PASSWORD('StrongPassword') WHERE User='account_srv' AND Host='%';

SET PASSWORD FOR 'account_srv'@'%' = PASSWORD('StrongPassword');

ALTER USER 'account_srv'@'%' identified by 'StrongPassword';

flush privileges;

-- 권한 확인
# show grants for 'user'@'접속위치';
show grants for 'account_srv'@'%';

-- 계정 삭제
# drop user '계정아이디'@'접속위치';
drop user 'account_srv'@'%';

-- 권한 삭제
# revoke all on DB이름.테이블 FROM '계정아이디'@'접속위치';
revoke all on *.* FROM 'account_srv'@'%';

Version - 8.0

-- 사용자 정보 조회 
SELECT user,authentication_string,plugin,host FROM mysql.user; 

-- 사용자 생성 
create user 'account_srv'@'%' identified by 'StrongPassword'; 

-- 권한 부여 
GRANT ALL PRIVILEGES ON *.* TO 'account_srv'@'%'; 
GRANT GRANT OPTION ON *.* TO 'account_srv'@'%'; 

-- 비밀번호 plugin 변경 
ALTER USER 'account_srv'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';

기능

- yyyy-mm-dd HH:mm:ss 형태로 날짜 데이터 입/출력

- python flask에서 get, post 형태로 날짜 데이터 처리

js

<!-- jQuery -->
<script src="/ path /plugins/jquery/jquery.min.js"></script>
<!-- Select2 -->
<script src="/ path /plugins/select2/js/select2.full.min.js"></script>
<!-- InputMask -->
<script src="/ path /plugins/moment/moment.min.js"></script>
<!-- Tempusdominus Bootstrap 4 -->
<script src="/ path /plugins/tempusdominus-bootstrap-4/js/tempusdominus-bootstrap-4.min.js"></script>

css

<!-- Font Awesome -->
<link rel="stylesheet" href="/ path /plugins/fontawesome-free/css/all.min.css">
  
<!-- Select2 -->
<link rel="stylesheet" href="/ path /plugins/select2/css/select2.min.css">
<link rel="stylesheet" href="/ path /plugins/select2-bootstrap4-theme/select2-bootstrap4.min.css">
  
<!-- Tempusdominus Bootstrap 4 -->
<link rel="stylesheet" href="/ Path /plugins/tempusdominus-bootstrap-4/css/tempusdominus-bootstrap-4.min.css">

html

datetimepicker ID : search_start_time, search_end_time

datetime text ID : search_start_input, search_end_input

<!-- Start Time -->
<div class="form-group col-md-4">
  <div class="input-group">
	<div class="input-group date" id="search_start_time" data-target-input="nearest">
	  <span class="btn btn-warning iw-100">Start Time</span>
	  <input type="text" data-date-format='yyyy-mm-dd' class="form-control datetimepicker-input" id="search_start_input" name="search_start_input" data-target="#search_start_time"/>
	  <div class="input-group-append" data-target="#search_start_time" data-toggle="datetimepicker">
		  <div class="input-group-text"><i class="fa fa-calendar"></i></div>
		</div>
	</div>
  </div>
</div>

<!-- End Time -->
<div class="form-group col-md-4">
  <div class="input-group">
	<div class="input-group date" id="search_end_time" data-target-input="nearest">
	  <span class="btn btn-warning iw-100">End Time</span>
	  <input type="text" data-date-format='yyyy-mm-dd' class="form-control datetimepicker-input" id="search_end_input" name="search_end_input" data-target="#search_end_time"/>
	  <div class="input-group-append" data-target="#search_end_time" data-toggle="datetimepicker">
		  <div class="input-group-text"><i class="fa fa-calendar"></i></div>
		</div>
	</div>
  </div>
</div>

javascript

datetimepicker ID에 icon 및 format 적용

<script type="text/javascript">
    $(function () {
    
        //값 확인
        start_time = $('#search_start_input').val();
        end_time = $('#search_end_input').val();
    
        //Date and time picker
        $('#search_start_time').datetimepicker({ icons: { time: 'far fa-clock' }, format: 'yyyy-MM-DD HH:mm:ss' });
        $('#search_end_time').datetimepicker({ icons: { time: 'far fa-clock' }, format: 'yyyy-MM-DD HH:mm:ss' });
    });
</script>

python flask

datetime text ID로 값을 조회

#get 방식
@bts_blueprint.route('search/datetime_ex', methods=['GET'])
@login_required
def search_datetime_ex(name=''):
    
    #get 방식
    search_start_time = request.args.get('search_start_input')
    search_end_time = request.args.get('search_end_input')

    # 처리 로직
    var results = 0

    return results

#post 방식
@bts_blueprint.route('search/datetime_ex', methods=['POST'])
@login_required
def search_datetime_ex(name=''):

    search_start_time = request.form['search_start_input']
    search_end_time = request.form['search_end_input']
    
    # 처리 로직
    var results = 0
    
    return results