facebook twitter hatena line email

「Mysql/sql文DDL」の版間の差分

提供: 初心者エンジニアの簡易メモ
移動: 案内検索
(現在のプロセス確認)
(テーブル名検索)
 
(同じ利用者による、間の28版が非表示)
行4: 行4:
 
===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以降)===
 
===rootパスワード設定(mysql5.7以降)===
行12: 行24:
  
 
===ユーザ権限追加===
 
===ユーザ権限追加===
 +
====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 'パスワード';
行49: 行69:
  
 
===復元===
 
===復元===
  mysql -uroot1 -ppass1 database1 < xxx.dump
+
  mysql -uroot1 -ppass1 database1 < database1.dump
  
 
===ダンプ===
 
===ダンプ===
  mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > xxx.log
+
  mysqldump -uroot1 -ppass1 --databases database1 --default-character-set=binary > database1.log
 
   
 
   
 
  テーブルのみを指定する時は
 
  テーブルのみを指定する時は
行79: 行99:
 
===gzipのバックアップとリストア===
 
===gzipのバックアップとリストア===
 
バックアップ
 
バックアップ
  mysqldump --opt --quote-names --databases xxx | gzip -c1 > xxx.dump.gz
+
  mysqldump --opt --quote-names --databases database1 | gzip -c1 > database1.dump.gz
 
リストア
 
リストア
  gzip -dc xxx.dump.gz | mysql -D xxx
+
  gzip -dc database1.dump.gz | mysql -D database1
  
 
===utf8でdb作成===
 
===utf8でdb作成===
行89: 行109:
 
  default-character-set = utf8
 
  default-character-set = utf8
 
  skip-character-set-client-handshake
 
  skip-character-set-client-handshake
  show variables like 'char%'でutf8確認
+
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のデッドロックを見つける===
行125: 行157:
 
  ALTER TABLE table1 AUTO_INCREMENT=4;
 
  ALTER TABLE table1 AUTO_INCREMENT=4;
  
==strage変更==
+
==storage変更==
 
  ALTER TABLE table1 ENGINE = InnoDB;
 
  ALTER TABLE table1 ENGINE = InnoDB;
  
行139: 行171:
  
 
== テーブル名検索==
 
== テーブル名検索==
  show tables where Tables_in_aladdin like 'hoge_*';
+
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時点における最新版

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 |