Vitess vtctld komutu - Reshard, Failover vs Yani Topology İçindir

Giriş

İki tane kavram var

1. vtctld sunucusu

2. vtctld  komutu

vtctld Sunucusu

 Açıklaması şöyle. Yani topology sunucusuna Http arayüzü açılır. Ekranlar için Vitess Topology Server Ekranları yazısına bakabilirsiniz

vtctld is an HTTP server that lets you browse the information stored in the Topology Service. It is useful for troubleshooting or for getting a high-level overview of the servers and their current states.

vtctld also acts as the server for vtctlclient connections.

vtctld Komutu

Bir başka açıklama şöyle.

- Controls the topology

- Runs ad hoc operations

- API server

- Reads/writes topo

- Uses locks

- Operates on tables

Örnek

Şöyle yaparız

#!/bin/bash

VTDATAROOT="${VTDATAROOT:-${PWD}/vtdataroot}" #???

# Aliases
alias vtctlclient="command vtctlclient -server localhost:15999 -log_dir ${VTDATAROOT}/tmp -alsologtostderr"

mkdir -p vtdataroot/tmp

echo "Starting vtctld...."
vtctld \
  -topo_implementation zk2 \
  -topo_global_server_address 'localhost:21811,localhost:21812,localhost:21813' \
  -topo_global_root /vitess/global \
  -log_dir=${VTDATAROOT}/tmp \
  -port=15000 \
  -grpc_port=15999 \
  -service_map='grpc-vtctl' \
  -pid_file $VTDATAROOT/tmp/vtctld.pid \

Topology Server Hataları

Örnek

Şöyle yaparız. Burada 3 tane tablet Topology Server'a bağlanıyor. Ancak sanırım Topology Server bu tabletlerin sağlıklı olduğunu düşünmüyor

$ kubectl logs adv-vitess-cluster-az1-vtctld-a22f4b1a-85b49c8546-f8254 -n rlwy-08 | more
ERROR: logging before flag.Parse: E0902 18:00:47.342808       1 syslogger.go:149] can't connect to syslog
I0902 18:00:47.377930       1 servenv.go:100] Version: 14.0.1 (Git revision 4c118cbe48a33fab1cfdbb21574debeb23e5bcec branch 'HEAD') built on Tue Jul 26 23:14:06 UTC 2022 by vitess@b321e9084996 using go1.18.4 l
inux/amd64
I0902 18:00:47.388983       1 healthcheck.go:281] loading tablets for cells: az1
I0902 18:00:47.389072       1 healthcheck.go:301] Setting up healthcheck for cell: az1
I0902 18:00:47.389331       1 long_polling.go:221] workflow Manager listening to web traffic at /api/workflow/{create,poll,delete}
I0902 18:00:47.389370       1 websocket.go:36] workflow Manager listening to websocket traffic at /api/workflow
I0902 18:00:47.389431       1 grpc_server.go:161] Setting grpc max message size to 16777216
I0902 18:00:47.389450       1 grpc_server.go:340] Building interceptors with 0 unary interceptors and 0 stream interceptors
I0902 18:00:47.389502       1 service_map.go:64] Registering vtctld for grpc, disable it with -grpc-vtctld service_map parameter
I0902 18:00:47.389515       1 service_map.go:64] Registering vtctl for grpc, disable it with -grpc-vtctl service_map parameter
I0902 18:00:47.392611       1 grpc_server.go:242] Listening for gRPC calls on port 15999
I0902 18:00:47.392920       1 unix_socket.go:36] Not listening on socket file
I0902 18:00:47.405388       1 healthcheck.go:343] Adding tablet to healthcheck: alias:{cell:"az1" uid:1330809953} hostname:"10.129.4.27" port_map:{key:"grpc" value:15999} port_map:{key:"vt" value:15000} keyspa
ce:"ADV" shard:"-" key_range:{} type:REPLICA db_name_override:"vt_ADV" mysql_hostname:"10.129.4.27" mysql_port:3306 db_server_version:"8.0.23" default_conn_collation:45
I0902 18:00:47.408594       1 tablet_health_check.go:111] HealthCheckUpdate(Serving State): tablet: az1-1330809953 (10.129.4.27) serving false => false for ADV/- (REPLICA) reason: healthCheck update error: vtt
ablet error: tabletserver uninitialized
I0902 18:01:47.396059       1 healthcheck.go:343] Adding tablet to healthcheck: alias:{cell:"az1" uid:4135592426} hostname:"10.128.2.21" port_map:{key:"grpc" value:15999} port_map:{key:"vt" value:15000} keyspa
ce:"ADV" shard:"-" key_range:{} type:RDONLY db_name_override:"vt_ADV" mysql_hostname:"10.128.2.21" mysql_port:3306 db_server_version:"8.0.23" default_conn_collation:45
I0902 18:01:47.396174       1 healthcheck.go:343] Adding tablet to healthcheck: alias:{cell:"az1" uid:3415112598} hostname:"10.129.2.27" port_map:{key:"grpc" value:15999} port_map:{key:"vt" value:15000} keyspa
ce:"ADV" shard:"-" key_range:{} type:REPLICA db_name_override:"vt_ADV" mysql_hostname:"10.129.2.27" mysql_port:3306 db_server_version:"8.0.23" default_conn_collation:45
I0902 18:01:47.402283       1 tablet_health_check.go:111] HealthCheckUpdate(Serving State): tablet: az1-3415112598 (10.129.2.27) serving false => false for ADV/- (REPLICA) reason: healthCheck update error: vtt
ablet error: no replication status (errno 100) (sqlstate HY000)
I0902 18:01:47.402931       1 tablet_health_check.go:111] HealthCheckUpdate(Serving State): tablet: az1-4135592426 (10.128.2.21) serving false => false for ADV/- (RDONLY) reason: healthCheck update error: vtta
blet error: no replication status (errno 100) (sqlstate HY000)

