The default data directory for MySQL and MariaDB installed from official repositories on modern distributions is: /var/lib/mysql This applies to CentOS 7, Rocky Linux 9, Fedora, and most RHEL-based systems. Finding the Data Directory Programmatically If you’ve customized your installation or need to verify the exact location, there are several reliable methods. Query MariaDB/MySQL Configuration…
The REPLACE() function is your primary tool for modifying string values within a column. It scans for a substring and replaces all occurrences with a new value. Basic syntax UPDATE table_name SET column_name = REPLACE(column_name, ‘old_string’, ‘new_string’) WHERE condition; Simple example Replace all instances of “old_domain” with “new_domain” in the email column: UPDATE users SET…
The LIMIT clause in MySQL lets you control how many rows a SELECT query returns. This is essential for pagination, performance tuning, and working with large datasets. Basic LIMIT Syntax To return only the first N rows from a table: SELECT * FROM qa_users LIMIT 10; This returns the first 10 rows from the qa_users…
Date comparison is one of the most common operations in SQL queries, whether you’re filtering orders, analyzing logs, or generating reports. The approach varies slightly across database systems, but the underlying principles remain consistent. Basic Date Comparison The simplest form uses standard comparison operators against a date string in YYYY-MM-DD format: SELECT * FROM orders…
When you run journalctl without options on a system with years of log history, it can take seconds or even minutes to parse everything. The slowness comes from journalctl loading and deserializing potentially gigabytes of journal data. The solution is targeted querying — you almost never need the entire journal, so restrict what journalctl reads…
Connecting your application to a MySQL database is fundamental to most backend systems. Rather than shelling out to the mysql command-line client, you’ll use language-specific connectors that provide proper connection pooling, error handling, and prepared statements. Language-Specific MySQL Drivers The best approach depends on your tech stack: Python Use mysql-connector-python for straightforward connections or PyMySQL…
Database commands fall into distinct categories based on what they do. The two most fundamental are DDL (Data Definition Language) and DML (Data Manipulation Language). Understanding the difference is essential for anyone working with databases, from writing migrations to managing production systems. DDL: Data Definition Language DDL statements define and modify the structure of your…
A database schema is the blueprint that defines how data is organized and stored in a database. Think of it as the architectural plan for your database—it specifies tables, columns, data types, relationships, constraints, and rules that govern how data can be stored and accessed. Core Components of a Schema A schema encompasses several key…
You have internal hosts on a private network (like 10.0.3.0/24) that aren’t directly accessible from your workstation. They sit behind a gateway (bastion host) like gateway.example.org. You need seamless SSH access to these internal IPs without manually hopping through the gateway each time. Using SSH ProxyJump The modern, straightforward approach is SSH’s built-in ProxyJump feature….
When you need to store form submissions where different form types have different attributes, you’re essentially implementing an Entity-Attribute-Value (EAV) model. This is a common pattern for flexible schemas, but it requires careful design. The Core Problem You have multiple form types (Laptop, Mobile, Tablet) with different fields. A naive approach is a wide table…
Counting page views isn’t as simple as incrementing a counter. You need to handle edge cases: duplicate views from the same user, multiple users behind a single IP (corporate networks), and bots. Here’s how to build a robust system. The Problem with IP-Based Counting Relying on IP addresses fails in several scenarios: Multiple users behind…
MySQL replication lets you maintain synchronized copies of your database across multiple servers. This is essential for high availability, read scaling, and backups. Here’s how to configure it properly. Prerequisites You need at least two MySQL instances: a primary (source) and one or more replicas. Both should run the same or compatible MySQL versions. MySQL…
To check which storage engine a table uses, query the information_schema database: SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’; Replace database_name and table_name with your actual values. You can also use the SHOW TABLE STATUS command for a quicker view: SHOW TABLE STATUS FROM database_name WHERE Name = ‘table_name’\G The…
To replace strings across one or more columns in MySQL tables, use the REPLACE() function with an UPDATE statement. This is useful when migrating domains, fixing URLs, updating API endpoints, or correcting data patterns at scale. Basic Syntax UPDATE table_name SET column_name = REPLACE(column_name, ‘old_string’, ‘new_string’) WHERE column_name LIKE ‘%old_string%’; The WHERE clause isn’t required,…
Install the required LAMP stack components. Fedora 41 and later use dnf instead of yum: sudo dnf install httpd php php-mysqli php-gd mariadb-server Note that php-mysql is deprecated; use php-mysqli instead for modern MySQL/MariaDB connections. Start and enable the services to run on boot: sudo systemctl start mariadb httpd sudo systemctl enable mariadb httpd Using…
Database corruption happens. Whether it’s from unexpected shutdown, filesystem errors, or hardware issues, knowing how to recover from backups is essential sysadmin knowledge. When to Use Backups vs. Native Repair Tools Before jumping to restore from backup, consider your options: Native repair tools (REPAIR TABLE, CHECK TABLE) are faster and preserve recent data Backup restoration…
Facebook’s engineering team has long been vocal about their heavy reliance on MySQL to power one of the world’s largest platforms. Understanding their approach offers valuable insights for anyone managing databases at scale. Storage Engine Philosophy Facebook treats MySQL as a generic data manipulation and storage engine rather than a monolithic application database. This architectural…
Facebook operates two distinct caching systems, each designed for different access patterns and consistency requirements. Memcache serves as a lookaside cache where the intelligence resides primarily on the client side. This architecture means applications are responsible for cache invalidation, consistency checks, and determining what data to cache. Memcache is stateless and horizontally scalable, making it…
A MySQL table marked as crashed typically occurs after unclean shutdowns, power failures, or server crashes. Error 145 indicates the table structure is corrupted and needs repair before queries can proceed. Checking Table Status Before attempting repair, check the table status: CHECK TABLE mybb_sessions; This returns detailed information about corruption. Common status values: OK —…
MySQL Cluster uses NDB (Network Database) storage engine to provide high availability and data redundancy across multiple nodes. This guide covers installation and configuration on modern RHEL-compatible systems. Prerequisites Three or more servers (minimum: one management node, two data nodes, one SQL node) Rocky Linux 9 or AlmaLinux 9 Root or sudo access Static IP…