mysql> SHUTDOWN -- コマンドではなくSQLステートメントなので、";"で終端しないといけない。
-> ;
Query OK, 0 rows affected (0.00 sec)
|PRIMARY KEY|ALTER |変換|メモ | |-----------|-----------------|----|----------------------------------------------| |あり,なし |ADD COLUMN |Yes | | |あり,なし |DROP COLUMN |Yes | | |あり,なし |ADD FOREIGN KEY |Yes | | |あり,なし |ADD KEY |No |ALGORITHM= INPLACE | |あり,なし |ADD KEY |Yes |ALGORITHM= COPY | |なし |ADD PRIMARY KEY |Yes | | |あり |DROP PRIMARY KEY |Yes | | |あり,なし |ADD UNIQUE KEY |No | | |あり,なし |MODIFY, CHANGE |No |int => int(カラムリネームのみ) | |あり,なし |MODIFY, CHANGE |No |varchar(32) => varchar(32)(カラムリネームのみ)| |あり,なし |MODIFY, CHANGE |No |varchar(32) => varchar(64)(latin1) | |あり,なし |MODIFY, CHANGE |Yes |varchar(32) => varchar(256)(latin1) | |あり,なし |MODIFY, CHANGE |Yes |int => bigint | |あり,なし |MODIFY, CHANGE |Yes |int => int unsigned | |あり,なし |MODIFY, CHANGE |Yes |int => varchar(32) | |あり,なし |Engine= InnoDB |Yes |OPTIMIZE TABLE | |あり,なし |ALTER SET DEFAULT|No | | |あり,なし |RENAME |No |テーブル名のリネーム |
mysql> CREATE TABLE t1 (num serial, val varchar(32));
mysql> LOAD DATA INFILE '/var/lib/mysql-files/md5' INTO TABLE t1; -- 容量があんまりなかったので10万件ほど突っ込む
$ while true ; do mysql -e "INSERT INTO d1.t1 (val) VALUES ('dummy')"; done
mysql> ALTER TABLE t1 MODIFY val varchar(64); -- 暗黙のROW_FORMAT変換は走らない
mysql> ALTER TABLE t1 MODIFY val varchar(256); -- 暗黙のROW_FORMAT変換が走る
$ git clone https://github.com/MarkLeith/mysql-sys.git $ cd mysql-sys $ mysql -uroot -p < ./sys_56.sql
$ cd /usr $ bin/mysql_install_db --no-defaults --datadir=/home/mysql/dummy
$ cp -ip /var/lib/mysql/mysql/user.MYD /home/mysql/dummy/mysql/user2.MYD $ cp -ip /var/lib/mysql/mysql/user.MYI /home/mysql/dummy/mysql/user2.MYI $ cp -ip /var/lib/mysql/mysql/user.frm /home/mysql/dummy/mysql/user2.frm $ bin/mysqld_safe --no-defaults --datadir=/home/mysql/dummy --port=23000 --socket=/home/mysql/dummy/mysql.sock & $ bin/mysql -uroot -S/home/mysql/dummy/mysql.sock mysql> SELECT user, host, password FROM mysql.user2; +-------------+-----------------+-------------------------------------------+ | user | host | password | +-------------+-----------------+-------------------------------------------+ | root | localhost | *........................................ | | xxxxxxxxx | localhost | *........................................ | | root | 127.0.0.1 | *........................................ | | xxxxxxxx | xxx.xxx.xxx.% | *........................................ | | xxxxxxxxxxx | localhost | *........................................ | | xxxx | localhost | *........................................ | | xxxxx | xxx.xxx.xxx.% | *........................................ | | xxxxx | xxx.xxx.xxx.xxx | *........................................ | +-------------+-----------------+-------------------------------------------+ 8 rows in set (0.00 sec)
mysql> UPDATE mysql.user2 SET password= PASSWORD('Do_you_love_Perl6?') WHERE (user, host)= ('root', 'localhost');
$ bin/mysqladmin -uroot -S/home/mysql/dummy/mysql.sock shutdown $ cp -ip /home/mysql/dummy/mysql/user2.MYD /var/lib/mysql/mysql/user.MYD $ cp -ip /home/mysql/dummy/mysql/user2.MYI /var/lib/mysql/mysql/user.MYI
$ pkill -HUP mysqld $ pkill -HUP mysqld
たぶん、内部的にprivilegeを読みなおしてからflush tablesしてる（この段階で古いファイルが閉じられる）から… > 「何故か2回SIGHUP送らないと反映されない」 / “日々の覚書: MySQLのrootのパスワ…” http://t.co/lljTEsYJFm
— Kazuho Oku (@kazuho) 2015, 10月 16
mysql> SELECT plugin_name FROM ALL_PLUGINS order by 1; +-----------------------------+ | plugin_name | +-----------------------------+ | ARCHIVE | | Aria | | AUDIT_NULL | | auth_0x0100 | | binlog | | BLACKHOLE | | cleartext_plugin_server | | CSV | | daemon_example | | FEDERATED | | FEEDBACK | | InnoDB | | InnoDB | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_POOL_STATS | | INNODB_BUFFER_POOL_STATS | | INNODB_CHANGED_PAGES | | INNODB_CMP | | INNODB_CMP | | INNODB_CMPMEM | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_CMPMEM_RESET | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMP_RESET | | INNODB_CMP_RESET | | INNODB_FT_BEING_DELETED | | INNODB_FT_BEING_DELETED | | INNODB_FT_CONFIG | | INNODB_FT_CONFIG | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_DELETED | | INNODB_FT_DELETED | | INNODB_FT_INDEX_CACHE | | INNODB_FT_INDEX_CACHE | | INNODB_FT_INDEX_TABLE | | INNODB_FT_INDEX_TABLE | | INNODB_LOCKS | | INNODB_LOCKS | | INNODB_LOCK_WAITS | | INNODB_LOCK_WAITS | | INNODB_METRICS | | INNODB_METRICS | | INNODB_SYS_COLUMNS | | INNODB_SYS_COLUMNS | | INNODB_SYS_DATAFILES | | INNODB_SYS_DATAFILES | | INNODB_SYS_FIELDS | | INNODB_SYS_FIELDS | | INNODB_SYS_FOREIGN | | INNODB_SYS_FOREIGN | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_INDEXES | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_TABLES | | INNODB_SYS_TABLESPACES | | INNODB_SYS_TABLESPACES | | INNODB_SYS_TABLESTATS | | INNODB_SYS_TABLESTATS | | INNODB_TRX | | INNODB_TRX | | MEMORY | | MRG_MyISAM | | MyISAM | | mysql_native_password | | mysql_old_password | | partition | | PERFORMANCE_SCHEMA | | qa_auth_interface | | qa_auth_server | | simple_parser | | test_plugin_server | | unix_socket | | XTRADB_INTERNAL_HASH_TABLES | | XTRADB_READ_VIEW | | XTRADB_RSEG | +-----------------------------+ 85 rows in set (13.21 sec)
FROM yoku0825/cent66:init RUN echo "NETWORKING=yes" > /etc/sysconfig/network RUN yum install -y which RUN rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm RUN rpm -ivh http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm RUN yum install -y mysql-community-mroonga groonga-tokenizer-mecab RUN service mysqld start && mysql -e "GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION" EXPOSE 3306 ENTRYPOINT /usr/sbin/mysqld --user=mysql
$ sudo docker run -d yoku0825/cent66:mroonga508
MAX_QUERIES_PER_HOUR count、MAX_UPDATES_PER_HOUR count、および MAX_CONNECTIONS_PER_HOUR count 制限は、いずれかの特定の 1 時間の間にこのアカウントに対して許可されるサーバーへのクエリー、更新、および接続の数を制限します。(結果がクエリーキャッシュから得られたクエリーは、MAX_QUERIES_PER_HOUR 制限に対してカウントされません。) count が 0 (デフォルト) である場合、これは、このアカウントに対する制限が存在しないことを示します。
mysql56> SHOW GRANTS; +-------------------------------------------------------------------+ | Grants for yoku0825@% | +-------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'yoku0825'@'%' WITH MAX_QUERIES_PER_HOUR 10 | | GRANT ALL PRIVILEGES ON `d1`.* TO 'yoku0825'@'%' | +-------------------------------------------------------------------+ 2 rows in set (0.00 sec)
$ while true ; do mysql56 -uyoku0825 -e "SELECT 1" d1 || break; done +---+ | 1 | +---+ | 1 | +---+ +---+ | 1 | +---+ | 1 | +---+ +---+ | 1 | +---+ | 1 | +---+ +---+ | 1 | +---+ | 1 | +---+ +---+ | 1 | +---+ | 1 | +---+ ERROR 1226 (42000) at line 1: User 'yoku0825' has exceeded the 'max_questions' resource (current value: 10)