Cursors

Giriş

Açıklaması şöyle

Properties of MySQL Cursors

- Non-Scrollable: You can only iterate through rows in one direction. You can't skip a row; you can't jump to a row; you can't go back to a row.

- Read-only: You can't update or delete rows using cursors.

- Asensitive: MySQL cursors point to the underlying data. It runs faster than an insensitive cursor. Insensitive cursors point to a snapshot of the underlying data, making it slower than the asenstive cursors.

Açıklaması şöyle

1. Creating a MySQL Cursor

To create a MySQL cursor, you'll need to work with the DECLARE, OPEN, FETCH, and CLOSE statements.

2. The Declare Statement

The DECLARE statement can declare variables, cursors, and handlers. There is a sequence of declarations that needs to be adhered to:

Variables

Cursors

Handlers

You must first declare at least one variable to use later with the FETCH statement later on.

DECLARE <variable_name>  <variable_type>

When declaring the cursor(s), you must attach a SELECT statement. Any valid SELECT statement will work. You also must declare at least one cursor.

DECLARE <cursor_name> CURSOR FOR <select_statement>

You also have to declare a NOT FOUND handler. When the cursor iterates and reaches the last row, it raises a condition that will be handled with the NOT FOUND handler. You can also declare other handlers depending on your needs. For example:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

3. The Open Statement

The OPEN statement initializes the result from the DECLARE cursor statement.

OPEN <cursor_name>

4. The Fetch Statement

The FETCH statement works as an iterator. It fetches the next row from the rows associated with the SELECT statement in the cursor declaration.

FETCH <cursor_name> INTO <variable_list>

The <variable_list> is one or more variables from the variables declared earlier.

FETCH <cursor_name> INTO a, b, c

If the next row exists, the variables store it; otherwise, a No Data condition with SQLSTATE of '02000' occurs. You can use the NOT FOUND handler to deal with this SQLSTATE.

5. The Close Statement

This statement closes the cursor opened in the `OPEN` statement.

CLOSE <cursor_name>

Örnek

Şöyle yaparız

DELIMITER $$ CREATE PROCEDURE cursordemo(INOUT average_goals FLOAT)

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE matches INT DEFAULT(0);
DECLARE goals INT DEFAULT(0);
DECLARE half_time_goals INT;
DECLARE team_cursor CURSOR FOR SELECT HTHG FROM epl.football WHERE (FTR = "H");
    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN team_cursor;
teams_loop:

LOOP
  FETCH team_cursor INTO half_time_goals;
  IF done THEN LEAVE teams_loop;
  END IF;

  SET goals = goals + half_time_goals;
  SET matches = matches + 1;

END LOOP teams_loop;

SET average_goals = goals / matches;
CLOSE team_cursor;

END 
$$ DELIMITER;

mysql komutu

Giriş 

Ubuntu'da kurulum için şöyle yaparız

sudo apt install mysql-client-core-8.0

Açıklaması şöyle

The equivalent of sqlplus / as sysdba connection is “mysql” terminal command with a flag -uroot. In the MySQL world, the superuser is called root. MySQL database users (including root) are defined by the name and host from where it can connect.

The information about user and hosts from where it can connect is stored in mysql.user table. With the connection attempt, MySQL checks if the client host, username and password match the row in the metadata table.

Örnek

Restore için şöyle yaparız

mysql mydatabase < some-database.sql

-D seçeneği
Bağlanılacak veritabanı adını belirtir.

-e seçeneği

Söz dizimi şöyle. Belirtilen ifade çalıştırılır. 

mysql -e "{command}"

Örnek

Şifreyi değiştirmek için şöyle yaparız.

#!/bin/bash
mysql.server start
mysql -u root -e "SET PASSWORD FOR root@'localhost' = PASSWORD(‘admin’);"

Örnek

Elimizde şöyle bir SQL olsun

SELECT c_id, c_first_name,c_last_name, c_address,last_modified_date
FROM customer
  WHERE last_modified_date >=@start_date AND last_modified_date <= @end_date;

Şöyle yaparız

$mysql –uuser_id -ppassword –h mysql-host -A \
  -e "set @start_date=${start_date}; set @end_date=${end_date};\
      source ${sql_script};"

-h seçeneği
Bağlanılacak Host adını belirtir.

-p seçeneği

-p'den sonra arada boşluk olmamalı. Şifreyi belirtir. 

Örnek

Şöyle yaparız.

mysql --host=localhost  -uroot -proot newdb < file.sql

Örnek

Şöyle yaparız

mysql -ptest

Eğer şöyle yaparsak bu -p kullanıldığı için şifre sor ve test veri tabanına bağlan demek

mysql -p test

-P seçeneği

Port numarasını belirtir

Örnek

Şöyle yaparız

mysql -h ${MYSQL_HOST} -P${MYSQL_PORT} -u root -p${MYSQL_ROOT_PASSWORD}

-u seçeneği

Kullanıcı adını belirtir. 

Örnek

Şöyle yaparız.

mysql -uabc -h 127.0.0.1 -D db -pabc

Örnek

Şöyle yaparız

$mysql -u root

Açıklaması şöyle

The default user for MySQL is root and by default it has no password.

Örnek

Şöyle yaparız

When the root password is not set this is enough. If the password is required then you 
should add the flag -p.

$mysql -u root -p

Vitess VSchema Dosyası - VIndex İçin Gerekir

Örnek

Şöyle yaparız. customerId ve sourceId vindex olarak kullanılıyor

{
  "sharded": true,
  "tables": {
    "card": {
      "column_vindexes": [
        {
          "column": [
            "customerId",
            "sourceId"
          ],
          "name": "hash"
        }
      ]
    }
  },
  "vindexes": {
    "hash": {
      "type": "hash"
    }
  }
}

Vitess mysqlctl komutu - MySQL Sunucusunu Başlatır

