「Mysql/sql文DDL」の版間の差分
提供: 初心者エンジニアの簡易メモ
(→テーブル名検索) |
|||
(同じ利用者による、間の40版が非表示) | |||
行1: | 行1: | ||
+ | ===mysqlデータをざっくり消す=== | ||
+ | rm -fr /var/lib/mysql | ||
+ | |||
===rootパスワード設定=== | ===rootパスワード設定=== | ||
mysqladmin -u root password '任意のパスワード' | mysqladmin -u root password '任意のパスワード' | ||
+ | |||
+ | ===rootパスワード再設定=== | ||
+ | # service mysqld stop | ||
+ | # systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" # なしに一時設定 | ||
+ | # service mysqld start | ||
+ | mysql -uroot -p #そのままエンターキー | ||
+ | mysql> flush privileges; | ||
+ | mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '任意のパスワード'; | ||
+ | mysql> exit | ||
+ | # systemctl set-environment MYSQLD_OPTS="" # ありに戻す | ||
+ | # service mysqld start | ||
+ | mysql -uroot -p | ||
+ | |||
+ | ===rootパスワード設定(mysql5.7以降)=== | ||
+ | パスが勝手に発行されているので以下コマンドで表示されたパスを確認 | ||
+ | # cat /var/log/mysqld.log | grep 'password is generated' | ||
+ | mysql -uroot -p | ||
+ | > SET PASSWORD = 'hogehoge'; // 大小文字文字英数字記号8文字以上 | ||
===ユーザ権限追加=== | ===ユーザ権限追加=== | ||
+ | ====mysql8==== | ||
+ | <pre> | ||
+ | use mysql | ||
+ | create user 'user1'@'localhost' identified by 'パスワード'; | ||
+ | GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON *.* TO user1@"localhost"; | ||
+ | </pre> | ||
+ | |||
+ | ====mysql5.7==== | ||
-- 全dbに指定sql分を許可 | -- 全dbに指定sql分を許可 | ||
GRANT USAGE ON *.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; | GRANT USAGE ON *.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; | ||
− | GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER ON *.* TO user1@"localhost"; | + | GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON *.* TO user1@"localhost"; |
-- 個別dbのみ | -- 個別dbのみ | ||
GRANT USAGE ON db1.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; | GRANT USAGE ON db1.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; | ||
− | GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER ON db1.* TO user1@"localhost"; | + | GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON db1.* TO user1@"localhost"; |
-- 権限確認 | -- 権限確認 | ||
SHOW GRANTS FOR user1@localhost; | SHOW GRANTS FOR user1@localhost; | ||
+ | |||
+ | 以下エラーが出た場合 | ||
+ | ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user' | ||
+ | $ sudo mysql_upgrade -u root -p | ||
+ | $ sudo service mysql restart | ||
==権限削除== | ==権限削除== | ||
-- db指定権限削除 | -- db指定権限削除 | ||
− | REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON db1.* FROM 'user1'@'localhost'; | + | REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON db1.* FROM 'user1'@'localhost'; |
-- db全指定権限削除 | -- db全指定権限削除 | ||
− | REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* FROM 'user1'@'localhost'; | + | REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* FROM 'user1'@'localhost'; |
-- 全権限削除 | -- 全権限削除 | ||
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost'; | REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost'; | ||
行35: | 行69: | ||
===復元=== | ===復元=== | ||
− | mysql -uroot1 -ppass1 database1 < | + | mysql -uroot1 -ppass1 database1 < database1.dump |
===ダンプ=== | ===ダンプ=== | ||
− | mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > | + | mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > database1.log |
テーブルのみを指定する時は | テーブルのみを指定する時は | ||
行65: | 行99: | ||
===gzipのバックアップとリストア=== | ===gzipのバックアップとリストア=== | ||
バックアップ | バックアップ | ||
− | mysqldump --opt --quote-names --databases | + | mysqldump --opt --quote-names --databases database1 | gzip -c1 > database1.dump.gz |
リストア | リストア | ||
− | gzip -dc | + | gzip -dc database1.dump.gz | mysql -D database1 |
===utf8でdb作成=== | ===utf8でdb作成=== | ||
行75: | 行109: | ||
default-character-set = utf8 | default-character-set = utf8 | ||
skip-character-set-client-handshake | skip-character-set-client-handshake | ||
− | show variables like 'char%' | + | utf8かどうか以下で確認 |
+ | show variables like 'char%'; | ||
+ | |||
+ | ==mysqlコンソールをutf8で表示する== | ||
+ | 修正前 | ||
+ | show variables like 'char%'; | ||
+ | character_set_server | latin1 | ||
+ | my.cnfの以下設定 | ||
+ | [mysqld] | ||
+ | character-set-server = utf8 | ||
+ | 修正後 | ||
+ | show variables like 'char%'; | ||
+ | character_set_server | utf8 | ||
===innodbのデッドロックを見つける=== | ===innodbのデッドロックを見つける=== | ||
行111: | 行157: | ||
ALTER TABLE table1 AUTO_INCREMENT=4; | ALTER TABLE table1 AUTO_INCREMENT=4; | ||
− | == | + | ==storage変更== |
ALTER TABLE table1 ENGINE = InnoDB; | ALTER TABLE table1 ENGINE = InnoDB; | ||
行123: | 行169: | ||
| 23722199 | user1 | localhost:37343 | test | Sleep | 0 | | NULL | | | 23722199 | user1 | localhost:37343 | test | Sleep | 0 | | NULL | | ||
| 23722093 | user1 | localhost:37170 | test | Query | 0 | FULLTEXT initialization | (SELECT `tests`.`name`, `tests`.`created`, `tests`.`updated`, `s | | | 23722093 | user1 | localhost:37170 | test | Query | 0 | FULLTEXT initialization | (SELECT `tests`.`name`, `tests`.`created`, `tests`.`updated`, `s | | ||
+ | |||
+ | == テーブル名検索== | ||
+ | use mydb; | ||
+ | show tables where Tables_in_mydb like 'hoge_*'; | ||
+ | |||
+ | ==mysql8でServer sent charset unknownのとき== | ||
+ | エラー詳細 | ||
+ | The server requested authentication method unknown to the client | ||
+ | |||
+ | 対応 | ||
+ | <pre> | ||
+ | mysql --help | grep cnf | ||
+ | /etc/my.cnf /etc/mysql/my.cnf | ||
+ | |||
+ | $ vi /etc/mysql/my.cnf | ||
+ | [client] | ||
+ | default-character-set=utf8 | ||
+ | |||
+ | [mysql] | ||
+ | default-character-set=utf8 | ||
+ | |||
+ | [mysqld] | ||
+ | collation-server=utf8mb4_bin | ||
+ | character-set-server=utf8mb4 | ||
+ | default_authentication_plugin=mysql_native_password | ||
+ | </pre> | ||
+ | mysql再起動 | ||
+ | service mysqld restart | ||
+ | |||
+ | ==mysql8で"The server requested authentication method unknown to the client"のエラーが出る場合== | ||
+ | pluginをcaching_sha2_passwordからmysql_native_passwordへ | ||
+ | mysql > alter user 'user1'@'localhost' identified with mysql_native_password by 'パスワード'; | ||
+ | <pre> | ||
+ | mysql> select user, host, plugin from user; | ||
+ | +------------------+----------------+-----------------------+ | ||
+ | | user | host | plugin | | ||
+ | +------------------+----------------+-----------------------+ | ||
+ | | user1 | localhost | mysql_native_password | | ||
+ | | mysql.infoschema | localhost | caching_sha2_password | | ||
+ | </pre> |
2023年8月10日 (木) 01:32時点における最新版
目次
- 1 mysqlデータをざっくり消す
- 2 rootパスワード設定
- 3 rootパスワード再設定
- 4 rootパスワード設定(mysql5.7以降)
- 5 ユーザ権限追加
- 6 権限削除
- 7 mysqlコンソールをutf8で表示する
- 8 現在発行中のsql一覧
- 9 クライアントツールでutf8を表示
- 10 テーブルと一緒にuniqueキー作成
- 11 テーブルと一緒にindexキー作成
- 12 auto_increment確認&設定
- 13 storage変更
- 14 現在のプロセス確認
- 15 テーブル名検索
- 16 mysql8でServer sent charset unknownのとき
- 17 mysql8で"The server requested authentication method unknown to the client"のエラーが出る場合
mysqlデータをざっくり消す
rm -fr /var/lib/mysql
rootパスワード設定
mysqladmin -u root password '任意のパスワード'
rootパスワード再設定
# service mysqld stop # systemctl set-environment MYSQLD_OPTS="--skip-grant-tables" # なしに一時設定 # service mysqld start mysql -uroot -p #そのままエンターキー mysql> flush privileges; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '任意のパスワード'; mysql> exit # systemctl set-environment MYSQLD_OPTS="" # ありに戻す # service mysqld start mysql -uroot -p
rootパスワード設定(mysql5.7以降)
パスが勝手に発行されているので以下コマンドで表示されたパスを確認
# cat /var/log/mysqld.log | grep 'password is generated' mysql -uroot -p > SET PASSWORD = 'hogehoge'; // 大小文字文字英数字記号8文字以上
ユーザ権限追加
mysql8
use mysql create user 'user1'@'localhost' identified by 'パスワード'; GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON *.* TO user1@"localhost";
mysql5.7
-- 全dbに指定sql分を許可 GRANT USAGE ON *.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON *.* TO user1@"localhost"; -- 個別dbのみ GRANT USAGE ON db1.* TO user1@"localhost" IDENTIFIED BY 'パスワード'; GRANT CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,EXECUTE,INSERT,LOCK TABLES,SELECT,UPDATE,INDEX,ALTER,REFERENCES ON db1.* TO user1@"localhost"; -- 権限確認 SHOW GRANTS FOR user1@localhost;
以下エラーが出た場合
ERROR 1054 (42S22): Unknown column 'password_last_changed' in 'mysql.user' $ sudo mysql_upgrade -u root -p $ sudo service mysql restart
権限削除
-- db指定権限削除 REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON db1.* FROM 'user1'@'localhost'; -- db全指定権限削除 REVOKE SEKECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,REFERENCES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* FROM 'user1'@'localhost'; -- 全権限削除 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'localhost';
ユーザ削除
DELETE FROM mysql.user WHERE user='ユーザ名';
mysqlに接続する方法
mysql -A -u [ユーザ名] -p password入力のプロンプトが出るので、パスを入力 -Aを入力するとログイン時データベース一覧を取得しないので早くなる
my.cnf場所
var/lib/mysql/my.cnf
~から~のDBへ接続できるかテストする
mysql -u root -p -h 接続先IP
復元
mysql -uroot1 -ppass1 database1 < database1.dump
ダンプ
mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > database1.log テーブルのみを指定する時は --tables [table]を追加する ロック中でも強制的にダンプするとき --skip-lock-tables データのみダンプ -t テーブル定義のみダンプ -d 文字コードがeuc-jpのときはujisとする
create文のみバックアップ
$ mysqldump -uroot1 -ppass1 -d database1 > create1.dmp
再起動
# /etc/init.d/mysqld start stop
または
# /etc/rd.d/init.d/mysqld start
gzipのバックアップとリストア
バックアップ
mysqldump --opt --quote-names --databases database1 | gzip -c1 > database1.dump.gz
リストア
gzip -dc database1.dump.gz | mysql -D database1
utf8でdb作成
create database database1 default character set utf8;
my.cnfの以下設定
[mysqld] default-character-set = utf8 skip-character-set-client-handshake
utf8かどうか以下で確認
show variables like 'char%';
mysqlコンソールをutf8で表示する
修正前
show variables like 'char%'; character_set_server | latin1
my.cnfの以下設定
[mysqld] character-set-server = utf8
修正後
show variables like 'char%'; character_set_server | utf8
innodbのデッドロックを見つける
/var/lib/mysql/のinnodb.status.[dddd]の中を見る
puttyコンソールから文字コード変更
プッティ側の設定を変更することで可能 (ウィンドウ/変換)
puttyコンソールからデータ表示枠の拡大は
ウィンドウ/スクロールバックの数を増やすしかない
現在発行中のsql一覧
mysqladmin processlist
クライアントツールでutf8を表示
set names utf8;
テーブルと一緒にuniqueキー作成
CREATE TABLE sample( id int(11) unsigned NOT NULL AUTO_INCREMENT, unique1 varchar(10), unique2 int(11), PRIMARY KEY(id), UNIQUE KEY `uniq_url` (`unique1`, `unique2`) );
テーブルと一緒にindexキー作成
KEY `idx_custom_created` (`code`,`created`),
auto_increment確認&設定
--確認 SHOW TABLE STATUS; --設定 ALTER TABLE table1 AUTO_INCREMENT=4;
storage変更
ALTER TABLE table1 ENGINE = InnoDB;
現在のプロセス確認
mysql> show processlist; +----------+------+-----------------------+-----------------+---------+------+-------+------------------+ | Id | User | Host | db | user1mand | Time | State | Info | +----------+------+-----------------------+-----------------+---------+------+-------+------------------+ | 23719307 | user1 | localhost | NULL | Query | 0 | init | show processlist | | 23722193 | user1 | localhost:37333 | test | Sleep | 1 | | NULL | | 23722199 | user1 | localhost:37343 | test | Sleep | 0 | | NULL | | 23722093 | user1 | localhost:37170 | test | Query | 0 | FULLTEXT initialization | (SELECT `tests`.`name`, `tests`.`created`, `tests`.`updated`, `s |
テーブル名検索
use mydb; show tables where Tables_in_mydb like 'hoge_*';
mysql8でServer sent charset unknownのとき
エラー詳細
The server requested authentication method unknown to the client
対応
mysql --help | grep cnf /etc/my.cnf /etc/mysql/my.cnf $ vi /etc/mysql/my.cnf [client] default-character-set=utf8 [mysql] default-character-set=utf8 [mysqld] collation-server=utf8mb4_bin character-set-server=utf8mb4 default_authentication_plugin=mysql_native_password
mysql再起動
service mysqld restart
mysql8で"The server requested authentication method unknown to the client"のエラーが出る場合
pluginをcaching_sha2_passwordからmysql_native_passwordへ
mysql > alter user 'user1'@'localhost' identified with mysql_native_password by 'パスワード';
mysql> select user, host, plugin from user; +------------------+----------------+-----------------------+ | user | host | plugin | +------------------+----------------+-----------------------+ | user1 | localhost | mysql_native_password | | mysql.infoschema | localhost | caching_sha2_password |