MySQL｜インコの技術メモ

MySQL

2008年4月 5日 22:51 ～

■ よく使うコマンド

mysql -u root -p（エンター）パスワード入力（エンター） or mysql -u root -p`パスワード`（エンター）	ログイン
SHOW DATABASES;	データベース一覧
SHOW TABLES FROM `test_db`	テーブル一覧を見る。
USE `test_db`;	データベースを掴む。
SHOW TABLES;	テーブルの定義を見る。
DESC `test_table`;	テーブルの定義を見る。
SHOW COLUMNS FROM `test_table`;	テーブルの定義を見る。上と同じ。
SHOW COLUMNS FROM `test_table` LIKE '%_id';	LIKE 句で絞る。（NOT LIKE は無い）
SHOW COLUMNS FROM `test_table` WHERE `Field` NOT LIKE '%ted%';	WHERE 句で絞る。
SHOW FULL COLUMNS FROM `test_table`;	コメントまで表示。
SELECT * FROM `test_table`;	全レコードを閲覧。
SELECT * FROM `test_table` WHERE `test_id` = 14;	条件を指定。
INSERT `test_table` SET `test_serial` = 'kwt00002';	レコードを追加する。
UPDATE `test_table` SET `test_serial` = 'kwt00033' WHERE `test_id` = 14;	レコードを更新する。
DELETE FROM `test_table` WHERE `test_serial` LIKE 'kwt%';	レコードの削除。
CREATE DATABASE `test2_db`;	データベース作成。
DROP DATABASE `test2_db`;	データベース削除。
ALTER TABLE `test_table` ADD `test_c2` INT(11) AFTER `test_c1`;	カラムの追加。
ALTER TABLE `test_table` ADD `test_c2` INT(11) COLLATE 'utf8_bin' AFTER `test_c1`;	カラム名で大文字小文字の区別をつけるとき。
ALTER TABLE `test_table` MODIFY `test_c2` DATE NOT NULL DEFAULT 0;	カラム定義の変更。
ALTER TABLE `test_table` DROP `test_c2`;	カラムの削除。
ALTER TABLE `test_table` ADD PRIMARY KEY(key_1,key_2);	プライマリーキーの追加（複合）。
CREATE TABLE `test4_table` LIKE `test_table`;	他のテーブルスキーマをコピーして作成。
INSERT INTO `test4_table SELECT * FROM `test_table`;	テーブルのデータを挿入。
ALTER TABLE `test_table` RENAME TO `test3_table`;	テーブル名の変更
DROP TABLE `test_table`;	テーブルの削除。
OPTIMIZE TABLE `test_table`	insert delete を繰り返し過ぎるテーブルのスッキリ。
exit;	終了

テーブル追加（例）

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game` varchar(32) DEFAULT NULL COMMENT 'タイトル',
  `type` varchar(32) DEFAULT NULL COMMENT 'ゲーム種別',
  `desc` varchar(2) DEFAULT NULL COMMENT '説明',
  `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `game_INDEX` (`game`),
  KEY `game_type_INDEX` (`game`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

インサート（例）

INSERT INTO users (name,loginid,password,created,updated)
  VALUES ('テストマン', 'aaaa', 'aaaa', NOW(), NOW());

■ ダンプ

mysqldump -u dbuser -p'dbpass' dbname > dbname.sql
（DROP, CREATE が要らないとき）
mysqldump -u dbuser -p'dbpass' -t dbname > dbname.sql
（環境が違う等でエラーになるときは多分これ）
mysqldump --set-gtid-purged=OFF --column-statistics=0 --no-tablespaces -u devuser -p'dbpass' -h localhost dbname > dbname.sql

■ スキーマだけダンプ

mysqldump -u dbuser -p'dbpass' -d dbname --no-data > dbname.s
（AUTO_INCREMENT なし）
mysqldump --opt --single-transaction -u dbuser -p'dbpass' -d dbname | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > dbname.s
mysqldump --no-data -u dbuser -p'dbpass' dbname | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > dbname.s

■ 特定のテーブルだけダンプ
mysqldump -u root -p databasename tablename > "tablename.dump"

■ それを違うＤＢにコピー
mysql -u root -p databasename2 < "tablename.dump"

■ ＤＢ構築（削除→作成→復旧）
プロンプト
mysqldump -u root -p ec > "ec508.dump"
MySQL
drop database ec;
create database ec;
プロンプト
mysql -u root -p ec < "ec511.dump"

■ 違うテーブルから更新

UPDATE hoge A, hage B
  SET A.name = B.name 
  WHERE A.serial = B.serial;

■ タスクを強制終了
なんか固まったらこれを確認。

show processlist; または show full processlist;
で
足引っ張ってそうなタスクの Id を殺す。
kill Id
ex.)
show processlist;
+-----------+---------+
| Id        | User    |～
+-----------+---------+
| 200380640 | devuser |～
| 200530682 | devuser |～ 犯人
+-----------+---------+
kill 200530682

■ 同じカラム名をもつテーブルを探す

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE COLUMN_NAME = 'horse_serial';

■ Unknown collation: 'utf8mb4_0900_ai_ci'

vi hogedb.sql
:
%s/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g
mysql fugadb < hogedb.sql

■ DB 容量一覧

SELECT table_schema, sum(data_length) /1024/1024 AS mb
 FROM information_schema.tables
 GROUP BY table_schema
 ORDER BY sum(data_length+index_length) DESC;

■ auto_increment の id を詰める
id を他のテーブルとの接続に使っていないこと。

# 既存データーを残したままのとき
ALTER TABLE `hoge_table` DROP COLUMN id;
ALTER TABLE `hoge_table` ADD id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
# データーを全削除したとき
ALTER TABLE `hoge_table` AUTO_INCREMENT =1

■ AWS EC2 での MySQL （ MariaDB ）初期パスワード

cat /var/log/mysqld.log | grep password

■ ORDER BY 文字を数字として
ORDER BY CAST(colunb_name AS SIGNED)
ex.)
SELECT post_name FROM wp_posts WHERE post_status='publish' AND post_parent=0 ORDER BY CAST(post_name AS SIGNED) DESC LIMIT 10;

■ オートインクリメントごと削除
TRUNCATE tablename;

■ 前から10文字
SELECT id, name, LEFT(address, 10) FROM users;

■ ジョイン
FROM model1 A LEFT JOIN model2 B ON A.id = B.a_id 　：左(A)全部
FROM model1 A RIGHT JOIN model2 B ON A.id = B.a_id　：右(B)全部
FROM model1 A INNER JOIN model2 B ON A.id = B.a_id　：両方が叶うもののみ
例１）
SELECT hoge1.id, hoge1.hage, hoge2.hage_r FROM hoge1 LEFT JOIN hoge2 ON hoge1.id = hoge2.hoge1_id WHERE hoge1.hage IS NULL AND hoge2.hage_r IS NOT NULL;
例２）
ただし、以下のように右でしか条件を選ばない&左∈右ならば RIGHT でも INNER でも結果は同じです。
当たり前と言えば当たり前ですが。
SELECT COUNT(A.name) FROM dtb_products A INNER JOIN dtb_products_class B ON A.product_id = B.product_id WHERE B.stock = 0;
SELECT COUNT(A.name) FROM dtb_products A RIGHT JOIN dtb_products_class B ON A.product_id = B.product_id WHERE B.stock = 0;

■ 異なる DB 間で JOIN
例） a_db の user テーブルにあって b_db の user テーブルに無いもの。

SELECT A.user_name, A.birthday
 FROM a_db.user A
 LEFT JOIN b_db.user B
 ON CONCAT (A.user_name, '_', A.birthday) = B.serial
 WHERE
 B.serial IS NULL;

■ Mroonga むるんが
全文検索エンジン。
MariaDB 10 からプリインストールされていました。
有効化すると、LIKE 検索の代わりに使える便利なやつが増えます。

mysql -u ほにゃらら -pほにゃらら
INSTALL PLUGIN Mroonga SONAME 'ha_mroonga.so';
CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_snippet RETURNS STRING SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_command RETURNS STRING SONAME 'ha_mroonga.so';
CREATE FUNCTION mroonga_escape RETURNS STRING SONAME 'ha_mroonga.so';

CREATE DATABASE hogedb;
USE hogedb;
CREATE TABLE hoges (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `body` longtext COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`id`),
 FULLTEXT INDEX `body_INDEX` (body) 
) ENGINE=Mroonga  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT = 'engine "InnoDB"';

INSERT INTO hoges VALUES 
(1, '若い時は体力は気力で補うとか言ってたが歳取ると逆だと気付く。'),
(2, '何かをするにも何かを始めるにも気力が必要です。'),
(3, 'それが病気だというならみんな病気だ。');

SELECT * FROM hoges WHERE match( body ) against('+気力' in boolean mode);
SELECT * FROM hoges WHERE match( body ) against('+気力 病気' in boolean mode);
SELECT * FROM hoges WHERE match( body ) against('+気力 +体力' in boolean mode);

■ ２つのテーブルで片方にだけあるものを抽出
SELECT * FROM table_a WHERE NOT EXISTS(SELECT * FROM table_b WHERE table_b.key=table_a.key);

■ UPDATE 文で条件
UPDATE hoges
SET hoge = CASE
WHEN id % 2 = 1 THEN 1
ELSE 0
END;

■ あるカラムを除いた SELECT文
例）
ＤＢ：hogedb
テーブル：users
カラム：id,name,kana,....,status,updated,created
↓
SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), ',status,updated,created', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND TABLE_SCHEMA = 'hogedb'), ' FROM users');
PREPARE statement1 FROM @sql;
EXECUTE statement1;

statement1 は任意の文字列。exitするまで覚えてくれてるので、２回目以降は EXECUTE statement1; だけでＯＫ。

■ テーブル名にAとかBとか
SELECT A.* FROM admin A, license B WHERE B.paid_date LIKE "2009%" AND A.id = B.admin_id GROUP BY A.id;

■ テーブル３つで連結
SELECT A.name AS user_name, C.name AS image_name FROM users A JOIN users8images B ON A.id = B.user_id JOIN images C ON B.item_id = C.id WHERE A.id=1;
SELECT A.name AS user_name, C.name AS image_name
FROM users A
JOIN users8images B ON A.id = B.user_id
JOIN images C ON B.item_id = C.id
WHERE A.id=1;

■ DBが他のサーバーにある場合
mysql -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase
mysqldump -hhoge.host.jp -u hogeuser -phogepasswd hogedatabase > "hoge.dump"

■ 重複しているレコードを探す
あるカラムでダブっているレコードを探す一例。

name カラムで重複しているレコード。
SELECT * FROM hogetable GROUP BY name HAVING COUNT(name) > 1 ORDER BY name;
name, birthday カラムで重複しているレコード。
SELECT * FROM hogetable GROUP BY name, birthday HAVING COUNT(name) > 1 ORDER BY name;
（ 5.7 以上）
SELECT * FROM hogetable
  WHERE name in (
    SELECT name FROM hogetable GROUP BY name, birthday HAVING COUNT(*) >= 2
  )
  ORDER BY name;

■ 部分的に重複しているレコード
作成日の年月別で調べたいときとか
select distinct left(created,7) from reports;
+-----------------+
| left(created,7) |
+-----------------+
| 2011-09 |
| 2011-10 |
| 2011-11 |
+-----------------+

■ echoを使った例
echo "select * from m_table"|mysql -u root pパスワード dbname;
echo "select * from m_table"|mysql -u root pパスワード dbname > result.txt;

■ バージョンを調べる
mysqladmin version

■ 自動起動の設定方法
# chkconfig mysqld on
# chkconfig --list mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
2?5がonであることを確認。

■ 大量データーを扱うときの注意
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
対象データーが大きくなると、↑のエラーがぼちぼち出ます。
設定を変えることによってスルーすることも出来ますが根本的な解決にはならないので、以下。
・JOIN しすぎると返ってこなくなります。
　INNER JOIN とかもってのほか。
　ソースが多少ダサくなってもSQL文を数回に分けたり、有る程度絞ってからループ文で回すなどすると100倍早くなります。というか処理してくれます。
・WHERE テーブル名 IN (v1,v2,v3,......) も危険。
　WHERE IN は結構早くて重宝するのですが、引数の部分で、対象のデーターが巨大だったりで、引数が多くなったりすると処理してくれなくなります。
　ここは反対に括弧の中の v1,v2,v3,...... の部分を SELECT ID FROM ? に置き換えてやると帰ってくるようになります。実質的には同じ内容でもSELECT文を書くのが良いみたいです。

■ 複数のデータベースを削除する
以下の様なテキストファイルでも作って cat drop.sql | mysql -u root -p
「drop.sql」
drop database if exists masu1;
drop database if exists masu2;
drop database if exists masu3;
drop database if exists masu4;
drop database if exists masu5;

■ table2 に存在しない id 値を持つすべてのレコード（table2 に対応するレコードがないすべてのレコード）が table1 で検索される。
mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

■ 重複しているものを１つにまとめて表示
SELECT * FROM t_account GROUP BY name1_1 HAVING COUNT(name1_1)>1;

■ 値が重複したレコードの取得
（上と同じだった）
SELECT * FROM users WHERE `kind`='1' GROUP BY passwd HAVING COUNT(*) > 1;

■ 日本語が入らない時
/etc/my.conf に以下を書き足して /etc/init.d/mysqld restart
[client]
default-character-set=ujis
[mysql]
default-character-set=ujis

■ 文字の置換え
TRANSLATEは、検索文字列式と置換文字列式を一文字づつ対応させて置き換えます。
REPLACEは、単語単位の置換えです。
例：
　文字列式=abcdefdcba
　検索文字列式=abcd
　置換文字式=置換文字
TRANSLATE(文字列式, 検索文字列式, 置換文字列式)　→　置換文字ef字文換置
REPLACE(文字列式, 検索文字列式, 置換文字列式)　　→　置換文字efdcba

■ 文字連結
update contents set meta=concat('aaaaa',カラム名);

■ 検索条件文
voice_? と index 以外。
select meta from contents where not(filename like "voice_%") and not(filename = 'index');

■ IDをふりなおす（＋値）
update image set id=id+値;

■ IDをふりなおす（１から）
mysql> ALTER TABLE my_table DROP id;
mysql> ALTER TABLE my_table ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

■ MySQL最大値
select max(id) from image;

■ csvファイルに出力
select A.iNumber,A.szTitle,A.szBody,B.szTitle,B.szBody from bbs_oya A,bbs_ko B where A.iNumber=B.iOya_number order by A.iNumber into outfile '/home/bbs.csv' fields terminated by ',';

■ メモ
update const set disp='当選者数／応募者数' where group_code='data_kind' and item_id='3';

■ keyカラムを指定しないとdeleteできないようにmy.cnfにsafe-updatesと書く
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
safe-updates
或いは起動時に --safe-updates を付ける
mysql -u root -p --safe-updates

■ safe-updatesを無視する
my.cnf に書いてある場合は --safe-updates=0 をつける
mysql -u root -p --safe-updates=0

■ my.cnf の作成
以下のところからコピーして置きます。
cp /usr/local/mysql/share/mysql/my-small.cnf /etc/my.cnf

■ safe-updates時のレコードのdelete
delete from m_inventory where serial>0 limit100;

■ 圧縮の方法
tar zcvf tabi.tgz patchfiles
左圧縮後ファイル名　右圧縮されるファイル

■ 解凍の方法
tar zxf 圧縮ファイル名

■ ユーザ一覧
mysql -u root -p
use mysql;
select * from user;

■ ユーザ追加
Amazon Linux 2023 MariaDB 10.5.18

CREATE USER 'hogeuser'@'localhost' IDENTIFIED BY 'hogepw';
GRANT ALL ON hogedb.* TO 'hogeuser'@'localhost' IDENTIFIED BY 'hogepw';
USE mysql;
SELECT Host,User,Password FROM user;
  +-----------+-------------+-------------------------------------------+
  | Host      | User        | Password                                  |
  +-----------+-------------+-------------------------------------------+
  | localhost | mariadb.sys |                                           |
  | localhost | root        | invalid                                   |
  | localhost | mysql       | invalid                                   |
  | localhost | hogeuser    | *C29F4AD5CA88D23D62F16076843E6F3F0C7B5460 |
  +-----------+-------------+-------------------------------------------+
SHOW GRANTS FOR hogeuser'@'localhost';
+---------------------------------------------------------------------------------+
| Grants for hogeuser@localhost                                                   |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hogeuser`@`localhost` IDENTIFIED BY PASSWORD 'ほにゃらら'|
| GRANT ALL PRIVILEGES ON `hogedb`.* TO `hogeuser`@`localhost`                    |
+---------------------------------------------------------------------------------+