Giriş

Bu komut MySQL sunucusunu ayağa kaldırır. Açıklaması şöyle

mysqlctld is the server version of mysqlctl

Docker Compose ve MySQL

Giriş

Bu yazıda "Docker Compose" kullanarak MySQL veri tabanını çalıştırma örnekleri var. Sadece "Docker" için Docker ve MySQL yazısına bakabilirsiniz.

Docker ile MySQL'i çalıştırmak için belirtilen parametreler şöyle

MYSQL_ROOT_PASSWORD

MYSQL_DATABASE

MYSQL_USER

MYSQL_PASSWORD

Çalıştırılacak SQL "docker-entrypoint-initdb.d" dizini altına kopyalanır. 

Benim Kullandıklarım

Örnek

Şöyle yaparız

version: '3.9'
services:
  mysql:
    # environment variables are not a list
    environment:
       MYSQL_ROOT_PASSWORD: root
       MYSQL_DATABASE: db
       # root can log in from every host
       MYSQL_ROOT_HOST: '%'
    container_name: mysql
    ports:
      - '3306:3306'
    # For volumes, it will execute files with extensions .sh, .sql and .sql.gz that are found in
    # /docker-entrypoint-initdb.d. Files will be executed in alphabetical order.
    volumes:
      - './schema.sql:/docker-entrypoint-initdb.d/schema.sql/'
    image: 'mysql:8.0'
  
# run with
# docker-compose up

# login with
# mysql -uroot -proot

Örnek - Storing MySQL Data on Host - Yani Bind mount

Şöyle yaparız. Bu sefer verinin D:/mysqlvolume dizininde saklanmasını istiyoruz

version: '3.1'
services:
  db:
    image: mysql:8.0.27
    container_name : mysql-8
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: advuser
      MYSQL_PASS: advuser
      MYSQL_DATABASE: adv
    volumes:
      - D:/mysqlvolume:/var/lib/mysql
    ports:
      - "3307:3306"

Tekrar tablo yaratıp dolduralım. SELECT yapalım ve tablonun dolu olduğunu görelim. Sonra şöyle yaparız. Yani MySQL'i durduralım ve bu container'a ait volume'ları da sil diyelim. Ancak bu sefer docker volume kullanmadığımız için diskteki verinin kaybolmadığını görürüz.

docker-compose down -v

Tekrar başlatalım

docker-compose up

Select yaparsak verinin aynen kaldığını görebiliriz.

Örnek

Şöyle yaparız

mysql:
    image: mysql:latest
    container_name: mysql
    restart: always
    ports:
      - 3306:3306
    environment:
      - MYSQL_ROOT_PASSWORD=123456
      - MYSQL_DATABASE=dashboard
      - MYSQL_USER=dashboard
      - MYSQL_PASSWORD=123456
    volumes:
      - ./mysql/data:/var/lib/mysql
      - ./mysql/init:/docker-entrypoint-initdb.d

Örnek - Storing MySQL Data on Named Volume

Şöyle yaparız

version: '3.1'
services:
  db:
    image: mysql:8.0.27
    container_name : mysql-8
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: advuser
      MYSQL_PASS: advuser
      MYSQL_DATABASE: adv
    volumes:
      - my-datavolume:/D:/mysqlvolume
    ports:
      - "3307:3306"
volumes:
  my-datavolume:      

Bu örneği şöyle de denedim. Yani 

- my-datavolume:/D:/mysqlvolume yerine 

- my-datavolume:/var/lib/mysql

kullandım aralarında bir fark yok. Her ikisi de aynı çalıştı

version: '3.1'
services:
  db:
    image: mysql:8.0.27
    container_name : mysql-8
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: advuser
      MYSQL_PASS: advuser
      MYSQL_DATABASE: adv
    volumes:
      - my-datavolume:/var/lib/mysql
    ports:
      - "3307:3306"
volumes:
  my-datavolume:        

Çalıştırmak için şöyle yaparız. Bu terminal beklemeye başlar. Veri tabanını kapatmak için Ctrl + C yapılır

docker compose up

Bash açmak için başka bir terminalden şöyle yaparız.

docker exec -it mysql-8 bash

Docker içinden mysql açmak için şöyle yaparız. Kullanıcı adı ve şifresi root

mysql -uroot -proot

Docker dışından mysql açmak için şöyle yaparız.

mysql -h 127.0.0.1 -uroot -proot -P 3307

Veri tabanlarına bakarız

show databases;

adv veri tabanına geçeriz

use adv;

Daha sonra bir tablo yaratırız.

CREATE TABLE product (sku VARBINARY(128),description VARBINARY(128),
  price BIGINT,PRIMARY KEY(sku));

Tabloya kayıt ekleyelim

INSERT INTO product(sku,description,price) VALUES (1,2,3);

Tablo için select denemesi yapalım

SELECT * FROM product;

Veri tabanını durduralım

docker compose stop

Tekrar başlatınca verinin kaybolmadığını görürüz. Volume docker tarafından bir yerlerde saklanıyor. Yerini bilmiyorum. Şöyle yapalım. Bu sefer hem MySQL'i durdurur hem de volume'un silinmesini istiyoruz. 

docker-compose down -v

Her şeyi tekrar başlatalım

docker compose up

Yine select yapalım

SELECT * FROM product;

Bu sefer şu hatayı alırız

Table 'adv.product' doesn't exist

command

Örnek - docker-entrypoint-initdb.d

Şöyle yaparız. 

- Burada init.sql çalıştırılıyor. 

- MySQL verisi Anonymous Volume üzerinde. 

version: '3'
services:
  ...
  mysql:
    image: mysql:8.0
    command: ["mysqld", "--init-file=/docker-entrypoint-initdb.d/init.sql"]
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydatabase
    ports:
      - "3306:3306"

Örnek

Şöyle yaparız

