- 追加された行はこの色です。
- 削除された行はこの色です。
#author("2019-08-24T09:44:57+09:00","default:dramsukohome","dramsukohome")
#author("2023-07-05T10:01:02+09:00","default:dramsukohome","dramsukohome")
[[Linux]]~
#shadowheader(1,mysql);
#contents
* Common [#t853db14]
** mysql server backup [#i7541d76]
*** command backup 1[#b396538c]
-
mysqldump --opt --all-databases --events --default-character-set=binary -u root --password=password > /backupDirectory/mysqlDump.sql
-ユーザー名とパスワードを外部ファイル化する。
--ログに以下が書かれるようになる
mysqldump: [Warning] Using a password on the command line interface can be insecure.
--conf ファイルの作成
---vi mysqlDump.conf
[mysqldump]
user=username
password=password
--バックアップコマンド
mysqldump --defaults-extra-file=/hogehoge/mysqlDump.conf --opt --all-databases --events --default-character-set=binary > /backupDirectory/mysqlDump.sql
*** backup script [#hae90da9]
[root@centos ~]# vi mysql-backup.sh作成
#!/bin/bash
PATH=$PATH:/usr/local/sbin:/usr/bin:/bin
# バックアップ先ディレクトリ
BACKDIR=/backup/mysql
# MySQLrootパスワード
ROOTPASS=xxxxxxxx
# バックアップ先ディレクトリ再作成
rm -rf $BACKDIR
mkdir -p $BACKDIR
# データベース名取得
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`
# データベースごとにバックアップ
for dbname in $DBLIST
do
table_count=`mysql -u root -p $ROOTPASS -B -e "show tables" $dbname|wc -l`
[ $table_count -ne 0 ] &&
mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
done
[root@centos ~]# chmod 700 mysql-backup.sh
*** command backup restore 2 [#l696e1f1]
-参考リンク
--[[mysqldumpまとめ(外部):https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c]]
--[[MySQLダンプ、リストア(外部):https://qiita.com/macer_fkm/items/d920ff77f0f5ae5484f9]]
-バックアップ(ダンプ, dump)
--データベース
mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME
--テーブル
mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME TABLE_NAME > OUTPUT_FILE_NAME
-リストア
mysql -u username -ppassword -D DBname < dumpfilename.sql
** その他 [#m333d688]
*** csv ファイル出力 [#t7009015]
SELECT * FROM テーブル名 INTO OUTFILE "ファイル名" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '囲み文字の指定';
*** グループしたカウントを条件に指定(group, count, mysql) [#q66a4e44]
- having を使う
-- 例
select column1,count(column2) as column2_num
from hoge_table
group by column2_num
having count(column2) = 1
***テーブルの作成_Create_Table [#l4f2c517]
- 例1 [#t8afa814]
create table testm (
key1 char(008) primary key,
data1 int8,
data2 int8,
data3 int8
) type=InnoDB;
- 例2 AUTO_INCREMENT, AUTO INCREMENT, PRIMARY KEY [#w456b100]
CREATE TABLE book2
(
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(64),
author_name VARCHAR(32),
detail TEXT,
image VARCHAR(64),
PRIMARY KEY (id)
);
-例3(合わせ技), DB TYPE, DB CHARSET, ENCODE [#i2fb695c]
CREATE TABLE book2
(
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(64),
author_name VARCHAR(32),
detail TEXT,
image VARCHAR(64),
PRIMARY KEY (id)
) type=InnoDB DEFAULT CHARSET=utf8;
*** テーブル構造の変更 [#xa6c7664]
- カラムの追加 [#xad2682e]
alter table personal add new_col_name varchar(20) after col_name;
- カラムの型変更 [#ga2b81a0]
-- alter table テーブル名 modify カラム名 型;
-- 例
alter table goods modify no smallint;
*** not exists の高速化 [#a400fecd]
-○○が含まれないデータを追加、などで not exists を使用する時があるが遅い。。。
-結局高速化はindexに頼るしか無かったが、一応メモ書き
-not exists -> left join.~
残念だがあまり高速化されなかった。
++ not exists code
insert into table1 (colum1,colum2,colum3)
select table2.colum1,table2.colum2,table2.colum3
from table2
where not exists
(select colum2 from table1
where table2.colum1=table1.colum1 and
table2.colum2=table1.colum2 and
table2.colum3=table1.colum3)
++ left join
insert into table1 (colum1,colum2,colum3)
select table2.colum1,table2.colum2,table2.colum3
from table2
left join table on
table2.colum1=table1.colum1 and
table2.colum2=table1.colum2 and
table2.colum3=table1.colum3
where table1.colum1 is null;
-indexを付ける。10倍位高速になりました。
ALTER TABLE table1 ADD INDEX index_name(colum1, colum2, colum3);
ALTER TABLE table2 ADD INDEX index_name(colum1, colum2, colum3);
*** バージョン情報の表示 [#c49cb9ee]
mysql --version
*** Global変数の表示 [#e38233b3]
SHOW GLOBAL VARIABLES
*** 状態の表示 [#h18a0870]
SHOW GLOBAL STATUS
*** Process の表示 [#kb497d03]
SHOW PROCESSLIST
SHOW FULL PROCESSLIST; ← Info が長い文字列の場合に省略しない
*** ページ, pager, [#pb8d9d00]
*** ページ, pager, less [#pb8d9d00]
-設定
pager less -S
-解除
nopager
*** ユーザーの作成 [#ffddaa0e]
CREATE USER 'hoge'@'sampleHost' IDENTIFIED BY 'password';
*** ユーザーの削除 [#sdd03e5f]
DROP USER 'hoge'@'sampleHost';
*** 作成したユーザーの権限変更 [#beb83071]
+グローバルレベル
GRANT ALL ON *.* TO user;
GRANT SELECT, INSERT ON *.* TO user;
+データベースレベル
GRANT ALL ON db_name.* TO user;
GRANT SELECT, INSERT ON dbName.* TO user;
+テーブルレベル
GRANT ALL ON db_name.table_name TO user;
GRANT SELECT, INSERT ON db_name.mytbl TO user;
+カラムレベル
GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;
+権限の確認
show grants for 'ユーザー名'@'ホスト名';
*** テーブルが壊れた時の修正。「Table is marked as crashed and should be repaired」 error, 修正。 [#of380c3a]
-参考サイト：[[pentan.info『Table is marked as crashed and should be repaired』の修復方法:https://pentan.info/sql/mysql/table_crashed_repaired.html]]
-自分の場合「/var」の容量が枯渇した際に発生。
-破損状況の確認
check table `【テーブル名】`;
--例)
check table tableName;
--結果
+-----------+-------+----------+----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------+-------+----------+----------------------------------------------+
| tableName | check | warning | Table is marked as crashed |
| tableName | check | error | Size of datafile is: 0 Should be: 84 |
| tableName | check | error | Corrupt |
+-----------+-------+----------+----------------------------------------------+
-tableの修復
repair table `【テーブル名】`;
--例)
repair table tableName;
-再度確認
check table `【テーブル名】`;
--例)
tableName
--結果
+-----------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| tableName | repair | status | OK |
+-----------+--------+----------+----------+
*** mysqldump: Got error: 1556 [#ifb608fb]
+「--lock-tables=0」を追加
+例
mysqldmup -h mysql_server -uroot -p --lock-tables=0 database_name > mysql_dump.sql
*** コンソール接続をどこから許すか [#g1af0fa9]
+bind-address
-- 複数指定はできないので注意
-- 例1)
bind-address = 127.0.0.1
-- 例2) 全許可
bind-address = 0.0.0.0
-- ユーザーを作成する際、アクセス元を指定して絞るのがお勧め
* Version 5.7 [#l31ad9cf]
** CentOS7 へのインストール [#o324665b]
*** リンク [#jfb67801]
+[[CentOS7に mysql-community-server をインストールし、LAN内の別マシンからアクセス可能にする。(外部):https://qiita.com/htsun/items/449dd20625ee84dc1379]]
+[[centOS7でLAMP環境を作る(外部):https://qiita.com/KaI_Qiita/items/6a085571e1a75c69fb4c]]
*** MariaDBの削除 [#cc9e12fd]
sudo yum remove mariadb-libs
sudo rm -rf /var/lib/mysql
*** リポジトリファイルのインストール [#g111bf61]
yum install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
*** MySQLをインストール [#k4399939]
sudo yum install mysql-community-server mysql-community-client
*** php-mysql のインストール [#sd7b99f9]
yum install --enablerepo=remi,remi-php70 php-mysqlnd
*** サービスの起動 [#d452dc36]
sudo systemctl start mysqld
*** 初回パスワードの確認 [#te6a01ef]
sudo cat /var/log/mysqld.log | grep password
*** 初回設定 [#kf36c47d]
mysql_secure_installation
*** サービスの再起動 [#kd184fd1]
sudo systemctl restart mysqld
*** サービスの自動起動 [#wa873606]
-設定
sudo systemctl enable mysqld
-確認
sudo systemctl status mysqld
** 旧バージョンからデータで上書きした際、起動できなくなる(バックアップ, リストア) [#x4a9f6c0]
*** リンク(外部) [#o44fe92e]
-[[CentOS7.2でのMySQLインストールでの問題:https://teratail.com/questions/53026]]
-[[CentOSのMySQLを5.1から5.7に更新する手順:https://kei178.me/programming/607/]]
*** 原因 [#ebb72d59]
- ユーザーのパスワードが上書きされる
- そのパスワードが新しいサーバーのパスワードポリシーに合っていない
*** 対策 [#l7afdbae]
- Safe mode(セーフモード)で起動(CentOS7 の場合)
-- systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
-- systemctl start mysqld
- mysql の upgrade
mysql_upgrade -u root -p
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
