MySQL

This guide covers creating a MySQL database, creating a dedicated user, and granting the appropriate privileges. It covers both MySQL 5.x (implicit GRANT-based user creation) and MySQL 8+ (explicit CREATE USER required).

Prerequisites

• MySQL server installed and running
• Access to the MySQL root account or an account with GRANT privileges

Steps

1. Log in to MySQL as root:

mysql -u root -p

2. Create the database with UTF-8 character set:

CREATE DATABASE myappdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

3a. MySQL 5.x — create user and grant privileges in one step:

GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
FLUSH PRIVILEGES;

3b. MySQL 8+ — create user first, then grant:

CREATE USER 'myappuser'@'localhost' IDENTIFIED BY 'StrongPassword123';
GRANT ALL PRIVILEGES ON myappdb.* TO 'myappuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

To allow access from any host instead of localhost, replace 'myappuser'@'localhost' with 'myappuser'@'%'.

Verify

SHOW DATABASES;
SELECT User, Host FROM mysql.user WHERE User = 'myappuser';
SHOW GRANTS FOR 'myappuser'@'localhost';

Notes

• In MySQL 8+, GRANT ... IDENTIFIED BY is no longer supported — always use CREATE USER first.
• If a user exists but cannot authenticate, check the authentication plugin: SELECT User, plugin FROM mysql.user; and update with ALTER USER if needed.

This guide explains how to create a full MySQL database backup using mysqldump and how to restore it. These procedures work for any MySQL or MariaDB server.

Prerequisites

• MySQL client tools installed (mysqldump, mysql)
• Sufficient disk space for the dump file
• Database credentials with SELECT, LOCK TABLES, and SHOW VIEW privileges

Steps — Backup

Back up a single database:

mysqldump -u [username] -p [dbname] > backup.sql

Back up a single table:

mysqldump -u [username] -p [dbname] [tablename] > table_backup.sql

Back up all databases:

mysqldump -u root -p --all-databases > all_databases.sql

Recommended options for production backups:

mysqldump -u root -p   --single-transaction   --routines   --triggers   --events   myappdb > myappdb_$(date +%F).sql

Steps — Restore

Restore to an existing database:

mysql -u [username] -p [dbname] < backup.sql

Restore all databases from a full dump:

mysql -u root -p < all_databases.sql

Verify

mysql -u [username] -p [dbname] -e "SHOW TABLES;"

Notes

• Use --single-transaction for InnoDB tables to get a consistent snapshot without locking tables.
• The -p flag followed immediately by the password (no space) avoids an interactive prompt: -pmypassword. For scripted use, prefer a credentials file (~/.my.cnf).
• To compress the dump: mysqldump ... | gzip > backup.sql.gz. To restore: gunzip -c backup.sql.gz | mysql ...

This error appears in phpMyAdmin when the linked-tables infrastructure (pmadb) is not set up. phpMyAdmin version 5+ introduced support for advanced features (bookmarks, query history, relations) that require a dedicated control user and database. Without it, phpMyAdmin logs this warning on every login.

Prerequisites

• phpMyAdmin 5.x or later installed
• MySQL root access

Steps

1. Create the phpMyAdmin control database and tables:

mysql -u root -p < /usr/share/phpmyadmin/sql/create_tables.sql

2. Create the phpMyAdmin control user:

mysql -u root -p

CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost';
FLUSH PRIVILEGES;

3. Update /etc/phpmyadmin/config.inc.php (or config.inc.php in the phpMyAdmin root) with the control user credentials:

$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmapassword';
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

Verify

Reload phpMyAdmin in the browser. The warning should no longer appear.

Notes

• The SQL file path may differ by distro. On Debian/Ubuntu it is typically /usr/share/phpmyadmin/sql/create_tables.sql.
• A sample config.inc.php with all pmadb settings is available at /usr/share/phpmyadmin/config.sample.inc.php.

The MySQL Yum repository provides official RPM packages for MySQL server, client, and utilities on Red Hat-based Linux distributions. This guide covers adding the repository and installing MySQL.

Prerequisites

• CentOS, RHEL, Oracle Linux, or Fedora
• Root or sudo access
• Internet access to repo.mysql.com

Steps

1. Download the MySQL Yum repository RPM:

wget https://repo.mysql.com/mysql80-community-release-el7-7.noarch.rpm

2. Install the repository package:

sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm

3. Verify the repository is enabled:

yum repolist enabled | grep mysql

4. Install MySQL server:

sudo yum install mysql-community-server

5. Start and enable MySQL:

sudo systemctl start mysqld
sudo systemctl enable mysqld

6. Retrieve the temporary root password from the log:

sudo grep 'temporary password' /var/log/mysqld.log

7. Secure the installation:

sudo mysql_secure_installation

Select a different MySQL version

To install a version other than the default (e.g., MySQL 5.7):

sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql57-community
sudo yum install mysql-community-server

Verify

mysql --version
sudo systemctl status mysqld

Notes

• Available MySQL versions and supported platforms are listed at https://repo.mysql.com/.
• On EL8/EL9, use dnf instead of yum.

The MySQL slow query log records queries that exceed a configurable execution time threshold. Enabling and analyzing this log is one of the most effective first steps in diagnosing database performance problems.

Prerequisites

• MySQL server with root or SUPER privilege access
• Write permission on the log directory (typically /var/log/mysql/)

Steps — Enable the Slow Query Log

Option A: Enable at runtime (no restart needed):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Option B: Enable permanently in /etc/mysql/my.cnf (or my.ini):

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1

Restart MySQL after editing the config file:

sudo systemctl restart mysql

Steps — Analyze the Log

View raw slow query log:

sudo tail -f /var/log/mysql/slow.log

Summarize with mysqldumpslow:

# Top 10 slowest queries by average execution time
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Top 10 by total time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Use pt-query-digest (Percona Toolkit) for richer analysis:

pt-query-digest /var/log/mysql/slow.log | head -100

Verify

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

Troubleshooting

• If the log file is not being written, check ownership: chown mysql:mysql /var/log/mysql/slow.log
• Setting long_query_time = 0 logs every query — useful briefly for profiling, but generates very large files on busy servers.
• Enable log_queries_not_using_indexes to catch full-table scans even when they are fast.

A quick reference for commonly used MySQL and MariaDB administrative commands, including table maintenance, service management, and log inspection.

Service Management

# Start / stop / restart
sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql

# For MariaDB
sudo systemctl start mariadb
sudo systemctl status mariadb

Table Maintenance

Check and auto-repair all tables:

mysqlcheck --auto-repair -A -u root -p

Check and repair a specific database:

mysqlcheck --auto-repair -u root -p mydatabase

Optimize all tables in a database:

mysqlcheck --optimize -u root -p mydatabase

User and Privilege Commands

-- Show all users
SELECT User, Host FROM mysql.user;

-- Show grants for a user
SHOW GRANTS FOR 'username'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON mydb.* FROM 'username'@'localhost';
DROP USER 'username'@'localhost';

Database and Table Information

SHOW DATABASES;
USE mydb;
SHOW TABLES;
DESCRIBE tablename;
SHOW CREATE TABLE tablename\G

Process and Status

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW STATUS LIKE 'Connections';
SHOW VARIABLES LIKE 'max_connections';

Log Inspection

# MySQL error log (Debian/Ubuntu)
sudo tail -f /var/log/mysql/error.log

# MariaDB log (CentOS/RHEL)
sudo tail -f /var/log/mariadb/mariadb.log

Notes

• If you see Table './mysql/proc' is marked as crashed and should be repaired in the error log, run mysqlcheck --auto-repair -A -u root -p immediately.
• Use KILL [process_id] to terminate a long-running query found in SHOW PROCESSLIST.

By default, MySQL stores all InnoDB data (including undo logs and the data dictionary) in a single shared tablespace file called ibdata1. On busy servers this file can grow to hundreds of gigabytes and never shrinks automatically. This guide explains how to reclaim disk space by enabling per-table tablespaces.

Prerequisites

• Root access to the server
• Sufficient disk space for a full database dump
• A maintenance window (MySQL restart required)

Steps

1. Dump all databases before making changes:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers > /backup/all_databases_$(date +%F).sql

2. Stop MySQL:

sudo systemctl stop mysql

3. Enable innodb_file_per_table in /etc/mysql/my.cnf:

[mysqld]
innodb_file_per_table = 1

4. Remove the old shared tablespace files:

sudo rm /var/lib/mysql/ibdata1
sudo rm /var/lib/mysql/ib_logfile0
sudo rm /var/lib/mysql/ib_logfile1

5. Reinitialize the data directory:

sudo mysqld --initialize --user=mysql

6. Start MySQL and restore the dump:

sudo systemctl start mysql
# Get the temporary root password from the log
sudo grep 'temporary password' /var/log/mysql/error.log
mysql -u root -p < /backup/all_databases_$(date +%F).sql

Verify

SHOW VARIABLES LIKE 'innodb_file_per_table';
ls -lh /var/lib/mysql/ibdata1

Notes

• With innodb_file_per_table = 1, each table gets its own .ibd file. Running OPTIMIZE TABLE tablename; reclaims space after large deletes.
• The ibdata1 file will still exist after this change, but it will only grow to hold the data dictionary and undo logs, not table data.
• This procedure destroys all existing data — the dump in step 1 is critical.

If a MySQL database was accidentally dropped and no SQL dump is available, it may be recoverable from the binary logs provided binary logging was enabled and the relevant log files have not been purged. This guide walks through a point-in-time recovery using mysqlbinlog.

Prerequisites

• Binary logging was enabled (log_bin in my.cnf)
• The binary log files covering the period before the drop still exist
• A new or empty target MySQL instance

Steps

1. Identify available binary logs:

