22 Ocak 2023 Pazar

Don’t Use Double Quotes in MySQL For Table and Column Names

Giriş
Eğer sütun isminde white space veya değişik bir karakter varsa bu sütun ismi backtick veya double quote ile escape edilir. Ancak bu yapılmamalı. Açıklaması şöyle
you should not create table or column names which contain spaces or special characters or are reserved words
Örnek
Bu da çalışır
CREATE TABLE foo (
  `time-taken` int(10) unsigned NOT NULL 
);
Eğer --sql-mode="ANSI_QUOTES" şeklinde çalıştırdıysam 
docker run \
  -d \
  -it \ 
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=db  \
  --name mysql \
  --rm \
  -p 3306:3306 \
  -v ${PWD}/schema.sql:/docker-entrypoint-initdb.d/schema.sql \
  mysql:8.0 --sql-mode="ANSI_QUOTES"
Bu çalışır
CREATE TABLE foo (
  "time-taken" int(10) unsigned NOT NULL 
);
Bu çalışmaz. Çünkü tire aslında minus operator gibi algılanıyor
CREATE TABLE foo (
  time-taken int(10) unsigned NOT NULL 
);
Örnek
Şöyle yaparız
SELECT * from `time-taken` WHERE `int` = 0

2 Ocak 2023 Pazartesi

Semi-synchronous Replication

Giriş
Açıklaması şöyle. Master tüm replica'ların da transaction'ı commit etmesini beklemez. Sadece bazı replica'ların relay log'a yazmasını bekler. Yani aslında replica'da halen commit yapmamıştır ama relay log'a başarıyla yazdığımı bildirmiştir.
Semisynchronous replication falls between asynchronous and fully synchronous replication. The source waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side. Semisynchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica.

Compared to asynchronous replication, semisynchronous replication provides improved data integrity, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semisynchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.

Compared to fully synchronous replication, semisynchronous replication is faster, because it can be configured to balance your requirements for data integrity (the number of replicas acknowledging receipt of the transaction) with the speed of commits, which are slower due to the need to wait for replicas.
Şekle şöyle


Semi-synchronous Replication da veri kaybedebilir. Açıklaması şöyle
Semisynchronous replication between a source and its replicas operates as follows:

- A replica indicates whether it is semisynchronous-capable when it connects to the source.

- If semisynchronous replication is enabled on the source side and there is at least one semisynchronous replica, a thread that performs a transaction commit on the source blocks and waits until at least one semisynchronous replica acknowledges that it has received all events for the transaction, or until a timeout occurs.

- The replica acknowledges receipt of a transaction's events only after the events have been written to its relay log and flushed to disk.

- If a timeout occurs without any replica having acknowledged the transaction, the source reverts to asynchronous replication. When at least one semisynchronous replica catches up, the source returns to semisynchronous replication.

- Semisynchronous replication must be enabled on both the source and replica sides. If semisynchronous replication is disabled on the source, or enabled on the source but on no replicas, the source uses asynchronous replication.
Vitess Açısından
Vitess açısından şöyle. Yani Vitess veri kaybetmez, veri kaybedeceğine transaction 'ı commit' lemez.
Vitess strongly recommends the use of Semi-synchronous replication for High Availability. When enabled in Vitess, semi-sync has the following characteristics:

- The primary will only accept writes if it has at least one replica connected, and configured correctly to send semi-sync ACKs. Vitess configures the semi-sync timeout to essentially an unlimited number so that it will never fallback to asyncronous replication. This is important to prevent split brain (or alternate futures) in case of a network partition. If we can verify all replicas have stopped replicating, we know the old primary is not accepting writes, even if we are unable to contact the old primary itself.

