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



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 ...