select @@innodb_log_file_size;

innodb_log_file_size は、ロググループ内の各ログファイルのサイズをバイト単位で定義します。

select @@innodb_log_files_in_group;

innodb_log_files_in_group は、ロググループ内のログファイルの数を定義します。デフォルトおよび推奨値は 2 です。

Oracle

(19c)

select * from v$log;
select * from v$logfile;
alter system archive log current;

redoロググループの追加
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/ORCL/redo04.log') SIZE 209715200;

redoロググループの削除
ALTER DATABASE DROP LOGFILE GROUP 4;

サイズ変更はサイズを変更したロググループの追加と変更前のロググループの削除で行う

PostgreSQL

(13)

WALログは、データディレクトリ以下のpg_walディレクトリに、
通常16メガバイトのサイズを持つセグメントファイルの集合として格納されています
(ただし、このサイズはinitdbの--with-wal-segsizeオプションで変更できます)。

トータルサイズの上限下限は以下の設定で行う

show max_wal_size;
show min_wal_size;

SQL Server

(2019)

select database_id,name,file_id,physical_name,size * 8/1024 mbyte
from sys.master_files
where database_id = DB_ID('test')
and type_desc = 'LOG'
;

トランザクションログファイルの拡張
ALTER DATABASE test MODIFY FILE ( NAME = 'test_log', SIZE = 10240KB )

トランザクションログファイルの縮小
use test
dbcc shrinkfile('test_log' , 0, truncateonly)

トランザクションログファイルの追加

alter database test add log file
(name = test_log2 ,filename = 'C:\test\test_log2.ldf', SIZE = 8MB , FILEGROWTH = 64MB
)
;

トランザクションログファイルの削除

alter database test remove file test_log2;

2021-07-24

{Aurora}Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード

AWS

-- 1. S3バケット作成

aws s3 mb s3://bucket123
aws s3 ls

-- 2. IAMポリシー作成
vim policy01.json

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowAuroraToExampleBucket",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:AbortMultipartUpload",
"s3:ListBucket",
"s3:DeleteObject",
"s3:GetObjectVersion",
"s3:ListMultipartUploadParts"
],
"Resource": [
"arn:aws:s3:::bucket123/*",
"arn:aws:s3:::bucket123"
]
}
]
}

aws iam create-policy \
--policy-name policy01 \
--policy-document file://policy01.json

-- 3. IAMロール作成
vim role01.json

