22 Eylül 2022 Perşembe

JDBC MysqlDataSource Sınıfı

Giriş
Şu satırı dahil ederiz
import com.mysql.cj.jdbc.MysqlDataSource;
MySql JDBC sürücüsünün ismi MySQL Connector/J'dir. Bu jar dosyasının CLASSPATH içinde olması gerekir.

Bu sınıf ile com.mysql.jdbc.jdbc2.optional.MysqlDataSource farklı şeyler. İkincisi eski ve kullanılmamalı

Maven
Şöyle yaparız.
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>
Gradle
Şöyle yaparız
implementation 'mysql:mysql-connector-java:8.0.27'
setRewriteBatchedStatements metodu
Örnek
Şöyle yaparız
MysqlDataSource dataSource = new MysqlDataSource();
 
String url = "jdbc:mysql://localhost/high_performance_java_persistence?useSSL=false";
 
dataSource.setURL(url);
dataSource.setUser(username());
dataSource.setPassword(password());
 
dataSource.setRewriteBatchedStatements(true);
setURL metodu
Örnek
Şöyle yaparız
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL("jdbc:mysql://localhost/high_performance_java_persistence");
dataSource.setUser("mysql");
dataSource.setPassword("admin");
setServerName metodu
Örnek
Şöyle yaparız.
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("scott");
dataSource.setPassword("tiger");
dataSource.setServerName("myDBHost.example.org");

20 Eylül 2022 Salı

\G Modifier

Giriş
Tüm komutların açıklaması şöyle
mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
               outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
               binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...)
for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout 
or file.

For server side help, type 'help contents'
\g vs \G Modifier
Açıklaması şöyle
The \g is basically the same as ; That is, it is the terminator for a statement and that means send it to the server for parsing and execution.
\G Modifier
Açıklaması şöyle
ego       (\G) Send command to mysql server, display result vertically.
Örnek
\G kullanılmazsa çıktı şöyle olur
> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000193 |     7061 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
\G ile konsola sığmıyorsa alt alta yazılır. Şöyle yaparız
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
            File: mysql-bin.000193
        Position: 7061
    Binlog_Do_DB:
Binlog_Ignore_DB:

18 Eylül 2022 Pazar

GRANT Privileges

Giriş
Bazı haklar şöyle
SUPER, PROCESS, REPLICATION SLAVE, RELOAD, SELECT, SHUTDOWN

ALL PRIVILEGES ON
WITH GRANT OPTION ile verilirse yeni kullanıcı da başkasına hak verebilir.

Örnek
Şöyle yaparız
CREATE DATABASE myDB
CREATE USER ‘myUser’@’%’ IDENTIFIED BY ‘myPassword’; 
GRANT ALL PRIVILEGES ON myDB.* TO 'myUser'@'%';
ALL ON
Örnek - *.* Altındaki tüm Tablolar
Şöyle yaparız
mysql> CREATE USER vtuser@'%' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO vtuser@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
SYSTEM_USER ON
Açıklaması şöyle
... SYSTEM_USER privilege, which is required to drop procedures created by other users in MySQL 8.0.23. 
Örnek
Şöyle yaparız
> GRANT SYSTEM_USER ON . TO 'user'@'host';

# check the current privileges of the user
> SHOW GRANTS FOR 'user'@'host';
SHUTDOWN
Örnek
Şöyle yaparız
mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass';
mysql> FLUSH PRIVILEGES; -- Effective immediately

PURGE BINARY LOGS

BEFORE
Örnek
Şöyle yaparız
mysql> PURGE BINARY LOGS BEFORE '2021-01-01 00:00:00';
TO
Örnek
Şöyle yaparız
mysql> PURGE BINARY LOGS TO 'mysql-bin.12345';





14 Eylül 2022 Çarşamba

Vitess VReplication

VReplication 
Açıklaması şöyle. Yani advanced replication içindir.  Örneğin Re-sharding için kullanılabilir
VReplication is a low level feature in Vitess that enables creating one or more VStreams. 
Bileşenleri şöyle 
1. VStream
2. VStreamer

The story of Vitess yazısında çok bilgi var

Her şeyi gösteren şekil şöyle. Benzer bir şekil burada
1. VStream
Açıklaması şöyle. VTGate üzerinde çalışır. Binlog event'lerine benzer bilgi taşır
VTGate exposes a gRPC service called VStream. It is a server-side streaming service. Any gRPC client can subscribe to the VStream service to get a continuous stream of change events from the underlying MySQL instances. The change events that VStream emits have similar information to the MySQL binary logs of the underlying MySQL instances. A single VStream can even subscribe to multiple shards for a given keyspace, making it quite a convenient API to build CDC tools.
2. VStreamer
Açıklaması şöyle. VTTablet üzerinde çalışır.
VTTablet host runs a component called VStreamer. VStreamer fetches events from the MySQL bin log and makes them accessible to other components upstream of VTTablet.

So lets say a write arrives at a VTTablet host, the following will happen

