「MySQL」の版間の差分
ナビゲーションに移動
検索に移動
219行目: | 219行目: | ||
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. | Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. | ||
− | mysql> set password for 'root'@'localhost' = ' | + | mysql> set password for 'root'@'localhost' = 'NewPassword'; |
ERROR 4102 (HY000): SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. | ERROR 4102 (HY000): SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. | ||
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; | mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; |
2022年8月30日 (火) 13:43時点における版
| XAMPP |
目次
MySQL
インストール
Windows
5.6
8.0
- [https://www.typea.info/blog/index.php/2022/02/03/mysql80_install_windows10/ Windows10 にMySQL8.0をインストール
Ubuntu(apt-getでインストール)
$ sudo apt-get install mysql-server
- 途中でパスワードの設定を求められる
CentOS(yumからインストール)
手順
- https://dev.mysql.com/downloads/repo/yum/ に移動
- 使用しているプラットフォーム用のリリースパッケージを選択してダウンロード
- インストール
$ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm yum update $ sudo yum install mysql-community-server
ダウンロード
MySQL Community Server
- http://dev.mysql.com/downloads/mysql/
- MySQL-5.6.13-1.linux_glibc2.5.i386.rpm-bundle.tar
アカウントの登録
- mysql グループと mysqlユーザーの作成
# groupadd mysql # useradd -g mysql mysql
解凍
$ tar xvf MySQL-5.6.13-1.linux_glibc2.5.i386.rpm-bundle.tar
インストール
- 標準の最低限のインストールには、サーバーとクライアント RPM をインストールします。
- RPM はまた MySQL サーバーを運用するためのユーザー mysql (存在しない場合) のログインアカウントを作成する
- サーバーがブート時に自動的に起動するように適切なエントリを /etc/init.d/ に作成する
# rpm -ivh MySQL-shared-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-shared-compat-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-server-5.6.13-1.linux_glibc2.5.i386.rpm # rpm -ivh MySQL-client-5.6.13-1.linux_glibc2.5.i386.rpm
<blockquote>serverインストール時に、初期パスワードの情報がコンソールに出力される</blockquote>
A random root password has been set. You will find it in '/root/.mysql_secret'.
<blockquote>もしくはログに出力されている</blockquote>
/var/log/mysqld.log | grep "temporary password"
開発で必要(Djangoなど)
# rpm -ivh MySQL-devel-5.6.13-1.linux_glibc2.5.i386.rpm
起動と停止
CentOS
# /sbin/service mysql start Starting MySQL... [ OK ] # /etc/init.d/mysql stop Shutting down MySQL.. [ OK ]
CentOS7
# systemctl enable mysqld # systemctl start mysqld
Ubuntu
$ sudo /etc/init.d/mysql start $ sudo /etc/init.d/mysql stop $ sudo /etc/init.d/mysql restart
mysqld_safe(mysqld のラッパ)
shell> cd mysql_installation_directory shell> bin/mysqld_safe &
起動設定がされたか確認
# chkconfig | grep "mysql" mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off
権限の初期設定
忘れたパスワードをリセット
Ubuntu 初期パスワードの場所
- 確認
$ sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = KJmv1zRL0acKBftn socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = KJmv1zRL0acKBftn socket = /var/run/mysqld/mysqld.sock
- ログイン
piroto@jinmu:/etc/mysql$ mysql -u debian-sys-maint -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) mysql> use mysql mysql> select User,Host,plugin from mysql.user; +------------------+-----------+-----------------------+ | User | Host | plugin | +------------------+-----------+-----------------------+ | debian-sys-maint | localhost | caching_sha2_password | | mysql.infoschema | localhost | caching_sha2_password | | mysql.session | localhost | caching_sha2_password | | mysql.sys | localhost | caching_sha2_password | | root | localhost | auth_socket | +------------------+-----------+-----------------------+ 5 rows in set (0.00 sec)
- rootパスワードの変更
mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.21-0ubuntu0.20.04.4 | +-------------------------+ 1 row in set (0.00 sec) mysql> set password for 'root'@'localhost' = 'new password'; Query OK, 0 rows affected, 1 warning (0.06 sec)
- クライアントから root でログイン
- sudo しないとERROR 1698 (28000): Access denied エラー となる
$ mysql -u root -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
iroto@jinmu:/etc/mysql$ mysql -u root -p Enter password: ERROR 1698 (28000): Access denied for user 'root'@'localhost' piroto@jinmu:/etc/mysql$ sudo mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26 Server version: 8.0.21-0ubuntu0.20.04.4 (Ubuntu) mysql>
mysqldを--skip-grant-tables オプションで起動
# mysqld --skip-grant-tables &
mysqld サーバに接続
# mysql -u root mysql
新しいパスワードの設定
mysql> update user set Password=PASSWORD('newpassword') -> where User='root'; Query OK, 4 rows affected (0.11 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
rootパスワードの変更
mysql> set password for 'root'@'localhost' = password('newpassword'); Query OK, 0 rows affected (0.00 sec)
パスワードの設定 MySQL8
mysql> set password for 'wordpress'@'localhost' = 'your password';
初期の権限変更ツール mysql_secure_installation
- 初期パスワード /var/log/mysqld.log | grep "temporary password"
# mysql_secure_installation Change the root password? [Y/n] n Remove anonymous users? [Y/n] Y Disallow root login remotely? [Y/n] Y Remove test database and access to it? [Y/n] n Reload privilege tables now? [Y/n] Y All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...
Failed! Error: SET PASSWORD has no significance for user... エラー
Estimated strength of the password: 100 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
- ループから抜け出せない、以下のプロンプトでCtrl+C
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) :
ubuntu@ip-172-26-11-57:~$ mysql -u debian-sys-maint -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> set password for 'root'@'localhost' = 'NewPassword'; ERROR 4102 (HY000): SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; Query OK, 0 rows affected (0.01 sec)
- mysql.initファイルに記入
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
- ファイルを引数に起動
$ sudo mysqld --init-file=/home/ubuntu/mysql.init &
- サーバーは、起動時に mysql.initシステム変数で指定されたファイルの内容を実行し、'root'@'localhost'アカウントのパスワードを変更します。
- サーバーの通常の起動方法によっては、その他のオプションも必要になる場合があります。 たとえば、init_file 引数の前に --defaults-file が必要な場合があります。
- サーバーが正常に起動されたら mysql.initを削除します。
簡易設定
バージョンの確認
mysql> select version(); +-----------+ | version() | +-----------+ | 5.0.77 | +-----------+ 1 row in set (0.00 sec)
MySQL 簡易設定
データベースの作成
作成
# mysql -u root -p mysql> create database test_db default character set utf8; Query OK, 1 row affected (0.00 sec)
文字コード
確認
mysql> show create database test_db; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | test_db | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec)
データベースの一覧
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | song_dict | | sys | +--------------------+ 5 rows in set (0.00 sec)
権限
- 接続を許可するユーザーをmysqlデータベース内で管理している
- ユーザーがアクセスする際にはアクセス元のホスト(IPアドレス)もセットで認証が行われる
権限テーブル
テーブル名 | 説明 |
---|---|
user | ユーザーの基本的な定義 |
host | ホストに対する権限の定義 |
db | データベースに対する権限の定義 |
tables_priv | テーブルに対する権限の定義 |
clumuns_priv | カラムに対する権限の定義 |
ユーザーの作成
mysql> create user test_user@localhost identified by 'new password'; Query OK, 0 rows affected (0.03 sec)
権限の付与
限定して付与
mysql> grant create,alter,select,insert,update,delete,index on *.* to test_user@localhost; Query OK, 0 rows affected (0.00 sec)
管理権限を付与
- test_admin@localhost を作成した上で管理権限を付与
- リモートアクセスを可能とするには、test_admin@localhost の部分を test_admin@'%' とする
mysql> grant all privileges on *.* to test_admin@localhost identified by 'newpassword' with grant option; Query OK, 0 rows affected (0.00 sec)
外部から接続
ポートを開ける
# firewall-cmd --permanent --zone=public --add-port=3306/tcp # firewall-cmd --reload
piroto@jinmu:~$ sudo ufw allow 3306 ルールを追加しました ルールを追加しました (v6) piroto@jinmu:~$ sudo ufw status 状態: アクティブ To Action From -- ------ ---- 80 ALLOW Anywhere 3306 ALLOW Anywhere 80 (v6) ALLOW Anywhere (v6) 3306 (v6) ALLOW Anywhere (v6)
root@%を追加
確認
mysql> select user,host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | pma | localhost | | root | localhost | | wordpress | localhost | +------------------+-----------+
ユーザーの追加
mysql> create user 'root'@'%' identified by 'パスワード'; mysql> set password for 'root'@'%' = 'パスワード'; mysql> grant all on *.* to 'root'@'%';
確認
mysql> select user, host from user; +------------------+-----------+ | user | host | +------------------+-----------+ | root | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | pma | localhost | | root | localhost | | wordpress | localhost | +------------------+-----------+
バックアップとリストア
mysqldump
テーブル名を指定してダンプ
mysqldump [オプション] データベース名 [テーブル名 ...]
複数のデータベースを対象とする
mysqldump [オプション] --databases [オプション] データベース名 [データベース名 ...]
すべてのデータベースを対象とする
mysqldump [オプション] --all-databases [オプション]
例
バックアップ例
- DBすべて
$ mysqldump --default-character-set=utf8 -u USER_NAME -p DB_NAME > OUTPUT_FILE.sql Enter password:
- テーブル指定
$ mysqldump -u USER_NAME -p -h HOST_NAME -t DB_NAME TABLE_NAME > OUTPUT_FILE.sql
レストア例
- mysqldumpの出力はSQL文の羅列であるため、復元するにはリダイレクトを使ってmysqlに結果を与えれば良い
# mysql -u root -p mt < mt_backup20140120.sql
接続
外部接続
Ubuntu エラー 111で接続できない
$ msyql -u hoge -p -h 192.168.0.43 foo_db Can't connect to MySQL server on '192.168.0.43' (111)
- localhostのみをリッスンしている
- 設定ファイルを編集
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
- bind-address =127.0.0.1 に制限されているためコメントアウト
# If MySQL is running as a replication slave, this should be # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir # tmpdir = /tmp # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. # bind-address = 127.0.0.1
- 再起動
$ sudo systemctl restart mysql
Python
Install
$ pip install --upgrade pip
Insert
import mysql.connector def ins(): add_song_element = ("INSERT INTO song_element " "(element, reading, reading_rev) " "VALUES (%s, %s, %s)") config = { 'user':'song_dict', 'password':'my password', 'host':'192.168.0.43', 'database':'song_dict' } try: cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cursor.execute(add_song_element, ('hoge', 'foo', 'bar')) except Exception as ee: print(ee)
Query
query = ("SELECT id FROM song_element " "WHERE reading=%s and element=%s") cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cursor.execute(query , ('hoge', 'foo')) for (id) in cursor: print(id)
Java
package info.typea.songdict.sample; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DbAccess { public void doWork() { try (Connection conn = DriverManager.getConnection( "jdbc:mysql://hostname/dbname", "username", "password")) { Statement stmt = conn.createStatement(); ResultSet resultSet = stmt.executeQuery("select substr(reading,1,2) from song_element group by substr(reading,1,2)"); while (resultSet.next()) { System.out.println(resultSet.getString(1)); } } catch(Exception e) { System.out.println(String.format("ERROR : %s", e)); } } }
phpMyAdmin
管理
データファイルの場所
/etc/my.cnf datadir=/var/lib/mysql
テーブル一覧を表示
# mysql -u root -p mysql> use mt Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_mt | +-------------------+ | mt_asset | | mt_asset_meta | | mt_association | : | mt_ts_funcmap | | mt_ts_job | +-------------------+ 44 rows in set (0.00 sec)
セッションの確認と切断
show processlist; kill [Id]
SQL
制約を無視してテーブルをtruncateする
- 一旦、FOREIGN_KEY_CHECKSを0にする
mysql> SET FOREIGN_KEY_CHECKS=0; mysql> truncate table hoge; mysql> SET FOREIGN_KEY_CHECKS=1;
パーティショニング
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21) );
リストパーティショニング
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16) );
CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE ) PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo') );
mysql> alter table jra_data -> partition by list COLUMNS(data_type) ( -> partition p0 values in ('0'), -> partition p1 values in ('1'), -> partition p2 values in ('2'), -> partition p5 values in ('5'), -> partition p7 values in ('7'), -> partition p9 values in ('9'), -> partition pA values in ('A'), -> partition pB values in ('B') -> );
- 確認
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATIO N_SCHEMA.PARTITIONS WHERE TABLE_NAME='jra_data'; +--------------+------------+----------------+----------------------------+------------+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS | +--------------+------------+----------------+----------------------------+------------+ | jra | jra_data | p0 | 1 | 8 | | jra | jra_data | p1 | 2 | 533260 | | jra | jra_data | p2 | 3 | 69894 | | jra | jra_data | p5 | 4 | 459127 | | jra | jra_data | p7 | 5 | 1052898 | | jra | jra_data | p9 | 6 | 5694 | | jra | jra_data | pA | 7 | 707408 | | jra | jra_data | pB | 8 | 10085 | +--------------+------------+----------------+----------------------------+------------+ 8 rows in set (0.06 sec)
mysqlコマンド
ソースファイルのSQLを実行
\.<ファイル名>
データベースの変更
use
ヘルプ
help
テーブル定義を確認
show create table テーブル名
テーブル一覧
show tables
ポートを調べる
mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.00 sec)
プログラミング
JDBC Download
C#から接続
DML(CREATE TABLE)
using MySql.Data.MySqlClient; using System.Data; using System.Diagnostics; public void CreateTable(string host, string database, string user, string password) { // create database jra default character set utf8; // create user 'jra'@'localhost' identified by 'new password'; // create user 'jra'@'%' identified by 'new password'; // grant all privileges on *.* to 'jra'@'%' with grant option; var conInfo = $"Database=jra;Data Source=192.168.0.2;User Id=jra;Password=password; pooling = false; convert zero datetime=True"; using (var conn = new MySqlConnection(conInfo)) { conn.Open(); var createTable = @" create table jra_data ( rec_spec varchar(2) not null, rec_key varchar(100) not null, make_date varchar(10) not null, data_type varchar(1) not null, data mediumtext, primary key(rec_spec, rec_key) )"; var cmd = new MySqlCommand(createTable); cmd.Connection = conn; cmd.ExecuteNonQuery(); } }
データ登録(INSERT)
public void Load(string host, string database, string user,string password, string fileName) { MySqlCommand insCmd = new MySqlCommand( @"insert into jra_data ( rec_spec, rec_key, make_date, data_type, data ) " + " values ( @rec_spec, @rec_key, @make_date, @data_type, @data )"); var conInfo = $"Database=jra;Data Source=192.168.0.2;User Id=jra;Password=password; pooling = false; convert zero datetime=True"; using (var conn = new MySqlConnection(conInfo)) { conn.Open(); insCmd.Connection = conn; using (var reader = new StreamReader(fileName)) { string line = null; while ((line = reader.ReadLine()) != null) { var record = line.Split("\t"); Debug.WriteLine(line); if (record.Length >= 5) { var recSpec = record[0]; var makeDate = record[1]; var dataType = record[2]; var recKey = record[3]; var data = record[4]; insCmd.Parameters.Clear(); insCmd.Parameters.Add(new MySqlParameter("rec_spec", recSpec)); insCmd.Parameters.Add(new MySqlParameter("rec_key", recKey)); insCmd.Parameters.Add(new MySqlParameter("make_date", makeDate)); insCmd.Parameters.Add(new MySqlParameter("data_type", dataType)); insCmd.Parameters.Add(new MySqlParameter("data", data)); insCmd.ExecuteNonQuery(); } } } } }
データ取得(SELECT)
using MySql.Data.MySqlClient; using System; using System.Data; namespace Mt2Wp { class Program { static void Main(string[] args) { var pgm = new Program(); pgm.AccessTest(); } public void AccessTest() { // Unable to convert MySQL date/time value to System.DateTime // https://stackoverflow.com/questions/2934844/unable-to-convert-mysql-date-time-value-to-system-datetime var conInfo = $"Database=wordpress;Data Source=192.168.0.2;User Id=root;Password={password}; pooling = false; convert zero datetime=True"; using(var conn = new MySqlConnection(conInfo)) { conn.Open(); var da = new MySqlDataAdapter("select * from wp_posts", conn); var ds = new DataSet(); da.Fill(ds); foreach (DataTable table in ds.Tables) { foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.Write($"{row[col]}\t"); } Console.WriteLine(""); } } } } } }
データ取得(カーソル)
using (var conn = new MySqlConnection(GetConnStr(host, database, user, password))) { conn.Open(); var command = new MySqlCommand("select * from jra_data", conn); using(var reader = command.ExecuteReader()) { while(reader.Read()) { Debug.WriteLine(reader.GetString("data")); } } }
Pythonから接続
- requirements.txt
mysql-connector-python==8.0.21
def normalize( out_base_dir, norm_prefix, parse_dir, parse_prefix, skip_when_exists=True, host='localhost'): add_song_element = ("INSERT INTO song_element " "(element, reading, reading_rev) " "VALUES (%s, %s, %s)") query = ("SELECT id FROM song_element " "WHERE reading=%s and element=%s") config = { 'user':'song_dict', 'password':'', 'host':host, 'database':'song_dict' } try: cnx = mysql.connector.connect(**config) cursor = cnx.cursor() cnt = 0; hashids = Hashids(salt=HASH_IDS_SALT) os.makedirs(out_base_dir, exist_ok=True) for file in glob.glob(os.path.join(parse_dir, '{0}*'.format(parse_prefix))): key = file.split(parse_prefix)[-1] print(key) with open(file, 'r') as inf: for l in inf.readlines(): try: el = l.split('\t') meta = el[-1].split(',') element = el[0].strip() reading = meta[-2].strip() reading_rev = "".join(reversed(reading)) if element and len(element) > 1: cursor.execute(query , (reading, element)) is_skip = False for (id) in cursor: is_skip = True break; if not is_skip: cursor.execute(add_song_element, (element, reading, reading_rev)) print("{s} : {s}".format(cursor.lastrowid, el)) except Exception as ee: print(ee) cnx.commit() except Exception as e: print(e) else: cnx.close()
© 2006 矢木浩人