Category: My SQL
-
Ghost Records
When any record get deleted from DB, SQL Server does not delete the records physically from disk immediately. The deleted records which exists physically in storage called Ghost Records. SQL Server does not use synchronous method to delete the records physically for DML operation because if SQL Server delete the records synchronously, this will going to drastically…
-
Export one table from one MySQL instance to other MySQL instance
Syntax Export one table: $ mysqldump -u root -p database_name table_name > dump.txt password ***** Import one table: $ mysqldump -u root -p database_name table_name < dump.txt password ***** Below was the actual request… Please copy MySQL table charge_master from clt-mysqlP_11.b_Rore to clt-mysqlD_61.SMS Step by step implementation… –Connect MySQL on clt-mysqlP_11 Step 1. Take the row count. mysql> select count(*) from…
-
InnoDB Architecture
Buffer Pool In MySQL, buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool. For efficiency…
-
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…
-
CAP Theorem
In theoretical computer science, the CAP theorem, also named Brewer’s theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees: Consistency Every read receives the most recent write or an error. Availability Every request receives a (non-error)…
-
Important SQL Server Scripts…
Restore Progress Check… ========================================================================== DECLARE @DBName VARCHAR(64) = ‘ODS’ DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery of database’, @DBName INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, ‘Recovery completed’, @DBName SELECT TOP 1 @DBName AS [DBName] ,[LogDate] ,CASE WHEN…
-
Serialized Dictionary Information (SDI)
In addition to storing metadata about database objects in the data dictionary, MySQL stores it in serialized form. This data is referred to as serialized dictionary information (SDI). InnoDB stores SDI data within its tablespace files. NDBCLUSTER stores SDI data in the NDB dictionary. Other storage engines store SDI data in .sdi files that are created for a given table in the table’s…
-
Removal of File-based Metadata Storage
In previous MySQL releases, dictionary data was partially stored in metadata files. Issues with file-based metadata storage included expensive file scans, susceptibility to file system-related bugs, complex code for handling of replication and crash recovery failure states, and a lack of extensibility that made it difficult to add metadata for new features and relational objects.…
-
Transactional Storage of Dictionary Data
The data dictionary schema stores dictionary data in transactional (InnoDB) tables. Data dictionary tables are located in the mysql database together with non-data dictionary system tables. Data dictionary tables are created in a single InnoDB tablespace named mysql.ibd, which resides in the MySQL data directory. The mysql.ibd tablespace file must reside in the MySQL data directory and its name cannot be modified…