MySQL
| XAMPP |
目次
MySQL
インストール
Windows
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...
簡易設定
バージョンの確認
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 [オプション]
例
バックアップ例
# mysqldump --default-character-set=utf8 -uroot -p mt > mt_backup20140120.sql Enter password:
レストア例
- 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 ＝１２７．０.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
Tips
管理
データファイルの場所
/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;
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.[[Access]]Test();
}
public void [[Access]]Test()
{
// 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 (Data[[R]]ow row in table.[[R]]ows)
{
foreach (DataColumn col in table.Columns)
{
Console.Write($"{row[col]}\t");
}
Console.WriteLine("");
}
}
}
}
}
}
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 矢木浩人