- The write will be recorded to MySQL
- As part of persisting this write the write will also be recored in the MySQL bin log (more details on MySQL bin log here).
- VStreamer will fetch updates from the bin log and expose those events for upstream components to consume.
Scaling VReplication
Scaling VReplication yazısında açıklaması şöyle
What is VReplication? To sum its docs, it’s a tool that lets you copy and maintain a live subset of data from one database to another – similar to Shopify’s Ghostferry, but tailored for the Vitess’s world and with an orchestration layer on top of it.

VReplication is a core component of Vitess that backs many of its features, but where does its code actually run?

- Whenever you create a VReplication stream by executing a SQL statement like INSERT INTO _vt.vreplication (db_name, source, pos, ...), VTTablet that is the destination of the stream will create multiple goroutines per each stream:

- A goroutine to manage the stream. This one will stop replication if you update the stream state to “Stopped” externally

- A goroutine to fetch binlog events from source over GRPC

- A goroutine to apply fetched binlog events

- A few goroutines to manage HTTP2/GRPC connection

Full dump of goroutines
Schema şöyle

Tablolar şöyle

vreplication tablosundaki sütunlar şöyle. Bu tablolara vtgate aracılığıyla dışarıdan erişemedim. 






mysqlbinlog komutu

--base64-output seçeneği

Örnek
Şöyle yaparız. Burada içeriğin SQL cümlesi olduğu görülebilir.
mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005 

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

13 Eylül 2022 Salı

Vitess VtTablet Ekranları

Sağlıklı bir tablet şeklen şöyle. Sağlıksız bir tablet ekran görüntüsü alamadım çünkü http sunucusu vttablet container üzerinde çalışıyor ve ve vttablet sağlıklı değilken http sunucusu cevap vermedi

Sağlıksız bir tablet şöyle. Burada vttablet çalışıyor ancak mysqld üzerinde commerce isimli bir veri tabanı yok



Vitess VtGate Ekranları

Health Check
Şeklen şöyle. Her tablete bakar



7 Eylül 2022 Çarşamba

MySQL Binary Logs

Giriş
Açıklaması şöyle
In MySQL, the changes that occur within the database are logged simultaneously. The MySQL BinLog (Binary Logs) is responsible for handling these updates and hence provide a description of such events to indicate the changes made to the database being used. These can be the changes made to the data or an operation like new table creation, etc. They even provide information on statements that could have lead to a potential change.

The MySQL BinLogs serve two important purposes:
  • Replication: When working on a master server, the binary logs contain a record of the changes that have occurred. These records are sent to the slave servers to help them execute those events & make the same data changes that were made on the master server. For more information on implementing replication, you can look into the replication manual.

  • Data Recovery: Some recovery operations require using binary logs. Once the backup is restored, the recorded events are re-executed and this brings the database up to date from the time of backup.
Binary Log Formatı
3 formattan birisi kullanılıyor
1. Statement-Based Logging
2. Row-Based Logging
3. Mixed Logging

Binary log içeriğini görmek için mysqlbinlog komutu kullanılır
1. Statement-Based Logging
Açıklaması şöyle
When statement-based logging is enabled, statements are logged to the binary log exactly as they were executed.
Örnek
Şöyle yaparız. Burada içeriğin SQL cümlesi olduğu görülebilir.
mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005 

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

2. Row-Based Logging
Açıklaması şöyle
When row-based logging is enabled, DML statements are not logged to the binary log. Instead, each insert, update, or delete performed by the statement for each row is logged to the binary log separately. DDL statements are still logged to the binary log.
3. Mixed Logging
Açıklaması şöyle
When mixed logging is enabled, the server uses a combination of statement-based logging and row-based logging. Statement-based logging is used by default, but when the server determines a statement may not be safe for statement-based logging, it will use row-based logging instead. 
Binary Log Formatını Görmek
Şu komutlardan herhangi birisini yaparız
SELECT @@global.binlog_format;

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

SELECT variable_value FROM information_schema.global_variables

WHERE variable_name='binlog_format';

Binary Log Formatını Değiştirmek
Örnek - SQL
Şöyle yaparız
-- Global
SET GLOBAL binlog_format='ROW'; -- Session için SET SESSION binlog_format='ROW';
Örnek - Konfigürasyon
Şöyle yaparız
binlog_format=ROW
Binary Log'un çalıştığını kontrol etmek
Örnek
Şöyle yaparız
mysql> show variables like '%bin%';
+---------------------------------+----------------------+
| Variable_name                   | Value                |
+---------------------------------+----------------------+
| binlog_cache_size               | 38152                |
| binlog_format                   | MIXED                |
| innodb_locks_unsafe_for_binlog  | OFF                  |
| log_bin                         | ON                   |
| log_bin_trust_function_creators | OFF                  |
| log_bin_trust_routine_creators  | OFF                  |
| max_binlog_cache_size           | 18446744073709547520 |
| max_binlog_size                 | 419430400            |
| sql_log_bin                     | ON                   |
| sync_binlog                     | 0                    |
+---------------------------------+----------------------+
10 rows in set (0.00 sec)
Binary Dosyalarını Silmek
PURGE BINARY LOGS ile elle silinebilir
Konfigürasyon Dosyasına otomatik silme eklenebilir.



