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

Linux?

mysql

Common
- mysql server backup
- その他
Version 5.7
- CentOS7 へのインストール
- 旧バージョンからデータで上書きした際、起動できなくなる(バックアップ, リストア)

Common †

mysql server backup †

command backup 1 †

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

ユーザー名とパスワードを外部ファイル化する。

ログに以下が書かれるようになる

mysqldump: [Warning] Using a password on the command line interface can be insecure.

conf ファイルの作成

vi mysqlDump.conf

[mysqldump]
user=username
password=password

バックアップコマンド

mysqldump --defaults-extra-file=/hogehoge/mysqlDump.conf --opt --all-databases --events --default-character-set=binary > /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

↑

command backup restore 2 †

参考リンク
- mysqldumpまとめ(外部)
- MySQLダンプ、リストア(外部)

バックアップ(ダンプ, dump)

データベース

mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME

テーブル

mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME TABLE_NAME > OUTPUT_FILE_NAME

リストア

mysql -u username -ppassword -D DBname < dumpfilename.sql

↑

その他 †

↑

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 [#t8afa814]

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

例2 AUTO_INCREMENT, AUTO INCREMENT, PRIMARY KEY [#w456b100]

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 [#i2fb695c]

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;

↑

テーブル構造の変更 †

カラムの追加 [#xad2682e]

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

カラムの型変更 [#ga2b81a0]
- 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);

↑

バージョン情報の表示 †

mysql --version

↑

Global変数の表示 †

SHOW GLOBAL VARIABLES

↑

状態の表示 †

SHOW GLOBAL STATUS

↑

Process の表示 †

SHOW PROCESSLIST
SHOW FULL PROCESSLIST;  ← Info が長い文字列の場合に省略しない

↑

ページ, pager, †

設定
```
pager less -S
```
解除
```
nopager
```

↑

ユーザーの作成 †

CREATE USER 'hoge'@'sampleHost' IDENTIFIED BY 'password';

↑

ユーザーの削除 †

DROP USER 'hoge'@'sampleHost';

↑

作成したユーザーの権限変更 †

グローバルレベル

GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;

データベースレベル

GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON dbName.* TO user;

テーブルレベル

GRANT ALL ON db_name.table_name TO user;
GRANT SELECT, INSERT ON db_name.mytbl TO user;

カラムレベル

GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;

権限の確認

show grants for 'ユーザー名'@'ホスト名';

↑

テーブルが壊れた時の修正。「Table is marked as crashed and should be repaired」 error, 修正。 †

参考サイト：pentan.info『Table is marked as crashed and should be repaired』の修復方法
自分の場合「/var」の容量が枯渇した際に発生。

破損状況の確認

check table `【テーブル名】`;

例)
```
check table tableName;
```

結果

+-----------+-------+----------+----------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                     |
+-----------+-------+----------+----------------------------------------------+
| tableName | check | warning  | Table is marked as crashed                   |
| tableName | check | error    | Size of datafile is: 0         Should be: 84 |
| tableName | check | error    | Corrupt                                      |
+-----------+-------+----------+----------------------------------------------+

tableの修復

repair table `【テーブル名】`;

例)
```
repair table tableName;
```

再度確認

check table `【テーブル名】`;

例)
```
tableName
```

結果

+-----------+--------+----------+----------+
| Table     | Op     | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| tableName | repair | status   | OK       |
+-----------+--------+----------+----------+

↑

Version 5.7 †

↑

CentOS7 へのインストール †

↑

リンク †

↑

MariaDBの削除 †

sudo yum remove mariadb-libs
sudo rm -rf /var/lib/mysql

↑

リポジトリファイルのインストール †

yum install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

↑

MySQLをインストール †

sudo yum install mysql-community-server mysql-community-client

↑

php-mysql のインストール †

yum install --enablerepo=remi,remi-php70 php-mysqlnd

↑

サービスの起動 †

sudo systemctl start mysqld

↑

初回パスワードの確認 †

sudo cat /var/log/mysqld.log | grep password

↑

初回設定 †

mysql_secure_installation

↑

サービスの再起動 †

sudo systemctl restart mysqld

↑

サービスの自動起動 †

設定
```
sudo systemctl enable mysqld
```
確認
```
sudo systemctl status mysqld
```

↑

旧バージョンからデータで上書きした際、起動できなくなる(バックアップ, リストア) †

↑

リンク(外部) †

↑

原因 †

ユーザーのパスワードが上書きされる
そのパスワードが新しいサーバーのパスワードポリシーに合っていない

↑

対策 †

Safe mode(セーフモード)で起動(CentOS7 の場合)
- systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
- systemctl start mysqld
mysql の upgrade
```
mysql_upgrade -u root -p
```