- Tablets of type rdonly will not send semi-sync ACKs. This is intentional because rdonly tablets are not eligible to be promoted to primary, so Vitess avoids the case where a rdonly tablet is the single best candidate for election at the time of primary failure.
rpl_semi_sync_master_wait_for_slave_count Ayarı
Bu ayar Primary tarafında kullanılır. Açıklaması şöyle
The primary waits up to rpl_semi_sync_master_timeout, after which it falls back to asynchronous replication mode, committing and responding to the user even if not all expected replicas have acknowledged receipt.


Asynchronous Replication

Açıklaması şöyle. Yani veri kaybedebilir.
MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.
Şeklen şöyle


Resmin açıklaması şöyle. Burada 400 tane dump thread yaratılıyor diyor ama doğrumu bilmiyorum. Replica tarafında IO thread aldığı veriyi Relay log denilen dosyaya yazar. Replica tarafında SQL thread relay log dosyasını okur ve işler.
First, let's look into the traditional style of replication. This replication process involves 4 major steps, 2 on the source end and 2 on the replica's side.

- Whenever the master's database is modified, the change is written to a file. This file is called the binary log or popularly binlog.  Binlog is written by the same client thread, which executed the query. The binary log serves as a written record of all events that modify database structure (DDL) or content (data) (DML) from the moment the server was started.

- The master has a thread, called the dump thread, that continuously reads the master's binlog and sends it to the replicas. Dump threads are created for each replica listening to the changes. For example, if in 1 second 100 writes happen on master and there are 4 replicas connected, then 400 dump threads will be created. Each replica that connects to the source requests a copy of the binary log. Note that replicas pull the data from the source, rather than the source pushing the data to the replica.

- On the replica side, it has a thread called IO thread that receives the binlog changes that are sent by the master's dump thread and writes it to a file: called the relay log.

- The replica has another thread, called the SQL thread, that continuously reads the relay log and applies the changes to the replica server.
slave_master_info  Tablosu
Açıklaması şöyle
The mysql.slave_master_info table contains the metadata information of the connection created between the replica and the source database. Remember, we mentioned, that we should use a different user for replication because the password is saved as plain text, we can see that in slave_master_info table.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_master_info\G"
                Number_of_lines: 33
                Master_log_name: mysql-bin-1.000005
                 Master_log_pos: 156
                           Host: master
                      User_name: replicationUser
                  User_password: replicationPass
                           Port: 3306
                  Connect_retry: 60
                    Enabled_ssl: 0
                         Ssl_ca:
                     Ssl_capath:
                       Ssl_cert:
                     Ssl_cipher:
                        Ssl_key:
         Ssl_verify_server_cert: 0
                      Heartbeat: 30
                           Bind:
             Ignored_server_ids: 0
                           Uuid: f329dfab-6a5d-11ec-8e93-0242ac110002
                    Retry_count: 86400
                        Ssl_crl:
                    Ssl_crlpath:
          Enabled_auto_position: 0
                   Channel_name:
                    Tls_version:
                Public_key_path:
                 Get_public_key: 0
              Network_namespace:
   Master_compression_algorithm: uncompressed
  Master_zstd_compression_level: 3
               Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
                      Gtid_only: 0
slave_relay_log_info Tablosu
Açıklaması şöyle
mysql.slave_relay_log_info table contains the metadata information about the SQL thread which reads the relay log and applies those transactions on the replica.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_relay_log_info\G

                             Number_of_lines: 14
                              Relay_log_name: ./replica1-relay-bin.000002
                               Relay_log_pos: 1222
                             Master_log_name: mysql-bin-1.000005
                              Master_log_pos: 1003
                                   Sql_delay: 0
                           Number_of_workers: 4
                                          Id: 1
                                Channel_name:
                   Privilege_checks_username: NULL
                   Privilege_checks_hostname: NULL
                          Require_row_format: 0
             Require_table_primary_key_check: STREAM
 Assign_gtids_to_anonymous_transactions_type: OFF