(全権限)
GRANT ALL ON *.* TO hogeuser@localhost IDENTIFIED BY 'hogepw';

(権限制限)
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO hogeuser@localhost IDENTIFIED BY 'hogepw';

(特定のDBしか見えないユーザー)
GRANT ALL PRIVILEGES ON hogedb.* TO hogeuser@localhost IDENTIFIED BY 'hogepasswd' WITH GRANT OPTION; FLUSH PRIVILEGES;

(or)

GRANT ALL ON hogedb.* to hogeuser@localhost;
FLUSH PRIVILEGES;
SET PASSWORD FOR hogeuser@localhost=password('hogepw');

(ver 5.7)
CREATE USER 'hogeuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'hogepw';
GRANT ALL ON hogedb.* TO hogeuser@localhost;
FLUSH PRIVILEGES;

※hogedb,hogeuser,hogepwは任意の文字列

■ ユーザ削除
DELETE FROM mysql.user WHERE user='username' and host='hostname';
FLUSH PRIVILEGES;

(ver 5.7)
DROP USER user@localhost;

■ 文字コード変更
/etc/my.cnf
default-character-set ujis
とかにする。ujis は EUC Japanese のこと。
コマンドラインではこう。
set names ujis;

■ 現在の文字コードの状態を調べる
１．現在の設定を表示
status
２．文字コード関係(ぽい)変数を表示
show variables like "char%";