2 Eylül 2022 Cuma

Vitess VtTablet Nedir?

Giriş
Açıklaması şöyle
Each MySQL server is assigned a vttablet
- A daemon/sidecar
- Controls the mysqld process
- Interacts with the mysqld server
- Typically on same host as mysqld
Ayrıca "connection pooling" yapar ve kendi "cache" mekanizması vardır. VtTablet podu içinde 3 tane container var. Bu container isimleri şöyle vttabletmysqldmysqld-exporter.

VtTablet Container Hataları
Örnek - logs
Şöyle yaparız. Burada tablet bir master arıyor ancak bulamıyor. Daha sonra REPLICA Serving olmak istiyor ancak vt_adv veri tabanına bağlanamıyor
$ kubectl logs adv-vitess-cluster-vttablet-az1-1330809953-8066577e -c vttablet -n rlwy-08 | more
ERROR: logging before flag.Parse: E0902 18:00:44.370487       1 syslogger.go:149] can't connect to syslog
I0902 18:00:44.419559       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:44.419736       1 streamlog.go:158] Streaming logs from TabletServer at /debug/querylog.
I0902 18:00:44.419756       1 streamlog.go:158] Streaming logs from TxLog at /debug/txlog.
I0902 18:00:44.420854       1 vttablet.go:145] Loaded config file  successfully:
cacheResultFields: true
consolidator: enable
db:
  allprivs:
    password: '****'
    useSsl: true
    user: vt_allprivs
  app:
    password: '****'
    useSsl: true
    user: vt_app
  appdebug:
    password: '****'
    useSsl: true
    user: vt_appdebug
  charset: utf8mb4
  dba:
    password: '****'
    useSsl: true
    user: vt_dba
  filtered:
    password: '****'
    useSsl: true
    user: vt_filtered
  repl:
    password: '****'
    useSsl: true
    user: vt_repl
gracePeriods: {}
healthcheck:
  degradedThresholdSeconds: 30
  intervalSeconds: 5
  unhealthyThresholdSeconds: 7200
hotRowProtection:
  maxConcurrency: 5
  maxGlobalQueueSize: 1000
  maxQueueSize: 20
  mode: disable
messagePostponeParallelism: 4
olapReadPool:
  idleTimeoutSeconds: 1800
  size: 96
oltp:
  maxRows: 100000
  queryTimeoutSeconds: 900
  txTimeoutSeconds: 30
oltpReadPool:
  idleTimeoutSeconds: 1800
  maxWaiters: 5000
  size: 96
queryCacheLFU: true
queryCacheMemory: 33554432
queryCacheSize: 5000
replicationTracker:
  heartbeatIntervalSeconds: 1
  mode: polling
rowStreamer:
  maxInnoDBTrxHistLen: 1000000
  maxMySQLReplLagSecs: 43200
schemaReloadIntervalSeconds: 1800
signalSchemaChangeReloadIntervalSeconds: 5
signalWhenSchemaChange: true
streamBufferSize: 32768
txPool:
  idleTimeoutSeconds: 1800
  maxWaiters: 5000
  size: 300
  timeoutSeconds: 1
I0902 18:00:44.420892       1 mycnf_flag.go:123] No mycnf_server_id, no mycnf-file specified, using default config for server id 1330809953: /vt/vtdataroot/vt_1330809953/my.cnf
I0902 18:00:44.421282       1 dbconfigs.go:388] DBConfigs: allprivs:
  password: '****'
  useSsl: true
  user: vt_allprivs
app:
  password: '****'
  useSsl: true
  user: vt_app
appdebug:
  password: '****'
  useSsl: true
  user: vt_appdebug
charset: utf8mb4
dba:
  password: '****'
  useSsl: true
  user: vt_dba
filtered:
  password: '****'
  useSsl: true
  user: vt_filtered
repl:
  password: '****'
  useSsl: true
  user: vt_repl