mysql:
  image: mysql:latest
  container_name: mysql
  command:
    --server-id=12345
    --log-bin=mysql-bin
    --binlog-format=ROW
    --binlog-row-image=full
  networks:
    - tutorial
  environment:
    MYSQL_USER: foo
    MYSQL_PASSWORD: bar
    MYSQL_ROOT_PASSWORD: foobar
  volumes:
    - db_data:/var/lib/mysql

environment

Genellikle şunlar tanımlanıyor

MYSQL_DATABASE

MYSQL_USER

MYSQL_PASSWORD

MYSQL_ROOT_PASSWORD

Örnek

Şöyle yaparız Burada volume olarak hem bind mount yani host üzerindeki bir dizin kullanılıyor hem de adminer isimli bir bir DB GUI kullanılıyor. http://localhost:8888 adresine gidilir.

version: '3.9'
...
services:

  #MYSQLDB - ERP
  mysql_db:
    container_name: datasource-mysql-db
    image: mysql:latest
    restart: unless-stopped
    environment:
      MYSQL_DATABASE: ${MY_SQL_DATABASE}
      MYSQL_USER: ${MY_SQL_USER}
      MYSQL_PASSWORD: ${MY_SQL_PASSWORD}
      MYSQL_ROOT_PASSWORD: ${MY_SQL_ROOT_PASSWORD}
    deploy:
      resources:
        limits:
          cpus: "0.5"
          memory: "256M"
    ports:
      - '3306:3306'
    volumes:
      - ./mysql-data:/var/lib/mysql
    profiles:
      - datasource
    networks:
      - datastack

  #SGBD
  adminer:
    container_name: datasource-adminer
    image: adminer:latest
    deploy:
      resources:
        limits:
          cpus: "0.2"
          memory: "256M"
    restart: always
    ports:
      - 8888:8080
    profiles:
      - datasource
    networks:
      - datastack

Örnek

Şöyle yaparız. 

- Burada init/mysql/scripts/bash dizini volume olarak yükleniyor. Bu dizinde ilk başlarken çalıştırılacak SQL dosyaları var. 

- MySQL verisi Anonymous Volume üzerinde. 

- Ayrıca  MAX_ALLOWED_PACKET atanıyor. Böylece veri tabanını büyük veri ile doldurmak mümkün

version: '3.8'
services:
  my_sql_local:
    container_name: my_sql_local
    image: mysql:8.0.31
    restart: always
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: customer
      MYSQL_USER: customer_user
      MYSQL_PASSWORD: customer_password
      MAX_ALLOWED_PACKET: 1073741824
    volumes:
      - ./init/mysql/scripts/bash:/docker-entrypoint-initdb.d

init/mysql/scripts/bash dizinindeki dosya şöyle. Bu dosya döngü içinde çok büyük veriyi dolduruyor

Vitess vtgate komutu - VtGate'i Çalıştırır

Giriş

Tüm seçenekler şöyle