{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "rds.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}

aws iam create-role \
--role-name role01 \
--assume-role-policy-document file://role01.json

-- 4. ポリシーをロールにアタッチ
aws iam attach-role-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01 \
--role-name role01

-- 5. クラスタパラメータグループ作成
aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-mysql5.7 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

vim a.json
[
{
"ParameterName": "aurora_select_into_s3_role",
"ParameterValue": "arn:aws:iam::999999999999:role/role01",
"ApplyMethod": "immediate"
},
{
"ParameterName": "aurora_load_from_s3_role",
"ParameterValue": "arn:aws:iam::999999999999:role/role01",
"ApplyMethod": "immediate"
},
{
"ParameterName": "aws_default_s3_role",
"ParameterValue": "arn:aws:iam::999999999999:role/role01",
"ApplyMethod": "immediate"
}
]

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters file://a.json

-- 6. クラスタの作成

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora-mysql \
--engine-version 5.7.mysql_aurora.2.10.0 \
--master-username root \
--master-user-password 'password' \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.t3.small \
--engine aurora-mysql \
--no-auto-minor-version-upgrade

-- 7. IAMロールをクラスタに関連付ける

aws rds add-role-to-db-cluster \
--db-cluster-identifier cluster11 \
--role-arn arn:aws:iam::999999999999:role/role01

-- 8. テスト用ユーザとデータの作成
create user 'user1'@'%' identified by 'password';

grant all on test.* to 'user1'@'%';
GRANT SELECT INTO S3 ON *.* TO 'user1'@'%';
GRANT LOAD FROM S3 ON *.* TO 'user1'@'%';

SHOW GRANTS FOR 'user1'@'%';

※DB クラスターのマスターユーザー名にはデフォルトで SELECT INTO S3 権限が付与されます。
※DB クラスターのマスターユーザー名にはデフォルトで LOAD FROM S3 権限が付与されます。

create database test;
use test;
create table tab1(col1 int primary key,col2 varchar(10) );

insert into tab1 values(1,'AAA');
insert into tab1 values(2,'BBB');
insert into tab1 values(3,'CCC');
insert into tab1 values(4,'DDD');

select * from tab1;

-- 9. S3へのデータアンロード

SELECT * FROM tab1
INTO OUTFILE S3 's3://bucket123/tab1'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
;

ERROR 63994 (HY000): S3 API returned error: Missing Credentials: Cannot instantiate S3 Client
→ IAMロールをクラスタに関連付けるのに少し時間がかかる。少し待てばこのエラーは消える

ERROR 63994 (HY000): S3 API returned error: Network Connection:Unable to connect to endpoint

→ S3ゲートウェイエンドポイントを作成してエラー解消
VPC: Auroraが使用しているVPC
ルートテーブル: Auroraが使用しているルートテーブル
ポリシー: フルアクセス

aws s3 ls s3://bucket123

-- 10. S3からデータロード

truncate table tab1;
select * from tab1;

LOAD DATA FROM S3 's3://bucket123/tab1.part_00000'
INTO TABLE tab1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(col1,col2);

select * from tab1;

select * from mysql.aurora_s3_load_history;

-- 11. クリーンアップ

-- ロールの一覧
aws iam list-roles | grep role01

-- ロールの削除

aws iam detach-role-policy \
--role-name role01 \
--policy-arn arn:aws:iam::999999999999:policy/policy01

aws iam delete-role --role-name role01

-- ポリシーの一覧
aws iam list-policies | grep policy01

-- ポリシーの削除
aws iam delete-policy \
--policy-arn arn:aws:iam::999999999999:policy/policy01

-- バケット一覧
aws s3 ls

-- バケット削除
aws s3 rb s3://bucket123 --force

-- クラスタ削除
aws rds delete-db-instance \
--db-instance-identifier cluster11-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier cluster11 \
--skip-final-snapshot

-- クラスタパラメータグループ削除
aws rds delete-db-cluster-parameter-group --db-cluster-parameter-group-name cpg01

-- S3ゲートウェイエンドポイントの削除

2021-07-24

{Aurora}Aurora マルチマスタークラスターの作成

AWS

現在、マルチマスタークラスターには、MySQL 5.6 と互換性のある Aurora MySQL バージョン 1 が必要です。
DB エンジンのバージョンを指定する場合は、5.6.10a を選択します。

インスタンスクラスはdb.r4.2xlarge以上が必要

aws rds create-db-cluster \
--db-cluster-identifier cluster11 \
--engine aurora \
--engine-version 5.6.10a \
--master-username root \
--master-user-password 'password' \
--engine-mode multimaster

aws rds create-db-instance \
--db-instance-identifier cluster11-instance01 \
--db-cluster-identifier cluster11 \
--db-instance-class db.r4.2xlarge \
--engine aurora \
--no-auto-minor-version-upgrade

aws rds create-db-instance \
--db-instance-identifier cluster11-instance02 \
--db-cluster-identifier cluster11 \
--db-instance-class db.r4.2xlarge \
--engine aurora \
--no-auto-minor-version-upgrade

2021-07-24

{Aurora}Aurora と MySQL との間、または Aurora と別の Aurora DB クラスターとの間のレプリケーション (バイナリログレプリケーション)

AWS

(1)Aurora --> RDS MySQL
レプリケーションソース: 5.7.mysql_aurora.2.10.0
レプリカターゲット: MySQL 5.7.34

--0. レプリカターゲット作成

aws rds create-db-instance \
--db-instance-identifier mysql01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine mysql \
--master-username root \
--master-user-password 'password' \
--no-multi-az \
--engine-version 5.7.34 \
--storage-type gp2 \
--no-publicly-accessible

-- 1.レプリケーションソースのバイナリログ記録を有効にする
aws rds create-db-cluster-parameter-group \
--db-parameter-group-family aurora-mysql5.7 \
--db-cluster-parameter-group-name cpg01 \
--description cpg01

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters ParameterName=binlog_format,ParameterValue=MIXED,ApplyMethod=pending-reboot

-- 2.レプリケーションソースのバイナリログを不要になるまで保持する
CALL mysql.rds_set_configuration('binlog retention hours', 24);

-- 3.レプリケーションソースのスナップショットを作成する

mysqldump --databases test --single-transaction --order-by-primary -r backup.sql -u root -h cluster11.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p

※"--master-data=2"を指定すると下記エラーとなる
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

DB クラスターのスナップショットを作成→復元することでイベントからbinlog ファイルの名前と場所を確認する必要がある

aws rds create-db-cluster-snapshot \
--db-cluster-snapshot-identifier snap01 \
--db-cluster-identifier cluster11

aws rds restore-db-cluster-from-snapshot \
--db-cluster-identifier dummy01 \
--snapshot-identifier snap01 \
--engine aurora-mysql \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier dummy01-instance01 \
--db-cluster-identifier dummy01 \
--db-instance-class db.t3.small \
--engine aurora-mysql \
--no-auto-minor-version-upgrade

aws rds describe-events

aws rds delete-db-instance \
--db-instance-identifier dummy01-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier dummy01 \
--skip-final-snapshot

aws rds delete-db-cluster-snapshot \
--db-cluster-snapshot-identifier snap01

-- 4.レプリカターゲットにスナップショットをロードする
mysql -h mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p
source backup.sql;

-- 5.レプリカターゲットでレプリケーションを有効にする

host mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com

-- レプリケーションソースでの作業

CREATE USER 'repl_user'@'172.31.555.555' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'172.31.555.555';

-- レプリカターゲットでの作業
CALL mysql.rds_set_external_master ('cluster11-instance01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.000003', 123, 0);
CALL mysql.rds_start_replication;

-- 6.レプリカをモニタリングする

SHOW SLAVE STATUS\G

-- 7.レプリカターゲットでバイナリログのレプリケーションを停止する
CALL mysql.rds_stop_replication;

-- 8.レプリケーションソースのバイナリログ記録を無効にする
aws rds modify-db-cluster \
--db-cluster-identifier cluster11 \
--db-cluster-parameter-group-name default.aurora-mysql5.7

aws rds stop-db-cluster --db-cluster-identifier cluster11

aws rds start-db-cluster --db-cluster-identifier cluster11

CALL mysql.rds_set_configuration('binlog retention hours', NULL);

(2)Aurora --> 外部 MySQL
レプリケーションソース: 5.7.mysql_aurora.2.10.0
レプリカターゲット: MySQL 5.7.35

--0. レプリカターゲット作成

sudo yum remove mysql
sudo yum localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql57-community

sudo yum info mysql-community-server

sudo yum -y install mysql-community-server

sudo mysqld --version

sudo systemctl enable mysqld.service
sudo systemctl restart mysqld.service
sudo systemctl status mysqld.service

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

sudo mysql_secure_installation

-- 初期設定

sudo vim /etc/my.cnf
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
character_set_server=utf8mb4

aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name cpg01 \
--parameters ParameterName=binlog_format,ParameterValue=MIXED,ApplyMethod=pending-reboot

-- 2.レプリケーションソースのバイナリログを不要になるまで保持する
CALL mysql.rds_set_configuration('binlog retention hours', 24);

-- 3.レプリケーションソースのスナップショットを作成する

mysqldump --databases test --single-transaction --order-by-primary -r backup.sql -u root -h cluster11.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p

※"--master-data=2"を指定すると下記エラーとなる
mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'root'@'%' (using password: YES) (1045)

DB クラスターのスナップショットを作成→復元することでイベントからbinlog ファイルの名前と場所を確認する必要がある

aws rds create-db-cluster-snapshot \
--db-cluster-snapshot-identifier snap01 \
--db-cluster-identifier cluster11

aws rds restore-db-cluster-from-snapshot \
--db-cluster-identifier dummy01 \
--snapshot-identifier snap01 \
--engine aurora-mysql \
--db-cluster-parameter-group-name cpg01

aws rds create-db-instance \
--db-instance-identifier dummy01-instance01 \
--db-cluster-identifier dummy01 \
--db-instance-class db.t3.small \
--engine aurora-mysql \
--no-auto-minor-version-upgrade

aws rds describe-events

aws rds delete-db-instance \
--db-instance-identifier dummy01-instance01 \
--skip-final-snapshot

aws rds delete-db-cluster \
--db-cluster-identifier dummy01 \
--skip-final-snapshot

aws rds delete-db-cluster-snapshot \
--db-cluster-snapshot-identifier snap01

-- 4.レプリカターゲットにスナップショットをロードする
mysql -h localhost -P 3306 -u root -p
source backup.sql;

-- 5.レプリカターゲットでレプリケーションを有効にする

host cluster11-instance01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com

-- レプリケーションソースでの作業
CREATE USER 'repl_user'@'172.31.444.444' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'172.31.444.444';

-- レプリカターゲットでの作業
CHANGE MASTER TO MASTER_HOST = '172.31.333.333', MASTER_PORT = 3306, MASTER_USER = 'repl_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin-changelog.000003', MASTER_LOG_POS = 123;
START SLAVE;

-- 6.レプリカをモニタリングする

SHOW SLAVE STATUS\G

-- 7.レプリカターゲットでバイナリログのレプリケーションを停止する
STOP SLAVE;

aws rds stop-db-cluster --db-cluster-identifier cluster11

aws rds start-db-cluster --db-cluster-identifier cluster11

CALL mysql.rds_set_configuration('binlog retention hours', NULL);

2021-07-24

undoファイルの拡張・縮小

MySQL

(8.0.22)
https://dev.mysql.com/doc/refman/8.0/ja/innodb-undo-tablespaces.html

undoファイルの拡張・縮小はできない

undo表領域の追加、削除は可能

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG'\G

CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';

ALTER UNDO TABLESPACE undo_003 SET INACTIVE;

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE NAME LIKE 'undo%';

DROP UNDO TABLESPACE undo_003;

Oracle

(19c)

drop tablespace undo2 including contents and datafiles;
create undo tablespace undo2 datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo2.dbf' size 10M autoextend off;

show parameter undo

alter system set undo_tablespace='UNDO2' scope=both;

select
d.tablespace_name,
d.mbytes "total[MB]",
NVL(f.mbytes,0) "free[MB]",
d.mbytes - NVL(f.mbytes,0) "used[MB]",
(1 - (NVL(f.mbytes,0)/d.mbytes))*100 "used_percent"
from
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_data_files GROUP BY tablespace_name) d
left outer join
(SELECT tablespace_name, (SUM(bytes)/(1024*1024)) mbytes
FROM dba_free_space GROUP BY tablespace_name) f
on d.tablespace_name=f.tablespace_name
;