I0902 18:00:44.428869       1 tx_throttler.go:95] Initialized transaction throttler with config: &{enabled:false topoServer:<nil> throttlerConfig:<nil> healthCheckCells:[]}
I0902 18:00:44.431462       1 mysqld.go:590] execCmd: /usr/sbin/mysqld /usr/sbin/mysqld [--version]
I0902 18:00:44.448218       1 mysqld.go:604] execCmd: /usr/sbin/mysqld output: /usr/sbin/mysqld  Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)
I0902 18:00:44.448252       1 mysqld.go:172] Using flavor: mysql, version: {8 0 23}
I0902 18:00:44.486252       1 tm_init.go:211] Using hostname: 10.129.4.27 from -tablet_hostname flag.
I0902 18:00:44.486786       1 tm_init.go:477] Reading/creating keyspace and shard records for ADV/-
I0902 18:00:44.495104       1 syslogger.go:129] ADV [keyspace] created value:
I0902 18:00:44.503160       1 vschema.go:51] successfully updated vschema for keyspace ADV:
I0902 18:00:44.503215       1 locks.go:234] Locking keyspace ADV for action CreateShard
I0902 18:00:44.528213       1 syslogger.go:129] ADV/- [shard] created value: key_range:{} is_primary_serving:true
I0902 18:00:44.528266       1 locks.go:273] Unlocking keyspace ADV for successful action CreateShard
I0902 18:00:44.555520       1 locks.go:234] Locking keyspace ADV for action RebuildKeyspace
E0902 18:00:44.596535       1 tablet.go:244] unable to connect to tablet cell:"az1" uid:1330809953: node doesn't exist: /vitess/adv-vitess-cluster/local/az1/tablets/az1-1330809953/Tablet
I0902 18:00:44.603558       1 locks.go:273] Unlocking keyspace ADV for successful action RebuildKeyspace
I0902 18:00:44.617092       1 tm_init.go:544] Keyspace rebuilt: ADV
I0902 18:00:44.628763       1 syslogger.go:129] ADV/-/az1-1330809953 [tablet] created
I0902 18:00:44.629375       1 tablegc.go:154] TableGC: init
I0902 18:00:44.629511       1 tablegc.go:240] TableGC: operating
I0902 18:00:44.629638       1 shard_sync.go:70] Change to tablet state
I0902 18:00:44.629826       1 replmanager.go:87] Replication Manager: starting
I0902 18:00:44.638139       1 replmanager.go:120] Replication is stopped, reconnecting to primary.
I0902 18:00:44.639637       1 replmanager.go:127] Failed to reconnect to primary: no primary tablet for shard ADV/-, will keep retrying.
I0902 18:00:44.639670       1 updatestreamctl.go:228] Enabling update stream, dbname: vt_ADV
I0902 18:00:44.639727       1 state_manager.go:214] Starting transition to REPLICA Serving, timestamp: 0001-01-01 00:00:00 +0000 UTC
I0902 18:00:44.639766       1 executor.go:314] onlineDDL Executor - Acquiring lock - initMutex
I0902 18:00:44.639770       1 executor.go:316] onlineDDL Executor - Acquired lock - initMutex
I0902 18:00:44.639775       1 tablegc.go:205] TableGC - started execution of Close. Acquiring initMutex lock
I0902 18:00:44.639779       1 tablegc.go:207] TableGC - acquired lock
I0902 18:00:44.639782       1 tablegc.go:210] TableGC - no collector is open
I0902 18:00:44.639790       1 engine.go:90] messager Engine - started execution of Close. Acquiring mu lock
I0902 18:00:44.639793       1 engine.go:92] messager Engine - acquired mu lock
I0902 18:00:44.639796       1 engine.go:95] messager Engine is not open
E0902 18:00:44.641042       1 state_manager.go:278] Error transitioning to the desired state: REPLICA, Serving, will keep retrying: Unknown database 'vt_adv' (errno 1049) (sqlstate 42000)
I0902 18:00:44.641072       1 state_manager.go:675] State: exiting lameduck
E0902 18:00:44.641084       1 tm_state.go:323] Cannot start query service: Unknown database 'vt_adv' (errno 1049) (sqlstate 42000)
I0902 18:00:44.641099       1 tm_state.go:400] Publishing state: alias:{cell:"az1" uid:1330809953} hostname:"10.129.4.27" port_map:{key:"grpc" value:15999} port_map:{key:"vt" value:15000} keyspace:"ADV" shard:
Örnek - describe
Genel hata şöyle. Burada hem vttablet hem de mysqld hazır değil
Warning  Unhealthy  4s (x3 over 6s)  kubelet  Readiness probe failed: HTTP probe failed with statuscode: 500
Warning  Unhealthy  2s (x5 over 6s)  kubelet  Readiness probe failed: dial tcp 10.129.3.183:3306: connect: connection refused
Örnek  - describe
Şöyle yaparız. Pod'a describe yapına Kubernetes'in 3306 ve 1500 portlarına readiness probe yapamadığını görüyoruz.
$ kubectl describe pod adv-vitess-cluster-vttablet-az1-1330809953-8066577 -n rlwy-08
Name: adv-vitess-cluster-vttablet-az1-1330809953-8066577e Namespace: rlwy-08 Priority: 1000 Priority Class Name: vitess Node: rlwy-08-59r5w-worker-a-th48t.c.product-oce-private.internal/172.18.17.100 Start Time: Fri, 02 Sep 2022 18:00:04 +0000 Labels: planetscale.com/cell=az1 planetscale.com/cluster=adv-vitess-cluster planetscale.com/component=vttablet planetscale.com/keyspace=ADV planetscale.com/shard=x-x planetscale.com/tablet-index=1 planetscale.com/tablet-type=replica planetscale.com/tablet-uid=1330809953 Annotations: drain.planetscale.com/supported: ensure that the tablet is not a primary k8s.v1.cni.cncf.io/network-status: [{ "name": "openshift-sdn", "interface": "eth0", "ips": [ "10.129.4.27" ], "default": true, "dns": {} }] k8s.v1.cni.cncf.io/networks-status: [{ "name": "openshift-sdn", "interface": "eth0", "ips": [ "10.129.4.27" ], "default": true, "dns": {} }] openshift.io/scc: oce planetscale.com/desired-state-hash: 1598ad46c5d6ee6fe2839415cc44a896 planetscale.com/mysqld-config-overrides: [mysqld] lower_case_table_names = 1 planetscale.com/observed-shard-generation: 1 rollout.planetscale.com/scheduled: spec: imagePullSecrets: null Status: Running IP: 10.129.4.27 IPs: IP: 10.129.4.27 Controlled By: VitessShard/adv-vitess-cluster-adv-x-x-6510f0e0 Init Containers: init-vt-root: Container ID: cri-o://06b5eac928f97be1d41083458e5db8916bb1ce919d3420cb67fb9d30b44419a9 Image: gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80 Image ID: gcr.io/product-spanner/oce/vitess/lite@sha256:1b0b253aaed5eb27e92d37264edcd4844a012bf4026545ac8a6d4d35b79afc8b Port: <none> Host Port: <none> Command: bash -c Args: set -ex mkdir -p /mnt/vt/bin cp --no-clobber /vt/bin/mysqlctld /mnt/vt/bin/ mkdir -p /mnt/vt/config if [[ -d /vt/config/mycnf ]]; then cp --no-clobber -R /vt/config/mycnf /mnt/vt/config/ else mkdir -p /mnt/vt/config/mycnf fi mkdir -p /mnt/vt/vtdataroot ln -sf /dev/stderr /mnt/vt/config/stderr.symlink echo "log-error = /vt/config/stderr.symlink" > /mnt/vt/config/mycnf/log-error.cnf echo "binlog_format=row" > /mnt/vt/config/mycnf/rbr.cnf echo "socket = /vt/socket/mysql.sock" > /mnt/vt/config/mycnf/socket.cnf State: Terminated Reason: Completed Exit Code: 0 Started: Fri, 02 Sep 2022 18:00:25 +0000 Finished: Fri, 02 Sep 2022 18:00:25 +0000 Ready: True Restart Count: 0 Environment: <none> Mounts: /mnt/vt from vt-root (rw) /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-rjdjk (ro) Containers: vttablet: Container ID: cri-o://191b9d30157493eff6516f6fb27b7980c6616d16f45718af8853f1ac6c22fd49 Image: gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80 Image ID: gcr.io/product-spanner/oce/vitess/lite@sha256:1b0b253aaed5eb27e92d37264edcd4844a012bf4026545ac8a6d4d35b79afc8b Ports: 15000/TCP, 15999/TCP Host Ports: 0/TCP, 0/TCP Command: /vt/bin/vttablet Args: --backup_engine_implementation=xtrabackup --backup_storage_compress=true --backup_storage_implementation=file --binlog_use_v3_resharding_mode=true --db-config-app-uname=vt_app --db-config-dba-uname=vt_dba --db-config-filtered-uname=vt_filtered --db-config-repl-uname=vt_repl --db_charset=utf8mb4 --enable_replication_reporter=true --enable_semi_sync=false --enforce_strict_trans_tables=true --file_backup_storage_root=/vt/backups/adv-vitess-cluster --grpc_max_message_size=67108864 --grpc_port=15999 --health_check_interval=5s --init_db_name_override=vt_ADV --init_keyspace=ADV --init_shard=- --init_tablet_type=replica --logtostderr=true --mycnf_socket_file=/vt/socket/mysql.sock --mysqlctl_socket=/vt/socket/mysqlctl.sock --port=15000 --queryserver-config-max-result-size=100000 --queryserver-config-pool-size=96 --queryserver-config-query-timeout=900 --queryserver-config-stream-pool-size=96 --queryserver-config-transaction-cap=300 --restore_concurrency=10 --restore_from_backup=false --service_map=grpc-queryservice,grpc-tabletmanager,grpc-updatestream --tablet-path=az1-1330809953 --tablet_hostname=$(POD_IP) --topo_global_root=/vitess/adv-vitess-cluster/global --topo_global_server_address=adv-vitess-cluster-etcd-07a83994-client.rlwy-08.svc:2379 --topo_implementation=etcd2 --wait_for_backup_interval=10s --xbstream_restore_flags=--parallel=2 --xtrabackup_backup_flags=--parallel=1 --xtrabackup_stream_mode=xbstream --xtrabackup_stripes=8 --xtrabackup_user=vt_dba State: Running Started: Fri, 02 Sep 2022 18:00:44 +0000 Last State: Terminated Reason: Error Exit Code: 1 Started: Fri, 02 Sep 2022 18:00:28 +0000 Finished: Fri, 02 Sep 2022 18:00:33 +0000 Ready: False Restart Count: 2 Limits: memory: 256Mi Requests: cpu: 200m memory: 256Mi Liveness: http-get http://:web/debug/status delay=300s timeout=1s period=10s #success=1 #failure=30 Readiness: http-get http://:web/healthz delay=0s timeout=1s period=10s #success=1 #failure=3 Environment: VTROOT: /vt VTDATAROOT: /vt/vtdataroot VT_MYSQL_ROOT: /usr MYSQL_FLAVOR: MySQL80 EXTRA_MY_CNF: /vt/config/mycnf/rbr.cnf:/vt/config/mycnf/log-error.cnf:/vt/config/mycnf/socket.cnf:/pod-config/mysqld-config-overrides:/vt/config/mycnf/vtbackup.cnf POD_IP: (v1:status.podIP) Mounts: /pod-config from pod-config (ro) /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-rjdjk (ro) /vt/backups from vitess-backups (rw) /vt/config from vt-root (ro,path="config") /vt/secrets/db-init-script from db-init-script-secret (ro) /vt/socket from vt-root (rw,path="socket") /vt/vtdataroot from vt-root (rw,path="vtdataroot") mysqld: Container ID: cri-o://d51134e7ce585abe628ec2ca050a56280a7eb4fe3eef4f0c09ed92535e9cbca2 Image: gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80 Image ID: gcr.io/product-spanner/oce/vitess/lite@sha256:1b0b253aaed5eb27e92d37264edcd4844a012bf4026545ac8a6d4d35b79afc8b Port: 3306/TCP Host Port: 0/TCP Command: /vt/bin/mysqlctld Args: --db-config-dba-uname=vt_dba --db_charset=utf8mb4 --init_db_sql_file=/vt/secrets/db-init-script/init_db.sql --logtostderr=true --mysql_socket=/vt/socket/mysql.sock --socket_file=/vt/socket/mysqlctl.sock --tablet_uid=1330809953 --wait_time=2h0m0s State: Running Started: Fri, 02 Sep 2022 18:00:26 +0000 Ready: True Restart Count: 0 Limits: memory: 1256Mi Requests: cpu: 200m memory: 256Mi Readiness: tcp-socket :3306 delay=0s timeout=1s period=2s #success=1 #failure=3 Environment: VTROOT: /vt VTDATAROOT: /vt/vtdataroot VT_MYSQL_ROOT: /usr MYSQL_FLAVOR: MySQL80 EXTRA_MY_CNF: /vt/config/mycnf/rbr.cnf:/vt/config/mycnf/log-error.cnf:/vt/config/mycnf/socket.cnf:/pod-config/mysqld-config-overrides:/vt/config/mycnf/vtbackup.cnf POD_IP: (v1:status.podIP) Mounts: /pod-config from pod-config (ro) /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-rjdjk (ro) /vt/bin from vt-root (ro,path="bin") /vt/config from vt-root (ro,path="config") /vt/secrets/db-init-script from db-init-script-secret (ro) /vt/socket from vt-root (rw,path="socket") /vt/vtdataroot from vt-root (rw,path="vtdataroot") mysqld-exporter: Container ID: cri-o://b74444c299442ad1365a09e5d2aad1cd1b10e41c145ed6eb29e4c700599a4afd Image: gcr.io/product-spanner/oce/prom/mysqld-exporter:v0.11.0 Image ID: gcr.io/product-spanner/oce/prom/mysqld-exporter@sha256:9f4fb61cca309cb4a8c1b9ed9fb4aa75af0f7a21f36d3954667db37c062a0172 Port: 9104/TCP Host Port: 0/TCP Command: /bin/mysqld_exporter Args: --config.my-cnf=/vt/vtdataroot/vt_1330809953/my.cnf --collect.info_schema.tables.databases=sys,_vt State: Running Started: Fri, 02 Sep 2022 18:00:28 +0000 Ready: True Restart Count: 0 Limits: cpu: 100m memory: 128Mi Requests: cpu: 10m memory: 32Mi Environment: DATA_SOURCE_NAME: vt_dba@unix(/vt/socket/mysql.sock)/ Mounts: /pod-config from pod-config (ro) /var/run/secrets/kubernetes.io/serviceaccount from kube-api-access-rjdjk (ro) /vt/bin from vt-root (ro,path="bin") /vt/config from vt-root (ro,path="config") /vt/secrets/db-init-script from db-init-script-secret (ro) /vt/socket from vt-root (rw,path="socket") /vt/vtdataroot from vt-root (rw,path="vtdataroot") Conditions: Type Status Initialized True Ready False ContainersReady False PodScheduled True Volumes: vitess-backups: Type: PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace) ClaimName: adv-vitess-backup ReadOnly: false vt-root: Type: EmptyDir (a temporary directory that shares a pod's lifetime) Medium: SizeLimit: <unset> pod-config: Type: DownwardAPI (a volume populated by information about the pod) Items: metadata.annotations['planetscale.com/mysqld-config-overrides'] -> mysqld-config-overrides db-init-script-secret: Type: Secret (a volume populated by a Secret) SecretName: adv-vitess-cluster-config Optional: false kube-api-access-rjdjk: Type: Projected (a volume that contains injected data from multiple sources) TokenExpirationSeconds: 3607 ConfigMapName: kube-root-ca.crt ConfigMapOptional: <nil> DownwardAPI: true ConfigMapName: openshift-service-ca.crt ConfigMapOptional: <nil> QoS Class: Burstable Node-Selectors: <none> Tolerations: node.kubernetes.io/memory-pressure:NoSchedule op=Exists node.kubernetes.io/not-ready:NoExecute op=Exists for 300s node.kubernetes.io/unreachable:NoExecute op=Exists for 300s Events: Type Reason Age From Message ---- ------ ---- ---- ------- Normal Scheduled 143m default-scheduler Successfully assigned rlwy-08/adv-vitess-cluster-vttablet-az1-1330809953-8066577e to rlwy-08-59r5w-worker-a-th48t.c.product-oce-private.internal Normal AddedInterface 143m multus Add eth0 [10.129.4.27/23] from openshift-sdn Normal Pulling 143m kubelet Pulling image "gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80" Normal Pulled 143m kubelet Successfully pulled image "gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80" in 15.951468551s Normal Created 143m kubelet Created container init-vt-root Normal Started 143m kubelet Started container init-vt-root Normal Pulling 143m kubelet Pulling image "gcr.io/product-spanner/oce/prom/mysqld-exporter:v0.11.0" Normal Started 143m kubelet Started container mysqld Normal Created 143m kubelet Created container mysqld Normal Pulled 143m kubelet Container image "gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80" already present on machine Normal Pulled 143m kubelet Successfully pulled image "gcr.io/product-spanner/oce/prom/mysqld-exporter:v0.11.0" in 1.025161s Normal Started 143m (x2 over 143m) kubelet Started container vttablet Normal Created 143m (x2 over 143m) kubelet Created container vttablet Normal Pulled 143m (x2 over 143m) kubelet Container image "gcr.io/product-spanner/oce/vitess/lite:v14.0.1-mysql80" already present on machine Normal Created 143m kubelet Created container mysqld-exporter Normal Started 143m kubelet Started container mysqld-exporter Warning Unhealthy 143m (x5 over 143m) kubelet Readiness probe failed: dial tcp 10.129.4.27:3306: connect: connection refused Warning Unhealthy 143m (x3 over 143m) kubelet Readiness probe failed: Get "http://10.129.4.27:15000/healthz": dial tcp 10.129.4.27:15000: connect: connection refused Warning Unhealthy 3m37s (x949 over 143m) kubelet Readiness probe failed: HTTP probe failed with statuscode: 500

