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

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
mysql へ行く。
- 1 (2018-10-22 (月) 18:12:40)
- 2 (2018-10-23 (火) 10:25:04)
- 3 (2019-02-23 (土) 14:58:06)
- 4 (2019-03-20 (水) 19:21:06)
- 5 (2019-08-24 (土) 09:44:58)
- 6 (2019-12-22 (日) 12:06:41)
- 7 (2020-11-08 (日) 21:20:45)
- 8 (2021-07-05 (月) 19:12:03)
- 9 (2021-11-27 (土) 17:57:19)
- 10 (2023-02-03 (金) 16:38:31)

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;

↑

カラムの型変更 †

alter table テーブル名 modify カラム名型;
例
```
alter table goods modify no smallint;
```

↑

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