WASHIMI@: MySQL

ラベル MySQL の投稿を表示しています。すべての投稿を表示

2013年2月5日火曜日

MySQLとテキストデータ（CSV）間でインポートとエクスポート

昨日に引き続き、MySQLネタを引越し作業を兼ねて残しておく。
よく使うのが、MｙSQLとCSVデータとのやりとり。

MySQLからテキストデータ（CSV）へ出力

SELECT * FROM [TableName] INTO OUTFILE "/home/user/[FileName]" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

※CSVファイルを出力するフォルダに権限を与える必要がある

テキストデータ（CSV）からMySQLへインポート

load data local infile "/path/test.csv" into table [table_name] fields terminated by ',';

2013年1月31日木曜日

MySQL基本コマンド

SQLコマンドは、しばらく使わないとすぐに忘れてしまう。備忘録の引越し作業も兼ねて、ここに基本コマンドを残しておこう。

MySQLへの接続

mysql -u[user name] -p

データベースへの接続

mysql -u[user name] -p [database name]

データベースへのアクセス

use [database name];

データベースの作成

create database [database name] character set utf8;

データベースの確認

show databases;

ユーザ管理

ユーザの追加 select,insert,update,delete,file 実行権限を持つユーザ

grant select,insert,update,delete,file on *.* to username identified by 'password';

ユーザの削除

DELETE FROM mysql.user WHERE user='ユーザ名';

ユーザの確認

SELECT host,user FROM mysql.user;

ユーザ権限の確認

SHOW GRANTS FOR [ユーザ名];

ユーザ権限のリフレッシュ データベースの権限テーブルから権限を再読み込みする

FLUSH PRIVILEGES;

テーブル

テーブルの作成

create table [table name] ([フィールド名] char(サイズ), [フィールド名] int, ...);

テーブルの確認

show tables;

テーブルの削除

drop table [テーブル名];

フィールド

フィールドの確認

show fields from [table name];

フィールド名の変更

alter table [tablename] change [旧フィールド名] [新フィールド名] [型];

フィールドの型（属性）変更

alter table [テーブル名] modify [フィールド名] [型];

フィールドの追加

alter table [テーブル名] add [フィールド名] [型];

レコード

レコードの追加(insert)

insert into [テーブル名] values ([フィールド1の値], [フィールド2の値], ...);

レコードの表示（select）

select * from [テーブル名];

フィールドを限定してレコードを表示（select）

select [フィールド名] from [テーブル名];

条件指定（where）

select * from [テーブル名] where [条件式];

レコードの修正（update）

update [テーブル名] set [フィールド名]=[修正値] where [条件式];

全レコードの修正（update）

update [テーブル名] set [フィールド名]=[修正値];

フィールドの値で更新（update）

update [テーブル名] set [フィールド名]=[フィールド名];

レコードの削除（delete）

delete from [テーブル名] where [条件式];

全レコード削除（delete）

delete from [テーブル名];

インポート

データのインポート

load data infile "テキストファイル" into table [テーブル名];

ソート

並び替え(昇順)

select * from [テーブル名] order by [フィールド名];

並び替え(降順)

select * from [テーブル名] order by [フィールド名] desc;

連番とindex

インデックスの作成

alter table [テーブル名] add index [インデックス名]([連番フィールド]);

連番フィールドの作成

alter table [テーブル名] modify [連番フィールド] int auto_increment;

※レコードの追加では値をあたえる（とりあえず 0をあたえると連番が自動で入る）

インデックスの連番でidフィールドを追加する場合

alter table testtable add id int;
alter table testtable add index index1(id);
alter table testtable modify id int auto_increment;

2012年3月20日火曜日

my.cnfの編集

基本UTF-8をベースとします。

# vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

default-character-set=utf8
skip-character-set-client-handshake

character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

設定の反映

# /etc/rc.d/init.d/mysqld restart
mysqld を停止中:                                           [  OK  ]
mysqld を起動中:                                           [  OK  ]

php.iniの編集

こちらもUTF-8を標準設定します。

# vi /etc/php.ini

//デフォルトの文字コード
;default_charset = "iso-8859-1"
default_charset = "UTF-8"