1 Eylül 2022 Perşembe

Vitess vtctldclient komutu - Backup

Giriş
vtctd poduna bash açılır. Şöyledir
kubectl exec -it adv-vitess-cluster-az1-vtctld-a22f4b1a-7c5c697f69-ggng2 -- /bin/bash
Ya da alias tanımlayarak şöyle yaparız
alias vc='kubectl exec -it $(kubectl get pod -l "planetscale.com/component=vtctld" -o jsonpath="{.items[0].metadata.name}" -n rlwy-08)  -n rlwy-08 -- /vt/bin/vtctlclient \
  --server $(kubectl get svc -l "planetscale.com/component=vtctld" -o jsonpath="{.items[0].metadata.name}" -n rlwy-08):15999'

vc ListBackups ope/-
ListBackups seçeneği
En son backup dosyası en altta listelenir

1. Tableti Primary Yapmak
Önce tabletlere bakalım
$/vt/bin/vtctlclient --server :15999 listalltablets
Şöyle yaparız. Burada tablet önce master yapılıyor. Daha sonra BackupShard ve ListBackups yapılıyor
$ /vt/bin/vtctlclient --server :15999 InitShardPrimary --force adv/- az1-0066013393
...
W0905 10:00:48.749063     233 main.go:94] W0905 10:00:48.748394 server.go:1781] primary-elect tablet az1-0066013393 is not the shard primary, proceeding anyway as -force was used
W0905 10:00:48.749106     233 main.go:94] W0905 10:00:48.748431 server.go:1787] primary-elect tablet az1-0066013393 is not a primary in the shard, proceeding anyway as -force was used
vitess@adv-vitess-cluster-az1-vtctld-a22f4b1a-7cd44588fd-5cjr6:/$