Usage of vtgate:
  -allowed_tablet_types value
        Specifies the tablet types this vtgate is allowed to route queries to
  -alsologtostderr
        log to standard error as well as files
  -buffer_drain_concurrency int
        Maximum number of requests retried simultaneously. More concurrency will increase the load on the PRIMARY vttablet when draining the buffer. (default 1)
  -buffer_implementation string
        Allowed values: healthcheck (default), keyspace_events (for testing) (default "healthcheck")
  -buffer_keyspace_shards string
        If not empty, limit buffering to these entries (comma separated). Entry format: keyspace or keyspace/shard. Requires --enable_buffer=true.
  -buffer_max_failover_duration duration
        Stop buffering completely if a failover takes longer than this duration. (default 20s)
  -buffer_min_time_between_failovers duration
        Minimum time between the end of a failover and the start of the next one (tracked per shard). Faster consecutive failovers will not trigger buffering. (default 1m0s)
  -buffer_size int
        Maximum number of buffered requests in flight (across all ongoing failovers). (default 10)
  -buffer_window duration
        Duration for how long a request should be buffered at most. (default 10s)
  -catch-sigpipe
        catch and ignore SIGPIPE on stdout and stderr if specified
  -cell string
        cell to use (default "test_nj")
  -cells_to_watch string
        comma-separated list of cells for watching tablets
  -consul_auth_static_file string
        JSON File to read the topos/tokens from.
  -cpu_profile string
        deprecated: use '-pprof=cpu' instead
  -datadog-agent-host string
        host to send spans to. if empty, no tracing will be done
  -datadog-agent-port string
        port to send spans to. if empty, no tracing will be done
  -dbddl_plugin string
        controls how to handle CREATE/DROP DATABASE. use it if you are using your own database provisioning service (default "fail")
  -ddl_strategy string
        Set default strategy for DDL statements. Override with @@ddl_strategy session variable (default "direct")
  -default_tablet_type value
        The default tablet type to set for queries, when one is not explicitly selected (default PRIMARY)
  -disable_local_gateway
        deprecated: if specified, this process will not route any queries to local tablets in the local cell
  -discovery_high_replication_lag_minimum_serving duration
        the replication lag that is considered too high when applying the min_number_serving_vttablets threshold (default 2h0m0s)
  -discovery_low_replication_lag duration
        the replication lag that is considered low enough to be healthy (default 30s)
  -emit_stats
        If set, emit stats to push-based monitoring and stats backends
  -enable_buffer
        Enable buffering (stalling) of primary traffic during failovers.
  -enable_buffer_dry_run
        Detect and log failover events, but do not actually buffer requests.
  -enable_direct_ddl
        Allow users to submit direct DDL statements (default true)
  -enable_online_ddl
        Allow users to submit, review and control Online DDL (default true)
  -enable_system_settings
        This will enable the system settings to be changed per session at the database connection level (default true)
  -foreign_key_mode string
        This is to provide how to handle foreign key constraint in create/alter table. Valid values are: allow, disallow (default "allow")
  -gate_query_cache_lfu
        gate server cache algorithm. when set to true, a new cache algorithm based on a TinyLFU admission policy will be used to improve cache behavior and prevent pollution from sparse queries (default true)
  -gate_query_cache_memory int
        gate server query cache size in bytes, maximum amount of memory to be cached. vtgate analyzes every incoming query and generate a query plan, these plans are being cached in a lru cache. This config controls the capacity of the lru cache. (default 33554432)
  -gate_query_cache_size int
        gate server query cache size, maximum number of queries to be cached. vtgate analyzes every incoming query and generate a query plan, these plans are being cached in a cache. This config controls the expected amount of unique entries in the cache. (default 5000)
  -gateway_implementation string
        Allowed values: discoverygateway (deprecated), tabletgateway (default) (default "tabletgateway")
  -gateway_initial_tablet_timeout duration
        At startup, the gateway will wait up to that duration to get one tablet per keyspace/shard/tablettype (default 30s)
  -grpc_auth_mode string
        Which auth plugin implementation to use (eg: static)
  -grpc_auth_mtls_allowed_substrings string
        List of substrings of at least one of the client certificate names (separated by colon).
  -grpc_auth_static_client_creds string
        when using grpc_static_auth in the server, this file provides the credentials to use to authenticate with server
  -grpc_auth_static_password_file string
        JSON File to read the users/passwords from.
  -grpc_ca string
        server CA to use for gRPC connections, requires TLS, and enforces client certificate check
  -grpc_cert string
        server certificate to use for gRPC connections, requires grpc_key, enables TLS
  -grpc_compression string
        Which protocol to use for compressing gRPC. Default: nothing. Supported: snappy
  -grpc_crl string
        path to a certificate revocation list in PEM format, client certificates will be further verified against this file during TLS handshake
  -grpc_enable_optional_tls
        enable optional TLS mode when a server accepts both TLS and plain-text connections on the same port
  -grpc_enable_tracing
        Enable GRPC tracing
  -grpc_initial_conn_window_size int
        gRPC initial connection window size
  -grpc_initial_window_size int
        gRPC initial window size
  -grpc_keepalive_time duration
        After a duration of this time, if the client doesn't see any activity, it pings the server to see if the transport is still alive. (default 10s)
  -grpc_keepalive_timeout duration
        After having pinged for keepalive check, the client waits for a duration of Timeout and if no activity is seen even after that the connection is closed. (default 10s)
  -grpc_key string
        server private key to use for gRPC connections, requires grpc_cert, enables TLS
  -grpc_max_connection_age duration
        Maximum age of a client connection before GoAway is sent. (default 2562047h47m16.854775807s)
  -grpc_max_connection_age_grace duration
        Additional grace period after grpc_max_connection_age, after which connections are forcibly closed. (default 2562047h47m16.854775807s)
  -grpc_max_message_size int
        Maximum allowed RPC message size. Larger messages will be rejected by gRPC with the error 'exceeding the max size'. (default 16777216)
  -grpc_port int
        Port to listen on for gRPC calls
  -grpc_prometheus
        Enable gRPC monitoring with Prometheus
  -grpc_server_ca string
        path to server CA in PEM format, which will be combine with server cert, return full certificate chain to clients
  -grpc_server_initial_conn_window_size int
        gRPC server initial connection window size
  -grpc_server_initial_window_size int
        gRPC server initial window size
  -grpc_server_keepalive_enforcement_policy_min_time duration
        gRPC server minimum keepalive time (default 10s)
  -grpc_server_keepalive_enforcement_policy_permit_without_stream
        gRPC server permit client keepalive pings even when there are no active streams (RPCs)
  -grpc_use_effective_callerid
        If set, and SSL is not used, will set the immediate caller id from the effective caller id's principal.
  -healthcheck_retry_delay duration
        health check retry delay (default 2ms)
  -healthcheck_timeout duration
        the health check timeout period (default 1m0s)
  -jaeger-agent-host string
        host and port to send spans to. if empty, no tracing will be done
  -keep_logs duration
        keep logs for this long (using ctime) (zero to keep forever)
  -keep_logs_by_mtime duration
        keep logs for this long (using mtime) (zero to keep forever)
  -keyspaces_to_watch value
        Specifies which keyspaces this vtgate should have access to while routing queries or accessing the vschema
  -lameduck-period duration
        keep running at least this long after SIGTERM before stopping (default 50ms)
  -legacy_replication_lag_algorithm
        use the legacy algorithm when selecting the vttablets for serving (default true)
  -lock_heartbeat_time duration
        If there is lock function used. This will keep the lock connection active by using this heartbeat (default 5s)
  -log_backtrace_at value
        when logging hits line file:N, emit a stack trace
  -log_dir string
        If non-empty, write log files in this directory
  -log_err_stacks
        log stack traces for errors
  -log_queries_to_file string
        Enable query logging to the specified file
  -log_rotate_max_size uint
        size in bytes at which logs are rotated (glog.MaxSize) (default 1887436800)
  -logtostderr
        log to standard error instead of files
  -max_memory_rows int
        Maximum number of rows that will be held in memory for intermediate results as well as the final result. (default 300000)
  -max_payload_size int
        The threshold for query payloads in bytes. A payload greater than this threshold will result in a failure to handle the query.
  -mem-profile-rate int
        deprecated: use '-pprof=mem' instead (default 524288)
  -message_stream_grace_period duration
        the amount of time to give for a vttablet to resume if it ends a message stream, usually because of a reparent. (default 30s)
  -min_number_serving_vttablets int
        the minimum number of vttablets for each replicating tablet_type (e.g. replica, rdonly) that will be continue to be used even with replication lag above discovery_low_replication_lag, but still below discovery_high_replication_lag_minimum_serving (default 2)
  -mutex-profile-fraction int
        deprecated: use '-pprof=mutex' instead
  -mysql_allow_clear_text_without_tls
        If set, the server will allow the use of a clear text password over non-SSL connections.
  -mysql_auth_server_impl string
        Which auth server implementation to use. Options: none, ldap, clientcert, static, vault. (default "static")
  -mysql_auth_server_static_file string
        JSON File to read the users/passwords from.
  -mysql_auth_server_static_string string
        JSON representation of the users/passwords config.
  -mysql_auth_static_reload_interval duration
        Ticker to reload credentials
  -mysql_auth_vault_addr string
        URL to Vault server
  -mysql_auth_vault_path string
        Vault path to vtgate credentials JSON blob, e.g.: secret/data/prod/vtgatecreds
  -mysql_auth_vault_role_mountpoint string
        Vault AppRole mountpoint; can also be passed using VAULT_MOUNTPOINT environment variable (default "approle")
  -mysql_auth_vault_role_secretidfile string
        Path to file containing Vault AppRole secret_id; can also be passed using VAULT_SECRETID environment variable
  -mysql_auth_vault_roleid string
        Vault AppRole id; can also be passed using VAULT_ROLEID environment variable
  -mysql_auth_vault_timeout duration
        Timeout for vault API operations (default 10s)
  -mysql_auth_vault_tls_ca string
        Path to CA PEM for validating Vault server certificate
  -mysql_auth_vault_tokenfile string
        Path to file containing Vault auth token; token can also be passed using VAULT_TOKEN environment variable
  -mysql_auth_vault_ttl duration
        How long to cache vtgate credentials from the Vault server (default 30m0s)
  -mysql_clientcert_auth_method string
        client-side authentication method to use. Supported values: mysql_clear_password, dialog. (default "mysql_clear_password")
  -mysql_default_workload string
        Default session workload (OLTP, OLAP, DBA) (default "OLTP")
  -mysql_ldap_auth_config_file string
        JSON File from which to read LDAP server config.
  -mysql_ldap_auth_config_string string
        JSON representation of LDAP server config.
  -mysql_ldap_auth_method string
        client-side authentication method to use. Supported values: mysql_clear_password, dialog. (default "mysql_clear_password")
  -mysql_server_bind_address string
        Binds on this address when listening to MySQL binary protocol. Useful to restrict listening to 'localhost' only for instance.
  -mysql_server_flush_delay duration
        Delay after which buffered response will be flushed to the client. (default 100ms)
  -mysql_server_port int
        If set, also listen for MySQL binary protocol connections on this port. (default -1)
  -mysql_server_query_timeout duration
        mysql query timeout
  -mysql_server_read_timeout duration
        connection read timeout
  -mysql_server_require_secure_transport
        Reject insecure connections but only if mysql_server_ssl_cert and mysql_server_ssl_key are provided
  -mysql_server_socket_path string
        This option specifies the Unix socket file to use when listening for local connections. By default it will be empty and it won't listen to a unix socket
  -mysql_server_ssl_ca string
        Path to ssl CA for mysql server plugin SSL. If specified, server will require and validate client certs.
  -mysql_server_ssl_cert string
        Path to the ssl cert for mysql server plugin SSL
  -mysql_server_ssl_crl string
        Path to ssl CRL for mysql server plugin SSL
  -mysql_server_ssl_key string
        Path to ssl key for mysql server plugin SSL
  -mysql_server_ssl_server_ca string
        path to server CA in PEM format, which will be combine with server cert, return full certificate chain to clients
  -mysql_server_tls_min_version string
        Configures the minimal TLS version negotiated when SSL is enabled. Defaults to TLSv1.2. Options: TLSv1.0, TLSv1.1, TLSv1.2, TLSv1.3.
  -mysql_server_version string
        MySQL server version to advertise.
  -mysql_server_write_timeout duration
        connection write timeout
  -mysql_slow_connect_warn_threshold duration
        Warn if it takes more than the given threshold for a mysql connection to establish
  -mysql_tcp_version string
        Select tcp, tcp4, or tcp6 to control the socket type. (default "tcp")
  -no_scatter
        when set to true, the planner will fail instead of producing a plan that includes scatter queries
  -normalize_queries
        Rewrite queries with bind vars. Turn this off if the app itself sends normalized queries with bind vars. (default true)
  -onclose_timeout duration
        wait no more than this for OnClose handlers before stopping (default 1ns)
  -onterm_timeout duration
        wait no more than this for OnTermSync handlers before stopping (default 10s)
  -opentsdb_uri string
        URI of opentsdb /api/put method
  -pid_file string
        If set, the process will write its pid to the named file, and delete it on graceful shutdown.
  -planner_version string
        Sets the default planner to use when the session has not changed it. Valid values are: V3, Gen4, Gen4Greedy and Gen4Fallback. Gen4Fallback tries the new gen4 planner and falls back to the V3 planner if the gen4 fails. All Gen4 versions should be considered experimental! (default "v3")
  -port int
        port for the server
  -pprof string
        enable profiling
  -proxy_protocol
        Enable HAProxy PROXY protocol on MySQL listener socket
  -purge_logs_interval duration
        how often try to remove old logs (default 1h0m0s)
  -querylog-filter-tag string
        string that must be present in the query for it to be logged; if using a value as the tag, you need to disable query normalization
  -querylog-format string
        format for query logs ("text" or "json") (default "text")
  -querylog-row-threshold uint
        Number of rows a query has to return or affect before being logged; not useful for streaming queries. 0 means all queries will be logged.
  -redact-debug-ui-queries
        redact full queries and bind variables from debug UI
  -remote_operation_timeout duration
        time to wait for a remote operation (default 30s)
  -retry-count int
        retry count (default 2)
  -schema_change_signal
        Enable the schema tracker
  -schema_change_signal_user string
        User to be used to send down query to vttablet to retrieve schema changes
  -security_policy string
        the name of a registered security policy to use for controlling access to URLs - empty means allow all for anyone (built-in policies: deny-all, read-only)
  -service_map value
        comma separated list of services to enable (or disable if prefixed with '-') Example: grpc-vtworker
  -sql-max-length-errors int
        truncate queries in error logs to the given length (default unlimited)
  -sql-max-length-ui int
        truncate queries in debug UIs to the given length (default 512) (default 512)
  -srv_topo_cache_refresh duration
        how frequently to refresh the topology for cached entries (default 1s)
  -srv_topo_cache_ttl duration
        how long to use cached entries for topology (default 1s)
  -srv_topo_timeout duration
        topo server timeout (default 5s)
  -stats_backend string
        The name of the registered push-based monitoring/stats backend to use
  -stats_combine_dimensions string
        List of dimensions to be combined into a single "all" value in exported stats vars
  -stats_common_tags string
        Comma-separated list of common tags for the stats backend. It provides both label and values. Example: label1:value1,label2:value2
  -stats_drop_variables string
        Variables to be dropped from the list of exported variables.
  -stats_emit_period duration
        Interval between emitting stats to all registered backends (default 1m0s)
  -statsd_address string
        Address for statsd client
  -statsd_sample_rate float
         (default 1)
  -stderrthreshold value
        logs at or above this threshold go to stderr (default 1)
  -stream_buffer_size int
        the number of bytes sent from vtgate for each stream call. It's recommended to keep this value in sync with vttablet's query-server-config-stream-buffer-size. (default 32768)
  -tablet_filters value
        Specifies a comma-separated list of 'keyspace|shard_name or keyrange' values to filter the tablets to watch
  -tablet_grpc_ca string
        the server ca to use to validate servers when connecting
  -tablet_grpc_cert string
        the cert to use to connect
  -tablet_grpc_crl string
        the server crl to use to validate server certificates when connecting
  -tablet_grpc_key string
        the key to use to connect
  -tablet_grpc_server_name string
        the server name to use to validate server certificate
  -tablet_manager_protocol string
        the protocol to use to talk to vttablet (default "grpc")
  -tablet_protocol string
        how to talk to the vttablets (default "grpc")
  -tablet_refresh_interval duration
        tablet refresh interval (default 1m0s)
  -tablet_refresh_known_tablets
        tablet refresh reloads the tablet address/port map from topo in case it changes (default true)
  -tablet_types_to_wait string
        wait till connected for specified tablet types during Gateway initialization
  -tablet_url_template string
        format string describing debug tablet url formatting. See the Go code for getTabletDebugURL() how to customize this. (default "http://{{.GetTabletHostPort}}")
  -topo_consul_lock_delay duration
        LockDelay for consul session. (default 15s)
  -topo_consul_lock_session_checks string
        List of checks for consul session. (default "serfHealth")
  -topo_consul_lock_session_ttl string
        TTL for consul session.
  -topo_consul_watch_poll_duration duration
        time of the long poll for watch queries. (default 30s)
  -topo_etcd_lease_ttl int
        Lease TTL for locks and leader election. The client will use KeepAlive to keep the lease going. (default 30)
  -topo_etcd_tls_ca string
        path to the ca to use to validate the server cert when connecting to the etcd topo server
  -topo_etcd_tls_cert string
        path to the client cert to use to connect to the etcd topo server, requires topo_etcd_tls_key, enables TLS
  -topo_etcd_tls_key string
        path to the client key to use to connect to the etcd topo server, enables TLS
  -topo_global_root string
        the path of the global topology data in the global topology server
  -topo_global_server_address string
        the address of the global topology server
  -topo_implementation string
        the topology implementation to use
  -topo_k8s_context string
        The kubeconfig context to use, overrides the 'current-context' from the config
  -topo_k8s_kubeconfig string
        Path to a valid kubeconfig file. When running as a k8s pod inside the same cluster you wish to use as the topo, you may omit this and the below arguments, and Vitess is capable of auto-discovering the correct values. https://kubernetes.io/docs/tasks/access-application-cluster/access-cluster/#accessing-the-api-from-a-pod
  -topo_k8s_namespace string
        The kubernetes namespace to use for all objects. Default comes from the context or in-cluster config
  -topo_read_concurrency int
        concurrent topo reads (default 32)
  -topo_zk_auth_file string
        auth to use when connecting to the zk topo server, file contents should be <scheme>:<auth>, e.g., digest:user:pass
  -topo_zk_base_timeout duration
        zk base timeout (see zk.Connect) (default 30s)
  -topo_zk_max_concurrency int
        maximum number of pending requests to send to a Zookeeper server. (default 64)
  -topo_zk_tls_ca string
        the server ca to use to validate servers when connecting to the zk topo server
  -topo_zk_tls_cert string
        the cert to use to connect to the zk topo server, requires topo_zk_tls_key, enables TLS
  -topo_zk_tls_key string
        the key to use to connect to the zk topo server, enables TLS
  -tracer string
        tracing service to use (default "noop")
  -tracing-enable-logging
        whether to enable logging in the tracing service
  -tracing-sampling-rate value
        sampling rate for the probabilistic jaeger sampler (default 0.1)
  -tracing-sampling-type value
        sampling strategy to use for jaeger. possible values are 'const', 'probabilistic', 'rateLimiting', or 'remote' (default const)
  -transaction_mode string
        SINGLE: disallow multi-db transactions, MULTI: allow multi-db transactions with best effort commit, TWOPC: allow multi-db transactions with 2pc commit (default "MULTI")
  -v value
        log level for V logs
  -version
        print binary version
  -vmodule value
        comma-separated list of pattern=N settings for file-filtered logging
  -vschema_ddl_authorized_users string
        List of users authorized to execute vschema ddl operations, or '%' to allow all users.
  -vtctld_addr string
        address of a vtctld instance
  -vtgate-config-terse-errors
        prevent bind vars from escaping in returned errors
  -warn_memory_rows int
        Warning threshold for in-memory results. A row count higher than this amount will cause the VtGateWarnings.ResultsExceeded counter to be incremented. (default 30000)
  -warn_payload_size int
        The warning threshold for query payloads in bytes. A payload greater than this threshold will cause the VtGateWarnings.WarnPayloadSizeExceeded counter to be incremented.
  -warn_sharded_only
        If any features that are only available in unsharded mode are used, query execution warnings will be added to the session