//タイムゾーン
;date.timezone =
date.timezone = "Asia/Tokyo"

//利用言語の設定
;mbstring.language = Japanese
mbstring.language = Japanese

//マルチバイト文字列関数のデフォルト文字コード
;mbstring.internal_encoding = EUC-JP
mbstring.internal_encoding = UTF-8

//HTTP入出力の文字コード変換。passにして、自動変換offとする。
;mbstring.http_input = auto
mbstring.http_input = pass

;mbstring.http_output = SJIS
mbstring.http_output = pass

;mbstring.encoding_translation = Off
mbstring.encoding_translation = Off

//文字コード自動判別に優先する文字コード
:mbstring.detect_order = auto
mbstring.detect_order = auto

//変換に失敗した場合に表示する文字
;mbstring.substitute_character = none;
mbstring.substitute_character = none;

//オーバーロードされる関数の指定（今回はオーバーロードしない）
;mbstring.func_overload = 0
mbstring.func_overload = 0

//文字コードの自動判別を厳密に行うかの設定
:mbstring.strict_detection = Off
mbstring.strict_detection = Off

//MIMEタイプの指定（空白）
;mbstring.http_output_conv_mimetype=
mbstring.http_output_conv_mimetype=

設定の反映

# /etc/rc.d/init.d/httpd restart
httpd を停止中:                                            [  OK  ]
httpd を起動中:                                            [  OK  ]

2012年3月19日月曜日

MySQLデータベースのバックアップ

旧サーバのMySQLデータベースのバックアップをとる、
下記コマンドで、すべてのテーブルをロックして、1つのファイルにダンプする

$ mysqldump -u [username] -p -x --all-databases > [filename]

バックアップしたMySQLデータベースのリストア

新サーバへバックアップしたMySQLデータベースをリストアする

$ mysql -u [username] -p < [filename]

MySQLデータベースの引越しは以上、とても簡単。

ユーザの追加

旧サーバで使用していた user を追加。
下記は、select,insert,update,delete,file 実行権限を持つユーザを追加する場合の例

mysql> grant select,insert,update,delete,
file on *.* to [username] identified by 'password';
Query OK, 0 rows affected (0.00 sec)

データベースの確認

$ mysql -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| public             |
| userdb             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

コマンドの覚書

MySQLへの接続
    mysql -u[user name] -p
データベースへの接続
    mysql -u[user name] -p [database name]
データベースへのアクセス
    use [database name];
データベースの確認
    show databases;

2012年3月1日木曜日

LAMPインストール

LinuxのRPM互換パッケージ管理システムであるyumを使ってLAMPをインストールする。
LAMPとは、Linux＋Apache＋MySQL＋PHPのことである。

まずはパッケージの確認

# yum list install httpd php mysql-server
httpd.i686                             2.2.15-15.el6.centos.1
mysql-server.i686                      5.1.61-1.el6_2.1
php.i686                               5.3.3-3.el6_2.6

確認ができたらインストール

# yum install httpd php mysql-server

次に追加モジュールのインストール、まずはパッケージの確認

# yum list install mod_auth_mysql php-mysql php-mbstring
 php-gd perl-Net-SSLeay expect

追加モジュールのインストール

# yum install mod_auth_mysql php-mysql php-mbstring
 php-gd perl-Net-SSLeay expect

Apacheの設定

# vi /etc/hosts

# vi /etc/httpd/conf/httpd.conf

（下記項目の値を変更）

MySQLの設定

# vi /etc/my.cnf

（下記を追加）

MySQLの再起動

# /etc/rc.d/init.d/mysqld start

rootでログインし、文字コードを確認

mysql -u root
mysql> show variables like 'char%';

MySQLパスワード設定

localhost,Sever,127.0.0.1パスワード設定をそれぞれ設定する
set password for root@localhost = password('パスワード');
set password for root@myserver = password('パスワード');
set password for root@127.0.0.1 = password('パスワード');

パスワードの確認

mysql> select host,user,password from mysql.user;

自動起動の設定

Apacheの自動起動設定

# /etc/rc.d/init.d/httpd start
# chkconfig httpd on

設定の確認

# chkconfig --list httpd

MySQLの自動起動設定

# chkconfig mysqld on