■ 文字化け、違う環境にて
$dbh->do('set character set ujis') がバージョンによって要ったり要らなかったりなので
my $dbh = DBI->connect( get_property('data_source'), get_property('db_user'), get_property('db_password') );
$dbh->do('set character set ujis') if((get_property('mysql_version') || '5')=~ /^4/o);
return $dbh;

■ localPCのMySQL
/usr/local/mysql/bin/mysqld_safe --user=mysql &
/usr/local/mysql/bin/mysqladmin -u root -p shutdown

■ データベースからデータだけ抜く
grep INSERT db20070409.dump > db20070409.insert.dump

■ ログの設定
/etc/my.cnf
log=/var/log/mysql.log
　> /var/log/mysql.log
/etc/init.d/mysqld restart

■ シンボリックリンクを貼る
ln -s /usr/local/mysql/bin/mysqldump mysqldump

■ リード例
select * from $B where name like '%$name%' and del_flg='active' order by disp_order,serial"
like あいまい検索 %%ではさむと部分検索になる。
and 条件追加。
order by ソートのキー。複数指定可能。後ろにDESCをつけると降順。

■ my.conf
上コマンドライン用のツーツの設定
下PerlやPHPからアクセスするときに必要なので追加して下さい。
[mysql]
default-character-set=sjis

