とらりもんHOME  Index  Search  Changes  Login

MySQL

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 <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
Last modified:2015/08/09 13:00:07
Keyword(s):
References:[とらりもんHOME]