Şöyle yaparız
DROP USER 'user'@'host';
30 Ocak 2023 Pazartesi
24 Ocak 2023 Salı
Sütun Tipleri - TIMESTAMP ve JDBC
Kullanım Özeti
- Ben Connector/J 8 ve üstünü kullanıyorum ve hem MySQL sunucusunun hem de uygulamamın saat dilimi UTC. Başka da bir şey yapmama gerek kalmadı.
- Eğer uygulamamın saat dilimini UTC yapamıyorsam, bağlantıya "preserveInstants=true&connectionTimeZone=UTC" eklemek yeterli.
- Eğer Hibernate kullanıyorsam şöyle yaparız
# JPA <property name="hibernate.jdbc.time_zone" value="UTC"/> #Spring spring.jpa.properties.hibernate.jdbc.time_zone=UTC
1. Bağlantı string'i
İki kullanım şekli var
- preserveInstants=true verilir ve bağlantıda belirtilen saat diliminin kullanılması istenir
- preserveInstants=false verilir ve JVM'in saat diliminin kullanılması istenir
- Eğer Bağlantı string'inde saat dilimi verilmemişse JVM saat dilimi kullanılır
Örnek
UTC için JDBC bağlantısına "preserveInstants=true&connectionTimeZone=UTC" eklenir. preserveInstants=true yerelleştirme ve belirttiğim saat dilimini kullanma anlamına geliyor.
1.1 preserveInstants=true + Saat Dilimi
preserveInstants=true için açıklama şöyle. JVM'in saat dilimi değil, bağlantıda belirtilen ayarlar kullanılıyor
When preserveInstants is true, Connector/J attempts to preserve the time instants by performing the conversions in a manner defined by the connection properties connectionTimeZone and forceConnectionTimeZoneToSession.
preserveInstants=true için bir örnek şöyle. Bu sefer değerler yerelleştirildi
Use preserveInstants=true&connectionTimeZone=SERVER . Connector/J then queries the value of the session time zone from the server, and converts the event timestamps between the session time zone and the JVM time zone. For example:Time zones: UTC+2 for JVM, UTC+1 for server sessionOriginal timestamp from client (in UTC+2): 2020-01-01 02:00:00Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (after conversion from UTC+2 to UTC+1)Timestamp value stored internally on the server: 2020-01-01 00:00:00 UTC (after internal conversion from UTC+1 to UTC)Timestamp value retrieved later into a server session in UTC+1: 2020-01-01 01:00:00 (after internal conversion from UTC to UTC+1)Timestamp values constructed by Connector/J in the same JVM time zone as before (UTC+2) and returned to an application: 2020-01-01 02:00:00 (after conversion from UTC+1 to UTC+2)Timestamp values constructed by Connector/J in another JVM time zone (say, UTC+3) and returned to an application: 2020-01-01 03:00:00 (after conversion from UTC+1 to UTC+3)Comment: Time instant is preserved.
Örnek
Şöyle yaparız. Kayıt şöyle '1970-01-01 00:00:01.000000'. Burada bağlantıda verilen UTC ayar kullanılır ve TimeStamp UTC'ye göre string'e çevrilir.
dataSource.setUrl("jdbc:mysql://172.18.17.209:32110/ADV ?preserveInstants=true&connectionTimeZone=UTC"); ... new Timestamp(1000)
1.2 preserveInstants=false
preserveInstants=false ise açıklaması şöyle. Yani JVM'in saat dilimine göre string'e çevrilir ve gönderilir. Ancak Connector/J 8.0.23 ile varsayılan davranış bu değil. Varsayılan davranış preserveInstants=true
With ‘preserveInstants=false’ Connector/J 8.0.23 always uses the JVM default time zone for rendering the values it sends to the server and for constructing the Java objects from the fetched data. It matches the default Connector/J 5.1 behavior. If ‘preserveInstants=true’ (which is the default value), Connector/J does its best to preserve the instant point on the time-line for Java instant-based objects such as java.sql.Timestamp or java.time.OffsetDateTime, instead of preserving the time’s original visual form.
preserveInstants=false için bir örnek şöyle. Yani gönderilen ve alınan değer hep aynı çıktı. Yerelleştirilmedi
Time zones: UTC for JVM, UTC+1 for server sessionOriginal timestamp from client (in UTC): 2020-01-01 01:00:00Timestamp sent to server by Connector/J: 2020-01-01 01:00:00 (no conversion)Timestamp values stored internally on the server: 2020-01-01 00:00:00 UTCTimestamp value retrieved later into a server section (in UTC+1): 2020-01-01 01:00:00Timestamp values constructed by Connector/J in some other JVM time zone then before (say, in UTC+3): 2020-01-01 01:00:00Comment: Time instant is not preserved
2. Eğer Bağlantı string'inde saat dilimi verilmemişse JVM saat dilimi kullanılır
Örnek - JVM GMT + 2 İse
new TimeStamp(0) olarak kaydedince veri tabanı '1970-01-01 02:00:00.000000' olarak gösterdi. Açıklaması şöyle
Internally a MySQL timestamp column is stored as UTC but when selecting a date MySQL will automatically convert it to the current session timezone.
Örnek - JVM UTC İse
Şöyle yapınca kaydetmedi
TimeZone.setDefault(TimeZone.getTimeZone("UTC")); ... new Timestamp(0)
Hata şöyle. Yani 1292 hatasını veriyor. Benzer bir hata da burada.
Incorrect datetime value: '1970-01-01 00:00:00' for column 'EXPIRATION_DATE' at row 1 (errno 1292) (sqlstate 22007)
Şöyle yapınca kaydetti. Kayıt şöyle '1970-01-01 00:00:01.000000'
TimeZone.setDefault(TimeZone.getTimeZone("UTC")); ... new Timestamp(1_000L)
Sütun Tipleri - TIMESTAMP ve time_zone İlişkisi
Giriş
Eğer düz SQL kullanıyorsak TIMESTAMP sütunu time_zone değişkeninden etkilenir. Veri tabanının saat diliminden etkilendiğini görmek için şöyle yaparız.mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name | Value |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone | Asia/Calcutta |
+------------------+---------------------+
mysql> create table datedemo(
-> mydatetime datetime,
-> mytimestamp timestamp
-> );
mysql> insert into datedemo values ((now()),(now()));
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+
mysql> set time_zone="america/new_york";
mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime | mytimestamp |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+2. Varsayılan DeğerBence kullanım şekli şöyle olmalı
-- Kullanmayın
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- Kullanın
ts TIMESTAMP DEFAULT (UTC_TIMESTAMP)
2.1 CURRENT_TIMESTAMPVarsayılan değer olarak CURRENT_TIMESTAMP() Kullanıyorsak - time_zone'dan etkilenir. ÖrnekŞöyle yaparız. CURRENT_TIMESTAMP ile NOW() aynı şeylerCREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Şöyle yaparız. Şu anda bağlantımın time_zone ayarı UTC olduğu için bilgisayarımın saati 10:59 olmasına rağmen, 07:59 olarak kaydediyor.INSERT INTO t1() VALUES();
SELECT * FROM t1;
+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2021-11-26 07:59:12 | 2021-11-26 07:59:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)
time_zone değerini değiştirelimSET @@session.time_zone = '+03:00';
Bu sefer farklı sonuç alırız.INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM t1;
+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2021-11-26 10:59:12 | 2021-11-26 10:59:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)
2.2 UTC_TIMESTAMPDolayısıyla bağlantının time_zone ayarı değişikliğinden etkilenmemek için UTC_TIMESTAMP() kullanılır. Ancak bazı eksikler var. Şöyle yapamıyorum, MySQL hata veriyorCREATE TABLE t1 ( ts TIMESTAMP DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP), dt DATETIME DEFAULT (UTC_TIMESTAMP) ON UPDATE (UTC_TIMESTAMP));
CREATE TABLE t1 ( ts TIMESTAMP DEFAULT (UTC_TIMESTAMP), dt DATETIME DEFAULT (UTC_TIMESTAMP));
CREATE TABLE t1 ( ts TIMESTAMP(6) DEFAULT (UTC_TIMESTAMP), dt DATETIME(6) DEFAULT (UTC_TIMESTAMP));
mysql> INSERT INTO t1() VALUES();
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM t1;
+----------------------------+----------------------------+
| ts | dt |
+----------------------------+----------------------------+
| 2021-11-26 08:17:46.000000 | 2021-11-26 08:17:46.000000 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)
Boolean Sütun Tipleri
2. BOOLEAN
Şöyle yaparız
CREATE TABLE IF NOT EXISTS ADV.ID_GENERATOR ( name VARCHAR(40), value INTEGER, increment INTEGER DEFAULT 1, max_value INTEGER, cyclic BOOLEAN DEFAULT 1, -- here region_id INT );
Örnek
Şöyle yaparız
CREATE TABLE foo ( v VARCHAR(100), b BOOLEAN, ti TINYINT, si SMALLINT, i INTEGER, bi BIGINT, dc DECIMAL, r REAL, dbl DOUBLE, tm TIME, dt DATE, ts TIMESTAMP, tstz TIMESTAMP WITH TIME ZONE );
INT Sütun Tipleri
Giriş
Şöyledir
Java karşılıkları şöyleTINYINT 1 Byte [-128,127]SMALLINT 2 Bytes [-32768,32767]MEDIUMINT 3 Bytes [-8388608,8388607}INT OR INTEGER 4 Bytes [-2147483648,2147483647]BIGINT 8 Bytes [-2^63,-2^63-1]
MySQL Type Java TypeCHAR, VARCHAR, LONGVARCHAR StringTEXT StringTINYINT byteSMALLINT shortINT (INTEGER) intBIGINT longNUMERIC, DECIMAL java.math.BigDecimalREAL floatFLOAT, DOUBLE doubleBIT booleanBINARY, VARBINARY, LONGVARBINARY byte[]BLOB, MEDIUMBLOB, LARGEBLOB byte[]DATE java.sql.DateTIME java.sql.TimeDATETIME, TIMESTAMP java.sql.Timestamp
INT ve Display Width
Açıklaması şöyle
Generally, the numbers after int are only valid when used together with zerofill
Açıklaması şöyle
In MySQL, when you define a column as int(4), the 4 in parentheses is used to specify the display width of the column, not the number of bytes it will use for storage. The display width is used only for formatting output and has no impact on the range of values that can be stored in the column. The range of values that can be stored in an int column is determined by its data type, not by the display width.
Örnek
Şöyle yaparız. Burada int(1) kullanınca halen en büyük sayıyı ekleyebildiğimizi görebiliyoruz
CREATE TABLE user ( id int(1) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; mysql> INSERT INTO user (idVALUES (4294967295); Query OK, 1 row affected (0.00 sec)
Nasıl Kullanılır ?
Display Width eğer zerofill ile birlikte kullanılırsa işe yarıyor
Örnek
Şöyle yaparız. Burada tablo yaratılırken zerofill belirtiliyor. Böylece sayılar 4 hane olacak şekilde gerekirse sıfır ile dolduruluyor
CREATE TABLE user ( id int(4) unsigned zerofill NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; mysql> INSERT INTO `user` (`id`) VALUES (1),(10),(100),(1000); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from user; +------+ | id | +------+ | 0001 | | 0010 | | 0100 | | 1000 | +------+ 4 rows in set (0.00 sec)
1. TINYINT
Java'daki Byte tipine denk gelir.
Örnek
2. SMALLINT
Java'daki Short tipine denk gelir.
Java'daki Short tipine denk gelir.
Örnek
3. INT
Java'daki Integer tipine denk gelir.
Örnek
Şöyle yaparız.
INT(11)INT(11) NOT NULL AUTO INCREMENTINT(11) NOT NULL DEFAULT '0'INT(11) DEFAULT NULLÖrnek
Şöyle yaparız.INT(10) UNSINGNED NOT NULL DEFAULT '0',CREATE TABLE list_relation
(
parent_id INT UNSIGNED NOT NULL,
...
);22 Ocak 2023 Pazar
Don’t Use Double Quotes in MySQL For Table and Column Names
Giriş
Eğer sütun isminde white space veya değişik bir karakter varsa bu sütun ismi backtick veya double quote ile escape edilir. Ancak bu yapılmamalı. Açıklaması şöyleyou should not create table or column names which contain spaces or special characters or are reserved words
Örnek
Bu da çalışır
CREATE TABLE foo ( `time-taken` int(10) unsigned NOT NULL );
Eğer --sql-mode="ANSI_QUOTES" şeklinde çalıştırdıysam
docker run \
-d \
-it \
-e MYSQL_ROOT_PASSWORD=root \
-e MYSQL_DATABASE=db \
--name mysql \
--rm \
-p 3306:3306 \
-v ${PWD}/schema.sql:/docker-entrypoint-initdb.d/schema.sql \
mysql:8.0 --sql-mode="ANSI_QUOTES"Bu çalışır
CREATE TABLE foo ( "time-taken" int(10) unsigned NOT NULL );
Bu çalışmaz. Çünkü tire aslında minus operator gibi algılanıyor
CREATE TABLE foo ( time-taken int(10) unsigned NOT NULL );
Örnek
Şöyle yaparız
SELECT * from `time-taken` WHERE `int` = 0
2 Ocak 2023 Pazartesi
Semi-synchronous Replication
Giriş
Açıklaması şöyle. Master tüm replica'ların da transaction'ı commit etmesini beklemez. Sadece bazı replica'ların relay log'a yazmasını bekler. Yani aslında replica'da halen commit yapmamıştır ama relay log'a başarıyla yazdığımı bildirmiştir.
Semisynchronous replication falls between asynchronous and fully synchronous replication. The source waits until at least one replica has received and logged the events (the required number of replicas is configurable), and then commits the transaction. The source does not wait for all replicas to acknowledge receipt, and it requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side. Semisynchronous replication therefore guarantees that if the source crashes, all the transactions that it has committed have been transmitted to at least one replica.Compared to asynchronous replication, semisynchronous replication provides improved data integrity, because when a commit returns successfully, it is known that the data exists in at least two places. Until a semisynchronous source receives acknowledgment from the required number of replicas, the transaction is on hold and not committed.Compared to fully synchronous replication, semisynchronous replication is faster, because it can be configured to balance your requirements for data integrity (the number of replicas acknowledging receipt of the transaction) with the speed of commits, which are slower due to the need to wait for replicas.
Şekle şöyle
Semisynchronous replication between a source and its replicas operates as follows:- A replica indicates whether it is semisynchronous-capable when it connects to the source.- If semisynchronous replication is enabled on the source side and there is at least one semisynchronous replica, a thread that performs a transaction commit on the source blocks and waits until at least one semisynchronous replica acknowledges that it has received all events for the transaction, or until a timeout occurs.- The replica acknowledges receipt of a transaction's events only after the events have been written to its relay log and flushed to disk.- If a timeout occurs without any replica having acknowledged the transaction, the source reverts to asynchronous replication. When at least one semisynchronous replica catches up, the source returns to semisynchronous replication.- Semisynchronous replication must be enabled on both the source and replica sides. If semisynchronous replication is disabled on the source, or enabled on the source but on no replicas, the source uses asynchronous replication.
Vitess Açısından
Vitess açısından şöyle. Yani Vitess veri kaybetmez, veri kaybedeceğine transaction 'ı commit' lemez.
Vitess strongly recommends the use of Semi-synchronous replication for High Availability. When enabled in Vitess, semi-sync has the following characteristics:
- The primary will only accept writes if it has at least one replica connected, and configured correctly to send semi-sync ACKs. Vitess configures the semi-sync timeout to essentially an unlimited number so that it will never fallback to asyncronous replication. This is important to prevent split brain (or alternate futures) in case of a network partition. If we can verify all replicas have stopped replicating, we know the old primary is not accepting writes, even if we are unable to contact the old primary itself.
- Tablets of type rdonly will not send semi-sync ACKs. This is intentional because rdonly tablets are not eligible to be promoted to primary, so Vitess avoids the case where a rdonly tablet is the single best candidate for election at the time of primary failure.
rpl_semi_sync_master_wait_for_slave_count Ayarı
Bu ayar Primary tarafında kullanılır. Açıklaması şöyle
The primary waits up to rpl_semi_sync_master_timeout, after which it falls back to asynchronous replication mode, committing and responding to the user even if not all expected replicas have acknowledged receipt.
Asynchronous Replication
Açıklaması şöyle. Yani veri kaybedebilir.
MySQL replication by default is asynchronous. The source writes events to its binary log and replicas request them when they are ready. The source does not know whether or when a replica has retrieved and processed the transactions, and there is no guarantee that any event ever reaches any replica. With asynchronous replication, if the source crashes, transactions that it has committed might not have been transmitted to any replica. Failover from source to replica in this case might result in failover to a server that is missing transactions relative to the source.
Şeklen şöyle
Resmin açıklaması şöyle. Burada 400 tane dump thread yaratılıyor diyor ama doğrumu bilmiyorum. Replica tarafında IO thread aldığı veriyi Relay log denilen dosyaya yazar. Replica tarafında SQL thread relay log dosyasını okur ve işler.
First, let's look into the traditional style of replication. This replication process involves 4 major steps, 2 on the source end and 2 on the replica's side.- Whenever the master's database is modified, the change is written to a file. This file is called the binary log or popularly binlog. Binlog is written by the same client thread, which executed the query. The binary log serves as a written record of all events that modify database structure (DDL) or content (data) (DML) from the moment the server was started.- The master has a thread, called the dump thread, that continuously reads the master's binlog and sends it to the replicas. Dump threads are created for each replica listening to the changes. For example, if in 1 second 100 writes happen on master and there are 4 replicas connected, then 400 dump threads will be created. Each replica that connects to the source requests a copy of the binary log. Note that replicas pull the data from the source, rather than the source pushing the data to the replica.- On the replica side, it has a thread called IO thread that receives the binlog changes that are sent by the master's dump thread and writes it to a file: called the relay log.- The replica has another thread, called the SQL thread, that continuously reads the relay log and applies the changes to the replica server.
slave_master_info Tablosu
Açıklaması şöyle
The mysql.slave_master_info table contains the metadata information of the connection created between the replica and the source database. Remember, we mentioned, that we should use a different user for replication because the password is saved as plain text, we can see that in slave_master_info table.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_master_info\G"
Number_of_lines: 33
Master_log_name: mysql-bin-1.000005
Master_log_pos: 156
Host: master
User_name: replicationUser
User_password: replicationPass
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: f329dfab-6a5d-11ec-8e93-0242ac110002
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 0
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
Gtid_only: 0
slave_relay_log_info Tablosu
Açıklaması şöyle
mysql.slave_relay_log_info table contains the metadata information about the SQL thread which reads the relay log and applies those transactions on the replica.
Replica üzerinde çalıştırılır Çıktısı şöyle
> select * from slave_relay_log_info\G
Number_of_lines: 14
Relay_log_name: ./replica1-relay-bin.000002
Relay_log_pos: 1222
Master_log_name: mysql-bin-1.000005
Master_log_pos: 1003
Sql_delay: 0
Number_of_workers: 4
Id: 1
Channel_name:
Privilege_checks_username: NULL
Privilege_checks_hostname: NULL
Require_row_format: 0
Require_table_primary_key_check: STREAM
Assign_gtids_to_anonymous_transactions_type: OFF
Assign_gtids_to_anonymous_transactions_value:
GTID
Açıklaması şöyle. Eski yöntemde dosyadaki konum önemli. Yeni yöntemdeki dosyadaki GTID numarası kullanılıyor. Yani artık konum önemli değil.
The traditional method is based on replicating events from the source's binary log, and requires the log files and positions in them to be synchronized between source and replica. The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between source and replica as long as all transactions committed on the source have also been applied on the replica.
Replication Lag
Açıklaması şöyle
Replication lag is caused when either the I/O Thread or SQL Thread cannot keep up with the ingestion from the source database.
It is rarely the case, that the IO thread is causing the issue. If the I/O Thread is suffering, this means that the network connection between the master and slave is slow. We should consider enabling the slave_compressed_protocol to compress network traffic from source to replica.
If the SQL thread is lagging, then there could be broadly 2 issues, we first need to categorize the issue and we need to find out if the replication is more CPU-bound or I/O-bound. Generally speaking, if we increase resources on the replica, these issues should be mitigated, however, we should first check the nature of the problem before proceeding with any solution.
Now, the obvious question would be: how would we know, whether it's an I/O thread issue or SQL thread issue? Well, MySQL has got us covered, with only show master status and show slave status, we can calculate which thread is becoming the bottleneck. The calculation is explained pretty well in this blog, so I am not going to show the calculation, please read this blog for the calculation. https://blogs.oracle.com/mysql/post/what-causes-replication-lag
Örnek
Master'da şöyle yaparız.
# On Master CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'slavepass'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; SHOW MASTER STATUS;
Burada Master şöyle çalıştırılıyor
... --enforce-gtid-consistency='ON' \ --log-slave-updates='ON' \ --gtid-mode='ON' \ --log-bin='mysql-bin-1.log'
Çıktısı şöyle
mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin-1.000003 | 660 | | | 50c17f4e-d61f-11ed-bedc-0242ac14000b:1-2 | +--------------------+----------+--------------+------------------+------------------------------------------+
Slave'de şöyle yaparız
# On Slave CHANGE MASTER TO MASTER_HOST='172.20.0.11', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_AUTO_POSITION = 1;" START SLAVE;
Örnek
Master'da şöyle yaparız
CREATE USER 'replicationUser'@'%' IDENTIFIED WITH \ mysql_native_password BY 'replicationPass'; GRANT REPLICATION SLAVE ON *.* TO 'replicationUser'@'%';
Açıklaması şöyle. Burada yeni bir kullanıcı yaratılıyor ve sadece ama sadece REPLICATION SLAVE hakkı veriliyor.
We have created a new user on master and given it the privilege of REPLICATION SLAVE, please note that any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege. Creating a new user for replication is recommended as the user having replication slave privilege's password is saved as plain text in mysql.slave_master_info table.
Daha sonra şöyle yaparız
> SHOW MASTER STATUS; mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------+ | mysql-bin-1.000005 | 708 | | | | +--------------------+----------+--------------+------------------+-------------------+
Slave'de şöyle yaparız
CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replicationUser', \ MASTER_PASSWORD='replicationPass', MASTER_LOG_FILE='mysql-bin-1.000005';
Slave'de şöyle yaparız
SHOW SLAVE STATUS\G;
Çıktısı şöyle
Slave_IO_State: Waiting for source to send event
Master_Host: master
Master_User: replicationUser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-1.000005
Read_Master_Log_Pos: 1003
Relay_Log_File: replica1-relay-bin.000002
Relay_Log_Pos: 1222
Relay_Master_Log_File: mysql-bin-1.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1003
Relay_Log_Space: 1434
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f329dfab-6a5d-11ec-8e93-0242ac110002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:Örnek
SQL komut satırından şöyle yaparız
> SHOW PROCESSLIST;ID USER HOST DB COMMAND TIME STATE INFO43' 'vt_repl', '172.17.0.10:46490', NULL, 'Binlog Dump GTID', '3183', 'Master has sent all binlog to slave; waiting for more updates', NULL
SQL komut satırından şöyle yaparız
> SHOW MASTER STATUS File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set vt-1330809953-bin.000001 10909 '' '' cb1656e8-dc03-11ec-a423-0242ac110009:1-35
Kaydol:
Kayıtlar (Atom)