Assign_gtids_to_anonymous_transactions_value:
GTID
Açıklaması şöyle. Eski yöntemde dosyadaki konum önemli. Yeni yöntemdeki dosyadaki GTID numarası kullanılıyor. Yani artık konum önemli değil.
The traditional method is based on replicating events from the source's binary log, and requires the log files and positions in them to be synchronized between source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica.
Replication Lag
Açıklaması şöyle
Replication lag is caused when either the I/O Thread or SQL Thread cannot keep up with the ingestion from the source database.

It is rarely the case, that the IO thread is causing the issue. If the I/O Thread is suffering, this means that the network connection between the master and slave is slow. We should consider enabling the slave_compressed_protocol to compress network traffic from source to replica.

If the SQL thread is lagging, then there could be broadly 2 issues, we first need to categorize the issue and we need to find out if the replication is more CPU-bound or I/O-bound. Generally speaking, if we increase resources on the replica, these issues should be mitigated, however, we should first check the nature of the problem before proceeding with any solution.

Now, the obvious question would be: how would we know, whether it's an I/O thread issue or SQL thread issue? Well, MySQL has got us covered, with only show master status and show slave status, we can calculate which thread is becoming the bottleneck. The calculation is explained pretty well in this blog, so I am not going to show the calculation, please read this blog for the calculation. https://blogs.oracle.com/mysql/post/what-causes-replication-lag
Örnek
Master'da şöyle yaparız.
# On Master
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SHOW MASTER STATUS;
Burada Master şöyle çalıştırılıyor
... --enforce-gtid-consistency='ON' \
  --log-slave-updates='ON' \
  --gtid-mode='ON' \
  --log-bin='mysql-bin-1.log'

Çıktısı şöyle
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+----------+--------------+------------------+------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+--------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin-1.000003 |      660 |              |                  | 50c17f4e-d61f-11ed-bedc-0242ac14000b:1-2 |
+--------------------+----------+--------------+------------------+------------------------------------------+
Slave'de şöyle yaparız
# On Slave
CHANGE MASTER TO MASTER_HOST='172.20.0.11', MASTER_PORT=3306, 
 MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1;"

START SLAVE;
Örnek
Master'da şöyle yaparız
CREATE USER 'replicationUser'@'%' IDENTIFIED WITH \
  mysql_native_password BY 'replicationPass'; 
GRANT REPLICATION SLAVE ON *.* TO 'replicationUser'@'%';
Açıklaması şöyle. Burada yeni bir kullanıcı yaratılıyor ve sadece ama sadece REPLICATION SLAVE hakkı veriliyor.
We have created a new user on master and given it the privilege of REPLICATION SLAVE, please note that any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. Creating a new user for replication is recommended as the user having replication slave privilege's password is saved as plain text in mysql.slave_master_info table.
Daha sonra şöyle yaparız
> SHOW MASTER STATUS;

mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysql-bin-1.000005 |      708 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
Slave'de şöyle yaparız
CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replicationUser', \
  MASTER_PASSWORD='replicationPass', MASTER_LOG_FILE='mysql-bin-1.000005';
Slave'de şöyle yaparız
SHOW SLAVE STATUS\G;
Çıktısı şöyle
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master
                  Master_User: replicationUser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-1.000005
          Read_Master_Log_Pos: 1003
               Relay_Log_File: replica1-relay-bin.000002
                Relay_Log_Pos: 1222
        Relay_Master_Log_File: mysql-bin-1.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1003
              Relay_Log_Space: 1434
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f329dfab-6a5d-11ec-8e93-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
Örnek
SQL komut satırından şöyle yaparız
> SHOW PROCESSLIST;

ID  USER HOST     DB    COMMAND TIME STATE INFO
43' 'vt_repl', '172.17.0.10:46490', NULL, 'Binlog Dump GTID',   '3183', 'Master has sent all binlog to slave; waiting for more updates', NULL
SQL komut satırından şöyle yaparız
> SHOW MASTER STATUS