サイズ指定の拡張
alter database datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo2.dbf' resize 11M;

サイズ指定の縮小
alter database datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo2.dbf' resize 10M;

格納されているデータサイズ以下に縮小はできない

ファイル追加
alter tablespace undo2 add datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo22.dbf' size 10M autoextend off;

ファイル削除
alter tablespace undo2 drop datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo22.dbf';

PostgreSQL

(13)
undoファイルなし

SQL Server

(2019)
https://support.microsoft.com/ja-jp/topic/sql-server-%E3%81%A7-tempdb-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%82%92%E5%9C%A7%E7%B8%AE%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-ea0a95c2-eff8-7075-9ee2-2ee42226ca1c

select database_id,name,file_id,physical_name,size * 8/1024 mbyte
from sys.master_files
;
exec sp_spaceused

サイズ指定の拡張

use tempdb
alter database tempdb modify file (name = tempdev , SIZE = 16MB);

サイズ指定の縮小

use tempdb
dbcc shrinkfile('tempdev' , 8)

※target_sizeはMB単位
格納されているデータサイズ以下に、ファイルを圧縮することはできません。

tempファイルの追加

alter database tempdb add file
(name = tempdev2 ,filename = 'C:\test\tempdev2.ndf', SIZE = 8MB , FILEGROWTH = 64MB
)
;

ユーザ定義ファイルグループはtempdbでは許可されません

tempファイルの削除

alter database tempdb remove file tempdev2;

ファイルが空でない場合は削除できません
→シングルユーザモードで起動して削除できた

HTN20190109の日記

redoファイルの拡張・縮小

{Aurora}Amazon S3 バケットのテキストファイルから Amazon Aurora MySQL DB クラスターへのデータのロード

{Aurora}Aurora マルチマスタークラスターの作成

{Aurora}Aurora と MySQL との間、または Aurora と別の Aurora DB クラスターとの間のレプリケーション (バイナリログレプリケーション)

undoファイルの拡張・縮小