$ /vt/bin/vtctlclient --server :15999 BackupShard adv/-
$ /vt/bin/vtctlclient --server :15999 ListBackups adv/-
2022-09-05.085540.vtbackup-1311431053
2022-09-05.100424.az1-4018497418
Örnek - Hatalı Durum
Şöyledir
$ /vt/bin/vtctlclient --server :15999 BackupShard ADV/-
BackupShard Error: rpc error: code = Unknown desc = ReplicationStatus(az1-1330809953) failed: rpc error: code = Unknown desc = TabletManager.ReplicationStatus on az1-1330809953 error: no replication status (errno 100) (sqlstate HY000): no replication status (errno 100) (sqlstate HY000)
E0901 13:07:25.732743     153 main.go:103] remote error: rpc error: code = Unknown desc = ReplicationStatus(az1-1330809953) failed: rpc error: code = Unknown desc = TabletManager.ReplicationStatus on az1-1330809953 error: no replication status (errno 100) (sqlstate HY000): no replication status (errno 100) (sqlstate HY000)
Örnek
Shard kullanmıyorsak şöyle yaparız.
#vtctdlye e bash aç
kubectl exec -it -n rlwy-07 adv-vitess-cluster-az1-vtctld-a22f4b1a- -- /bin/bash $ /vt/bin/vtctlclient --server :15999 ListBackups ADV/- 2022-09-01.115828.vtbackup-0196944669
Örnek
Şöyle yaparız
# Do backup
vc --logtostderr BackupShard ope/-