cells_to_watch

Açıklaması şöyle

A vtgate’s main job is to forward requests to the vttablets in the local cell. However, vtgates can go cross-cell in two situations:
1. vtgate receives queries to the master, and the master is not in the current cell.
2. vtgate was configured to go to other cells in case no local vttablets were available.

For sending master queries across cells, you must specify an additional cells_to_watch flag. This will make the vtgates watch those additional cells, and will help them keep track of the masters in those cells.

The cells_to_watch flag is a required parameter and must at least include the current cell.

Açıklaması şöyle. 

vtgate can then be configured with a list of cells to watch for tablets using the -cells_to_watch command line parameter. It can then use all tablets in all cells to route traffic. Note this is necessary to access the primary in another cell.

Örnek

Minikube üzerinde çalışan tek availability zone vtgate çıktısı şöyle

kubectl describe pod example-zone1-vtgate-bc6cde92-64bfdcfb57-5mhtn
Name:                 example-zone1-vtgate-bc6cde92-64bfdcfb57-5mhtn
Namespace:            default
Priority:             1000
Priority Class Name:  vitess
Node:                 minikube/10.10.30.220
Start Time:           Mon, 14 Dec 2020 17:29:18 +0800
Labels:               planetscale.com/cell=zone1
                      planetscale.com/cluster=example
                      planetscale.com/component=vtgate
                      pod-template-hash=64bfdcfb57