File 		    	    Position  Binlog_Do_DB  Binlog_Ignore_DB Executed_Gtid_Set
vt-1330809953-bin.000001    10909     ''            ''               cb1656e8-dc03-11ec-a423-0242ac110009:1-35

25 Aralık 2022 Pazar

Debezium Connector

Giriş
1. Connector'a bir isim verilir
2. connector.class her zaman io.debezium.connector.mysql.MySqlConnector olarak belirtilir. Bu connector veri tabanından okumak içindir. Açıklaması şöyle
connector.class - class that implements capturing logic. In our case, it will read mysql binlogs.
3. Veri tabanı bağlantısı bilgisi tanımlanır. Bu alanlar şöyle
database.hostname
database.port
database.user
database.password

4. Kafka bağlantı bilgisi tanımlanır. Bunlar şöyle
database.history.kafka.bootstrap.servers
database.history.kafka.topic

5. whitelist Alanları
database.whitelist ile hangi veri tabanının kullanılacağı
table.whitelist ile hangi tabloların kullanılacağı belirtilir

database.server.\*  Alanları
Açıklaması şöyle
database.server.\* - to identify mysql server. Will be used as a prefix when creating topics.
1. database.server.id
Açıklaması şöyle
This is the id given to the MySQL server during installation. Please refer to the section “Customize default MySQL configuration”.
2. database.server.name
Açıklaması şöyle
This is any String that can be used to uniquely identify the MySQL instance. This is especially useful when we are using a MySQL cluster.
database.history.kafka.bootstrap.servers
Kafka sunucusunun adresi

database.history.kafka.topic
Açıklaması şöyle
This is the topic Debezium connector will use to store the schema changes.
Açıklaması şöyle
database.history.kafka.topic - connect will upload db schemas there, and will use it to inform from where connect should begin reading if restarted.
database.whitelist Alanı
Açıklaması şöyle
If there are multiple databases in the instance, which one(s) need to be monitored and synched.
table.whitelist Alanı
Açıklaması şöyle
Which tables in the whitelist database list need to be monitored and synched.
Örnek
Şöyle yaparız. Burada docker compose ile çalıştırılan mysql isimli sunucuya debezium ismi ve passme şifresi ile bağlanıyor. ecommerce veri tabanındaki ecommerce.product isimli tabloyu dinlemeye başlıyor. Tabloyu ecommerce-cdc isimli kafka topic'e yazıyor. Schema değişikliklerini de ecommerce-schema-cdc isimli kafka topic'e yazıyor.
name=MySqlConnector
connector.class=io.debezium.connector.mysql.MySqlConnector

# number of connector instance for horizontally scaling
tasks.max=1

# just a unique and random number here
database.server.id=83456

# MySQL connectivity information 
# Hostname of MySQL instance is “mysql” as defined in docker compose
database.hostname=mysql
database.user=debezium
database.password=passme
database.allowPublicKeyRetrieval=true

# The target database 
database.include.list=ecommerce

# The target table
table.include.list=ecommerce.product

# Kafka topic prefix for CDC. Topic naming convention: <prefix>.<database>.<table name>
# In this example, the topic for product table is ecommerce-cdc.ecommerce.product
topic.prefix=ecommerce-cdc