# List backups 
vc ListBackups ope/-

# create ope-restore keyspace of SNAPSHOT type 
vc CreateKeyspace -- 
  --keyspace_type=SNAPSHOT 
  --base_keyspace=ope 
  --snapshot_time=2022-08-25T07:25:00Z ope-restore

# get ope-restore keyspace 
vc getKeyspace ope-restore
Restore seçeneği
Örnek
Şöyle yaparız
$ vc ListAllTablets
region1-1159134015 ope - primary 172.17.0.24:15000 172.17.0.24:3306 [] 2022-08-04T18:16:30Z
region1-1626475909 ope - replica 172.17.0.27:15000 172.17.0.27:3306 [] <null>
region1-3903730772 ope - replica 172.17.0.25:15000 172.17.0.25:3306 [] <null>
$ vc ListBackups ope/-
2022-08-04.181606.vtbackup-4216616067
2022-08-04.182648.region1-3903730772

# run manual restore of replica tablet region1-3903730772:
$ vc --logtostderr RestoreFromBackup region1-3903730772


Vitess Topology Server Ekranları

Giriş
Şöyle yaparız. Böylece http://localhost:15000/app/ ile bağlanabiliriz.
kubectl port-forward --address localhost 
  example-zone1-vtctld-1d4dcad0-6cf946ddc6-cp8s5 15000:15000&
