mysqldump --skip-add-locks --skip-lock-tables db table > test.dump.sql

▶ 덤프 도중 다른 세션에서 insert, update, delete 가능

mysqldump --single-transaction  db table > test.dump.sql

▶ 덤프 도중 다른 세션에서 insert, update, delete 가능

mysqldump --lock-tables  db table > test.dump.sql

▶ 덤프 도중 다른 세션에서 insert, update, delete 불가능

--single-transaction 옵션은 4.0.2 버전부터 추가되었으며, InnoDB에서만 사용 가능합니다.

출처: https://www.unclegom.com/5 [한가한 곰 아저씨의 작업장:티스토리]

MySQL 서버의 time out 설정은 크게 3가지가 있습니다. 
  mysqld time out 
    - connect_timeout (bad handshake timeout)      
    - interactive_timeout (interactive 모드에서 connection time out)      
    - wait_timeout (none interactive 모드에서 connection time out)  

connect_timeout, interactive_timeout 은 튜닝과 좀 거리가 멀고, 실제로 바쁜 서버라면, 반드시 wait_timeout 을 따로 튜닝하여 설정해줘야 합니다. 

[connect_timeout] 
이 설정은 mysqld 와 mysql client 가 연결(connection)을 맺기 위해서mysqld 가 연결 패킷을 기다리는 최대 시간입니다. 
즉 TCP 연결을 맺는 과정(3-way handshake)에서, connect_timeout 동안에도연결 패킷이 들어오지 않으면 연결이 실패(취소가 아님)되고,bad handshake 로 응답합니다. 

  *참고)  
        - 연결 실패 : 연결 과정중에서 fail 되는 경우 (Aborted_connects)      
        - 연결 취소 : 연결이 된 상태에서 강제로 close 된 경우 (Aborted_clients)  

다시 말하면 mysqld 와 mysql client 가 TCP 연결을 맺는 최대 시간으로이 시간보다 큰 경우는 모두 Aborted_connects 에 해당됩니다.(단위는 초) 
  연결 실패율(POF) =  (      Aborted_connects * 100 / Connections  ) 
  연결이 실패되는 경우        
        - 연결 패킷에 올바른 연결 정보가 없는 경우      
        - 특정 user 가 권한이 없는 데이터베이스에 접근할 경우      
        - mysqld 접근 password 가 틀린 경우      
        - connect_timeout 보다 긴 연결 과정 

연결 실패율(POF)이 높은 경우는, 대부분 권한이 없는 데이터베이스 연결이나, 틀린 password 를 사용할 경우가 많습니다. 
기본값은 대부분 5(초)로 설정되어 있으며, 따로 튜닝할 필요는 없습니다. 
mysqld 의 --warnings 옵션 사용과 xxx.err 파일에 기록됩니다. 


[interactive_timeout] 
interactive 모드에서 time out 을 말합니다. 
interactive 모드는 'mysql>' 과 같은 프롬프트 있는 콘솔이나 터미널 모드를말합니다. 
mysqld 와 mysql client 가 연결을 맺은 다음, 다음 쿼리까지 기다리는최대 시간을 의미합니다. 
설정된 interactive_timeout 까지도 아무런 요청(쿼리)이 없으면 연결은취소되고, 
그 이후에 다시 요청이 들어오면 연결은 자동으로 맺어집니다. 

interactive_timeout 안에 다시 요청이 들어오면 wait time은 0으로 초기화됩니다(CLIENT_INTERACTIVE). 

  ERROR 2006: MySQL server has gone away  No connection. Trying to reconnect...  Connection id:    12002  Current database: xxx 

이와 같은 연결 취소는 Aborted_clients 에 누계되고, wait_timeout 의결과도 함께 포함됩니다 
기본 값은 28800(8시간) 초로 설정되어 있는데 상당히 관대한 설정입니다. 
약 1시간(3600) 정도로 설정하는 것을 권장합니다. 


[wait_timeout] 
이 설정은 제일 중요한 파라메터 항목입니다. 
interactive 모드가 아닌 경우에 해당되며,mysqld 와 mysql client 가 연결을 맺은 후, 
다음 쿼리까지 기다리는최대 시간을 의미합니다. 
즉 대부분 PHP 나 C, PERL, python 등등의 API 를 이용한 client 프로그램모드를 말합니다. 
interactive_timeout 과 마찬가지로 wait_timeout 까지 아무런 요청(쿼리)이없으면 연결은 취소되고 
그 결과는 Aborted_clients 에 누계됩니다. 
wait_timeout 안에 다시 요청이 들어오면 wait time 은 0 으로 초기화 됩니다.(SESSION.WAIT_TIMEOUT) 
  연결 취소율(POC) =  (      Aborted_clients * 100 / Connections  ) 
  연결이 취소되는 경우(강제 종료됨)  
      - 종료(exit) 되기전 mysql_close() 가 없는 경우      
      - wait_timeout 이나 interactive_timeout 시간까지 아무런 요청(쿼리)이 없는 경우  