# Configuration to capture schema changes
schema.history.internal.kafka.bootstrap.servers=localhost:9092
schema.history.internal.kafka.topic=ecommerce-schema-cdc
database.history.* Alanı
Açıklaması şöyle
The database.history.kafka.bootstrap.servers and database.history.kafka.topic properties are used to store and manage the database schema changes in Kafka.
Örnek
Şöyle yaparız
{
  "name": "inventory-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql",
    "database.port": "3306",
    "database.user": "mysqluser",
    "database.password": "mysqlpw",
    "database.server.id": "184054",
    "database.server.name": "dbserver1",
    "database.whitelist": "inventory",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes.inventory"
  }
}
transforms* Alanı
Örnek - route
Şöyle yaparız
name=debezium-mysql-source
connector.class=io.debezium.connector.mysql.MySqlConnector
tasks.max=1
database.hostname=mysql
database.port=3306
database.user=root
database.password=<mysql-root-password>
database.server.id=223344
database.server.name=dbserver1
database.history.kafka.topic=students-schema
database.whitelist=test
table.whitelist=test.students
message.key.columns=test.students:id
database.history.kafka.bootstrap.servers=kafkaconnect-cp-kafka:9092
transforms=route
transforms.route.type=org.apache.kafka.connect.transforms.RegexRouter
transforms.route.regex=([^.data]+)\\.([^.]+)\\.([^.]+)
transforms.route.replacement=$3
transforms* için açıklaması şöyle
This set of properties are used to configure a Route SMT (Single Message Transformation). By default when the Debezium connector receives an update, it tries to send them to a topic named <database.server.name>.<database.name>.<table.name>. So in our example, it would be “dbserver1.test.students”. In this example, we only want to store the information in the “students” topic therefore, we are stipping off the first two parts.
Örnek - reroute
Açıklaması şöyle
To route change event records for multiple physical tables to the same topic, configure the topic routing transformation in the Kafka Connect configuration for the Debezium connector. Configuration of the topic routing SMT requires you to specify regular expressions that determine:

- The tables for which to route records. These tables must all have the same schema.
- The destination topic name.
Şöyle yaparız. Burada tablo isminde customers_shard olan tüm kayıtlar $1customers_all_shards topic'ine gönderiliyor
transforms=Reroute
transforms.Reroute.type=io.debezium.transforms.ByLogicalTableRouter
transforms.Reroute.topic.regex=(.*)customers_shard(.*)
transforms.Reroute.topic.replacement=$1customers_all_shards
Şöyle yaparız. Burada tablo isminde TABLE_ olan tüm kayıtlar TABLE_TOPIC topic'ine gönderiliyor. Her satır için de primary key yerine unique_id isimli sütun ekleniyor
"name": "table_connectors_v0",
    "config": {
        "connector.class": "io.debezium.connector.mysql.MySqlConnector",
        "database.user": "user",
        "database.server.id": "1111111",
        "database.hostname": "121.0.0.1",
        "database.password": "password",
        "database.history.kafka.bootstrap.servers": "121.0.0.1:9092",
        "database.history.kafka.topic": "dbhistory.table1",
        "database.history.skip.unparseable.ddl": "true",
        "database.whitelist": "dbname",
        "database.server.name": "dbname",
        "database.port": "4444",
        "include.schema.changes": "false",
        "transforms": "Reroute",
        "transforms.Reroute.type": "io.debezium.transforms.ByLogicalTableRouter",
        "transforms.Reroute.topic.regex": "(.*)TABLE_(.*)",
        "transforms.Reroute.topic.replacement": "TABLE_TOPIC",
        "transforms.Reroute.key.field.name":"unique_id",
        "table.whitelist": "dbname.TABLE_.*",
        "header.converter":"org.apache.kafka.connect.storage.SimpleHeaderConverter",
        "snapshot.mode":"schema_only"
  }
}
transforms.Reroute.key.field.name için açıklama şöyle
.. you can configure the topic routing transformation to insert a different field into the key. To do this, specify the key.field.name option and set it to a field name that does not clash with existing primary key field names
....
Name of a field to be added to the change event key. The value of this field identifies the original table name. For the SMT to add this field, key.enforce.uniqueness must be true, which is the default.




20 Aralık 2022 Salı

Docker Compose ve MySQL Debezium

