# ecs-database

Part of **ECS**

# ECS Database Troubleshooting Guide

## Problem Index

| Problem | Symptom | Severity | Solution Summary |
|--------|--------|---------|------------------|
| MySQL fails to start due to PID file permission denied | Error: `Can't start server: can't create PID file: Permission denied` (MY-010092) | High | Fix ownership and permissions of MySQL data directory |
| MySQL cannot access binlog.index or data files | Error: `OS errno 13` / `Permission denied` on `./binlog.index` | High | Run `chown -R mysql:mysql` on the data directory |
| Forgotten or lost MySQL root password | Unable to log in as root; authentication fails | Medium | Reset root password using safe mode (`--skip-grant-tables`) |

## Problem Details

### Problem 1: MySQL Fails to Start Due to PID File Permission Denied

**Symptoms**
- Error message: `Can't start server: can't create PID file: Permission denied`
- Error code: `MY-010092`
- Behavior: `systemctl start mysqld` fails immediately; service remains inactive
- Context: Occurs after changing the MySQL data directory or restoring from backup without proper permissions

**Root Cause**
The MySQL process (running as user `mysql`) lacks write permission to the configured PID file location, typically inside the data directory. This is often caused by incorrect file ownership after manual directory changes or file restoration.

**Solution**
1. Identify the MySQL data directory from the configuration file:
   ```bash
   grep "datadir" /etc/my.cnf
   ```
2. Ensure the entire data directory is owned by the `mysql` user and group:
   ```bash
   sudo chown -R mysql:mysql /var/lib/mysql
   ```
   Replace `/var/lib/mysql` with your actual data directory path if different.
3. Verify directory permissions (should be `750` or `700`):
   ```bash
   sudo chmod 750 /var/lib/mysql
   ```
4. Restart the MySQL service:
   ```bash
   sudo systemctl start mysqld
   ```

**Verification**
- Check service status:
  ```bash
  sudo systemctl status mysqld
  ```
- Confirm the PID file exists and is writable:
  ```bash
  ls -l /var/lib/mysql/*.pid
  ```
- Expected output: active (running) state with no permission errors in logs.

---

### Problem 2: MySQL Cannot Access binlog.index or Data Files (OS errno 13)

**Symptoms**
- Error message: `mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)`
- Behavior: MySQL crashes on startup or refuses to initialize
- Context: Common after moving the data directory, restoring snapshots, or running MySQL as a non-standard user

**Root Cause**
The `mysql` system user does not have read/write access to critical files in the data directory, including `binlog.index`, InnoDB files, or socket files. The operating system returns "Permission denied" (errno 13) when the process attempts file operations.

**Solution**
1. Stop the MySQL service if it’s partially running:
   ```bash
   sudo systemctl stop mysqld
   ```
2. Recursively assign ownership of the entire data directory to the `mysql` user:
   ```bash
   sudo chown -R mysql:mysql /var/lib/mysql
   ```
3. Ensure all files are readable and writable by the owner:
   ```bash
   sudo find /var/lib/mysql -type f -exec chmod 660 {} \;
   sudo find /var/lib/mysql -type d -exec chmod 750 {} \;
   ```
4. Start MySQL again:
   ```bash
   sudo systemctl start mysqld
   ```

**Verification**
- Check the MySQL error log for remaining permission issues:
  ```bash
  sudo tail -n 20 /var/log/mysqld.log
  ```
- Confirm successful startup with no `OS errno 13` messages.
- Validate binary logging is active (if enabled):
  ```sql
  SHOW VARIABLES LIKE 'log_bin';
  ```

---

### Problem 3: Forgotten or Lost MySQL Root Password

**Symptoms**
- Behavior: `mysql -u root -p` fails with `Access denied for user 'root'@'localhost'`
- Context: After initial setup or long periods without use; no password recovery mechanism configured

**Root Cause**
The root password was set during installation but lost or forgotten. MySQL enforces authentication by default, blocking access without valid credentials.

**Solution**
1. Stop the MySQL service:
   ```bash
   sudo systemctl stop mysqld
   ```
2. Start MySQL in safe mode with grant tables skipped:
   ```bash
   sudo mysqld_safe --skip-grant-tables --skip-networking &
   ```
3. Connect to MySQL without a password:
   ```bash
   mysql -u root
   ```
4. In the MySQL shell, update the root password (for MySQL 5.7+):
   ```sql
   FLUSH PRIVILEGES;
   ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPassword123!';
   EXIT;
   ```
5. Kill the safe-mode process and restart normally:
   ```bash
   sudo pkill mysqld_safe
   sudo systemctl start mysqld
   ```

**Verification**
- Log in using the new password:
  ```bash
  mysql -u root -p
  ```
- Enter the new password when prompted.
- Expected result: successful connection to the MySQL prompt.

## FAQ

**Q: How do I locate the MySQL error log on ECS?**  
A: By default, the error log is located at `/var/log/mysqld.log`. You can confirm the path by checking the `log_error` variable in MySQL:  
```sql
SHOW VARIABLES LIKE 'log_error';
```

**Q: What permissions should the MySQL data directory have?**  
A: The data directory (e.g., `/var/lib/mysql`) must be owned by the `mysql` user and group. Directories should have `750` permissions, and files should have `660`. Never assign world-writable permissions.

**Q: Why does MySQL fail to start after changing the data directory in my.cnf?**  
A: Changing `datadir` requires copying all existing files to the new location and updating ownership with `chown -R mysql:mysql <new_path>`. SELinux contexts may also need updating on RHEL-based systems using `semanage fcontext` and `restorecon`.

**Q: How can I prevent root password loss in the future?**  
A: Store the root password in a secure vault, create a dedicated admin user with full privileges, or use configuration management tools to rotate and recover credentials safely.

**Q: Is it safe to use `--skip-grant-tables` in production?**  
A: No. `--skip-grant-tables` disables all authentication and should only be used temporarily for recovery on isolated systems. Always combine it with `--skip-networking` to prevent remote access during the reset.