Annotations:          planetscale.com/secret-hash: af04fed47ed2a09bb59a670fe6fdb65c
Status:               Running
IP:                   172.17.0.5
IPs:                  <none>
Controlled By:        ReplicaSet/example-zone1-vtgate-bc6cde92-64bfdcfb57
Containers:
  vtgate:
    Container ID:  docker://86ca6d811b11666e076dececb0cf5371ceeb4f65816125dd968f26ac8a8fac4f
    Image:         vitess/lite:v6.0.20-20200429
    Image ID:      docker-pullable://vitess/lite@sha256:a74ba7fe9aa070ed471e9f17bcf7ba358f01bda87d52000e9ef2785f257105ec
    Ports:         15000/TCP, 15999/TCP, 3306/TCP
    Host Ports:    0/TCP, 0/TCP, 0/TCP
    Command:
      /vt/bin/vtgate
    Args:
      --buffer_max_failover_duration=10s
      --buffer_min_time_between_failovers=20s
      --cell=zone1
      --cells_to_watch=zone1
      --enable_buffer=true
      --gateway_implementation=discoverygateway
      --grpc_max_message_size=67108864
      --grpc_port=15999
      --logtostderr=true
      --mysql_auth_server_impl=static
      --mysql_auth_server_static_file=/vt/secrets/vtgate-static-auth/users.json
      --mysql_server_port=3306
      --port=15000
      --service_map=grpc-vtgateservice
      --tablet_types_to_wait=MASTER,REPLICA
      --topo_global_root=/vitess/example/global
      --topo_global_server_address=example-etcd-faf13de3-client:2379
      --topo_implementation=etcd2
    State:          Running
      Started:      Mon, 14 Dec 2020 17:31:33 +0800
    Last State:     Terminated
      Reason:       Error
      Exit Code:    1
      Started:      Mon, 14 Dec 2020 17:30:59 +0800
      Finished:     Mon, 14 Dec 2020 17:31:04 +0800
    Ready:          True
    Restart Count:  3
    Limits:
      memory:  256Mi
    Requests:
      cpu:        100m
      memory:     256Mi
    Liveness:     http-get http://:web/debug/status delay=300s timeout=1s period=10s #success=1 #failure=30
    Readiness:    http-get http://:web/debug/health delay=0s timeout=1s period=10s #success=1 #failure=3
    Environment:  <none>
    Mounts: ######挂载位置
      /var/run/secrets/kubernetes.io/serviceaccount from default-token-wv9ds (ro)
      /vt/secrets/vtgate-static-auth from vtgate-static-auth-secret (ro)