[mysqld]
default-character-set=sjis
old-passwords

（2006.1.11 書き忘れててもブラウザ(Perl)から見れた(^-^;)

■ SELECT LAST_INSERT_ID();
インサートしたそのIDを取得したい時に使われます。
良く理解せずに使っていたので確認しましたよというメモ。

【疑問】
(1) INSERT INTO users SET name = 'testman';
してからの、
(2) SELECT LAST_INSERT_ID();

(2)で最後にインサートされた ID を取得しているわけだけれど、万が一、万が一だけど(1)と(2)の間に他の人が INSERT したらどうなるの。
つまりこういうこと、

Ａさんの処理で、INSERT INTO users SET name = 'testman'; ID に 1 が自動採番。
Ｂさんの処理で、INSERT INTO users SET name = 'testgal'; ID に 2 が自動採番。
Ａさんの処理で、SELECT LAST_INSERT_ID(); ID = 2 が返ってくる。
Ａさん的には 1 が欲しかったのに、横殴りのせいで 2 を取得してしまう。

こんな絶妙なタイミングで入れ子になること自体がそもそもないっ、とも思えますが可能性としては無くは無い。
これがもし本当に怒ってしまったら、会員登録した瞬間別人の内容が見えてしまったりするかもしれない、怖い。

しかし、安心してください。1 が返ってきますよ。

