MySQL

<1> ソースコードより ※おすすめしない。メモ程度。

mysql-5.5.8.tar.gz をダウンロード。

# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/mysql-5.5.8.tar.gz
# mv mysql-5.5.8.tar.gz /usr/local
# cd /usr/local
# tar zxvf mysql-5.5.8.tar.gz
# cd mysql-5.5.8
# apt-get install g++ cmake

sudo aptitude install paco

MYSQL Download http://dev.mysql.com/downloads/

$ tar xvf mysql-5.5.8.tar.gz
$ cd mysql-5.5.8

The mysql ver 5.5.8 doesn't have configure file. Aternatively that has configure.cmake file. So you have to install narrow packages.

$ sudo aptitude install cmake libncurses5-dev libncursesw5-dev sysv-rc-conf g++
$ sudo apt-get install bison
$ sudo cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

$ sudo make
error が出る場合
$ vi config.h.cmake
#cmakedefine SOCKET_SIZE_TYPE @SOCKET_SIZE_TYPE@
を以下に書き換える。
#cmakedefine SOCKET_SIZE_TYPE size_t
もう一度cmakeを行い、makeしてみる。

$ sudo paco -D make install
paco is tool to manage software safly.

$ cd /usr/local/mysql
$ sudo scripts/mysql_install_db --user=mysql --no-defaults

Setting logfile

$ sudo mkdir /var/log/mysql/
$ sudo touch /var/log/mysql/error.log
$ sudo touch /var/log/mysql/query.log
$ sudo touch /var/log/mysql/slow.log
$ sudo chmod 755 /var/log/mysql/
$ sudo chmod 666 /var/log/mysql/*
$ sudo chown -R mysql. /var/log/mysql/

Setting value owner

$ sudo chown -R root:root /usr/local/mysql
$ sudo chown -R mysql:mysql /usr/local/mysql/var
$ vi .bashrc
export PATH=$PATH:/usr/local/mysql/bin
$ source .bashrc

<2>パッケージ

• make environment

sudo apt-get install mysql-server
sudo su
mysql -u root -p
enter : passwd

• start

/etc/init.d/mysql start

• stop mysql server

/etc/init.d/mysql stop

• show databases

SHOW DATABASES;

• select databese

mysql> use "NAME of database";
mysql> select database();

• create database

mysql> create database tesdb ;

• delete database

mysql> drop database "NAME of database " ;

• adduser

mysql> create user 'www-data'@'localhost' identified by 'passwd' ;
mysql> www-data, ryuiki

• setting user option

mysql> grant all on testdb.* to 'kentaro'@'localhost' ;

• if you use databases from other networks.

mysql> grant all on testdb.* to 'kentaro'@'%' ;
※In this case , you can not this database from localhost.

• if you want to change passwd.

mysql> set password  for kentaro@localhost=password('newpasswd');

• delete user

if your version is 5.0.2 or before
mysql> remove all on testdb.* from 'kentaro'@'localhost'
mysql> drop user kentaro@localhost

• ユーザーのリストを表示

mysql> select Host, User, Password from mysql.user;

基本操作

起動

$ mysql -u username -p
→ パスワードを入れる。

データベースのリストを表示

mysql> show databases;

データベースの選択決定

mysql> use <databasename>;

テーブルのリストを表示

show tables;

ひとつのテーブルのフィールド仕様を表示

show fields from <tablename>;

テーブル内の全データを表示

mysql> select * from <tablename>;

create table

mysql> create table "$NAME" (name1 type, name2 type , name3 type ,,,,);
#delete table
mysql> drop table "$NAME"

setting main key

mysql> create table "$NAME" (name1 type primary key, name2 type , name3 type ,,,,);

show tabel

mysql> desc staff;

setting NOTNULL

mysql> create table "$NAME" (name1 type primary key, name2 type not null, name3 type ,,,,);

add record

mysql> insert into "$NAME" values ('140.01', '36.55','4');
mysql> insert into "$NAME" ( east , landsurface) values ('140.01', '4');

show all record

mysql> select * from "$NAME";

search record

ex1)
mysql> select east,north from '$NAME';

ex2)
mysql> select east as '経度' , north as '緯度' from '$NAME';

ex3)
mysql> select * from '$NAME' where east <= 140;
*=,!=,IN(there are any word)

ex4)
mysql> select * from '$NAME' where landsurface link '1%' ;
*not link

make group

mysql> select * from test by landsurface
*only show one of avarege from landsurface

sort recode

mysql> select ***** order by east
*desc <--- reverce

output textfile

mysql> select * from test into outfile 'test.txt'
*default = /var/lib/mysql/test/

シェルスクリプトとMySQL

 #!/bin/bash
   mysql -u user_name -pxxxxxx << EOF
   SQL文;
   SQL文;
   SQL文;
   EOF

$ mysql -u user_name -pxxxxxx -e “"SQL文"

「;」で区切れば複数のSQL文発行も可能。

$ mysql -u user_name -pxxxxxx -e “SQL文;SQL文;SQL文”

$ echo “SQL文;” | mysql -u user_name -pxxxxxx

$ mysql -u user_name -pxxxxxx < file_name

$ mysql -u user_name -pxxxxxx db_name < file_name

$ mysql < file_name | less

$ mysql -u user_name -pxxxxxx -e “SQL文;” > outfile