Conditions:
  Type              Status
  Initialized       True
  Ready             True
  ContainersReady   True
  PodScheduled      True
Volumes:
  vtgate-static-auth-secret:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  example-cluster-config      ####secretname名称
    Optional:    false
  default-token-wv9ds:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  default-token-wv9ds
    Optional:    false
QoS Class:       Burstable
Node-Selectors:  <none>
Tolerations:     node.kubernetes.io/not-ready:NoExecute for 300s
                 node.kubernetes.io/unreachable:NoExecute for 300s
Events:          <none>      

mysql_server_version 

Açıklaması şöyle

The problem is that MySQL 8.0 removed the query cache and associated variables, but Vitess vtgate still advertises to clients that it is a MySQL 5.5 server. The MySQL JDBC connector mistakenly believes it can send queries for deprecated variables.

https://github.com/vitessio/vitess/issues/5205

Workaround: use vtgate -mysql_server_version to advertise a mysql 8.x version string (e.g. "8.0.17 Source distribution")

Eğer bu seçeneği vermezsek şöyle bir hata alırız

java.sql.SQLException: 

target: commerce.0.primary: vttablet: rpc error: code = InvalidArgument desc = Unknown system variable 'query_cache_size' (errno 1193) ...

Örnek

Şöyle yaparız

-mysql_server_version '8.0.22'