MySQL/Tips の履歴(No.8)

• 履歴一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• MySQL/Tips へ行く。
  • 1 (2017-08-17 (木) 06:24:51)
  • 2 (2017-08-17 (木) 06:28:44)
  • 3 (2018-06-13 (水) 15:13:06)
  • 4 (2019-10-25 (金) 13:58:00)
  • 5 (2019-10-26 (土) 12:41:48)
  • 6 (2019-10-26 (土) 23:54:17)
  • 7 (2020-07-28 (火) 14:11:44)
  • 8 (2022-04-11 (月) 15:28:00)

Tag: MySQL/Tips

目次†

関連ページ†

参考情報†

データベース全般†

InnoDBのインデックスの最大キー長†

• 第32回　InnoDBインデックスの最大キー長について：MySQL道普請便り｜gihyo.jp … 技術評論社
• 基本的に767バイト、innodb_large_prefixを有効にするなどして拡張すれば最大3072バイトまで拡張できる。
• ただしutf8では1文字3バイト、utf8mb4では1文字4バイトでとなる。
• MySQLのテーブル定義は文字数で指定するので、最大3072バイトまで拡張している場合でもutf8の場合「varchar(1024)」、utf8mb4の場合「varchar(768)」が限界となる。
• 拡大していない場合は「varchar(255)」「varchar(191)」が限界となる。

文字コードを確認する†

• 以下のコマンドを実行する

  show variables like '%char%';

全データベースをバックアップしてリストアする†

• 以下のコマンドでバックアップ。

  mysqldump --single-transaction --default-character-set=binary --quick -uroot -p --all-databases | bzip2 > database.sql.bz2

• 以下のコマンドでリストア

  bzcat database.sql.bz2 | mysql

全データベースを削除する†

• Drop All Databases in MySQL | Mohinish's Blogによると以下のコマンドで削除できる

  mysql -uroot -p  -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | grep -v OLD |gawk '{print "drop database " $1 ";select sleep(0.1);"}' | mysql -uroot -ppassword

• もしくは削除するSQLを作成できる

  mysql -uroot -pxxxxx  -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema| grep -v test | grep -v OLD | gawk '{print "drop database " $1";select sleep(0.1);"}' > droppeddatabases.sql

• なお"grep -V"はinverse matchの意味。Databaseやmysqlは除外するという意味になる。またmysql -eの出力は表形式だがリダイレクトすると罫線部分は除外される模様。上では除外されていないがperformance_schemaも除外したほうがいいかも？ mysql -uroot -pxxxxx -e "show databases" | grep -v Database | grep -v mysql | grep -v information_schema| grep -v test | grep -v OLD | gawk '{print "drop database " $1";select sleep(0.1);"}' > droppeddatabases.sql
• さらにシンプルに

  mysql -e "show databases" | grep -Ev 'Database|^test$|mysql|_schema' | awk '{print "drop database "$1";"}' > dropdatabase.sql

ibdataを削減する†

参考情報†

• 肥大化したibdata1を縮小する - Qiita
• innodb - What is the best way to reduce the size of ibdata in mysql? - Database Administrators Stack Exchange

手順(上記2番目のリンクに基づく)†

• 全てのデータベースをダンプする

  mysqldump --single-transaction --default-character-set=binary --quick -uroot -p --all-databases | bzip2 > database.sql

• mysql、performance_schema、information_schema以外の全てのデータベースをドロップ
• mysqlをシャットダウン
• my.cnfで以下を確認

  [mysqld]
  innodb_file_per_table = 1

• ibdata1、ib_logfile0、ib_logfile1を削除。
• mysqlを起動
• バックアップしたデータベース(SQL)をリストア。mysqlデータベース込みでリストアした場合ユーザーやパスワードも変わるので注意。

疑問†

• ibdata1を削除したときユーザー名やパスワードは維持されるのか(mysqlのデータ)？