mysql のバックアップ(No.1)

Linux?

mysql

mysql server backup
- command backup
- backup script
その他

mysql server backup †

command backup †

mysqldump --opt --all-databases --events --default-character-set=binary -u root --password=password > /backupDirectory/mysqlDump.sql

↑

backup script †

[root@centos ~]# vi mysql-backup.sh作成
#!/bin/bash

PATH=$PATH:/usr/local/sbin:/usr/bin:/bin

# バックアップ先ディレクトリ
BACKDIR=/backup/mysql

# MySQLrootパスワード
ROOTPASS=xxxxxxxx

# バックアップ先ディレクトリ再作成
rm  -rf $BACKDIR
mkdir -p $BACKDIR

# データベース名取得
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# データベースごとにバックアップ
for dbname in $DBLIST
do
   table_count=`mysql -u root -p $ROOTPASS -B -e "show tables" $dbname|wc -l`
   [ $table_count -ne 0 ] &&
   mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
done

[root@centos ~]# chmod 700 mysql-backup.sh

↑

その他 †

↑

csv ファイル出力 †

SELECT * FROM テーブル名 INTO OUTFILE "ファイル名" FIELDS TERMINATED BY  ',' OPTIONALLY ENCLOSED BY '囲み文字の指定';

↑

グループしたカウントを条件に指定(group, count, mysql) †

having を使う

例

select column1,count(column2) as column2_num
from hoge_table
group by column2_num
having count(column2) = 1

↑

テーブルの作成_Create_Table †

↑

例1 †

create table testm (
  key1           char(008)     primary key,
  data1          int8,
  data2          int8,
  data3          int8
) type=InnoDB;

↑

例2 AUTO_INCREMENT, AUTO INCREMENT, PRIMARY KEY †

CREATE TABLE book2
    (
    id INT(11) NOT NULL AUTO_INCREMENT,
    title VARCHAR(64),
    author_name VARCHAR(32),
    detail TEXT,
    image VARCHAR(64),
    PRIMARY KEY (id)
    );

↑

例3(合わせ技), DB TYPE, DB CHARSET, ENCODE †

CREATE TABLE book2
    (
    id INT(11) NOT NULL AUTO_INCREMENT,
    title VARCHAR(64),
    author_name VARCHAR(32),
    detail TEXT,
    image VARCHAR(64),
    PRIMARY KEY (id)
    ) type=InnoDB DEFAULT CHARSET=utf8;

↑

カラムの追加 †

alter table personal add new_col_name varchar(20) after col_name;

↑

not exists の高速化 †

○○が含まれないデータを追加、などで not exists を使用する時があるが遅い。。。
結局高速化はindexに頼るしか無かったが、一応メモ書き

not exists -> left join.
残念だがあまり高速化されなかった。

not exists code

insert into table1 (colum1,colum2,colum3) 
select table2.colum1,table2.colum2,table2.colum3 
from table2
where not exists 
(select colum2 from table1
  where table2.colum1=table1.colum1 and 
  table2.colum2=table1.colum2 and 
  table2.colum3=table1.colum3)

left join

insert into table1 (colum1,colum2,colum3) 
select table2.colum1,table2.colum2,table2.colum3
from table2
left join table on 
table2.colum1=table1.colum1 and 
table2.colum2=table1.colum2 and 
table2.colum3=table1.colum3
where table1.colum1 is null;

indexを付ける。10倍位高速になりました。

ALTER TABLE table1 ADD INDEX index_name(colum1, colum2, colum3);
ALTER TABLE table2 ADD INDEX index_name(colum1, colum2, colum3);