Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL Parameters の裏側 / MySQL Parameters backend

MySQL Parameters の裏側 / MySQL Parameters backend

とみたまさひろ

November 25, 2020
Tweet

More Decks by とみたまさひろ

Other Decks in Technology

Transcript

  1. mysqld のコマンドラインパラメータの値 mysql のコマンドラインパラメータの値 サーバー変数の値 ステータスの値 Character set 名 Collation

    名 権限名 関数名 information_schema のテーブル名とカラム名 performance_schema のテーブル名とカラム名 エラーメッセージ 4
  2. データは JSON データは JSON バージョン情報 { "8.0.22": "json/8.0.22.json", "8.0.21": "json/8.0.21.json",

    "8.0.20": "json/8.0.20.json", ... } https://mysql-params.tmtms.net/mysqld/json/version.json 14
  3. Linux Generic 64bit バイナリを使用 mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz インストール先は /usr/local/mysql に統一 ホスト名は hostname

    に統一 ちゃんと初期設定しないとちゃんと表示できない scripts/mysql_install_db --no-defaults bin/mysqld --initialize 20
  4. mysqld --help -v Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value

    (after re ------------------------------------------------------------ ------------- abort-slave-event-count 0 activate-all-roles-on-login FALSE admin-address (No default val admin-port 33062 admin-ssl TRUE admin-ssl-ca (No default val admin-ssl-capath (No default val admin-ssl-cert (No default val admin-ssl-cipher (No default val admin-ssl-crl (No default val admin-ssl-crlpath (No default val ... 21
  5. 権限の取得 権限の取得 xxxx_priv から xxxx を取得 DESC mysql.user; Field Type

    Null Key Default Extra Host char(255) NO PRI User char(32) NO PRI Select_priv enum('N','Y') NO N Insert_priv enum('N','Y') NO N Update_priv enum('N','Y') NO N Delete_priv enum('N','Y') NO N Create_priv enum('N','Y') NO N Drop_priv enum('N','Y') NO N Reload_priv enum('N','Y') NO N ... 23
  6. 結果は見てなくて PROXY 権の有無を確認してるだけ DESC mysql.proxies_priv; Field Type Null Key Default

    Extra Host char(255) NO PRI User char(32) NO PRI Proxied_host char(255) NO PRI Proxied_user char(32) NO PRI With_grant tinyint(1) NO 0 Grantor varchar(288) NO MUL 24
  7. 8.0 なら DESC mysql.user の代わりにこれだけでいい CREATE USER test IDENTIFIED BY

    "xxxxxx"; GRANT ALL ON *.* TO test WITH GRANT OPTION; SHOW GRANTS FOR test; 25
  8. SHOW GRANTS の違い SHOW GRANTS の違い 5.7 8.0 GRANT ALL

    PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT O GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROC ESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORA RY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CR EATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, T RIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test`@`%` WIT H GRANT OPTION GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BIN LOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GRO UP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIS T_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_ GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSIO N_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_AD MIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test`@`%` WITH GRAN T OPTION 26
  9. 関数 関数 最初は help functions から mysql> help functions You

    asked for help about help category: "Functions" For more information, type 'help <item>', where <item> is one of the follo wing categories: Aggregate Functions and Modifiers Bit Functions Cast Functions and Operators Comparison Operators Control Flow Functions Date and Time Functions ... mysql> help Aggregate Functions and Modifiers You asked for help about help category: "Aggregate Functions and Modifier s" For more information, type 'help <item>', where <item> is one of the follo wing topics: AVG BIT_AND BIT_OR BIT_XOR ... 27
  10. 今は mysql.help_category & mysql.help_topic から mysql> select help_category_id,name,parent_category_id from mysql.help_cat

    egory; +------------------+---------------------------------+-------------------- + | help_category_id | name | parent_category_id | +------------------+---------------------------------+-------------------- + | 0 | Contents | 0 | | 1 | Help Metadata | 0 | | 2 | Data Types | 0 | | 3 | Administration | 0 | | 4 | Functions | 0 | ... mysql> select help_topic_id,name,help_category_id from mysql.help_topic; +---------------+----------------------------+------------------+ | help_topic_id | name | help_category_id | +---------------+----------------------------+------------------+ | 0 | HELP_DATE | 1 | | 1 | HELP_VERSION | 1 | | 2 | AUTO_INCREMENT | 2 | | 3 | HELP COMMAND | 3 | 28
  11. 関数っぽいカテゴリからトピックの名前を抽出 バージョンごとにカテゴリが変わったりする select t.name from mysql.help_topic t join mysql.help_category c

    using (help_category_i d) where c.name rlike "functions|operators|geometry (c onstructors|relations)|properties|mbr|wkt|wkb|xml|gti d" and c.name != "user-defined functions" and t.name not rlike "definition" 29
  12. エラーメッセージ エラーメッセージ /usr/local/mysql/include/mysqld_error.h から エラー番号を取得 //#define OBSOLETE_ER_HASHCHK 1000 //#define OBSOLETE_ER_NISAMCHK

    1001 #define ER_NO 1002 #define ER_YES 1003 #define ER_CANT_CREATE_FILE 1004 #define ER_CANT_CREATE_TABLE 1005 #define ER_CANT_CREATE_DB 1006 #define ER_DB_CREATE_EXISTS 1007 #define ER_DB_DROP_EXISTS 1008 //#define OBSOLETE_ER_DB_DROP_DELETE 1009 #define ER_DB_DROP_RMDIR 1010 //#define OBSOLETE_ER_CANT_DELETE_FILE 1011 30
  13. エラー番号で perror コマンドを実行 % perror 1002 MySQL error code MY-001002

    (ER_NO): NO % perror 1003 MySQL error code MY-001003 (ER_YES): YES % perror 1004 MySQL error code MY-001004 (ER_CANT_CREATE_FILE): Ca n't create file '%-.200s' (errno: %d - %s) % perror 1005 MySQL error code MY-001005 (ER_CANT_CREATE_TABLE): Ca n't create table '%-.200s' (errno: %d - %s) % perror 1006 MySQL error code MY-001006 (ER_CANT_CREATE_DB): Can't create database '%-.192s' (errno: %d - %s) 31