H2 Database 에서 Windows 기반을 선택한 경우
./webtoon 과 같이 dir path 를 지정하는 경우 에러가 난다.
그러니까 . 기반은 linux 에서나 작동하던 것 같다는 소리인데,
결과적으로 ~ 기준으로 하면 상대경로 기반에서 들어감으로 생성이 매우매우매우매우 잘된다는 것이다.
H2 Database 에서 Windows 기반을 선택한 경우
./webtoon 과 같이 dir path 를 지정하는 경우 에러가 난다.
그러니까 . 기반은 linux 에서나 작동하던 것 같다는 소리인데,
결과적으로 ~ 기준으로 하면 상대경로 기반에서 들어감으로 생성이 매우매우매우매우 잘된다는 것이다.
sudo mysqld_safe --skip-grant-tables &
mysql Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Depending on the MySQL or MariaDB server version you are running on your system, you will need to use different commands to recover the root password.
You can find your server version by issuing the following command:
mysql --version
If you have MySQL installed in your system the output will look something like this:
MariaDB case
mysql Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Be sure to make a note of which version of MySQL or MariaDB you’re running.
Follow these steps to reset your MySQL/MariaDB root password:
To change the root password first, you need to stop the MySQL server. To do so type the following command:
sudo systemctl stop mysql
Start the database server without loading the grant tables:
sudo mysqld_safe --skip-grant-tables &
The ampersand & at the end of the command above will cause the program to run in the background , so you can continue to use the shell.
When the --skip-grant-tables option is used, anyone can to connect to the database server without a password and with all privileges granted.
Now you can connect to the database server as the root user:
mysql -u root
Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;
In mysqld_safe mode, ALTER 'root' user command was denied,
- Information Gathering
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
It will mean "Bloat" status, Hence Query need to be updated.
MariaDB> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
MariaDB> FLUSH PRIVILEGES;
Or We colud choose FLUSH
MariaDB> FLUSH PRIVILEGES;
MariaDB> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';
MariaDB> FLUSH PRIVILEGES;
Now that the root password is set, stop the database server and start it normally:
mysqladmin -u root -p shutdown
You will be prompted to enter the new root password.
Start the database server normally:
For MariaDB, type:
sudo systemctl start mariadb
How we used Hibernate Filter and Inspector to dynamically add partition key to SQL queries? (0) | 2024.03.14 |
---|---|
Spring - 샤딩 모듈 개발 이야기 (feat. AbstractRoutingDataSource) (61) | 2024.03.14 |
데이터베이스 파티셔닝과 샤딩 (31) | 2024.03.13 |
Sharded MySQL Cluster 도입 배경과 개발기 (부제: 우당탕탕 좌충우돌 개발기) (0) | 2024.03.13 |
PostgreSQL version 10 introduced several exciting features and improvements to the existing landscape of PostgreSQL. One such feature is logical replication using a publish and subscribe framework. AWS provides two managed PostgreSQL options: Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL. This post discusses how to use the existing framework to create a self-managed read replica from Amazon RDS for PostgreSQL or Aurora. You can apply a similar process to create a read replica on Amazon Elastic Compute Cloud (Amazon EC2)-based PostgreSQL, Amazon RDS for PostgreSQL, and Aurora PostgreSQL.
Amazon RDS for PostgreSQL supports the publication and subscription framework from engine version 10.4 and above. Aurora PostgreSQL supports the publication and subscription framework from engine version 2.2 (compatible with 10.6) and above.
A typical deployment of Amazon RDS for PostgreSQL and Aurora PostgreSQL databases involves application writes being served by a master instance and reads being offloaded to AWS managed replicas. However, certain business use cases also require you to set up an additional self-managed replica instance to serve other independent downstream applications that only need to operate on a subset of data (some databases or some tables only). Such partial replication allows individual replicas to handle independent parts of the workload in parallel, thereby increasing the system’s overall scalability.
With the introduction of logical replication using a publish and subscribe framework, the rds_superuser role can now set up a custom self-managed replication for PostgreSQL versions 10 and above. Because logical replication is a database-level solution and has possibilities of replicating some or all tables of a database, it’s still recommended to consider AWS-generated read replicas for Amazon RDS for PostgreSQL and Aurora PostgreSQL to serve the read traffic of the primary production application by elastically scaling out beyond the capacity constraints of a single master DB instance. For more information, see Working with PostgreSQL Read Replicas in Amazon RDS and Replication with Amazon Aurora PostgreSQL. We cover further considerations around the features of logical replication later in this post.
Logical replication uses a publish and subscribe model wherein subscribers pull data from the publications they subscribe to. Logical replication starts by copying a snapshot of initial existing data on the publisher database. When complete, the changes on the publisher (INSERT, UPDATE, and DELETE) are relayed to the subscriber in near-real time. The subscriber applies data in the same order in which commits were made on the publisher to ensure transactional consistency.
This is in contrast with physical replication, where exact block addresses are used for a byte-by-byte replication.
The following diagram represents the data flow for initial data copy and synchronization.
The process includes the following steps:
The following diagram illustrates the flow for transactional data (after the initial snapshot is copied).
The process includes the following steps:
Before implementing this solution, you must set up logical replication. For instructions for Amazon RDS for PostgreSQL, see Logical Replication for PostgreSQL on Amazon RDS. For instructions for Aurora PostgreSQL, see Configuring Logical Replication.
Some common considerations when using logical replication include the following:
For this post, we use Amazon RDS for PostgreSQL (version 10.4) as the publisher instance and a self-managed PostgreSQL instance (version 10.6) as the subscriber. The publisher is intended to replicate all tables of the database postgres to the self-managed subscriber.
You can use the subscriber database as a publisher for other downstream databases by defining its own publications (cascading replication configuration). However, for the sake of simplicity, this post assumes that the application treats the subscriber (self-managed PostgreSQL database) as read-only.
For visual clarity, SQL statements involve the following psql prompts (customized via psqlrc file) for the publisher and subscriber:
The following code lists the connection and permission metadata:
rds-master=> \conninfo
You are connected to database "postgres" as user "nikhil" on host "pg-10-ident.cxxxxxxbz.us-east-1.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
rds-master=> \du nikhil
List of roles
Role name | Attributes | Member of
-----------+-------------------------------+------------------
nikhil | Create role, Create DB +| {rds_superuser}
| Password valid until infinity |
self-managed-replica=# \conninfo
You are connected to database "postgres" as user "khokhar" via socket in "/tmp" at port "5432".
self-managed-replica=# \du khokhar
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+------------
khokhar | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
To create your objects at the publisher before setting up replication, complete the following steps:
rds-master=> create table table_before_publication (id1 int, id2 int);
CREATE TABLE
rds-master=> insert into table_before_publication values (1,2);
INSERT 0 1
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
rds-master=> CREATE PUBLICATION alltables FOR ALL TABLES;
CREATE PUBLICATION
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
self-managed-replica=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres host=pg-10-ident.cxxxxxbz.us-east-1.rds.amazonaws.com user=nikhil password=pxxxxxd' PUBLICATION alltables;
ERROR: relation "public.table_before_publication" does not exist
self-managed-replica=# create table table_before_publication(placeholder_col1 char(2), id1 int, placeholder_col2 char(2), id2 bigint);
CREATE TABLE
self-managed-replica=# CREATE SUBSCRIPTION mysub CONNECTION 'dbname=postgres host=pg-10-ident.cxxxxxz.us-east-1.rds.amazonaws.com user=nikhil password=pxxxxxd' PUBLICATION alltables;
NOTICE: created replication slot "mysub" on publisher
CREATE SUBSCRIPTION
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
------------------------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
mysub_16391_sync_16422 | pgoutput | logical | 13934 | postgres | t | t | 31808 | | 661 | 0/12016490 | 0/120164C8
mysub | pgoutput | logical | 13934 | postgres | f | t | 31772 | | 661 | 0/12016490 | 0/120164C8
(2 rows)
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+----------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
mysub | pgoutput | logical | 13934 | postgres | f | t | 31772 | | 661 | 0/12016490 | 0/120164C8
(1 row)
rds-master=> select * from table_before_publication;
id1 | id2
-----+-----
1 | 2
(1 row)
self-managed-replica=# select * from table_before_publication;
placeholder_col1 | id1 | placeholder_col2 | id2
------------------+-----+------------------+-----
| 1 | | 2
(1 row)
rds-master=> insert into table_before_publication values (2,3);
INSERT 0 1
self-managed-replica=# select * from table_before_publication;
placeholder_col1 | id1 | placeholder_col2 | id2
------------------+-----+------------------+-----
| 1 | | 2
| 2 | | 3
(2 rows)
self-managed-replica=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+--------------------------+------------+------------
16391 | table_before_publication | 57/80197D0 | r
(1 rows)
rds-master=> select * from pg_stat_replication;
-[ RECORD 1 ]----+-----------------------------
pid | 4218
usesysid | 16389
usename | nikhil
application_name | mysub
client_addr | xxx.31.89.29
client_hostname |
client_port | 57078
backend_start | 2020-xx-xx 07:xx:49.72425+00
backend_xmin |
state | streaming
sent_lsn | 57/14000060
write_lsn | 57/14000060
flush_lsn | 57/14000060
replay_lsn | 57/14000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
To create your objects at the publisher after setting up replication, see the following code:
rds-master=> create table table_after_publication (id1 int, id2 int);
CREATE TABLE
rds-master=> insert into table_after_publication values (1,2);
INSERT 0 1
rds-master=> select * from pg_publication_tables;
pubname | schemaname | tablename
-----------+------------+-----------
alltables | public | table_before_publication
alltables | public | table_after_publication
(2 rows)
self-managed-replica=# select * from table_after_publication;
ERROR: relation "table_after_publication" does not exist
LINE 1: select * from table_after_publication;
^
self-managed-replica=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+--------------------------+------------+------------
16391 | table_before_publication | 57/80197D0 | r
self-managed-replica=# alter subscription mysub REFRESH PUBLICATION;
ERROR: relation "public.table_after_publication" does not exist
Schema must exist at the self-managed subscriber for the replication to work. Once created, refresh the metadata using alter subscription. See the following code:
self-managed-replica=# create table table_after_publication (placeholder_col1 char(2), id1 int, placeholder_col2 char(2), id2 bigint);
CREATE TABLE
self-managed-replica=# alter subscription mysub REFRESH PUBLICATION;
ALTER SUBSCRIPTION
self-managed-replica=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+--------------------------+------------+------------
16391 | table_before_publication | 57/80197D0 | r
16391 | table_after_publication | | d
(2 rows)
self-managed-replica=# select * from table_after_publication;
placeholder_col1 | id1 | placeholder _col2 | id2
-----------------+-----+-------------------+-----
| 1 | | 2
(1 row)
We successfully achieved replication of the publisher’s object (with int column) to the subscriber’s object (with bigint column). To further illustrate the behavior with incompatible conversions, create a table named schema_conversion_test with int data type in the publisher instance. See the following code:
rds-master=> create table schema_conversion_test (col1 int);
CREATE TABLE
rds-master=> insert into schema_conversion_test values (1),(2);
INSERT 0 2
Create a table named schema_conversion_test with timestamp data type in the subscriber instance. See the following code:
self-managed-replica=# create table schema_conversion_test (col1 timestamp);
CREATE TABLE
Refresh the subscription and validate the data. See the following code:
self-managed-replica=# alter subscription mysub REFRESH PUBLICATION;
ALTER SUBSCRIPTION
self-managed-replica=# select srsubid, pg_filenode_relation(0,srrelid), srsublsn, srsubstate from pg_subscription_rel;
srsubid | pg_filenode_relation | srsublsn | srsubstate
---------+--------------------------+-------------+------------
16391 | table_after_publication | 57/80197D0 | r
16391 | table_before_publication | 57/8019818 | r
16391 | schema_conversion_test | | d
(3 rows)
self-managed-replica=# select * from schema_conversion_test;
id
----
(0 rows)
In the preceding code, the subscription state (srsubstate) remains at d (data is being copied); however, no new rows were found in the subscriber’s object. A quick review of the error logs generated by the subscriber outlines the actual state of replication. The logical replication worker attempts the initial synchronization via COPY but fails on the data type conversion. As the worker continues to retry, the following excerpt is continuously reproduced in the error logs accordingly:
2020-xx-xx 08:xx:06 UTC::@:[2067]:LOG: logical replication table synchronization worker for subscription "mysub", table "schema_conversion_test" has started
2020-xx-xx 08:xx:06 UTC::@:[2067]:ERROR: invalid input syntax for type path: "1"
2020-xx-xx 08:xx:06 UTC::@:[2067]:CONTEXT: COPY schema_conversion_test, line 1, column col1: "1"
2020-xx-xx 08:xx:06 UTC::@:[5334]:LOG: background worker "logical replication worker" (PID 2067) exited with exit code 1
The metadata of publication suggests UPDATE and DELETE operations are replicated. See the following code:
rds-master=> select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-----------+----------+--------------+-----------+-----------+-----------
alltables | 16389 | t | t | t | t
(1 row)
However, an UPDATE operation fails if the table schema doesn’t involve a replica identity. See the following code:
rds-master=> update table_before_publication set id1=1111 where id1=1;
ERROR: cannot update table "table_before_publication" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
A published table must have a replica identity to replicate UPDATE and DELETE operations. This configuration modifies the information written to the transaction logs (WAL) and helps in identifying the rows involved. Possible options are the following:
If any option other than FULL is set on the publisher side, a replica identity comprising the same or fewer columns must also be set on the subscriber side.
For the sake of simplicity, the replica identity is set to FULL in this post. A FULL configuration implies that the entire row of the table becomes the key and the entire row of old value is stored. This approach can become inefficient from a storage point of view (compared to other options) and should only be used as a last resort for objects that can’t have a primary key or index. For example, you can use a replica identity as FULL with audit and history tables, which typically don’t have an associated primary key or index. See the following code:
rds-master=> alter table table_before_publication REPLICA IDENTITY FULL;
ALTER TABLE
rds-master=> update table_before_publication set id1=1111 where id1=1;
UPDATE 1
rds-master=> select * from table_before_publication;
id1 | id2
------+-----
2 | 3
1111 | 2
(2 rows)
self-managed-replica=# select * from table_before_publication;
placeholder_col1 | id1 | placeholder_col2 | id2
------------------+------+------------------+-----
| 2 | | 3
| 1111 | | 2
(2 rows)
After completing this walkthrough, you should clean up your resources. See the following code:
self-managed-replica=# drop subscription mysub;
NOTICE: dropped replication slot "mysub" on publisher
DROP SUBSCRIPTION
rds-master=> select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)
rds-master=> select * from pg_publication_tables;
pubname | schemaname | tablename
-----------+------------+-----------
alltables | public | table_before_publication
alltables | public | table_after_publication
alltables | public | schema_conversion_test
(3 rows)
rds-master=> select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
-----------+----------+--------------+-----------+-----------+-----------
alltables | 16389 | t | t | t | t
(1 row)
rds-master=> drop publication alltables;
DROP PUBLICATION
rds-master=> select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
(0 rows)
rds-master=> select * from pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-----------
(0 rows)
This post illustrated the need of parallelizing independent parts of a workload and the ways to achieve it using partial data replication. You can use this approach to achieve low-downtime upgrades, break databases into shards, make online schema changes, and more. We encourage you to use the logical replication framework of community PostgreSQL in your Amazon RDS for PostgreSQL and Aurora PostgreSQL environments to set up partial replication of data without managing intermediary resources. As always, AWS welcomes feedback, so please leave your thoughts or questions in the comments.
데이터를 좀 뽑을 일이 있어서 쿼리를 짜는 와중에, 자연스럽게 ROW_NUMBER 함수를 썻다가 Syntax Error 가 나는 것을 보고 ?를 띄우며 당황스러움을 감추지 못했다.
SELECT t.*
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY week_num, ranking_type ORDER BY score DESC) AS rn
FROM (
SELECT *,
YEAR(created_at) AS year_num,
WEEK(created_at) AS week_num
FROM your_table_name
WHERE YEAR(created_at) = 2022
) AS subquery
) AS t
WHERE t.rn <= 3
ORDER BY t.week_num, t.ranking_type, t.score DESC;
그래서 이런 ROW_NUMBER 함수는 변환해서 써줘야 하는데,
이런 경우에는 JOIN 이 매우매우 좋다.
따라서 JOIN 으로 뿌셔버리고 해당 부분에 대한 문제를 해결해줫다.
물론 번외로 MySQL 8.0 에서는 지원이 된다고 하니 버전업을 읏샤읏샤 해버리고 싶다만은.. 그게 그렇게 간단한 일이 아니잖아?
[MySQL] MySQL High Availability - Replication Database (0) | 2021.07.25 |
---|---|
How to replace & to & in SQL? (0) | 2019.07.16 |
[MySQL] 레코드 데이터 치환하기 (REPLACE) (0) | 2019.05.07 |
innodb 설치 및 옵션 (0) | 2018.05.17 |
MySQL 오늘기준으로 최근 한달 동안 데이터 가져오기 (0) | 2018.05.13 |