This article examines the pain points of MySQL DDL operations for large tables, analyzes the limitations of online DDL, compares tools like gh‑ost and pt‑osc, and outlines a comprehensive workflow and implementation details for an automated online DDL work‑order system.
The article explains why MySQL raises a GTID consistency error when a single transaction updates both InnoDB and MyISAM tables, demonstrates how to reproduce the issue, and evaluates three mitigation strategies: separating the updates, converting MyISAM to InnoDB, or disabling ENFORCE_GTID_CONSISTENCY.
This guide walks through a step‑by‑step experiment that shows how to recover MySQL files from a Linux virtual machine that cannot boot, by mounting its disk on a new VM, activating LVM volumes, copying configuration and data files, and restarting the database.
This article explains MySQL master‑slave replication, covering replication modes, use cases, required configurations, underlying mechanisms, common issues, and solutions such as semi‑synchronous and parallel replication, along with practical configuration examples and monitoring commands.
The article experimentally evaluates three ways of creating replication users—only on the master, separately on master and slave with CREATE statements, and separately with GRANT statements—to show how each method affects MySQL 5.7 master‑slave replication, error handling, and compatibility with GTID and MySQL 8.0.
This article explains how SELinux enforces mandatory access control on Linux, describes its three modes, shows how to view and modify SELinux contexts for MySQL processes and data directories, and provides step‑by‑step commands to add custom paths, logs, PID files, and ports while preserving system security.
This guide lists useful MySQL client shortcuts, explains the help command usage, provides common mysqladmin administration examples, and shows how to diagnose high CPU usage by tracing the offending SQL statements through process and performance schema queries.
This article explains the importance of MySQL backup and recovery, outlines common failure scenarios, recommends backup architectures and verification processes, reviews popular logical and physical backup tools such as mysqldump, mydumper, and xtrabackup, and shares practical case studies and best‑practice guidelines.
This article explains MySQL 8's persisted system variables feature, showing how to use SET PERSIST and SET PERSIST_ONLY to make variable changes survive restarts, how to view and clear persisted settings, how to disable persistence, required privileges, and how to monitor persisted variables via performance_schema.
This comprehensive cheat sheet gathers over eighty practical T‑SQL statements covering instance properties, configuration, system views, lock monitoring, backup progress, replication, Always On, FILESTREAM, job management, index maintenance, and security, providing DBAs with ready‑to‑run commands for everyday SQL Server administration and troubleshooting.
This guide walks you through installing PostgreSQL 12 and the web‑based pgAdmin4 management tool on RHEL 8, covering module selection, database initialization, service configuration, and Apache‑based server‑mode setup with necessary SELinux and firewall adjustments.
This guide explains how to recover a lost MySQL root password by editing the MySQL configuration to skip grant tables, restarting the service, and then setting a new password via SQL commands, with platform-specific steps for Windows, macOS, and Linux.
This article provides a detailed collection of SQL Server commands and techniques covering database creation, table and index management, common DML statements, advanced set operators, various join types, grouping, pagination, maintenance tasks, replication setup, linked‑server usage, and a synchronization stored procedure.
A detailed walkthrough shows how a mismatched COMPATIBLE parameter caused ORA-600 errors that blocked log apply on an Oracle 11.2.0.4 standby, and explains the step‑by‑step queries, diagnostics, and commands needed to correct the issue and resume synchronization.
This article provides a detailed, hands‑on walkthrough for upgrading Oracle 19c Grid Infrastructure (GI) using the GIRU 19.7.0.0.200414 patch, covering OPatch updates, directory backups, CRS conflict analysis, patch application, data‑dictionary updates, and troubleshooting common errors.
This guide walks through preparing the environment, configuring the master and slave MySQL servers, and using Python's Paramiko library to automate SSH commands, edit configuration files, restart services, and verify replication, all while handling common pitfalls.
This guide outlines step‑by‑step methods to identify high load causes, troubleshoot replication interruptions, resolve instance hangs, and address common MySQL errors using commands like SHOW PROCESSLIST, EXPLAIN, iostat, and GTID management.
This guide walks through essential Oracle Database administration tasks, covering how to start and shut down the database and listener, create and manage tablespaces, users, and permissions, control transactions, build and maintain indexes, views, materialized views, sequences, synonyms, and partitioned tables, with command examples and best‑practice tips.
A comprehensive collection of SQL Server commands covering server properties, configuration, system views, monitoring, backup, replication, Always On, file management, index operations, and troubleshooting techniques for database administrators.
This comprehensive guide covers SQL Server basics such as logging in, creating and dropping databases, tables, indexes and views, advanced query techniques like UNION, outer joins and pagination, plus maintenance tasks, backup and restore, linked‑server data transfer, replication setup, and useful scripts for database administration.
This guide walks through preparing the environment, configuring MySQL instances, creating an InnoDB ReplicaSet using MySQL Shell, adding replicas, setting up MySQL Router for read/write splitting, testing connections, and performing common management tasks such as promoting a primary and removing or re‑adding nodes.
This guide introduces several Percona Toolkit command‑line tools—pt‑archiver, pt‑config‑diff, pt‑heartbeat, pt‑variable‑advisor, and pt‑pmp—explaining their purpose, providing example commands for data archiving, configuration comparison, accurate replication lag measurement, variable optimization suggestions, and stack‑trace debugging, plus a quick installation snippet.
The article explains MySQL logical and physical backup methods, compares online and offline backup approaches, details the advantages and disadvantages of each, and provides practical mysqldump commands and master‑slave backup techniques for reliable database protection.
The article explains how MySQL Group Replication handles unintended member exits, introduces the OFFLINE_MODE option added in 8.0.18, and shows how DBAs can configure and query the system to maintain high availability during failures.
This guide explains how to create a production‑grade, near‑real‑time MySQL data‑synchronisation and query platform using Alibaba's open‑source Otter middleware, covering architecture, key features, installation steps, configuration of manager and node components, and best practices for safe query operations.
This article explains how MySQL 8.0.18 introduced privilege checking for replication applier threads, outlines the three-step process to create a restricted‑privilege user, assign necessary permissions or roles, associate the user with the CHANGE MASTER TO statement, and discusses observability and important considerations.
This article walks through a real‑world case of an Oracle RAC/ADG deployment on CentOS 7 that suffered severe performance degradation, detailing the root‑cause analysis, OS and listener tuning, patch installation, cache‑reclamation settings, and compatibility fixes to restore stability.
The article analyzes how the DBA job market is shrinking as traditional enterprises shift away from legacy systems, cloud adoption reshapes responsibilities, and DBAs face limited advancement unless they transition to architecture or data‑analytics roles, highlighting the growing risk and low reward of staying in pure DBA work.
A busy DBA can reduce weekend firefights by automating SQL code formatting, detecting injection risks, assessing performance impact, and fostering a collaborative review culture with developers through testing frameworks and knowledge‑sharing sessions.
MySQL 8.0 adds JSON‑formatted error logs and flexible filtering components, allowing administrators to install JSON sink and filter modules, extract specific fields with tools like jq, and apply rules that drop or throttle messages to improve log readability and automation.
This guide explains how to restore MySQL InnoDB tables and their dictionary metadata after accidental drops, corruption, or disk failures by using the undrop‑for‑innodb toolkit, parsing system tables, and executing a series of command‑line steps to rebuild schema and data.
The article explains how MySQL 8.0 resource groups can be used to confine CPU‑intensive queries such as ORDER BY RAND() to specific CPU cores, preventing them from monopolizing server resources, and provides step‑by‑step commands, script alternatives, and known limitations.
Tencent Cloud Academy will livestream a MySQL schema design and development standards session on September 17 at 20:00, featuring experts Ye Jinrong and Wu Bingxi, covering best practices such as default InnoDB engine, utf8mb4 charset, row size limits, partitioning, appropriate data types, index guidelines, and operational recommendations.
This article walks through a DBA’s daily responsibilities, outlines a step‑by‑step MySQL performance‑tuning workflow, highlights key optimization areas such as application access, hardware selection, OS and instance settings, and shares practical automation practices for backup, monitoring, failover, security auditing, and log management.
This guide explains why an Oracle DBMS_JOB can end up with a next execution date of 4000‑01‑01, how to interpret its status fields, diagnose the underlying errors, and restore the job using manual execution and log‑based troubleshooting steps.
This guide explains three practical steps to harden MySQL installations: configuring strict datadir permission modes, relocating the MySQL socket file into the datadir with safe permissions, and leveraging the login‑path feature via mysql_config_editor to store credentials securely while simplifying client access.
When a sales employee's workflow records vanished after a WeChat ID change, the team traced the deletion to MySQL binary logs, extracted the relevant log entries, and used bulk UPDATE statements to replace the old user_id with the new one, fully restoring the missing data.
This guide explains Oracle datafile concepts, how to query absolute and relative file numbers, create and add files to tablespaces, modify file size with autoextend or resize, change file availability, rename or move files, and safely delete files while observing key restrictions.
This comprehensive guide shares a senior DBA's hard‑learned lessons on MySQL environment awareness, security, configuration, routine operations, architecture, business understanding, and mindset, offering practical tips and tools to prevent costly mistakes and improve operational confidence.
DBA Best PracticesDatabase SecurityOperational Procedures
This article explains MySQL’s various log files—including error log, slow query log, binary log and general query log—detailing how to locate, configure, view, and analyze them using MySQL commands, and how to adjust settings such as long_query_time and log_output for optimal performance.
This article examines common scenarios where duplicate rows prevent creating unique constraints, presents eight SQL‑based methods—including array functions, window functions, NOT IN/EXISTS, combined IN/NOT IN, EXISTS/NOT EXISTS, single‑statement deletes, and table‑copy approaches—provides test data, performance comparisons, and practical guidance for DBA operations.
This guide walks through MySQL’s high‑level capabilities, covering built‑in and user‑defined functions, the creation and advantages of stored procedures, the variety of storage engines with their locking and transaction characteristics, and essential administration tools.
This article presents two comprehensive Shell scripts—one to validate Oracle RMAN backup sets and another to perform a full backup‑restore experiment—detailing SSH trust configuration, source information collection, log transfer, directory preparation, and step‑by‑step RMAN restore commands, all illustrated with practical screenshots and a GitHub repository link.
The article shares a DBA’s three‑year journey at Ganji, detailing core responsibilities, painful incidents like accidental table deletions and massive Redis growth, and practical lessons on stability, backup, hardware prioritization, business alignment, and improving communication between operations and development teams.
This guide provides a comprehensive, step‑by‑step tutorial on using Oracle SQL*Plus for connecting to databases, querying tables, saving and executing scripts, handling variables, formatting output, managing tablespaces, creating external tables, and defining various partitioning strategies.
This guide explains why SQL Server monitoring is essential, lists the critical metrics to watch, defines deadlocks, and provides multiple methods—including Performance Monitor, trace flags, Profiler, Extended Events, and the system_health session—along with step‑by‑step instructions and sample queries to detect and analyze deadlocks effectively.
This guide thoroughly explains MongoDB's permission system, covering key concepts, role creation, inheritance, privilege assignment, user management, and essential precautions to avoid common security pitfalls and simplify administration.
This guide explains Oracle datafile concepts, how to query file numbers, create and add files, adjust sizes and auto‑extend settings, toggle online/offline status, rename files, and safely drop them while observing limits and recovery procedures.
This guide details a production‑grade MongoDB replica‑set deployment, covering hardware specs, configuration files, replica‑set initialization, user creation, incremental backup with mongobackup scripts, primary node switching, cold backup procedures, troubleshooting authentication errors, and test data generation for performance validation.
This guide explains the concept of Oracle High Water Mark (HWM), why it grows with DML operations, and provides a step‑by‑step procedure—including pre‑check, shrink space, and post‑check—to reclaim unused blocks, reduce tablespace usage, and improve scan efficiency.
This guide explains why Oracle Enterprise Manager fails when listeners lack dynamic registration, demonstrates step‑by‑step configuration of two listeners (listener and lsnr2) using the local_listener parameter, and shows how to verify the setup with lsnrctl commands.
This guide demonstrates how to switch Oracle Database 11g between Automatic Shared Memory Management (ASMM) and Automatic Memory Management (AMM), covering environment setup, parameter adjustments, spfile/pfile handling, restart procedures, and troubleshooting, with concrete SQL commands and shared‑memory observations.
This guide explains how to restore accidentally deleted or incorrectly updated Oracle database rows using logical backups with import, physical backup with incomplete recovery, LogMiner analysis of redo logs, and the Flashback Query/DBMS_FLASHBACK features, detailing each step, required settings, and practical tips.
Oracle 11g’s Flashback Data Archive lets you retain UNDO information for extended periods, enabling point-in-time queries of tables; this guide walks through creating an ASSM tablespace, setting up a flashback archive, assigning tables, and using SCN-based queries to retrieve past data.
This guide explains why Oracle 11g's automatic optimizer statistics collection can affect 24‑hour systems, shows how to check its status, and provides step‑by‑step commands to disable, re‑enable, and verify the task, including differences from Oracle 10g.
This guide explains how to monitor Oracle UNDO tablespace usage, adjust automatic UNDO management parameters, interpret V$UNDOSTAT statistics, and safely reclaim space by creating a new UNDO tablespace and dropping the old one, helping DBAs avoid ORA‑01555 errors and space shortages.
This tutorial walks through preparing a CentOS 6.6 environment, creating MHA users and SSH keys, configuring hosts, installing MHA Node and Manager, verifying configurations, and testing master failover to achieve reliable MySQL high‑availability using MHA.
An Oracle DBA recounts how a full archive log halted business, how manual deletion uncovered a backup script failure caused by a mismatched SYS password across RAC nodes, and the steps taken to fix the issue and prevent future overflow.
This guide explains two powerful Linux commands that instantly terminate all Oracle-related processes and clean up shared memory, providing a quick recovery method for stuck Oracle databases while warning of the risks involved.
This article shares a collection of real DBA deletion mishaps—from accidental Oracle home removal to mistaken rm‑rf commands—and offers practical prevention tips such as aliasing rm, monitoring storage, backing up before deletions, and enforcing peer review to improve operational safety.
This article explains how to simulate a flashback feature for MySQL and MariaDB by parsing binlogs, allowing you to restore tables after accidental DELETE or UPDATE operations without relying on full backups, and provides step‑by‑step commands and precautions.
This guide provides Oracle SQL queries to list currently running statements, retrieve historical SQL, identify high‑CPU or high‑disk‑usage queries, find slow statements, and detect uncommitted transactions, enabling DBAs to quickly diagnose performance issues.
This article shares practical Oracle DBA experiences—including role definitions, environment considerations, monitoring strategies, performance‑tuning case studies, development‑business integration, and future‑oriented operations—offering concrete guidance and hard‑won insights for database administrators.
This guide explains why the ORA-01555 "snapshot too old" error occurs in Oracle databases, where related logs are stored, the underlying UNDO and LOB mechanisms that trigger it, and step‑by‑step solutions to diagnose and eliminate the problem.
This guide explains how to leverage MongoDB's flexible document model, introduces the open‑source Variety tool for schema analysis, demonstrates practical commands for sampling, depth control, filtering, sorting and result persistence, and covers MongoDB 3.2+ document validation features and their limitations.
This article explores practical DBA strategies for painless MySQL version upgrades, balancing specialization versus breadth across multiple databases, effective participation in automation platforms, choosing horizontal or vertical management models for multi‑DB environments, and fostering collaborative learning practices within DBA teams.
This article shares a MySQL InnoDB core developer’s recommendations on DBA learning stages, Oracle’s stance on MySQL’s future, official comparisons with MariaDB and PostgreSQL, and how MySQL’s globally distributed development team collaborates, offering practical guidance for database professionals.
This comprehensive guide explains MySQL's various log types, how to view and configure error, general, slow, and binary logs, and details essential server variables for effective database administration and troubleshooting.
This guide explains how Percona XtraBackup works for MySQL hot backups, covering required permissions, key terminology, backup and incremental backup mechanisms, essential command‑line options, and practical script examples to ensure reliable, low‑impact data protection.
This article walks through a real Oracle OLTP query that took 28 seconds per execution, explains how execution‑plan analysis, index hints, and correcting stale statistics reduced the runtime to sub‑millisecond levels, and shows the final combined index that eliminated the performance issue.
In this detailed DBA case study, the speaker explains how improper settings of Oracle's parallel execution parameters on an AIX‑based 10gR2 database led to process saturation, undo‑related wait events, and temporary connection issues, and describes the step‑by‑step diagnosis, parameter adjustments, and lessons learned for future tuning.
Senior DBA and architect Bai Shan answers community questions on advancing from DBA to architect, future career routes, testing environment setup, interpreting AWR storage metrics, using sar and iostat for disk I/O monitoring, cache sizing, and the growing importance of automation in cloud-era database operations.
DD Boost integrates tightly with Oracle RMAN to provide a flexible, policy‑driven backup solution that allows DBAs to manage local and disaster‑recovery sites independently, simplifies deployment via a simple plugin, and dramatically improves performance by sending only unique data blocks to Data Domain for deduplication.
This article summarizes a live Q&A where experts compare MySQL official, MariaDB, and Percona branches, discuss the pros and cons of each, and explore how MySQL DBAs can balance source‑code knowledge with operational expertise to advance their careers and influence architecture and business integration.
This comprehensive guide covers MySQL's evolution, its strengths and limitations, detailed development and operational standards, backup and recovery strategies, replication techniques, performance tuning, and practical Q&A for database administrators seeking to optimize large‑scale MySQL deployments.
Performance Optimizationbackup and recoverydatabase administration
Discover twelve practical MySQL techniques—from avoiding dump file edits and tuning MyISAM block size to using EXPLAIN, query caching, and secure root password changes—that can boost performance, prevent data loss, and streamline database management for developers.
This guide explains the two scenarios for removing nodes from a Redis cluster—deleting a master node by first migrating its slots and then removing it, and deleting a slave node directly—along with the exact redis-trib.rb commands and verification steps to ensure successful removal.
This article provides a comprehensive overview of MySQL/MariaDB, detailing its architecture, core components, server and client programs, command-line options, data types, SQL language parts, metadata, and variable management, offering practical guidance for developers and administrators.
This guide shares a curated list of foundational books and resources for beginners aiming to master SQL, deepen MySQL expertise, and develop the operational and data‑centric mindset needed for a successful DBA career, including links to downloads and a weekly MySQL radio show.