mysql の変更点

https://tar3.net:443/pukiwiki/index.php?mysql

[ トップ ]   [ 編集 | 差分 | バックアップ | 添付 | リロード ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ ]

• 追加された行はこの色です。
• 削除された行はこの色です。
• mysql へ行く。
• mysql の差分を削除

---

#author("2023-02-03T16:38:18+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, 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

       

Site admin: anonymous

PukiWiki 1.5.1 © 2001-2016 PukiWiki Development Team. Powered by PHP 7.4.33. HTML convert time: 0.034 sec.