기본 값은 interactive_timeout 과 마찬가지로 28800(8시간) 초로 설정되어있는데, 역시 너무 관대한 설정이라고 할 수 있습니다. 
앞에서 연결 취소율(POC)을 계산해 보면, MySQL 서버가 어느 정도 비율로 강제종료하는지 알 수 있습니다. 
예를 들어 POC 가 1 % 이라면, 100 개의 커넥션당 하나 정도는 mysql_close()없이 강제 종료(exit)되고 있다는 의미입니다. 
이 값이 0 %에 가까울수록 좋습니다. 이 의미는 클라이언트 프로그램에서모두 정상적으로 종료했다는 의미입니다.

출처 : https://blog.naver.com/bomyzzang/221550485417

MariaDB에 하이픈(-)이나 점(.)을 포함한 이름으로 데이터베이스를 만들면 에러가 납니다.

예를 들어

라고 하면 다음과 같은 에러 메시지를 출력하면서 데이터베이스를 생성하지 못합니다.

이 문제를 해결하는 방법은 데이터베이스 이름을 낮은 액센트표(grave accent)로 감싸는 것입니다. 즉

와 같이 하면 됩니다.

출처 : https://www.manualfactory.net/10161

MySQL mysqldump 시 특정테이블만 백업 받는 방법

DB 테이블 덤프할 때 table_* 이런형태의 asterisk 별표를 사용한 특정 테이블만 받고 싶을때 사용할 수 있습니다.

방법은 여러가지가 있지만 아래처럼 하면 간단하게 백업을 받을 수 있습니다.

이렇게 하면 에러가 나지요~

이런 방법으로 백업이 가능합니다.

$() 변수로 받는다는 의미입니다.

괄호 안에 있는 내용을 먼저 테스트 해보고 사용하면 됩니다.

출처: https://ivps.tistory.com/204 [iVPS 가상서버호스팅]

확인된 환경 : MySQL 5.0x, MariaDB 10.1.x,

전체 DB 언어셋 및 collation 확인

SELECT SCHEMA_NAME AS 'database', DEFAULT_CHARACTER_SET_NAME AS 'character_set', DEFAULT_COLLATION_NAME AS 'collation' FROM information_schema.SCHEMATA;

특정 db 언어셋 확인

show create database DB명; (Collation확인은) use DB명 → SHOW VARIABLES LIKE 'character_set_database';

특정 테이블 언어셋 확인

show create table 테이블명;  (Collation확인은) SHOW TABLE STATUS WHERE NAME LIKE '테이블명';

특정 테이블 칼럼별 collation 확인

SHOW FULL COLUMNS FROM 테이블명;

특정 데이터베이스의 default character set 수정방법

alter database 디비명 default character set = utf8;

특정 테이블의 default character set 수정방법

alter table 테이블명 default character set = utf8;

[출처] http://www.legendry.net/board_YNNJ65/783

Mysql 4.0 ( euc_kr ) 환경에서 Mysql 5.x ( UTF-8) 환경으로 데이터 이전시

레코드에 포함된 한글 문자열이 모두 깨져서 입력된 경우 이를 올바르게

처리했던 성공사례를 기록한다.

A서버 (Mysql 4.0)

mysqldump -u root -p --default-character-set=euc_kr DB명 > DB명.sql

scp 명령을 이용하여 B서버 (Mysql 5.x) 로 파일 복사

B서버 (Mysql 5.x)

vi 에디터로 sql 파일 내의 TYPE=MyISAM 을 ENGINE=MyISAM 으로 모두 치환

iconv -c -f CP949 -t UTF-8 DB명.sql > DB명_UTF8.sql

CP949 언어셋에 대한 상세 내용은 아래 링크 참조

https://ko.m.wikipedia.org/wiki/%EC%BD%94%EB%93%9C_%ED%8E%98%EC%9D%B4%EC%A7%80_949

새롭게 생성된 UTF-8용 sql 파일을 B서버의 DB명 에 import 시킨 후 phpmyadmin 과 같은

프로그램에서 문자열이 깨지지 않는지 체크해 본다.

1. between 응용

특정 범위 내의 데이터를 조회 하고 싶을 때

select * from 테이블명 where 필드명 between 값1 and 값2;

ex) board01 테이블의 no 필드가 1부터 20 까지의 모든 데이터를 추출할 경우

select * from board01 where no between 1 and 20; 

초기 mysql root 패스워드 설정 및 비번 변경과 root 비번 잃어버렸을 경우 변경 하는방법

1. 초기 mysql root 패스워드 설정하기