SELECT LAST_INSERT_ID() は自分が行った分しか管理していません。
もっと厳密に言うと、
接続ごと（ＤＢにコネクトしてからクローズするまでの間）に、自分が行ったオートインクリメントの値だけを保持し呼び出されれば返す
ということです。
なので、INSERT してから SELECT LAST_INSERT_ID の間に、他の人が何人割り込まれても大丈夫です。
（ロールバックが起こったり、入れ子になっている瞬間は自分が確保した ID がとびます）

注意１
上記のことから、以下のようにすると 0 が返ってきてしまうので注意してください。

Perl の例
my $dbh = DBI->connect($dsn, $dbUser, $dbPass); # 接続
my $query = "INSERT INTO users SET name = 'testman'";
$rows = $dbh->do($query); # 挿入
$dbh->disconnect; # 切断
my $dbh = DBI->connect($dsn, $dbUser, $dbPass); # 接続
my $rows = $dbh->selectall_arrayref("SELECT LAST_INSERT_ID()", { Columns => {} });
print Dumper $rows->[0]; # 最後のオートインクリメント表示
0 になります。
切断するとリセットされます。

■ ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)
ある日突然繋がらなくなりました。
＿|?|○
my.iniを旧に戻しても繋がりませんでした。
my.iniを旧に戻してＰＣを再起動したら繋がりました。
このあとmy.iniを元に戻して見たら見えました。ＰＣ再起動はしていません。
b
↑
# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=sjis
ここだけshisにして他のdefault-character-setを全てeucにしたら繋がるし化けません