Örnek
4 tane container başlatır.
confluentinc/cp-zookeeper:6.0.0
confluentinc/cp-kafka:6.0.0
confluentinc/cp-kafka-connect:latest
debezium/example-mysql:1.7
Şöyle yaparız
---
version: '2'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:6.0.0
    hostname: zookeeper
    container_name: zookeeper
    ports:
      - "2181:2181"
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
  broker:
    image: confluentinc/cp-kafka:6.0.0
    hostname: broker
    container_name: broker
    depends_on:
      - zookeeper
    ports:
      - "29092:29092"
      - "9999:9999"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: 'zookeeper:2181'
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://broker:9092,PLAINTEXT_HOST://localhost:29092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
      KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
      KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
      KAFKA_JMX_PORT: 9999
      KAFKA_JMX_HOSTNAME: localhost
  connect:
    image: confluentinc/cp-kafka-connect:latest
    hostname: connect
    container_name: connect
    depends_on:
      - broker
    ports:
      - "8083:8083"
    command:
      - bash
      - -c
      - |
        confluent-hub install --no-prompt confluentinc/kafka-connect-jdbc:latest
        confluent-hub install --no-prompt confluentinc/kafka-connect-datagen:latest
        confluent-hub install --no-prompt debezium/debezium-connector-mysql:latest
        /etc/confluent/docker/run
    environment:
      CONNECT_BOOTSTRAP_SERVERS: "broker:9092"
      CONNECT_REST_ADVERTISED_HOST_NAME: connect
      CONNECT_REST_PORT: 8083
      CONNECT_GROUP_ID: compose-connect-group
      CONNECT_CONFIG_STORAGE_TOPIC: docker-connect-configs
      CONNECT_CONFIG_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_OFFSET_FLUSH_INTERVAL_MS: 10000
      CONNECT_OFFSET_STORAGE_TOPIC: docker-connect-offsets
      CONNECT_OFFSET_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_STATUS_STORAGE_TOPIC: docker-connect-status
      CONNECT_STATUS_STORAGE_REPLICATION_FACTOR: 1
      CONNECT_KEY_CONVERTER: org.apache.kafka.connect.storage.StringConverter
      CONNECT_VALUE_CONVERTER: org.apache.kafka.connect.json.JsonConverter
      CONNECT_PLUGIN_PATH: "/usr/share/java,/usr/share/confluent-hub-components"
      CONNECT_LOG4J_LOGGERS: org.apache.zookeeper=ERROR,org.I0Itec.zkclient=ERROR,org.reflections=ERROR
      KAFKA_JMX_OPTS: -Dcom.sun.management.jmxremote=true -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Djava.rmi.server.hostname=connect -Dcom.sun.management.jmxremote.local.only=false -Dcom.sun.management.jmxremote.rmi.port=5555 -Dcom.sun.management.jmxremote.port=5555
  mysql:
    image: debezium/example-mysql:1.7
    hostname: mysql
    container_name: mysql
    depends_on:
      - broker
    environment:
      - MYSQL_ROOT_PASSWORD=debezium
      - MYSQL_USER=mysqluser
      - MYSQL_PASSWORD=mysqlpw
    ports:
      - '3306:3306'
Örnek
Şöyle yaparız. Burada debezium/connect kullanılıyor. Kafka yerine de redpanda kullanılıyor. 
# redpanda-debezium.compose.yml
version: "3.3"
services:
  redpanda:
    image: vectorized/redpanda
    ports:
      - "9092:9092"
      - "29092:29092"
    command:
      - redpanda
      - start
      - --overprovisioned
      - --smp
      - "1"
      - --memory
      - "1G"
      - --reserve-memory
      - "0M"
      - --node-id
      - "0"
      - --kafka-addr
      - PLAINTEXT://0.0.0.0:29092,OUTSIDE://0.0.0.0:9092
      - --advertise-kafka-addr
      - PLAINTEXT://redpanda:29092,OUTSIDE://redpanda:9092
      - --check=false
  connect:
    image: debezium/connect
    depends_on:
      - redpanda
    ports:
      - "8083:8083"
    environment:
      BOOTSTRAP_SERVERS: "redpanda:9092"
      GROUP_ID: "1"
      CONFIG_STORAGE_TOPIC: "inventory.configs"
      OFFSET_STORAGE_TOPIC: "inventory.offset"
      STATUS_STORAGE_TOPIC: "inventory.status"
  mysql:
    image: debezium/example-mysql:1.6
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: debezium
      MYSQL_USER: mysqluser
      MYSQL_PASSWORD: mysqlpw