GCP üzerinde şöyle yaptım. Bağlancak port adresine "kubectl get svc" ile bakmak gerekiyor. Böylece "http://172.18.17.99:32553/app/" ile bağlandım
kubectl expose pod adv-vitess-cluster-az1-vtctld-a22f4b1a-7c5c697f69-ggng2 
  --port=15000 
  --target-port=15000 
  --type=NodePort 
  --name topology
Topology Server ekranları yerine aynı şeyi bir tane pod'a bash shell açtıktan şöyle yaparız
$ /vt/bin/vtctlclient -server=172.30.179.0:15999 -logtostderr listalltablets
az1-1330809953 ADV - replica 10.128.4.33:15000 10.128.4.33:3306 [] <null>
az1-3415112598 ADV - primary 10.128.2.31:15000 10.128.2.31:3306 [] 2022-03-28T19:03:40Z
az1-4135592426 ADV - rdonly 10.129.2.34:15000 10.129.2.34:3306 [] <null>
Dashboard Ekranı
Dashboard ekranından linkleri takip edince tabletler şöyle
Eğer replicas sayısını 3 yaparsak şöyle olur

Eğer rdonly istersek şöyle yaparız
tabletPools:
  - cell: zone1
    type: replica
    replicas: 2
    vttablet:
      extraFlags:
        db_charset: utf8mb4
      resources:
        limits:
          memory: 256Mi
        requests:
          cpu: 100m
          memory: 256Mi
    mysqld:
      resources:
        limits:
          memory: 256Mi
        requests:
          cpu: 100m
          memory: 256Mi
    dataVolumeClaimTemplate:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi
  - cell: zone1   
    type: rdonly 
    replicas: 1
    vttablet:
      extraFlags:
        db_charset: utf8mb4
      resources:
        limits:
          memory: 256Mi
        requests:
          cpu: 100m
          memory: 256Mi
    mysqld:
      resources:
        limits:
          memory: 256Mi
        requests:
          cpu: 100m
          memory: 256Mi
    dataVolumeClaimTemplate:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi 
Bu durumda tabletler şöyle olur

Eğer iki tane cell yaparsak şöyle olur. az1 ve az2 isimli iki tane cell veya "availability zone" var. Sadece tek master var
Actions
Master için şeklen şöyle
Replica için şöyle


Menüler şöyle
Status altında
- Ping
- Refresh State
- Run Health Check
Change altında
- Ignore Health Error
- Delete Table
Schema Ekranı
Ben schema kullanmadığım için boş geliyor. Şeklen şöyle

Topology Ekranı
Bir tane cell olduğu için şöyle



Topology bread crumb şeklinde gidiyor. Şeklen şöyle. Burada az altındaki tabletlerden bir tanesi görülebilir.





CREATE EVENT - Scheduled Task İçindir

Örnek Şöyle yaparız CREATE EVENT myevent     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR     DO       UPDATE myschema.mytable SET myc...