■ データベースの保存復元のLinux→Windowsで日本語が文字化け
いろいろ試したけどutf8で半分だけ見える。(-_-;
linuxでダンプとると強制的にutf8になる模様。バージョンによっては以下の対応で直
ることがあるらしいが、今の環境ではダメでした。binalyが指定できない。
↓
問題になったのは、２の部分で文字化けに悩まされてました。
結果的には、mysqldumpの文字コードの問題（内部で強制的にutf-8に変換してしまう）のためで、
my.cnfの[mysqldump]のdefault-character-setをbinaryにしたら解決しました。
/etc/my.cnf に下記行を追加
[mysqldump]
default-character-set=binary
ちなみにcharacter-set = ujis等に指定しても文字化けするのでbinary以外はだめみたいです。
あとは、Linux上バックアップ
$ mysqldump -u root -pパスワード DB名 > backup.sql
Windows上でデータベースを作成してからリストア
mysql > create database データベース名
$ mysql -u root -pパスワード DB名 < backup.sql

■ データベースの保存と復元
Windowsのコマンドプロンプトから。
保存
C:\>mysqldump -u root -p test_db > "test_db.dump"
復元
C:\>mysql -u root -p test_db < "c:\test_db.dump"
違うMySQLへの復元するときはあらかじめ受け皿を作っておく。
C:\mysql -u root -p
mysql> create database test_db;
mysql> exit;
削除
また、既存のＤＢの中にテーブルを復旧させる場合は、同じ名前のテーブルがすでにその
ＤＢにあると復旧は行われませんので、そのテーブルを削除しておきます。
mysql> drop database test_db;

MySQL

About

Category