mysql

Linux

mysql

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.
  残念だがあまり高速化されなかった。
  1. 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)

  2. 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, less †

• 設定

  pager less -S

• 解除

  nopager

ユーザーの作成 †

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

ユーザーの削除 †

DROP USER 'hoge'@'sampleHost';

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

1. グローバルレベル

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

2. データベースレベル

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

3. テーブルレベル

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

4. カラムレベル

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

5. 権限の確認

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

mysqldump: Got error: 1556 †

1. 「--lock-tables=0」を追加
2. 例

   mysqldmup -h mysql_server -uroot -p  --lock-tables=0 database_name > mysql_dump.sql

コンソール接続をどこから許すか †

1. bind-address
   • 複数指定はできないので注意
   • 例1)

     bind-address = 127.0.0.1

   • 例2) 全許可

     bind-address = 0.0.0.0

   • ユーザーを作成する際、アクセス元を指定して絞るのがお勧め

Version 5.7 †

CentOS7 へのインストール †

リンク †

1. CentOS7に mysql-community-server をインストールし、LAN内の別マシンからアクセス可能にする。(外部)
2. centOS7でLAMP環境を作る(外部)

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

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

リンク(外部) †

• CentOS7.2でのMySQLインストールでの問題
• CentOSのMySQLを5.1から5.7に更新する手順

原因 †

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

対策 †

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

  mysql_upgrade -u root -p