mysql 서버에 패스워드 없이 로그인 하게되면 서버에 만들어진 데이터베이스나 테이블
기타 이곳에 저장된 자료가 외부인에게 노출될 수 있다. 따라서 이러한 보안을 목적으로
root 패스워드를 지정하면 mysql 서버를 안전하게 보호할 수 있다.

mysql> use mysql;
mysql> update user set password=password('123456') where user='root';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges; <--- 적용
flush privileges; 를 하지 않으면 mysql에서 빠져나와서 root 로그인이 안된다.
확인한다.
mysql> select host, user, password from user;
+----------------------------------------------------------------+
| Host user password |
+----------------------------------------------------------------+
| localhost root 2e01146f5c065853 |
| localhost.localdomain root 2e01146f5c065853 |
+----------------------------------------------------------------+

mysql> \q
Bye
------------------------------------------------------------------------------------
[주의]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
그대는 mysql 을 shutdown 하거나 reload 하면 된다.
# /usr/local/mysql/bin/mysqladmin -u root -p shutdown
하면 패스워드를 묻는데 이때 입력하면 설정한 mysql이 적용이된다.
# /usr/local/mysql/bin/mysqladmin -u root -p reload;
------------------------------------------------------------------------------------

# mysql -u root -p [Enter] // root 패스워드로 로그인
Enter password: ******

※ Mysql 서버에 root 패스워드가 설정되면 이제 부터는 mysql 서버에 접속하기 위해서는
위와 같이 -u 뒤에 root입력하고 -p 옵션을 사용하여 패스워드를 사용한다는 것을 명시해야 한다.
그리고 엔터를 치면 root 패스워드를 입력받기 위해 커서가 멈춘다.
(mysql 명령어만 입력하고 엔터를 치면 패스워드를 묻지 앟고 바로 mysql> 프롬프트가 나온 옛 시절을 생각해 보면 금방 차이를 느낄수 있음)

2. 루트 비번 변경

mysql> use mysql;
mysql> update user set password=password('123456') where user='root';
mysql> flush privileges;

3. root 패스워드를 분실한 경우(응급조치)

mysql을 오랫동안 사용하지 않았을 경우에 간혹 root패스워드가 기억나질않아서 당황할 때가 있습니다. 시스템관리자라면 시스템의 root나 MySQL의 root의 암호를 잊어 버렸을 때를 대비해서 패스워드를 새로 설정하는 방법을 반드시 숙지하고 있어야 할 것입니다.

① 실행중인 msyql 종료

# ps -ef | grep mysqld
root 9567 1 0 Mar16 ? 00:00:00 sh ./mysqld_safe
root 9576 9567 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld
root 9578 9576 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld
root 9579 9578 0 Mar16 ? 00:00:00 /usr/local/mysql/libexec/mysqld

# killall mysqld

② grant-table 미사용모드로 mysql시작(권한 테이블을 사용하지 않는 옵션으로 데몬 실행)

nt일 경우 :
c:\program files\mysql\mysql server 4.1\bin>mysqld-nt --standalone --skip-grant-tables
만약 Error 2003 Hy000 Can't connect to MySQL server on 'localhost' 와 같은 메세지가 나온다면

c:\program files\mysql\mysql server 4.1\bin>mysqld --defaults-file="c:\program files\mysql\mysql server 4.1\my.ini" --consol --skip-grant-tables
따로 프롬프트는 떨어지지 않으므로 새로 cmd창을 열어서 접속해 본다.

# ./safe_mysqld --skip-grant-tables &
[1] 12084
# Starting mysqld daemon with databases from /usr/local/mysql/data
#

# ./mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.22.24
Type 'help' for help.
mysql>

※ mysqld_safe 명령어는 mysql 데몬을 실행시킨다.

③ update문으로 root사용자 패스워드 갱신

mysql> update user set password=password('123') where user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> \q
Bye

④ 실행중인 mysql 다시 종료(권한 테이블을 사용하지 않는 데몬을 종료)

# ps -ef | grep mysqld
root 12084 11558 0 20:10 pts/2 00:00:00 sh ./mysqld_safe --skip-grant-ta
root 12090 12084 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12092 12090 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12093 12092 0 20:10 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
#
# killall mysqld
mysqld daemon ended
[1]+ Done ./mysqld_safe --skip-grant-tables
#

⑤ Mysql 데몬 재 실행 후 갱신된 패스워드로 로그인

# ./safe_mysqld &
[1] 12102
# Starting mysqld daemon with databases from /usr/local/mysql/data
#

# ps -ef | grep mysql
root 12102 11558 0 20:13 pts/2 00:00:00 sh ./mysqld_safe
root 12108 12102 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12110 12108 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld
root 12111 12110 0 20:13 pts/2 00:00:00 /usr/local/mysql/libexec/mysqld

# mysql -u root -p
Enter Password: *************** 

<권한설정>