Category: MySQL L1
-
Table Level Replication in MySQL on Windows OS
Hello guys, Hope you are going great. In this article, we are going to talk about configuring MySQL replication between two servers. Here there will be one master and one slave server. It’s simple setup there are basically 6 major steps which are below… Step 1 –> ============================================================================ /*Take dump of the tables which we…
//
-
Slow query log purge in MySQL
There are manly 3 steps… 1. Rename existing log file with _old. 2. Connect mysqladmin using socket and use flush-logs. 3. Delete _old file created in first step. Get the show query log location: mysql> show variables like slow_query_log %’; Get the socket file location: mysql> show variables like ‘socket%’; In my case I got…
//
-
Enable Data Load Local InFile
We were getting below error at the time of loading data into MySQL. Error: Programming Error: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides. Solution: Check the status of local_infile variable in MySQL… > show variables like ‘%local%’; +—————+——-+ | Variable_name | Value | +—————+——-+ |…
//
-
Read a big file using PowerShell
Get-Content C:\MySQL_Dump_8April2022\agii-tdsqlpw01_08April_11PM.sql | Select-Object -skip 0 -first 100
//
-
MySQL master – slave replication setup
Hi guys, I hope you are doing great. Today here I am going to explain about MySQL master – slave replication setup. For the setup I have two servers clota-labl01 and clota-labl02. Here on clota-labl01 I am going to setup master and clota-labl02 as slave DB. Connect master server… ++++++++++++++++++++ clota-labl01 ++++++++++++++++++++ my.cnf configuration… =================…
//
-
Out of Memory Killer in Linux OS
In Linux systems, MySQL generally has a concept called Out of Memory killer OOM controlled by the kernel. This is to prevent the possible runaway process in OS to avoid race conditions and a server crash. Since MySQL and its optimized memory buffers are memory hogs, the OS may often kill the mysqld process to…
//
-
DBA Worries Continued DBMS Concepts L1 DBA My SQL MySQL Architecture MySQL Basics MySQL L1 SQL Server
Deterministic and Nondeterministic Functions
Deterministic functions: Deterministic functions always result in the same output every time they are called with a fixed set of input values and given the same condition of the database. For example, AVG() function always results the same result given the qualifications stated above. Nondeterministic functions: Nondeterministic functions result in different output each time they…
//
-
Index in MySQL
Index Info… SELECT DISTINCT TABLE_NAME, INDEX_NAME,COLUMN_NAME, index_type FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA =’thinklouder_optimization’; SHOW INDEXES FROM table_name IN database_name; —————————————————— OPTIMIZE TABLE tbl; will rebuild the indexes and do ANALYZE; it takes time. —————————————————— ANALYZE TABLE tbl; is fast for InnoDB to rebuild the stats. With 5.6.6 it is even less needed. —————————————————— Read the…
//
-
Read a big file using PowerShell
# Command-Format: Get-Content ‘your-file-path’ -Tail <number-of-line>Get-Content ‘.\mylargefile.txt’ -Tail 5000 # Get-Content D:\MyBigFeedFile.txt | Select-Object -skip 123798766555672 -first 6
//
-
Win OS Related Quick Commands
Find Who Restarted Windows Server –Run the following command to filter the System Logs with Source as User32. Get-EventLog -LogName System | Where Source -eq User32 –You may also filter the system logs with Event ID 1074 with the following command. Get-EventLog -LogName System | Where EventID -eq 1074
//
-
My SQL Innodb Cluster Setup
Server List clota-dbl05 @@version 8.0.32-commercial 192.168.0.156 My SQL root password Secure@123 ——————————————————————- ota-dbl06 <cota-dbl06> @@version 8.0.33 192.168.0.197 My SQL root password Secure@123 ——————————————————————- clota-dbl07 192.168.0.195 @@version @@version 8.0.33 My SQL root password Secure@321 ——————————————————————- Step 1: Modify /etc/hosts with IP and server_name on all the nodes… Like below vim /etc/hosts 192.168.0.156 clota-dbl05 192.168.0.197 ota-dbl06 192.168.0.195 clota-dbl07…
//