![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
#author("2020-04-07T16:00:50+00:00","","") [[hidekiwiki]] [[hidekiwiki]] #html{{ <meta name="description" content="https://fukumoto.tokyo/wiki/" />  <meta name="keywords" content="MySQL,データベース,コマンド,変更,root,設定,作成,削除,追加,ユーザ,操作" />  <link rel="canonical" href="https://fukumoto.tokyo/wiki/" /> <br> <!-- admax --> <script src="http://adm.shinobi.jp/s/1a0429ab1f292195e9a461469817a0ca"></script> <!-- admax --> </html> }} *MySQLコマンド [#m54922fe] #contents **ユーザの確認 [#o3a9f6f9] mysql> select host,user,password from mysql.user; +-------------+--------------+------------------+ | host | user | password | +-------------+--------------+------------------+ | localhost | root | 284e59d71f64e3ec | | localhost | ueda | 423da20727393e52 | | localhost | vpopmailuser | 7d60b94d42e45b72 | | localhost | xoops | 3cf31278467575ed | | localhost | stats | 3cf31278467575ed | | localhost | cappuccino | 7cd2b5942be28759 | | localhost | ecommerce | 3cf31278467575ed | | 192.168.1.% | vpopmailuser | 7d60b94d42e45b72 | +-------------+--------------+------------------+ 8 rows in set (0.00 sec) **ユーザ追加 [#da093391] testデータベースに対して全ての権限を持ったmatsuiユーザ作成、パスワードはpass mysql> grant all privileges on test.* to matsui@localhost identified by 'pass'; **ユーザ削除 [#v0f26f9a] matuiユーザを削除 mysql> delete from mysql.user where user='matsui' and host='localhost'; **データベース作成 [#m21f2b82] mysql> create database test; **データベース削除 [#x1f28f1e] mysql> create table test; **テーブル情報を表示(DESCRIBE) [#mfd04952] DESCRIBE データベース名.テーブル名 ; または DESC データベース名.テーブル名 ; -例 mysql> DESCRIBE mysql.user; +-----------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------------------+------+-----+---------+-------+ | Host | varchar(60) | NO | PRI | | | | User | varchar(16) | NO | PRI | | | | Password | varchar(16) | NO | | | | | 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 | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | +-----------------------+-----------------------------------+------+-----+---------+-------+ 31 rows in set (0.01 sec) **文字コードなど、ステータス確認 [#w7261e70] mysql> STATUS -------------- mysql Ver 14.14 Distrib 5.1.28-rc, for portbld-freebsd6.3 (i386) using 5.2 Connection id: 104553 Current database: syslog Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.28-rc-log FreeBSD port: mysql-server-5.1.28 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: ujis Db characterset: ujis Client characterset: ujis Conn. characterset: ujis UNIX socket: /tmp/mysql.sock Uptime: 15 days 11 hours 10 min 32 sec Threads: 2 Questions: 2293516 Slow queries: 0 Opens: 14189 Flush tables: 22 Open tables: 64 Queries per second avg: 1.716 -------------- **文字コード関係 [#u7c1582a] ***現在の環境確認 [#m6da045e] SHOW VARIABLES LIKE 'character%'; ***データベースの文字コード設定を調べる [#bba52507] mysql> show create database データベース名; ***文字コードを指定してDB作成 [#r92dae4a] CREATE DATABASE hoge CHARACTER SET SJIS; ***テーブルの文字コード設定を調べる [#n7cba00d] mysql> show create table テーブル名 \G ***文字コードを変更する。 [#kf4c42a6] SET NAMES 'sjis'; 使える文字コード sjis ujis utf8 **リフレッシュ [#fbced8fb] mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.05 sec)
