#!/usr/bin/perl

#use Mysql;

use DBI;

use DBD::mysql;


# CONFIG VARIABLES

$platform = "mysql";

$database = "MYDATABASE";

$host = "localhost";

$port = "3306";

$user = "MYUSER";

$pw =   "MYPASSWORD";


# DATA SOURCE NAME

$dsn = "dbi:mysql:$database:$host:$port";


# PERL DBI CONNECT

$connect = DBI->connect($dsn, $user, $pw);


fetch_db("MYTABLE");


sub fetch_db() {

my($table) = @_;

my $file = $table . ".txt";


open($FH, ">$file") or die("Can't open : $file $!\n");


print "테이블  $table\n";

print $FH "테이블  $table\n";


get_count($table);



print $FH "\n";

print "\n";


print $FH "---END--\n";

close $FH;



}


sub get_count() {


my($table) = @_;

# PREPARE THE QUERY

$query = "select TYPE, CAST(STORE_ID AS UNSIGNED INTEGER), count(*) from $table group by TYPE, CAST(STORE_ID AS UNSIGNED INTEGER)";

$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY

$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES

$query_handle->bind_columns(undef, \$id, \$type, \$cnt);

print "* 샵 별 건수\n";

print $FH  "* 샵 별 건수\n";

# LOOP THROUGH RESULTS

print "TYPE\tSHOP_ID\tCNT\n";

print $FH "TYPE\tSHOP_ID\tCNT\n";

print "-------\t----\t---\n";

print $FH "-------\t----\t---\n";

while($query_handle->fetch()) {

  print "$id\t$type\t$cnt\n";

  print $FH "$id\t$type\t$cnt\n";



출처:http://stackoverflow.com/questions/3613704/mysql-show-current-connection-info


mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.5.8, for Win32 (x86)

Connection id:          1
Current database:       test
Current user:           ODBC@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.5.8 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 7 min 16 sec

Threads: 1  Questions: 21  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.48
--------------

mysql>



etc/my.cnf 에 등록하면 된다


참조:http://stackoverflow.com/questions/14432299/should-etc-my-cnf-be-set-to-add-character-set-server-utf8-and-default-character

 

 /etc/my.cnf.


[mysqld]

default-character-set=utf8   
character-set-server=utf8  
skip-character-set-client-handshake  
collation-server=utf8_unicode_ci  
init-connect='SET NAMES utf8'  
init_connect='SET collation_connection = utf8_general_ci'  
port = 3306



츨처:https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html



mysql> create table test3 (a int primary key, b int ,c int);

Query OK, 0 rows affected (0.02 sec)


mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 1 row affected (0.01 sec)


mysql> select * from test3;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    2 |    3 |

+---+------+------+

1 row in set (0.00 sec)


mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 2 rows affected (0.00 sec)


mysql> select * from test3;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    2 |    4 |

+---+------+------+

1 row in set (0.00 sec)


mysql> INSERT INTO test3 (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

Query OK, 2 rows affected (0.00 sec)


mysql> select * from test3;

+---+------+------+

| a | b    | c    |

+---+------+------+

| 1 |    2 |    5 |

+---+------+------+

1 row in set (0.00 sec)


mysql 테이블 크기

mysql 2014.02.27 11:08

출처:http://blog.mongee.net/7

select table_name,(data_length)/(1024*1024) as 'DATA(MB)',(index_length)/(1024*1024) as 'INDEX(MB)' from information_schema.tables where table_name like '%_TBL';




새 PC 는 windows7. 기존 xp 에서 쓰던 mediawiki 1.11.1 이 작동되지 않는다.


1. 데이터베이스 백업

새 pc 에 wamp  새로 설치하고, 옛 pc 에서 mysql db 데이터를 dump 했다.

command 창에서 mysqldump.exe 디렉토리 까지 들어가서 mysqldump 하면 된다.


2. 새 장비에서 데이터베이스 복구

새 pc 에서도 마찬가지로 mysql.exe 이 있는 디렉토리에서 mysql 복구하면 된다


E:\wamp\bin\mysql\mysql5.5.16\bin>m
Enter password:

E:\wamp\bin\mysql\mysql5.5.16\bin>


3. 업데이트 실행 (데이터베이스 구조 수정)

이 단계를 빠뜨리면 글이 입력되기는 하지만 아래 에러 메시지가 출력된다 

 Unknown column 'cl_sortkey_prefix' mediawiki

역시 command 창에서

E:\wamp\bin\php5.3.8>php.exe E:\wamp\www\mediawiki1.18.1\maintenance\update.php


를 실행하면 아래 처럼 화면이 출력되면서 DB 구조가 바뀐다.