SHOW BINARY LOGS;

2. Find the log file and position of the DROP DATABASE statement:

mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001 | grep -i "drop database" -A 5

3. Find the log file and position of the CREATE DATABASE statement (the beginning of your database):

mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001 | grep -i "create database" -A 2

4. Extract and replay all events from the CREATE up to just before the DROP:

# If the events span a single file
mysqlbinlog --start-position=START_POS --stop-position=DROP_POS   /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

# If events span multiple log files
mysqlbinlog --start-position=START_POS /var/lib/mysql/mysql-bin.000001   /var/lib/mysql/mysql-bin.000002   --stop-position=DROP_POS /var/lib/mysql/mysql-bin.000003 | mysql -u root -p

5. If you only need a specific database from the logs:

mysqlbinlog --database=mydroppeddb   --start-position=START_POS --stop-position=DROP_POS   /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

Verify

SHOW DATABASES;
USE mydroppeddb;
SHOW TABLES;
SELECT COUNT(*) FROM important_table;

Notes

• Set expire_logs_days = 30 (or binlog_expire_logs_seconds in MySQL 8) in my.cnf to retain enough binary log history for recovery.
• Binary logs only contain DML (INSERT, UPDATE, DELETE) and DDL (CREATE, ALTER, DROP). They do not replace a proper backup.
• Use mysqlbinlog --base64-output=decode-rows -v to make row-based binary logs human-readable.

This guide covers migrating an existing self-managed MySQL server to Amazon RDS using replication to minimize downtime. The approach creates an initial dump, restores it to RDS, then sets up replication so RDS stays in sync until you are ready to cut over.

Prerequisites

• An existing MySQL server (source) with binary logging enabled
• An Amazon RDS MySQL instance provisioned and reachable from the source server
• A MySQL user on the source with REPLICATION SLAVE privilege
• AWS CLI and MySQL client installed on the migration host

Steps

1. Ensure binary logging is enabled on the source:

[mysqld]
log_bin = mysql-bin
server-id = 1
binlog_format = ROW

2. Create a replication user on the source:

CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplPass123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

3. Take a consistent dump with binary log position:

mysqldump -u root -p   --single-transaction   --master-data=2   --routines --triggers --events   --all-databases > full_dump.sql

4. Note the binary log file and position from the dump header:

head -50 full_dump.sql | grep "CHANGE MASTER"
# Example output:
# CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=12345;

5. Restore the dump to RDS:

mysql -h your-rds-endpoint.rds.amazonaws.com -u admin -p < full_dump.sql

6. Configure RDS as a replica of the source:

CALL mysql.rds_set_external_master(
  'source-server-ip',
  3306,
  'repl',
  'ReplPass123',
  'mysql-bin.000003',
  12345,
  0
);
CALL mysql.rds_start_replication;

7. Monitor replication lag until it reaches zero:

SHOW SLAVE STATUS\G
-- Watch: Seconds_Behind_Master

8. Cut over: stop writes to the source, wait for lag to reach 0, update application connection strings to point to RDS, then stop replication:

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;

Verify

-- On RDS after cutover
SHOW DATABASES;
SELECT COUNT(*) FROM myapp.important_table;

Notes

• RDS Multi-AZ provides automatic failover. Enable it in the RDS console before cutover.
• RDS does not allow SUPER privilege. Stored procedures using DEFINER clauses may need adjustment — strip or replace DEFINER in the dump with sed before restoring.
• Test the RDS endpoint connectivity before step 5: mysql -h rds-endpoint -u admin -p -e "SELECT 1;"

mysql_config_editor (available since MySQL 5.6.6) stores MySQL login credentials in an encrypted file (~/.mylogin.cnf). This avoids exposing passwords in shell scripts, command history, or cron jobs.

Prerequisites

• MySQL 5.6.6 or later
• mysql_config_editor binary available (included with MySQL client tools)

Steps — Store Credentials

Create a login path named "local":

mysql_config_editor set --login-path=local --host=localhost --user=root --password

You will be prompted for the password. It is stored encrypted in ~/.mylogin.cnf.

Create a login path for a remote server:

mysql_config_editor set --login-path=production --host=db.example.com --user=appuser --password --port=3306

Steps — Use Stored Credentials

Connect using a stored login path:

mysql --login-path=local
mysql --login-path=production myappdb

Use with mysqldump:

mysqldump --login-path=local myappdb > backup.sql

Manage Login Paths

# List all stored login paths
mysql_config_editor print --all

# Remove a login path
mysql_config_editor remove --login-path=production

Verify

mysql_config_editor print --all
# Should show the stored host/user (password is always masked)

Notes

• The ~/.mylogin.cnf file is readable only by the owner. Do not change its permissions.
• Login paths work with all standard MySQL client programs: mysql, mysqldump, mysqlcheck, mysqladmin.
• On Windows, the file is stored in %APPDATA%\MySQL\.mylogin.cnf.

This error prevents MySQL from starting and usually indicates a permissions problem, a corrupted data directory, or a port conflict. The error message appears in the terminal output and in the MySQL error log.

Prerequisites

• Root or sudo access
• Access to the MySQL error log (typically /var/log/mysql/error.log)

Diagnosis

Always check the error log first for the specific cause:

sudo tail -50 /var/log/mysql/error.log

Fix 1 — Permissions on the data directory

sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
sudo systemctl start mysql

Fix 2 — Stale PID file

sudo rm /var/run/mysqld/mysqld.pid
sudo rm /var/run/mysqld/mysqld.sock
sudo systemctl start mysql

Fix 3 — Port already in use

sudo lsof -i :3306
# Kill any process occupying the port, then start MySQL
sudo systemctl start mysql

Fix 4 — Corrupted InnoDB files

# Add to /etc/mysql/my.cnf temporarily to force recovery
[mysqld]
innodb_force_recovery = 1

sudo systemctl start mysql
# If it starts, immediately dump all databases
mysqldump -u root -p --all-databases > recovery_dump.sql
# Then remove innodb_force_recovery and restore cleanly

Fix 5 — Disk full

df -h
# Free space, then restart
sudo systemctl start mysql

Verify

sudo systemctl status mysql
mysql -u root -p -e "SELECT 1;"

Notes

• Increase innodb_force_recovery from 1 to 6 one step at a time if MySQL still does not start. Higher values restrict more InnoDB operations.
• Never leave innodb_force_recovery > 0 in production — it disables crash recovery safeguards.

On recent Ubuntu and Debian installations, the MySQL root account uses the auth_socket plugin by default. This means it authenticates via the OS user rather than a password, so mysql -u root -p fails for non-root system users. This guide explains the cause and provides two fixes.

Prerequisites

• Ubuntu or Debian with MySQL 5.7+ or MySQL 8.x
• Sudo access on the server

Diagnosis

sudo mysql -u root
USE mysql;
SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

If the plugin column shows auth_socket or unix_socket, the root account uses OS authentication.

Fix 1 — Switch root to password authentication (recommended for applications)

sudo mysql -u root

-- MySQL 5.7
UPDATE mysql.user SET plugin='mysql_native_password', authentication_string=PASSWORD('YourNewPassword') WHERE User='root';
FLUSH PRIVILEGES;

-- MySQL 8.0+
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourNewPassword';
FLUSH PRIVILEGES;

Fix 2 — Create a separate admin user (avoids changing root)

sudo mysql -u root

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongAdminPass';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Then connect as: mysql -u admin -p

Verify

mysql -u root -p -e "SELECT CURRENT_USER();"

Notes

• The auth_socket plugin is intentionally secure — it prevents remote root logins. Only change it if you have a specific need for password-based root access.
• The sudo mysql method (Fix via system user) continues to work even after switching plugins, as long as you run it as the root OS user.

Moving the MySQL data directory to a dedicated drive improves I/O performance and separates database storage from the OS. This guide covers the process for Red Hat-based systems (CentOS, AlmaLinux, RHEL) with or without cPanel.

Prerequisites

• A second drive mounted and formatted (e.g., mounted at /data)
• Root access
• Enough space on the new drive for the existing MySQL data directory

Steps

1. Confirm the current MySQL data directory path:

# Standard Linux (non-cPanel)
ls /var/lib/mysql

# cPanel servers
ls /var/lib/mysql   # or check /etc/my.cnf for datadir

2. Identify the new drive mount point:

lsblk
df -h

3. Stop MySQL:

sudo systemctl stop mysql

4. Copy the data directory to the new location (preserving permissions):

sudo rsync -avz /var/lib/mysql/ /data/mysql/

5. Rename the old directory as a backup:

sudo mv /var/lib/mysql /var/lib/mysql_old

6. Update the MySQL configuration to point to the new location:

# Edit /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
datadir = /data/mysql
socket  = /data/mysql/mysql.sock

7. Fix ownership on the new directory:

sudo chown -R mysql:mysql /data/mysql

8. If AppArmor is active (Ubuntu), update the profile:

sudo nano /etc/apparmor.d/usr.sbin.mysqld
# Add:
/data/mysql/ r,
/data/mysql/** rwk,
sudo systemctl restart apparmor

9. Start MySQL:

sudo systemctl start mysql

Verify

mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"
sudo systemctl status mysql

Notes

• Use rsync -avz rather than cp -r to preserve file permissions and ownership.
• Do not remove /var/lib/mysql_old until MySQL is confirmed to be running correctly from the new location.
• On SELinux systems, relabel the new directory: sudo restorecon -R /data/mysql

This guide covers an alternative method for moving MySQL to a new drive using a bind mount. The bind mount approach avoids changing MySQL configuration files and works well when symlinks are not accepted by MySQL or AppArmor/SELinux.

Prerequisites

• A second drive mounted (e.g., at /mnt/newdrive)
• Root access and a maintenance window

Steps — Using Bind Mount

1. Stop MySQL:

sudo systemctl stop mysql

2. Copy the data directory with permissions intact:

sudo rsync -avzh /var/lib/mysql /mnt/newdrive/mysql

3. Back up the original directory:

sudo mv /var/lib/mysql /var/lib/mysql_backup

4. Create an empty mount point in the original location:

sudo mkdir /var/lib/mysql

5. Bind-mount the new location to the old path:

sudo mount --bind /mnt/newdrive/mysql /var/lib/mysql

6. Make the bind mount persistent across reboots — add to /etc/fstab:

/mnt/newdrive/mysql  /var/lib/mysql  none  bind  0  0

7. Start MySQL:

sudo systemctl start mysql

Steps — Using rsync + datadir Change (alternative)

sudo rsync -avzh /var/lib/mysql /mnt/newdrive/
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Set: datadir = /mnt/newdrive/mysql
sudo systemctl start mysql

Verify

mysql -u root -p -e "SHOW VARIABLES LIKE 'datadir';"
df -h /var/lib/mysql

Notes

• Bind mounts work transparently — MySQL sees the original path and no configuration change is required.
• Symlinks from /var/lib/mysql to a new location may fail if AppArmor or SELinux restricts MySQL to the original path.
• Keep /var/lib/mysql_backup until the new setup has been running stably for several days.

Dumping each MySQL database to a separate SQL file simplifies selective restores, makes transfers easier, and keeps backups organized. This guide shows how to loop over all databases and dump each one individually.

Prerequisites

• MySQL client tools installed
• Credentials with SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, and SHOW DATABASES privileges
• Sufficient disk space for all dumps

Steps — Dump Each Database to Its Own File

mysql -N -e 'SHOW DATABASES' | while read dbname; do
  mysqldump     --complete-insert     --routines     --triggers     --single-transaction     "$dbname" > "/backup/${dbname}_$(date +%F).sql"
done

This skips the system databases only if you add an exclusion:

mysql -N -e 'SHOW DATABASES' | grep -Ev '^(information_schema|performance_schema|sys|mysql)$' | while read dbname; do
  mysqldump     --complete-insert     --routines     --triggers     --single-transaction     "$dbname" > "/backup/${dbname}_$(date +%F).sql"
done

Steps — Compress the Dumps

mysql -N -e 'SHOW DATABASES' | grep -Ev '^(information_schema|performance_schema|sys|mysql)$' | while read dbname; do
  mysqldump --single-transaction --routines --triggers "$dbname" |     gzip > "/backup/${dbname}_$(date +%F).sql.gz"
done

Steps — Restore a Single Database from Its File

mysql -u root -p myappdb < /backup/myappdb_2025-01-15.sql

# From a compressed file:
gunzip -c /backup/myappdb_2025-01-15.sql.gz | mysql -u root -p myappdb

Verify

ls -lh /backup/*.sql
# Check that each database has a corresponding file

Notes

• Consider storing credentials in ~/.my.cnf or using --login-path to avoid passwords in scripts or shell history.
• To run this as a nightly cron job, add to /etc/cron.d/mysql-backup: 0 2 * * * mysql -N -e 'SHOW DATABASES' | ...

If a MySQL server crashed and only the raw data files (.frm and .ibd) were recovered, it is still possible to restore the table data. The .frm file contains the table structure; the .ibd file contains the InnoDB data. This guide covers extracting the schema from .frm files and importing the data.

Prerequisites

• MySQL server with innodb_file_per_table = 1 (required for .ibd restore)
• The recovered .frm and/or .ibd files
• A running MySQL instance of the same or compatible version

Steps — Extract Schema from .frm Files

1. Copy the .frm files into the MySQL data directory under a new database folder:

sudo mkdir /var/lib/mysql/recovered_db
sudo cp /recovered_files/*.frm /var/lib/mysql/recovered_db/
sudo chown -R mysql:mysql /var/lib/mysql/recovered_db

2. Use mysqlfrm (from MySQL Utilities) to read the table structure:

mysqlfrm --diagnostic /var/lib/mysql/recovered_db/tablename.frm

3. Create the tables using the extracted CREATE TABLE statements in the target database:

mysql -u root -p recovered_db < schema.sql

Steps — Import Data from .ibd Files (InnoDB Transportable Tablespaces)

1. Create the table using the schema from the .frm step:

mysql -u root -p recovered_db -e "CREATE TABLE tablename (...);"

2. Discard the new empty tablespace to allow import:

mysql -u root -p recovered_db -e "ALTER TABLE tablename DISCARD TABLESPACE;"

3. Copy the recovered .ibd file into place:

sudo cp /recovered_files/tablename.ibd /var/lib/mysql/recovered_db/
sudo chown mysql:mysql /var/lib/mysql/recovered_db/tablename.ibd

4. Import the tablespace:

mysql -u root -p recovered_db -e "ALTER TABLE tablename IMPORT TABLESPACE;"

Verify

mysql -u root -p recovered_db -e "SELECT COUNT(*) FROM tablename;"

Notes

• The .ibd import requires that the table was created with innodb_file_per_table = 1. Shared tablespace (ibdata1) data cannot be imported this way.
• The source and target MySQL versions must be compatible for IMPORT TABLESPACE to work.
• If mysqlfrm is not available, use Percona's recovery-tools or copy the .frm files and run SHOW CREATE TABLE from a MySQL shell pointed at the recovered directory.

This guide documents fixes for common MySQL and phpMyAdmin errors, including count() deprecation warnings in phpMyAdmin and authentication issues.

Fix 1 — phpMyAdmin: count() Parameter Must Be an Array

This warning appears in phpMyAdmin when running PHP 7.2+ with an older phpMyAdmin version.

Fix in sql.lib.php:

# File: /usr/share/phpmyadmin/libraries/sql.lib.php
# Find:
(count($analyzed_sql_results['select_expr'] == 1)
# Replace with:
(count($analyzed_sql_results['select_expr']) == 1

Fix in plugin_interface.lib.php:

# File: /usr/share/phpmyadmin/libraries/plugin_interface.lib.php
# Find:
if ($options != null && count($options) > 0) {
# Replace with:
if ($options != null && count((array)$options) > 0) {

Fix 2 — MySQL: Access Denied for Root After Plugin Change

-- Reset plugin and password for a user (MySQL 5.7)
UPDATE mysql.user SET plugin='mysql_native_password',
  authentication_string=PASSWORD('newpassword')
  WHERE User='username';
FLUSH PRIVILEGES;

Fix 3 — MySQL: Table is Marked as Crashed

If the error log shows a table marked as crashed:

mysqlcheck --auto-repair -u root -p --all-databases

Fix 4 — MySQL: Too Many Connections

# Check current setting
SHOW VARIABLES LIKE 'max_connections';

# Increase at runtime
SET GLOBAL max_connections = 500;

# Persist in /etc/mysql/my.cnf
[mysqld]
max_connections = 500

Notes

• For phpMyAdmin errors, upgrading phpMyAdmin to the latest version for your PHP version is always the best long-term fix.
• Always check /var/log/mysql/error.log for the root cause before applying fixes.

Atlassian Confluence requires specific MySQL configuration to function correctly. The settings below ensure proper character encoding, transaction isolation, and binary logging format as required by Confluence.

Prerequisites

• MySQL 5.7 or 8.0 installed
• Root access to the MySQL configuration file
• A maintenance window (MySQL restart required)

Steps

1. Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or on older Ubuntu/Debian:
sudo nano /etc/mysql/my.cnf

2. Add the following settings under [mysqld]:

[mysqld]
character-set-server    = utf8
collation-server        = utf8_bin
default-storage-engine  = INNODB
innodb_log_file_size    = 2G
transaction-isolation   = READ-COMMITTED
binlog_format           = ROW

3. Restart MySQL:

sudo systemctl restart mysql

4. Create the Confluence database and user:

CREATE DATABASE confluencedb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'confluenceuser'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT ALL PRIVILEGES ON confluencedb.* TO 'confluenceuser'@'localhost';
FLUSH PRIVILEGES;

Verify

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';
SHOW VARIABLES LIKE 'transaction_isolation';
SHOW VARIABLES LIKE 'binlog_format';

Notes

• Confluence requires READ-COMMITTED transaction isolation and ROW binlog format when binary logging is enabled. Without these, Confluence may show database errors at startup.
• The innodb_log_file_size of 2 GB is recommended for Confluence instances with large attachments and page content.
• Changing innodb_log_file_size requires MySQL to be stopped, the old log files deleted, and MySQL restarted to regenerate them.

This guide covers upgrading MySQL to a newer version on Ubuntu/Debian using the official MySQL APT repository. The process uses a minimal-downtime approach with a package upgrade.

Prerequisites

• Ubuntu or Debian server
• Root or sudo access
• A full database backup before starting

Steps

1. Back up all databases:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers > /backup/pre_upgrade_$(date +%F).sql

2. Download and install the MySQL APT config package:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

In the interactive dialog, select the desired MySQL version (e.g., MySQL 8.0).

3. Stop the MySQL service:

sudo service mysql stop

4. Update the package list and upgrade:

sudo apt-get update
sudo apt-get --with-new-pkgs upgrade

5. Run the MySQL upgrade utility:

sudo mysql_upgrade -u root -p

6. Restart MySQL:

sudo systemctl restart mysql

Verify

mysql --version
mysql -u root -p -e "SELECT VERSION();"
sudo systemctl status mysql

Notes

• In MySQL 8.0.16+, mysql_upgrade is run automatically on server startup — you can skip step 5.
• Review the MySQL release notes for breaking changes before upgrading (especially authentication plugin changes in MySQL 8.0).
• Always test the upgrade on a staging server before applying to production.

This guide covers upgrading MySQL 5.5 to 5.6 or 5.7 on Ubuntu/Debian and CentOS/RHEL systems. Direct upgrades from 5.5 to 5.7 are not recommended — upgrade to 5.6 first, then to 5.7.

Prerequisites

• Root or sudo access
• A full backup of all databases before starting
• Note: do not skip from 5.5 directly to 5.7

Steps — Ubuntu 12.04/14.04 (MySQL 5.5 to 5.6)

1. Back up all databases:

mysqldump -u root -p --all-databases --single-transaction > /backup/mysql55_$(date +%F).sql

2. Back up the MySQL configuration:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

3. Add the MySQL 5.6 repository and install:

sudo add-apt-repository 'deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.6'
wget -O - https://repo.mysql.com/RPM-GPG-KEY-mysql | sudo apt-key add -
sudo apt-get update
sudo apt-get install mysql-server-5.6

4. Run the upgrade utility:

sudo mysql_upgrade -u root -p

5. Restart MySQL:

sudo systemctl restart mysql

Steps — CentOS/RHEL

# Remove old MySQL
sudo yum remove mysql mysql-server

# Install MySQL 5.6 repository
wget https://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum install mysql-community-server

sudo systemctl start mysqld
sudo mysql_upgrade -u root -p

Verify

mysql -u root -p -e "SELECT VERSION();"
sudo systemctl status mysql

Notes

• MySQL 5.1 to 5.6/5.7 direct upgrade will damage tables — always step through versions.
• After upgrading, run mysqlcheck -u root -p --all-databases --check-upgrade to identify tables that need updating.
• Review /var/log/mysql/error.log after the upgrade for deprecation warnings that indicate queries requiring changes.

This error occurs after upgrading MariaDB from 10.1 to 10.2+ when the unix_socket authentication plugin is referenced in the user table but not loaded by the new server. The fix involves switching the root user back to password authentication.

Prerequisites

• Sudo access on the server
• MariaDB 10.2+ installed

Steps

1. Start MariaDB in safe mode to bypass authentication:

sudo systemctl stop mariadb
sudo mysqld_safe --skip-grant-tables &

2. Connect without a password and fix the root user:

mysql -u root

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
UPDATE user SET authentication_string=PASSWORD('YourNewRootPassword') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

3. Stop the safe-mode instance and start MariaDB normally:

sudo mysqladmin -u root shutdown
sudo systemctl start mariadb

4. Log in with the new password to confirm:

mysql -u root -p

Verify

SELECT User, Host, plugin FROM mysql.user WHERE User = 'root';

Notes

• In MariaDB 10.4+, the recommended authentication for root is mysql_native_password or ed25519. The unix_socket plugin was removed from the default build in some versions.
• If mysqld_safe is not available, use: sudo mysqld --skip-grant-tables --skip-networking &

Optimizing MySQL query performance reduces server load, speeds up application response times, and scales your database further before requiring hardware upgrades. This guide covers practical, high-impact optimization techniques for DBAs and developers.

Prerequisites

• MySQL 5.6 or later
• Access to the slow query log and EXPLAIN output
• Basic understanding of SQL and table structure

1. Use Indexes Effectively

-- Check if a query uses an index
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Add an index on frequently queried columns
CREATE INDEX idx_email ON users (email);

-- Check existing indexes
SHOW INDEX FROM users;

2. Avoid SELECT *

-- Bad: fetches all columns
SELECT * FROM orders WHERE user_id = 42;

-- Good: fetch only what you need
SELECT id, order_date, total FROM orders WHERE user_id = 42;

3. Use LIMIT on Large Tables

SELECT id, name FROM products ORDER BY name LIMIT 50 OFFSET 0;

4. Optimize InnoDB Buffer Pool

# In /etc/mysql/my.cnf — set to 70-80% of available RAM
[mysqld]
innodb_buffer_pool_size = 4G

5. Run OPTIMIZE TABLE to Reclaim Space

-- After large deletes or updates
OPTIMIZE TABLE orders;

-- Or use mysqlcheck for all tables
mysqlcheck -u root -p --optimize --all-databases

6. Analyze Query Performance

-- Enable profiling for a session
SET profiling = 1;
SELECT * FROM large_table WHERE status = 'active';
SHOW PROFILES;

Verify

SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Handler_read%';

Notes

• Use MySQLTuner (see the MySQLTuner guide) for automated recommendations based on runtime statistics.
• Indexes speed up reads but slow down writes. Add indexes only on columns used in WHERE, JOIN, or ORDER BY clauses.
• Use EXPLAIN EXTENDED and EXPLAIN FORMAT=JSON for deeper query analysis in MySQL 5.6+.

MySQLTuner is a Perl script that analyzes MySQL runtime variables and statistics and provides tuning recommendations. Running it on a server that has been live for at least 24 hours gives the most useful output.

Prerequisites

• MySQL or MariaDB running
• Perl installed (perl --version)
• Root or sudo access

Steps — Install and Run MySQLTuner

1. Download MySQLTuner:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

2. Run against the local MySQL instance:

sudo perl mysqltuner.pl --user root --pass YourRootPassword

3. Run with detailed recommendations:

sudo perl mysqltuner.pl --user root --pass YourRootPassword   --buffers --queries --json

Applying Common Recommendations

Increase InnoDB buffer pool (most common recommendation):

[mysqld]
innodb_buffer_pool_size = 4G   # 70-80% of available RAM

Increase query cache (MySQL 5.7 and earlier only):

[mysqld]
query_cache_type = 1
query_cache_size = 64M

Increase max connections:

[mysqld]
max_connections = 300

Tune InnoDB log file size:

[mysqld]
innodb_log_file_size = 256M

After editing /etc/mysql/my.cnf, restart MySQL:

sudo systemctl restart mysql

Verify

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

Notes

• Run MySQLTuner after the server has been under typical load for at least 24 hours for meaningful recommendations.
• Query cache was deprecated in MySQL 5.7.20 and removed in MySQL 8.0 — do not enable it on MySQL 8.
• Apply changes one at a time and monitor the effect before applying the next recommendation.

This error means the MySQL or MariaDB systemd service unit has been masked — a deliberate action that prevents the service from being started directly. It commonly occurs after a failed upgrade or when a previous administrator masked the service to prevent accidental starts.

Prerequisites

• Root or sudo access
• systemd-based Linux distribution

Steps

1. Unmask the service:

sudo systemctl unmask mysql
# or for MariaDB:
sudo systemctl unmask mariadb

2. Remove any conflicting old database files (if the data directory is corrupted or from a failed install):

sudo rm -rf /var/lib/mysql*

3. Reinitialize the MySQL data directory:

sudo mysql_install_db --user=mysql

4. Start the service:

sudo systemctl start mysql
# or:
sudo systemctl start mariadb

5. Enable the service to start on boot:

sudo systemctl enable mysql

Verify

sudo systemctl status mysql
mysql -u root -p -e "SELECT VERSION();"

Notes

• A masked unit is symlinked to /dev/null. systemctl unmask removes that symlink and restores normal behavior.
• Step 2 (removing /var/lib/mysql) is only appropriate if the data directory is empty, corrupted, or from a failed installation. Skip it if you have live data to preserve.
• After reinitializing, run sudo mysql_secure_installation to set a root password and secure the instance.

This error occurs when connecting to MySQL 8+ from an older client or application that does not support the caching_sha2_password authentication plugin introduced as the default in MySQL 8.0.4. The fix involves either upgrading the client or configuring the user to use the legacy mysql_native_password plugin.

Prerequisites

• MySQL 8.0+ server
• Root or admin access to MySQL

Fix 1 — Change a Specific User to mysql_native_password

ALTER USER 'username'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Fix 2 — Set Default Plugin to mysql_native_password Server-Wide

Add to /etc/mysql/my.cnf (or the MySQL container config):

[mysqld]
default-authentication-plugin = mysql_native_password

Restart MySQL:

sudo systemctl restart mysql

Fix 3 — Create a New User with mysql_native_password

CREATE USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';
GRANT ALL PRIVILEGES ON myappdb.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

Verify

SELECT User, Host, plugin FROM mysql.user WHERE User = 'username';

Notes

• caching_sha2_password is more secure than mysql_native_password. Prefer upgrading the client library (e.g., mysqlclient, pymysql, JDBC connector) to one that supports caching_sha2_password rather than downgrading the server plugin.
• PHP mysqli with mysqlnd supports caching_sha2_password from PHP 7.4+.
• In MySQL 8.4+, mysql_native_password is disabled by default and must be explicitly enabled.

Starting with MySQL 8.0.4, the default authentication plugin changed from mysql_native_password to caching_sha2_password. This guide explains the change, its implications, and how to manage compatibility with existing applications.

What Changed

• Before MySQL 8.0.4: default plugin is mysql_native_password
• MySQL 8.0.4+: default plugin is caching_sha2_password
• MySQL 8.4+: mysql_native_password is disabled by default

Why caching_sha2_password Is Better

• Uses SHA-256 hashing (stronger than SHA-1 used in mysql_native_password)
• Caches the authentication result to reduce CPU cost on subsequent logins
• Supports challenge-response without requiring plaintext passwords over the wire

Check Current Authentication Plugin

SELECT User, Host, plugin FROM mysql.user;
SHOW VARIABLES LIKE 'default_authentication_plugin';

Set Default Plugin Globally (for backward compatibility)

[mysqld]
default-authentication-plugin = mysql_native_password

Change a User's Plugin

-- Switch a user to caching_sha2_password (recommended)
ALTER USER 'appuser'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'StrongPassword';

-- Switch back to mysql_native_password (for legacy apps)
ALTER USER 'appuser'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'StrongPassword';

FLUSH PRIVILEGES;

Notes

• Client libraries that support caching_sha2_password: MySQL Connector/J 8.0+, MySQL Connector/Python 8.0+, PHP mysqlnd (PHP 7.4+), libmysqlclient 8.0+.
• Older ORMs and frameworks that bundle their own MySQL connector may not support caching_sha2_password — check compatibility before upgrading MySQL.
• When using SSL/TLS connections, caching_sha2_password works without pre-caching the first login.

MySQL 5.7 enables strict SQL mode (STRICT_TRANS_TABLES) by default, which rejects invalid or missing values in INSERT and UPDATE statements. If you are running a legacy application written for older MySQL versions, you may need to disable strict mode temporarily or permanently.

Prerequisites

• MySQL 5.7+ on Ubuntu, Debian, or CentOS/RHEL
• Root or sudo access

Steps — Check the Current SQL Mode

SHOW VARIABLES LIKE 'sql_mode';

Steps — Disable Strict Mode Permanently

1. Create a new config snippet file:

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf

2. Add the following content:

[mysqld]
sql_mode = IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

This removes STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY from the default mode.

3. Restart MySQL:

sudo systemctl restart mysql

Steps — Disable Strict Mode at Runtime (Session Only)

SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Verify

SHOW VARIABLES LIKE 'sql_mode';

Notes

• Disabling strict mode allows invalid dates (like 0000-00-00) and truncated strings to be silently accepted. This masks data quality issues — fix the application instead when possible.
• MySQL 8.0 removes NO_AUTO_CREATE_USER from the available modes — omit it on MySQL 8.
• In Ubuntu, the conf.d directory is /etc/mysql/conf.d/; on CentOS/RHEL it may be /etc/mysql/ or configured via /etc/my.cnf.

The error mysqli_connect(): (HY000/2002): Permission denied typically occurs when PHP attempts to connect to MySQL via a Unix socket but is blocked by filesystem permissions or SELinux/AppArmor policies.

Prerequisites

• PHP with the mysqli extension
• MySQL or MariaDB running
• Root or sudo access

Diagnosis

# Check MySQL socket location
mysql -u root -p -e "SHOW VARIABLES LIKE 'socket';"

# Check socket file permissions
ls -la /var/run/mysqld/mysqld.sock

# Check SELinux status (CentOS/RHEL)
sestatus

Fix 1 — Correct Socket File Permissions

sudo chmod 755 /var/run/mysqld/
sudo chown mysql:mysql /var/run/mysqld/mysqld.sock

Fix 2 — Use TCP Instead of Unix Socket

In your PHP application or php.ini, use 127.0.0.1 (TCP) instead of localhost (which triggers Unix socket use):

// PHP connection string
$conn = mysqli_connect('127.0.0.1', 'username', 'password', 'dbname');
// NOT: mysqli_connect('localhost', ...)

Fix 3 — SELinux Blocking the Connection (CentOS/RHEL)

# Allow httpd (Apache/Nginx) to connect to MySQL
sudo setsebool -P httpd_can_network_connect_db 1

# Check SELinux denials
sudo ausearch -c 'php' --raw | audit2allow

Fix 4 — AppArmor Blocking the Connection (Ubuntu)

# Check AppArmor status
sudo aa-status

# Temporarily disable AppArmor for mysqld
sudo aa-complain /usr/sbin/mysqld

Verify

php -r "mysqli_connect('127.0.0.1','root','password') or die(mysqli_connect_error());"

Notes

• Connecting via 127.0.0.1 always uses TCP; connecting via localhost uses the Unix socket. This distinction is the most common source of this error.
• Ensure the MySQL user grants access for 'user'@'127.0.0.1' if you switch to TCP, as MySQL treats it differently from 'user'@'localhost'.

The MySQL Yum repository provides official RPM packages for Red Hat-based distributions including CentOS, RHEL, Oracle Linux, and Fedora. This guide covers adding the repository and performing a clean MySQL installation.

Prerequisites

• CentOS 6/7/8, RHEL, Oracle Linux, or Fedora
• Root or sudo access
• Internet access to repo.mysql.com

Steps

1. Browse the available repository packages:

# All repo RPMs are available at:
# https://repo.mysql.com/

2. Install the MySQL Yum repository for EL7 (MySQL 8.0):

wget https://repo.mysql.com/mysql80-community-release-el7-7.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-7.noarch.rpm

3. Verify the repository is configured:

yum repolist all | grep mysql

4. Install MySQL Community Server:

sudo yum install mysql-community-server

5. Start MySQL and enable it on boot:

sudo systemctl start mysqld
sudo systemctl enable mysqld

6. Get the auto-generated temporary root password:

sudo grep 'temporary password' /var/log/mysqld.log

7. Run the security script to set the root password:

sudo mysql_secure_installation

Install a Specific MySQL Version

# Disable the MySQL 8.0 repo and enable MySQL 5.7
sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql57-community
sudo yum install mysql-community-server

Verify

mysql --version
sudo systemctl status mysqld

Notes

• EL8 and EL9 platforms use dnf instead of yum — replace yum with dnf in all commands above.
• Not all MySQL versions are available for all platform versions — check the repository page for the compatibility matrix.

The MySQL APT repository provides official deb packages for Debian and Ubuntu, allowing installation and upgrades of MySQL server, client, and utilities. All available packages are listed at https://repo.mysql.com/.

Prerequisites

• Debian 9/10/11 or Ubuntu 18.04/20.04/22.04
• Root or sudo access

Steps

1. Download the MySQL APT repository configuration package:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb

2. Install the configuration package (an interactive dialog will ask which MySQL version to install):

sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb

3. Update the package list:

sudo apt-get update

4. Install MySQL server:

sudo apt-get install mysql-server

5. Secure the installation:

sudo mysql_secure_installation

Change the MySQL Version After Installation

# Reconfigure the APT repository package
sudo dpkg-reconfigure mysql-apt-config
sudo apt-get update
sudo apt-get install mysql-server

Verify

mysql --version
sudo systemctl status mysql

Notes

• Supported versions vary by platform. For example, Ubuntu 14.04 binaries are maintained for MySQL 5.7 but not MySQL 8.0.
• Use apt-cache policy mysql-server after updating to see available versions from the repository.
• If you get a GPG verification error after adding the repository, see the guide for fixing MySQL APT GPG signature errors.

When you need a specific MySQL version rather than the latest available, use the MySQL Community repository and enable only the version-specific sub-repository. This guide covers the process for both Yum (CentOS/RHEL) and APT (Debian/Ubuntu) package managers.

Prerequisites

• MySQL Yum or APT repository already configured (see the repository installation guides)
• Root or sudo access

Steps — CentOS/RHEL (Yum)

1. Install the MySQL community release RPM:

sudo rpm -ivh mysql80-community-release-el7-2.noarch.rpm

2. List available MySQL version repositories:

yum repolist all | grep mysql

3. Disable the default (latest) version and enable the desired version:

# Example: switch from MySQL 8.0 to MySQL 5.6
sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql56-community

4. Install MySQL:

sudo yum install mysql-community-server
sudo systemctl start mysqld
sudo systemctl enable mysqld

5. Get the temporary root password:

sudo grep 'temporary password' /var/log/mysqld.log

Steps — Ubuntu/Debian (APT)

# Download and configure the APT repo package
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
# Select the desired version in the interactive dialog
sudo apt-get update
sudo apt-get install mysql-server

Verify

mysql --version

Notes

• Only one MySQL community version repository should be enabled at a time.
• Available MySQL repositories are listed at https://repo.mysql.com/.

MySQL 5.7+ includes the validate_password plugin (MySQL 8.0: validate_password component) which enforces password complexity rules. This guide covers viewing, configuring, and adjusting the validation policy.

Prerequisites

• MySQL 5.7 or MySQL 8.0+
• Root access to MySQL

Steps — View Current Validation Settings

SHOW VARIABLES LIKE 'validate_password%';

Common variables returned:

• validate_password_length — minimum password length (default: 8)
• validate_password_policy — LOW, MEDIUM, or STRONG
• validate_password_mixed_case_count — required uppercase/lowercase chars
• validate_password_number_count — required digits
• validate_password_special_char_count — required special characters

Steps — Adjust the Policy at Runtime

-- Set to LOW (length check only)
SET GLOBAL validate_password_policy = LOW;

-- Reduce minimum length
SET GLOBAL validate_password_length = 6;

-- Or set to MEDIUM (default): length + mixed case + digits + special chars
SET GLOBAL validate_password_policy = MEDIUM;

Steps — Persist Settings in my.cnf

[mysqld]
validate_password_policy = MEDIUM
validate_password_length = 8

Steps — Disable validate_password (MySQL 5.7)

-- At runtime
UNINSTALL PLUGIN validate_password;

-- Or in my.cnf (prevents loading at startup)
[mysqld]
# validate_password = OFF

Verify

SHOW VARIABLES LIKE 'validate_password%';
-- Test with a weak password:
SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD('weak');

Notes

• In MySQL 8.0, the plugin was replaced by a component: INSTALL COMPONENT 'file://component_validate_password';
• The STRONG policy additionally checks passwords against a dictionary file specified by validate_password_dictionary_file.
• Setting the policy to LOW is not recommended for production — use it only for development environments.

Global Transaction Identifiers (GTID) simplify replication failover and consistency, but some scenarios require disabling GTID mode — for example, when migrating dumps that contain GTID information, or when switching replication topology. This guide covers disabling GTID on the replica without touching the master.

Prerequisites

• MySQL 5.6+ with GTID-based replication configured
• Access to the replica with SUPER privilege
• A maintenance window (replica restart required)

Steps

1. Confirm GTID mode is currently ON:

-- On the master
SHOW PROCESSLIST\G
SHOW VARIABLES LIKE 'gtid_mode';

2. Stop the replica (slave) SQL and I/O threads:

STOP SLAVE;

3. Transition GTID mode off step by step (MySQL 5.7.6+):

-- Step through the modes: ON → ON_PERMISSIVE → OFF_PERMISSIVE → OFF
SET GLOBAL gtid_mode = ON_PERMISSIVE;
SET GLOBAL gtid_mode = OFF_PERMISSIVE;
-- Wait until Ongoing_anonymous_transaction_count reaches 0
SHOW STATUS LIKE 'Ongoing_anonymous_transaction_count';
SET GLOBAL gtid_mode = OFF;

4. Disable GTID enforcement:

SET GLOBAL enforce_gtid_consistency = OFF;

5. Update /etc/mysql/my.cnf on the replica to persist the change:

[mysqld]
gtid_mode = OFF
enforce_gtid_consistency = OFF

6. Restart the replica and resume replication:

CHANGE MASTER TO MASTER_AUTO_POSITION = 0,
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 4;
START SLAVE;

Verify

SHOW SLAVE STATUS\G
-- Check: Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running

Notes

• Do not skip the intermediate GTID mode steps — jumping directly from ON to OFF may break replication.
• Disabling GTID on the replica only does not require touching the master configuration.
• In MySQL 8.0, SLAVE terminology is replaced by REPLICA: use STOP REPLICA, START REPLICA, and SHOW REPLICA STATUS.

When MySQL fails to start after an upgrade or kernel change and no useful errors appear in the log, a clean reinstall may be the most effective solution. This guide covers a full MySQL removal and reinstallation on Ubuntu/Debian while preserving your data if possible.

Prerequisites

• Ubuntu or Debian server
• Root or sudo access
• A backup of /var/lib/mysql if data is to be preserved

Steps

1. Back up the data directory if MySQL can still be read:

sudo rsync -avz /var/lib/mysql /backup/mysql_$(date +%F)

2. Fully remove MySQL and its configuration:

sudo apt-get purge mysql-server mysql-common mysql-client
sudo rm -rf /etc/mysql
sudo rm -rf /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

3. Reinstall MySQL:

sudo apt-get update
sudo apt-get install mysql-server

4. If the fresh install also fails to start, check the error log:

sudo journalctl -xe -u mysql
sudo tail -50 /var/log/mysql/error.log

5. If the new instance starts successfully, restore your databases from a dump or from the backup of the data directory:

mysql -u root -p < /backup/all_databases.sql

Verify

sudo systemctl status mysql
mysql -u root -p -e "SHOW DATABASES;"

Notes

• Restoring by copying /var/lib/mysql directly only works if the MySQL versions are identical. For cross-version restores, always use a SQL dump.
• If no dump is available, try starting the new MySQL instance and then copying the old data directory files over before starting MySQL — this may work for InnoDB tables with compatible versions.
• Check /var/log/upstart/mysql.log (Ubuntu 14.04 and earlier) or journalctl -u mysql (systemd) for startup errors.

MySQL 5.6 is not available in the default Ubuntu 16.04 or 18.04 repositories (which ship MySQL 5.7 and 8.0 respectively). To install MySQL 5.6 on these systems, use the Ubuntu 14.04 (Trusty) archive repository or the official MySQL APT repository with the 5.6 sub-repository enabled.

Prerequisites

• Ubuntu 16.04 or 18.04 (64-bit)
• Root or sudo access

Method 1 — From Ubuntu 14.04 (Trusty) Archive

1. Add the Trusty universe repository:

sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe'
sudo apt-get update

2. Install MySQL 5.6 client and server:

sudo apt-get install mysql-client-5.6 mysql-server-5.6

Method 2 — From MySQL Official APT Repository

wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
# In the dialog, select MySQL 5.6
sudo apt-get update
sudo apt-get install mysql-server

Steps — Post-Installation

sudo mysql_secure_installation
sudo systemctl enable mysql
sudo systemctl start mysql

Verify

mysql --version
# Expected: mysql  Ver 14.14 Distrib 5.6.x
sudo systemctl status mysql

Notes

• MySQL 5.6 reached end-of-life in February 2021 and no longer receives security patches. Use MySQL 5.7 or 8.0 for new installations.
• Installing from the Trusty archive is generally safe for MySQL 5.6 packages, but be aware that library versions may differ from what the packages expect on Ubuntu 18.04.

If the MySQL root password is lost or unknown, it can be reset by starting MySQL in a mode that bypasses authentication. This guide covers two methods: using mysql_secure_installation (simplest) and using the --skip-grant-tables method (for when the first method does not work).

Prerequisites

• Root or sudo access on the server
• MySQL 5.7 or 8.0 on Ubuntu 18.04+

Method 1 — Using mysql_secure_installation

sudo mysql_secure_installation

When prompted for the current root password, press Enter if blank. Then enter and confirm the new password.

Method 2 — Skip Grant Tables (Manual Reset)

1. Stop MySQL:

sudo systemctl stop mysql

2. Start MySQL without grant tables:

sudo mysqld_safe --skip-grant-tables --skip-networking &

3. Connect without a password:

mysql -u root

4. Reset the root password:

-- MySQL 5.7
UPDATE mysql.user SET authentication_string=PASSWORD('NewPassword123') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;

-- MySQL 8.0
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123';
FLUSH PRIVILEGES;
EXIT;

5. Restart MySQL normally:

sudo mysqladmin -u root shutdown
sudo systemctl start mysql

Method 3 — Using init-file (MySQL 8.0 recommended approach)

# Create a file with the reset statement
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';" > /tmp/reset.sql
sudo systemctl stop mysql
sudo mysqld --init-file=/tmp/reset.sql &
# Wait a few seconds, then kill the background process
sudo systemctl start mysql

Verify

mysql -u root -p -e "SELECT CURRENT_USER();"

Notes

• On Ubuntu 18.04 with MySQL 5.7, the root account often uses auth_socket by default. In that case, sudo mysql works without a password and you can run ALTER USER directly.
• Always remove the /tmp/reset.sql file after use: rm /tmp/reset.sql

Storing MySQL credentials in a ~/.my.cnf file allows mysqldump and other MySQL tools to authenticate without prompting for a password. This is essential for automated backup scripts and cron jobs.

Prerequisites

• MySQL client tools installed
• A MySQL user account with the appropriate privileges

Steps — Using ~/.my.cnf

1. Create or edit ~/.my.cnf:

nano ~/.my.cnf

2. Add the credentials:

[mysqldump]
user     = my_username
password = my_password
host     = localhost

3. Restrict file permissions to owner only:

chmod 600 ~/.my.cnf

4. Run mysqldump without the -p flag:

# Correct — reads password from ~/.my.cnf
mysqldump -u my_username my_db > my_db.sql

# Wrong — the -p flag overrides the config file and prompts interactively
mysqldump -u my_username -p my_db > my_db.sql

Steps — Using a Custom Config File Path

mysqldump --defaults-file=/path/to/.my.cnf -u my_username my_db > my_db.sql

Steps — Using mysql_config_editor (More Secure)

mysql_config_editor set --login-path=backup --host=localhost --user=backup_user --password
# Then run:
mysqldump --login-path=backup my_db > my_db.sql

Verify

mysqldump my_db > /tmp/test.sql && echo "Success"

Notes

• The [mysqldump] section in ~/.my.cnf applies only to mysqldump. Use [mysql] for the interactive client and [client] for all MySQL client programs.
• mysql_config_editor stores credentials in an encrypted format and is preferred over plaintext ~/.my.cnf for sensitive environments.
• Avoid putting the password directly in the username option: -ppassword in scripts is visible in the process list.

When a full MySQL dump (--all-databases) contains multiple databases, you can restore just one without splitting the dump file. This guide covers two methods: using the --one-database option and using sed to extract the target database.

Prerequisites

• A dump file created with mysqldump --all-databases
• The target database already created on the destination server, or you are creating it fresh

Method 1 — Using --one-database

# Create the target database first
mysql -u root -p -e "CREATE DATABASE destdbname;"

# Restore only destdbname from the full dump
mysql -u root -p --one-database destdbname < all_databases.sql

Replace destdbname with the name of the database as it appears in the dump.

Method 2 — Extract with sed and Restore

# Extract just the desired database from the dump
sed -n '/^-- Current Database: `destdbname`/,/^-- Current Database: `/p' all_databases.sql > destdbname_only.sql

# Restore the extracted file
mysql -u root -p destdbname < destdbname_only.sql

Method 3 — Extract a Database by Name (Alternative sed Pattern)

sed -n '/^USE `destdbname`;/,/^USE `/p' all_databases.sql | mysql -u root -p destdbname

Verify

mysql -u root -p destdbname -e "SHOW TABLES;"
mysql -u root -p destdbname -e "SELECT COUNT(*) FROM important_table;"

Notes

• The --one-database option tells MySQL to ignore all SQL statements until it sees a USE destdbname; statement, then execute everything until the next USE. It relies on the dump containing these USE statements — dumps created with --all-databases always include them.
• This approach is slower on very large dumps compared to having per-database dump files. See the guide on dumping each database to its own file.

This guide provides a shell script that backs up all MySQL user databases to separate files, excludes the system schemas (information_schema, performance_schema, sys), and uses mysql_config_editor for secure credential storage.

Prerequisites

• MySQL client tools installed
• A MySQL backup user with SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, and SHOW DATABASES privileges
• mysql_config_editor configured with a "local-backup" login path

Steps — Set Up the Backup User and Login Path

-- Create backup user
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'BackupPassword';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, SHOW DATABASES ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

# Store credentials securely
mysql_config_editor set --login-path=local-backup --host=localhost --user=backup --password

Steps — Backup Script

#!/bin/bash
# backup-mysql.sh — backs up each database to its own compressed file

BACKUP_DIR="/backup/mysql"
DATE=$(date +%F)
mkdir -p "$BACKUP_DIR/$DATE"

# Get list of databases, excluding system schemas
DATABASES=$(mysql --login-path=local-backup -N -e   "SELECT schema_name FROM information_schema.schemata
   WHERE schema_name NOT IN ('information_schema','performance_schema','sys','mysql');")

for DB in $DATABASES; do
  echo "Backing up $DB..."
  mysqldump --login-path=local-backup     --single-transaction     --routines     --triggers     --events     "$DB" | gzip > "$BACKUP_DIR/$DATE/${DB}.sql.gz"
done

echo "Backup complete: $BACKUP_DIR/$DATE"

Make the script executable and schedule it:

chmod +x /usr/local/bin/backup-mysql.sh

# Add to cron (runs daily at 2 AM)
echo "0 2 * * * root /usr/local/bin/backup-mysql.sh" > /etc/cron.d/mysql-backup

Verify

ls -lh /backup/mysql/$(date +%F)/
# Each database should have its own .sql.gz file

Notes

• The mysql system schema can be backed up with mysqldump --login-path=local-backup mysql > mysql_system.sql but is generally best recreated fresh during a restore.
• Retain backups for at least 30 days and store off-server (e.g., S3, NFS mount) for disaster recovery.
• Test restores periodically: gunzip -c /backup/mysql/DATE/mydb.sql.gz | mysql -u root -p mydb_test

MySQL error #1227 - Access denied; you need (at least one of) the SUPER privilege(s) commonly occurs when importing a dump that contains stored procedures, functions, or triggers with a DEFINER clause referencing a user that does not exist on the target server, or when the importing user lacks SUPER privilege.

Prerequisites

• MySQL root access or an account with SUPER privilege
• The SQL dump file to be imported

Fix 1 — Import as Root or SUPER User

mysql -u root -p targetdb < dump.sql

Fix 2 — Strip or Replace DEFINER Clauses Before Importing

# Remove all DEFINER clauses from the dump
sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' dump.sql
mysql -u root -p targetdb < dump.sql

Fix 3 — Replace DEFINER with the Importing User (cPanel/shared hosting)

# Replace the original definer username with your cPanel username
sed -i 's/DEFINER=`original_user`@`localhost`/DEFINER=`cpanel_username`@`localhost`/g' dump.sql
mysql -u cpanel_username -p targetdb < dump.sql

Fix 4 — Grant SUPER Privilege to the Importing User

-- Run as root
GRANT SUPER ON *.* TO 'importuser'@'localhost';
FLUSH PRIVILEGES;

Fix 5 — Set log_bin_trust_function_creators (for RDS or restricted environments)

-- Required when importing functions/triggers without SUPER on binary-log-enabled servers
SET GLOBAL log_bin_trust_function_creators = 1;

Verify

mysql -u root -p targetdb < dump.sql
SHOW FUNCTION STATUS WHERE Db = 'targetdb';
SHOW PROCEDURE STATUS WHERE Db = 'targetdb';

Notes

• Amazon RDS does not grant SUPER privilege — Fix 2 (stripping DEFINER clauses) or Fix 5 is required for RDS imports.
• After setting log_bin_trust_function_creators = 1, reset it: SET GLOBAL log_bin_trust_function_creators = 0;

MySQL 5.6 may fail to start after an upgrade from an earlier version due to InnoDB file format incompatibilities, particularly when the existing ibdata1 uses the Antelope format. This guide covers diagnostic steps and multiple recovery options.

Prerequisites

• Root or sudo access
• Access to the MySQL error log

Diagnosis

sudo tail -50 /var/log/mysql/error.log
# Look for: "InnoDB: Error: corrupted page" or "InnoDB: Plugin initialization aborted"

Fix 1 — Clean Reinstall (Safest if no data to preserve)

sudo apt-get purge mysql-server-5.6
sudo mv /var/lib/mysql /var/lib/mysql_old
sudo apt-get install mysql-server-5.6
sudo systemctl start mysql

Fix 2 — Use a Clean Data Directory

sudo systemctl stop mysql
sudo mv /var/lib/mysql /var/lib/mysql_old
sudo mkdir -p /var/lib/mysql/mysql
sudo cp /var/lib/mysql_old/mysql/* /var/lib/mysql/mysql/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

Fix 3 — Force InnoDB Recovery

Add to /etc/mysql/my.cnf temporarily:

[mysqld]
innodb_force_recovery = 1

sudo systemctl start mysql
# If MySQL starts, immediately dump all data
mysqldump -u root -p --all-databases --single-transaction > /backup/emergency_dump.sql
# Remove innodb_force_recovery, then restore from dump

Fix 4 — Upgrade the InnoDB File Format

# After MySQL 5.6 is running (possibly with innodb_force_recovery)
# Set Barracuda format for new tables
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;
# Then run OPTIMIZE TABLE on InnoDB tables to convert them

Verify

sudo systemctl status mysql
mysql -u root -p -e "SELECT VERSION(); SHOW ENGINES;"

Notes

• Increase innodb_force_recovery from 1 to a maximum of 6 one step at a time if lower values do not allow startup. Higher values progressively disable more InnoDB operations.
• Never use innodb_force_recovery in production without immediately planning to dump and restore data on a clean instance.

MySQL 5.6 is not in the default CentOS repositories. This guide covers installing it from the MySQL Community repository on CentOS 6.x or 7.x.

Prerequisites

• CentOS 6.x or 7.x (64-bit)
• Root or sudo access
• No existing MySQL installation (or it has been removed)

Steps

1. Install the MySQL Community repository:

# For CentOS 6
sudo rpm -Uvh http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

# For CentOS 7
sudo rpm -Uvh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2. Verify the repository is configured:

yum repolist | grep mysql

3. Install MySQL 5.6 server:

sudo yum install mysql-community-server

4. Start MySQL and enable it on boot:

# CentOS 7 (systemd)
sudo systemctl start mysqld
sudo systemctl enable mysqld

# CentOS 6 (SysVinit)
sudo service mysqld start
sudo chkconfig mysqld on

5. Get the temporary root password (MySQL 5.6 generates one at startup):

sudo grep 'temporary password' /var/log/mysqld.log

6. Secure the installation:

sudo mysql_secure_installation

Verify

mysql --version
mysql -u root -p -e "SELECT VERSION();"
sudo systemctl status mysqld

Notes

• MySQL 5.6 reached end-of-life in February 2021 and no longer receives security updates. Use MySQL 5.7 or 8.0 for new installations.
• If MariaDB is already installed (default on CentOS 7), remove it first: sudo yum remove mariadb mariadb-server

This error occurs when creating an index on a column that exceeds the maximum index key length of 767 bytes in MySQL 5.6 and earlier (or when innodb_large_prefix is disabled in MySQL 5.7). It commonly happens when importing a database created on MySQL 5.7 into MySQL 5.6, or when using utf8mb4 character set with long columns.

Understanding the Limit

• InnoDB default max index key length: 767 bytes
• utf8mb4 uses up to 4 bytes per character, so a VARCHAR(192) column uses 768 bytes — exceeding the limit
• utf8 uses up to 3 bytes per character, so a VARCHAR(255) uses 765 bytes — within the limit

Fix 1 — Enable innodb_large_prefix (MySQL 5.6/5.7)

[mysqld]
innodb_large_prefix  = 1
innodb_file_format   = Barracuda
innodb_file_per_table = 1

sudo systemctl restart mysql

Then ensure the table uses ROW_FORMAT=DYNAMIC:

ALTER TABLE mytable ROW_FORMAT=DYNAMIC;

Fix 2 — Reduce the Column Length in the Table Definition

-- Instead of VARCHAR(255) with utf8mb4 (1020 bytes):
ALTER TABLE mytable MODIFY COLUMN mycolumn VARCHAR(191) CHARACTER SET utf8mb4;

191 characters × 4 bytes = 764 bytes, which is within the 767-byte limit.

Fix 3 — Use a Prefix Index

-- Index only the first N characters of the column
CREATE INDEX idx_mycolumn ON mytable (mycolumn(191));

Fix 4 — Use utf8 Instead of utf8mb4

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Verify

SHOW CREATE TABLE mytable\G
-- Check ROW_FORMAT and character set
SHOW VARIABLES LIKE 'innodb_large_prefix';

Notes

• MySQL 8.0 raises the default index key length to 3072 bytes — this error is much less common on MySQL 8.0.
• If importing a dump from MySQL 5.7 into MySQL 5.6, apply Fix 1 before importing to avoid multiple key errors.

MySQL can authenticate users without a manually entered password in two secure ways: via the auth_socket (or unix_socket) plugin that authenticates based on the OS user, and via mysql_config_editor which stores encrypted credentials. This guide covers both methods.

Method 1 — auth_socket Plugin (OS-Based Authentication)

The auth_socket plugin allows a system user to log in to MySQL as the matching MySQL user without a password, authenticated by the OS.

Configure a MySQL user to use auth_socket:

-- MySQL 5.7 / MariaDB (unix_socket plugin)
CREATE USER 'deploy'@'localhost' IDENTIFIED WITH unix_socket;
GRANT ALL PRIVILEGES ON deploydb.* TO 'deploy'@'localhost';

-- MySQL 8.0 (auth_socket plugin)
CREATE USER 'deploy'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON deploydb.* TO 'deploy'@'localhost';

Connect as the matching OS user:

# Log in as the 'deploy' OS user, then:
mysql -u deploy deploydb
# No password required — authenticated by OS user identity

Method 2 — mysql_config_editor (Encrypted Credentials File)

Store credentials once:

mysql_config_editor set --login-path=myapp --host=localhost --user=appuser --password
# Enter password at prompt — stored encrypted in ~/.mylogin.cnf

Connect without a password prompt:

mysql --login-path=myapp myappdb
mysqldump --login-path=myapp myappdb > backup.sql

Verify

# View stored login paths (password is always masked)
mysql_config_editor print --all

# Test connection
mysql --login-path=myapp -e "SELECT CURRENT_USER();"

Notes

• auth_socket is the most secure option for local connections — there is no password to steal. It requires the OS user and MySQL user names to match.
• mysql_config_editor works for both local and remote connections and supports any MySQL client program.
• Avoid storing passwords in shell scripts, environment variables, or command-line arguments — they are visible in ps aux output and shell history.

When running apt-get update after adding the MySQL APT repository, you may see an error indicating that the repository signature cannot be verified because the GPG key has changed or expired. MySQL periodically rotates its signing keys, which causes this error on existing systems.

Symptoms

W: An error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://repo.mysql.com/apt/debian buster InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 467B942D3A79BD29

Prerequisites

• Debian or Ubuntu with the MySQL APT repository configured
• Root or sudo access

Fix 1 — Import the New GPG Key Directly

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 467B942D3A79BD29

Replace the key ID with the one shown in your error message.

Fix 2 — Download and Install the Key from MySQL

wget -qO - https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 | sudo apt-key add -
sudo apt-get update

Fix 3 — Reinstall the mysql-apt-config Package

wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
sudo apt-get update

Fix 4 — Use the Signed-By Method (Recommended for Modern Systems)

# Download the key to the trusted keyring directory
wget -qO /usr/share/keyrings/mysql.gpg https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

# Update the sources.list entry to reference the key
# Edit /etc/apt/sources.list.d/mysql.list and add signed-by parameter:
# deb [signed-by=/usr/share/keyrings/mysql.gpg] https://repo.mysql.com/apt/debian buster mysql-8.0

sudo apt-get update

Verify

sudo apt-get update
# Should show: Hit: ... repo.mysql.com ... InRelease (no warnings)

Notes

• MySQL maintains multiple GPG keys for different release years. Check https://repo.mysql.com/ for the current key files.
• The apt-key command is deprecated in Debian 12+ and Ubuntu 22.04+. Use the signed-by approach (Fix 4) on newer systems.

This guide covers the most common MySQL restore scenario: copying a MySQL data directory backup to a fresh server. This approach is intended for restoring a newly installed MySQL instance with no existing user databases.

Prerequisites

• A fresh MySQL installation with no manually created databases
• SSH access to the remote server
• The backup archive (a compressed copy of /var/lib/mysql)

Steps

1. Transfer the backup archive to the remote server (or unzip locally and rsync):

rsync -avz /local/mysql_backup.tar.gz user@remote:/tmp/

2. Stop MySQL on the remote server:

sudo systemctl stop mysql

3. Back up the existing data directory on the remote server:

sudo cp -r /var/lib/mysql /var/lib/mysql_backup

4. Remove the InnoDB redo log directory to prevent startup conflicts:

sudo rm -rf /var/lib/mysql/#innodb_redo/*

5. Extract and copy the backup over the data directory:

cd /tmp
tar -xzf mysql_backup.tar.gz
sudo rsync -avz mysql/ /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql

6. Start MySQL:

sudo systemctl start mysql

Verify

sudo systemctl status mysql
mysql -u root -p -e "SHOW DATABASES;"

Notes

• This method requires the source and destination MySQL versions to be the same or compatible. For cross-version restores, use a SQL dump (mysqldump) instead.
• If MySQL fails to start after the restore, check the error log: sudo tail -50 /var/log/mysql/error.log
• If you have existing user databases on the destination server, this method will overwrite them. Always back up the destination data directory first.

Importing a mysqldump from a self-managed MySQL 5.7 server into Amazon RDS requires pre-processing the dump file to strip statements that RDS does not allow (GTID-related headers, DEFINER clauses, and binary log control statements). This guide covers the required steps.

Prerequisites

• A dump file created with mysqldump from MySQL 5.7
• An Amazon RDS MySQL or MariaDB instance
• MySQL client installed on the machine running the import
• Network access from your machine to the RDS endpoint (port 3306)

Steps — Pre-Process the Dump File

1. Remove GTID-related statements (RDS does not allow setting GTID_PURGED):

sed -i '/GTID_PURGED/d' dump.sql
sed -i '/SQL_LOG_BIN/d' dump.sql
sed -i '/MYSQLDUMP_TEMP_LOG_BIN/d' dump.sql

2. Remove DEFINER clauses (RDS users lack SUPER privilege):

sed -i 's/DEFINER=`[^`]*`@`[^`]*`//g' dump.sql

3. If renaming the database during import, replace the old name:

sed -i 's/old_database_name/new_database_name/g' dump.sql

Steps — Configure RDS for Function Import

If the dump contains stored functions or triggers, set this parameter in the RDS Parameter Group (or at runtime):

-- Enable on RDS (requires admin user)
SET GLOBAL log_bin_trust_function_creators = 1;

To verify it is set:

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

Steps — Import the Dump

mysql -h your-rds-endpoint.rds.amazonaws.com   -u admin -p   target_database < dump.sql

Verify

mysql -h your-rds-endpoint.rds.amazonaws.com -u admin -p target_database   -e "SHOW TABLES; SELECT COUNT(*) FROM important_table;"

Notes

• For large dumps (>1 GB), consider using AWS Database Migration Service (DMS) or loading to an EC2 instance in the same VPC for faster network transfer.
• Reset log_bin_trust_function_creators to 0 after the import is complete.
• If the import is very large, use nohup mysql ... < dump.sql & to prevent disconnection from killing the import process.

MySQL supports multiple authentication plugins per user. Changing a user's authentication plugin is required when migrating between MySQL versions, troubleshooting client compatibility, or switching between password-based and socket-based authentication.

Prerequisites

• MySQL 5.7, 8.0, or 8.4
• Root or admin access with ALTER USER privilege

Steps — Check Current Authentication Plugin

SELECT user, host, plugin FROM mysql.user WHERE user = 'username';

Steps — Change Authentication Plugin

Switch to mysql_native_password (for legacy client compatibility):

ALTER USER 'username'@'localhost'
  IDENTIFIED WITH mysql_native_password BY 'StrongPassword';
FLUSH PRIVILEGES;

Switch to caching_sha2_password (recommended for MySQL 8+):

ALTER USER 'username'@'localhost'
  IDENTIFIED WITH caching_sha2_password BY 'StrongPassword';
FLUSH PRIVILEGES;

Switch to auth_socket (OS-based, passwordless, local connections only):

-- MySQL 8.0
ALTER USER 'username'@'localhost' IDENTIFIED WITH auth_socket;

-- MariaDB / MySQL 5.7
ALTER USER 'username'@'localhost' IDENTIFIED WITH unix_socket;
FLUSH PRIVILEGES;

Switch to sha256_password:

ALTER USER 'username'@'localhost'
  IDENTIFIED WITH sha256_password BY 'StrongPassword';
FLUSH PRIVILEGES;

Verify

SELECT user, host, plugin, authentication_string FROM mysql.user WHERE user = 'username';

-- Test login with the new plugin
mysql -u username -p -e "SELECT CURRENT_USER();"

Notes

• In MySQL 8.4, mysql_native_password is disabled by default. To re-enable it: add mysql_native_password=ON to the [mysqld] section of my.cnf.
• After changing a user's plugin, update the application's connection string if the driver previously cached the authentication mechanism.
• The auth_socket and unix_socket plugins only work for connections via the Unix domain socket from a matching OS user — remote TCP connections cannot use them.

MySQL table optimization reclaims disk space, defragments data files, and updates table statistics for the query optimizer. This guide covers table-level optimization commands and key configuration tuning for MariaDB/MySQL servers managed with WHM/cPanel or standalone.

Prerequisites

• MySQL or MariaDB server
• Root or admin access

Steps — Optimize Tables

Optimize a single table:

OPTIMIZE TABLE tablename;

Optimize all tables in a database:

mysqlcheck -u root -p --optimize myappdb

Optimize all tables on the server:

mysqlcheck -u root -p --optimize --all-databases

Analyze tables to update index statistics:

ANALYZE TABLE tablename;
mysqlcheck -u root -p --analyze --all-databases

Steps — Key Configuration Tuning

Edit /etc/mysql/my.cnf (or on WHM: /etc/mysql/my.cnf):

[mysqld]
# Allow larger queries (e.g., for large blobs or bulk inserts)
max_allowed_packet = 1073741824   # 1 GB

# Increase concurrent connections
max_connections = 1000

# InnoDB buffer pool — set to 70-80% of available RAM
innodb_buffer_pool_size = 4294967296   # 4 GB

Restart MySQL after changing configuration:

sudo systemctl restart mysql

Steps — Check Table Status

-- Check for fragmented tables
SELECT table_schema, table_name, data_free
FROM information_schema.tables
WHERE data_free > 0
  AND table_schema NOT IN ('information_schema','performance_schema','mysql','sys')
ORDER BY data_free DESC;

Verify

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

Notes

• OPTIMIZE TABLE on InnoDB tables rebuilds the table and index, which can lock the table for a long time on large datasets. Use pt-online-schema-change from Percona Toolkit for online optimization without locking.
• A high ratio of Innodb_buffer_pool_reads to Innodb_buffer_pool_read_requests indicates the buffer pool is too small.
• Run perl mysqltuner.pl to get automated tuning recommendations based on current runtime statistics.