とらりもんHOME  Index  Search  Changes  Login

とらりもん - MySQL Diff

  • Added parts are displayed like this.
  • Deleted parts are displayed like this.

!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;


!2.MYSQLの使い方
!!基本操作

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

データベースのリストを表示
mysql> show databases;

データベースの選択選択決定
mysql> use database<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