Örnek
Şöyle yaparız
version: '3'

services:
  mysql:
    image: debezium/example-mysql:latest
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: dbz
      MYSQL_USER: mysqluser
      MYSQL_PASSWORD: mysqlpw
      MYSQL_DATABASE: inventory
    networks:
      - inventory

  zookeeper:
    image: debezium/zookeeper:1.0
    ports:
      - "2181:2181"
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000
    networks:
      - inventory

  kafka:
    image: debezium/kafka:1.0
    ports:
      - "9092:9092"
    environment:
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_CONNECT_BOOTSTRAP_SERVERS: kafka:9092
    networks:
      - inventory

  connect:
    image: debezium/connect:1.0
    ports:
      - "8083:8083"
    environment:
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: my_connect_configs
      OFFSET_STORAGE_TOPIC: my_connect_offsets
      STATUS_STORAGE_TOPIC: my_connect_statuses
      BOOTSTRAP_SERVERS: kafka:9092
    networks:
      - inventory
    depends_on:
      - kafka

networks:
  inventory:



14 Aralık 2022 Çarşamba

CREATE TABLE

PARTITION BY X
5 çeşit partition yöntemi var. 

Eğer bölümleme yaptıktan sonra silmek istersek şöyle yaparız
ALTER TABLE customers REMOVE PARTITIONING;

1. RANGE partitioning
RANGE partitioning yazısına taşıdım. Sütün hangi aralığa girerse o bölümlemeye yazılır

2. LIST partitioning
LIST partitioning yazısına taşıdım. Sütün hangi listeye girerse o bölümlemeye yazılır

3. HASH partitioning
HASH partitioning  yazısına taşıdım. Sütun bir hash fonksiyonunda girdikten sonra dönen değer N kadar bölümlemeye ayrılır

4. KEY partitioning
Açıklaması şöyle
KEY partitioning is similar to partitioning by HASH, except that only one or more columns to be evaluated are supplied, and the SQL server provides its own hashing function. These columns can contain other than integer values, since the hashing function supplied by SQL guarantees an integer result regardless of the column data type. An extension to this type, LINEAR KEY, is also available.
Açıklaması şöyle
For this type of partitioning, the same as the LIST partitioning, the key has to be part of a primary/unique key be it single or compound.
ALTER TABLE
Örnek
Şöyle yaparız
CREATE TABLE sales ( saleDate date, … ) 
  PARTITION BY KEY(saleDate) PARTITIONS 16;

ALTER TABLE sales 
  ADD PARTITION (date = '2016-05-14');
Örnek
Şöyle yaparız
ALTER TABLE customers PARTITION BY KEY(registered_at) PARTITIONS 4;

5. COLUMN partitioning
Açıklaması şöyle
COLUMN partitioning is a variant of RANGE and LIST partitioning. COLUMNS partitioning enables the use of multiple columns in partitioning keys. All of these columns are taken into account both for the purpose of placing rows in partitions and for the determination of which partitions are to be checked for matching rows in partition pruning.
Örnek
Şöyle yaparız
CREATE TABLE rcx (
  a INT,
  b INT,
  c CHAR(3),
  d INT
)
PARTITION BY RANGE COLUMNS(a,d,c) (
  PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
  PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
  PARTITION p2 VALUES LESS THAN (15,30,'sss'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);


LIMIT ve Covering Index + Subquery

Örnek Şöyle yaparız SELECT t1.name, t1.age, t1.gender, t1.create_time FROM student as t1    INNER JOIN     (SELECT id FROM student ORDER BY ...