https://oracle-japan.github.io/ocitutorials/beginners/creating-mds/
https://solutions.system-exe.co.jp/oracle-cloud/blog/mysql-database-service
https://docs.oracle.com/ja-jp/iaas/mysql-database/index.html
https://docs.oracle.com/ja-jp/iaas/mysql-database/doc/creating-db-system1.html
前提:
セキュリティリストでVCN内から3306番ポート許可したVCN作成済み
mysql-clientをインストールした接続確認用コンピュートインスタンス作成済み
MySQL Database—Standard - AMD E4 - Compute
MySQL.VM.Standard.E4.1.8GB
2core 8GB
¥5.32 OCPU per hour
-- 1. DBシステムの作成
oci mysql shape list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--query 'sort_by(data, &"cpu-core-count").{"name":"name","cpu-core-count":"cpu-core-count","memory-size-in-gbs":"memory-size-in-gbs"}' \
--output table
→ MySQL.VM.Standard.E4.1.8GB
oci mysql version list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000
→ 8.0.35
oci mysql configuration list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--query 'sort_by(data, &"display-name").{"display-name":"display-name","id":"id","shape-name":"shape-name"}' \
--output table
→ MySQL.VM.Standard.E4.1.8GB.Standalone
oci mysql db-system list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000
oci mysql db-system create --generate-full-command-json-input
oci mysql db-system create \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--shape-name MySQL.VM.Standard.E4.1.8GB \
--subnet-id ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--admin-username "root" \
--admin-password "passwordpassword" \
--availability-domain OEIw:US-ASHBURN-AD-3 \
--backup-policy '{
"isEnabled": false
}' \
--configuration-id ocid1.mysqlconfiguration.oc1..000000000000000000000000000000000000000000000000000000000000 \
--crash-recovery ENABLED \
--data-storage-size-in-gbs 50 \
--database-management DISABLED \
--deletion-policy '{
"automaticBackupRetention": "DELETE",
"finalBackup": "SKIP_FINAL_BACKUP",
"isDeleteProtected": false
}' \
--description mysql11 \
--display-name mysql11 \
--hostname-label mysql11 \
--ip-address 10.0.2.11 \
--is-highly-available false \
--mysql-version 8.0.35 \
--port 3306 \
--port-x 33060
oci mysql db-system list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--query 'data.{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table
oci mysql db-system delete \
--db-system-id ocid1.mysqldbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--force
oci mysql db-system delete \
--db-system-id ocid1.mysqldbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--force
-- 2. DB Systemへの接続
DBシステム・エンドポイントには、インターネットから直接アクセスできません。
コンピュート・インスタンス、VPN接続、要塞セッションまたはネットワーク・ロード・バランサなどのネットワーク・サービスを使用して、
DBシステムのエンドポイントにアクセスします。
接続確認用コンピュートインスタンスでの作業
ssh -i $HOME/.ssh/id_rsa ubuntu@192.0.2.1
sudo apt install -y unzip
wget https://downloads.mysql.com/docs/world-db.zip
unzip world-db.zip
wget https://downloads.mysql.com/docs/world_x-db.zip
unzip world_x-db.zip
mysql -h 10.0.2.11 -u root -p
source world-db/world.sql
source world_x-db/world_x.sql
SHOW DATABASES;
use world;
SHOW TABLES;
use world_x;
SHOW TABLES;
SELECT * FROM world.city LIMIT 5;
SELECT * FROM world_x.city LIMIT 5;
exit
exit
-- 3. DB Systemの起動、停止
oci mysql db-system list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--query 'data.{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table
oci mysql db-system stop \
--db-system-id ocid1.mysqldbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--shutdown-type FAST
oci mysql db-system start \
--db-system-id ocid1.mysqldbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000000
-- 4. 手動バックアップの作成
oci mysql backup list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000
oci mysql backup create \
--db-system-id ocid1.mysqldbsystem.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--backup-type FULL \
--description mysql11bk01 \
--display-name mysql11bk01 \
--retention-in-days 1
oci mysql backup list \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--query 'data[].{"display-name":"display-name","id":"id","lifecycle-state":"lifecycle-state"}' \
--output table
oci mysql backup delete \
--backup-id ocid1.mysqlbackup.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--force
-- 5. バックアップからのリストア
oci mysql db-system clone \
--compartment-id ocid1.compartment.oc1..000000000000000000000000000000000000000000000000000000000000 \
--shape-name MySQL.VM.Standard.E4.1.8GB \
--source-backup-id ocid1.mysqlbackup.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--subnet-id ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000000 \
--availability-domain OEIw:US-ASHBURN-AD-1 \
--description mysql12 \
--display-name mysql12 \
--hostname-label mysql12 \
--ip-address 10.0.2.12 \
--data-storage-size-in-gbs 50