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

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
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)

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

↑

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 が長い文字列の場合に省略しない

↑

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
```