This guide provides a comprehensive collection of common and advanced MySQL commands. It serves as a quick reference for managing your MySQL databases effectively. Whether you’re extracting data, importing information, managing users, or performing administrative tasks, these commands are designed to be robust and reliable.

Extracting Data from MySQL
Importing Data into MySQL
MySQL Passwords
MySQL Administration
MySQL Datadir and Tmpdir

Extracting data from MySQL

This section covers commands for exporting data from your MySQL server, including full database dumps, specific tables, and user privileges.

Dump all databases

This command will dump all databases, excluding system databases like information_schema, performance_schema, test, cphulkd, and eximstats. Each database will be saved into its own .sql file. This command includes routines (stored procedures and functions).


echo "SHOW DATABASES;" | mysql -Bs | grep -v '^(information_schema|performance_schema|test|cphulkd|eximstats)$' | while read i ; do echo "Dumping $i" ; mysqldump --single-transaction --routines "$i" > "$i.sql" ; done

Note: The --single-transaction option is suitable for InnoDB tables to ensure data consistency without locking tables. For MyISAM tables, consider a different approach or schedule during low-traffic periods.

Dump and compress all databases

Similar to the above, but this command also compresses each database dump into a .sql.gz file to save space.


echo "SHOW DATABASES;" | mysql -Bs | grep -v '^information_schema$' | while read i ; do echo "Dumping $i" ; mysqldump --single-transaction "$i" | gzip -c > "$i.sql.gz" ; done

Dump all tables from all non-system databases

This command iterates through all non-system databases and dumps each table into a separate database_name.table_name.sql file.


echo "SELECT table_schema, table_name FROM information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema');" | mysql -Bs | while read db table; do echo "Dumping $db $table" ; mysqldump "$db" "$table" > "$db.$table.sql" ; done

Dump all tables (alternative for MySQL system instability)

If information_schema is unavailable or MySQL is unstable, this command attempts to dump tables by finding .frm files. Use with caution.


find /var/lib/mysql/ -type f -name "*.frm" | awk -F'/' '$(NF-1) !~ /^(mysql|performance_schema|information_schema)$/ {gsub(/.frm$/,"",$NF); print $(NF-1), $NF}' | while read db table; do sleep 2; echo "Dumping $db $table" >> ./export.log ; mysqldump "$db" "$table" > "$db.$table.sql" 2>>./export.log ; done

Note: This method relies on the filesystem structure and might not be universally reliable. It logs its actions and errors to ./export.log.

Dump all tables within one specific database

Replace [DATABASE_NAME] with the actual name of your database. Each table will be dumped into a [DATABASE_NAME].table_name.sql file.


db="[DATABASE_NAME]"; for table in $(mysql -Ne "USE \`$db\`; SHOW TABLES;"); do echo "Dumping $table"; mysqldump "\$db" "\$table" > "\$db.\$table.sql"; done

Dump all tables – primary key columns only (schema and primary key data)

This script dumps the schema for all databases and then extracts only the primary key column(s) data into CSV files. Set dumpdest to your desired output directory and host to your MySQL host.


# Set these variables in your bash shell before running the mysqldump command
# bash&gt; dumpdest=/path/to/your/dump/directory
# bash&gt; host=your_mysql_host

mysqldump -h $host --no-data -A --single-transaction | awk 'BEGIN {table=db=""} /^CREATE DATABASE/ {db=$7; gsub(/`/,"",db);} /^CREATE TABLE/ {table=$3; gsub(/`/,"",table);} $1 ~ /PRIMARY/ {gsub(/\(|(\)\,)|\)/,"",$3); print db, table, "SELECT " $3 " FROM \`" db "\`.\`" table "\`;"}' | while read -r db table line; do mysql -h $host -Bse "$line" >> "$dumpdest/$db.$table.$host.csv"; done

Important: Ensure the dumpdest directory exists and is writable.

Extract SQL for a single database from a full dump file

If you have a dump file containing multiple databases (e.g., fulldump.sql), you can extract the SQL for a specific database.

Replace singledb with the name of the database you want to extract.
Replace fulldump.sql with the name of your full dump file.
Replace singledb.sql with the desired name for the new file containing only the specified database.


sed -n '/^-- Current Database: \`singledb\`/,/^-- Current Database: \`/p' fulldump.sql > singledb.sql

Note: This command works well if the dump file was created with mysqldump comments indicating database context. The final /^-- Current Database: \`/p part might need adjustment if `singledb` is the last database in the dump. To ensure it captures up to the end of `singledb`’s data, you might need a more robust `sed` expression or use tools like `csplit` if available, especially if the “next” `Current Database` comment doesn’t exist.

Extract SQL for a single table from a database dump file

To extract a single table’s structure and data from a database dump file.

Replace singletable with the name of the table you want to extract.
Replace dbdump.sql with the name of your database dump file.
Replace singletable.sql with the desired name for the new file containing only the specified table.


sed -n '/^-- Table structure for table \`singletable\`/,/^-- Table structure for table/p' dbdump.sql > singletable.sql

Note: Similar to extracting a single database, this relies on mysqldump‘s comment format. If `singletable` is the last table dumped, the second pattern might not work as expected to delimit the end. You might need to adjust the end pattern or manually edit the resulting file.

Dump MySQL user privileges (grants)

This command dumps all user grant statements (privileges) from the mysql database. The --extended-insert=FALSE option makes each grant statement a separate line, which can be easier for review or selective restoration.


mysqldump --extended-insert=FALSE mysql user > user_grants.sql
# For a more complete dump of privileges including those on db, tables, columns, and routines:
mysqldump --extended-insert=FALSE --system=users,role_edges,default_roles,global_grants mysql > all_privileges.sql
# A simpler and often sufficient method for just user accounts and their global privileges:
mysqldump mysql user > mysql_user_table.sql
# To get all grants in SQL format (recommended):
# First, ensure your MySQL client user has appropriate permissions.
# Then run this within the mysql client:
# SHOW GRANTS FOR 'user1'@'localhost';
# SHOW GRANTS FOR 'user2'@'%';
# (Repeat for all users)
# Or, to script it (advanced):
# mysql -BNe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', User, '\'@\'', Host, '\';') FROM mysql.user;" | mysql -Bs | sed 's/$/;/' > all_grants.sql

For a comprehensive backup of grants, use `pt-show-grants` from Percona Toolkit if available, or ensure you capture all grant types. The provided `mysqldump –extended-insert=FALSE mysql > mysql.sql` is a broad approach that gets the `mysql` database, including the grant tables. For just grants, `FLUSH PRIVILEGES;` followed by selectively dumping tables like `mysql.user`, `mysql.db`, `mysql.tables_priv`, `mysql.columns_priv`, and `mysql.procs_priv` might be more targeted, or use `SHOW GRANTS` for each user.

MySQL database dump script for backups (cron job example)

This is an example script for setting up automated daily backups using cron.

Backups run daily at 4:02 AM.
Dumps are stored in /backup/mysql_backup/.
Database dumps are compressed.
Important: Change email_address@domain.com to your desired notification email.
Place this script in /etc/cron.d/mysql_backups.


#!/bin/bash
MAILTO="email_address@domain.com"

# Minute Hour DayOfMonth Month DayOfWeek User Command
2 4 * * * root mkdir -p /backup/mysql_backup/ ; /bin/echo "SHOW DATABASES;" | /usr/bin/mysql -Bs | /bin/egrep -v '^(information_schema|performance_schema|mysql|test|cphulkd|eximstats|roundcube|modsec|horde|leechprotect|logaholicDB.*)$' | while read db; do /usr/bin/mysqldump --single-transaction "$db" | gzip > "/backup/mysql_backup/$db.sql.gz"; done

Considerations:

Ensure the user running cron (root in this example) has MySQL access or configure .my.cnf for passwordless login for this specific task.
The egrep pattern excludes several common system or application-specific databases. Adjust this list to your needs.
This script does not include backup rotation. Implement a separate mechanism for managing old backups to prevent disk space issues.

Importing data into MySQL

This section details commands for importing data into your MySQL server, from individual tables to full database restores.

Create databases for each SQL dump in a folder

If you have a folder containing .sql files where each file is a dump of a database (and the filename matches the database name, e.g., mydatabase.sql), this command will create the necessary databases before importing.


find . -maxdepth 1 -type f -name "*.sql" -exec bash -c 'DBNAME=$(basename "{}" .sql); mysqladmin create "$DBNAME"' \;
# Alternative using awk if filenames are strictly dbname.sql:
# find . -maxdepth 1 -type f -name "*.sql" | awk -F'.' '{gsub(/^.*\//, "", $(NF-1)); print $(NF-1)}' | sort | uniq | xargs -L1 mysqladmin create

Note: The first command is generally safer with varied filenames. Ensure your MySQL user has CREATE DATABASE privileges.

Import all database dumps in a folder

This command imports all .sql files found in the current directory into databases with corresponding names (e.g., dbname.sql imports into database dbname).


find . -maxdepth 1 -type f -name "*.sql" | while read file; do dbname=$(basename "$file" .sql); echo "Importing $dbname"; mysql "$dbname" < "$file"; done

Decompress and import all Gzipped database dumps in a folder

Similar to the above, but for compressed .sql.gz files.


find . -maxdepth 1 -type f -name "*.sql.gz" | while read file; do dbname=$(basename "$file" .sql.gz); echo "Importing $dbname"; gunzip -c "$file" | mysql "$dbname"; done

Import a single database from a full dump (containing multiple databases)

If you have a full dump file (e.g., fulldump.sql) that contains multiple databases, you can import just one specific database from it.

Replace database_name with the name of the database you wish to import.
Replace fulldump.sql with the name of your comprehensive dump file.


mysql --one-database database_name < fulldump.sql

Important: The target database (database_name) must exist on the server before running this command. If it doesn’t, create it first: mysqladmin create database_name.

Import all table dumps in a folder (Structure: database.table.sql)

If you have table dumps named in the format database_name.table_name.sql, this command imports each table into its respective database.


find . -maxdepth 1 -type f -name "*.sql" | while read file; do
    filename=$(basename "$file")
    dbname=$(echo "$filename" | awk -F'.' '{print $(NF-2)}')
    tablename=$(echo "$filename" | awk -F'.' '{print $(NF-1)}') # This might be problematic if filename is just db.table.sql
    # A more robust way to extract db and table if format is strictly db.table.sql
    # Requires files to be named like "databasename.tablename.sql"
    if [[ "$filename" =~ ^([^\.]+)\.([^\.]+)\.sql$ ]]; then
        dbname="${BASH_REMATCH[1]}"
        # tablename="${BASH_REMATCH[2]}" # Not used in the mysql command directly
        echo "Importing $filename into database $dbname";
        mysql "$dbname" < "$file";
    else
        echo "Skipping $filename: Does not match expected format database.table.sql"
    fi
done

The original command: find . -maxdepth 1 -type f -name "*.sql"|awk -F'.' '{gsub(/^\//, "", $(NF-2)); print $(NF-2) " " $(NF-1)}'|while read db table; do echo "Importing $db $table"; mysql $db < $db.$table.sql; done assumes a specific awk parsing that can be fragile. The updated loop is more explicit.

Import a single table from a full database dump

Warning

Always back up your target database before performing partial imports to prevent data loss.

This two-step process first extracts the specific table’s SQL from a full database dump and then imports it.

Extract the table SQL:
- Replace tablename with the name of the table you want to import.Replace dumpfile.sql with the name of your full database dump file.Replace table.sql with the desired name for the new file containing only the specified table’s SQL.
sed -n '/-- Table structure for table `'$tablename'`/,/-- Table/{ /^--.*$/d;p }' $dumpfile.sql > $table.sql mysql $database < $table.sql
Import the extracted table SQL:
- Replace database with the name of the target database.
- Replace table.sql with the file created in the previous step.
mysql "$database" < "$table_sql_file"

Important notes for table extraction/import:

The sed command relies heavily on the specific comment formatting produced by mysqldump. Variations in mysqldump versions or options could affect its accuracy.
Ensure the table structure (CREATE TABLE statement) and the data (INSERT statements) are both correctly extracted. The UNLOCK TABLES; delimiter is common for mysqldump.
Foreign key constraints can complicate single-table imports. You might need to temporarily disable foreign key checks:
- SET foreign_key_checks = 0;
- -- Your import command here --
- SET foreign_key_checks = 1;

MySQL passwords

Managing MySQL user passwords, especially with varying hash formats across versions.

Note: In commands shown as mysql> SELECT ..., the mysql> indicates the MySQL command-line client prompt. You should run the SQL query that follows it within the MySQL client.

Old password format vs. new password format

MySQL 5.6 and later versions deprecated the old 16-character password hashing format in favor of a more secure 41-character format (prefixed with an asterisk). Importing users with old-style password hashes into newer MySQL versions will likely fail. You may need to locate passwords in application configuration files and reset them using the new format.

Find users with old-style (16-character) passwords

From the MySQL client:


SELECT Host, User, Password AS Hash FROM mysql.user WHERE LENGTH(Password) = 16 AND Password REGEXP '^[0-9a-fA-F]{16}$' ORDER BY User, Host;

Or, from the Bash shell:


mysql -Bse "SELECT User, Host, LENGTH(Password) FROM mysql.user WHERE LENGTH(Password) = 16 AND Password REGEXP '^[0-9a-fA-F]{16}';" | awk '{print $1 "@" $2}' | sort | uniq

Find users with new-style (41-character) passwords

From the MySQL client:


SELECT Host, User, Password AS Hash FROM mysql.user WHERE Password REGEXP '^\\*[0-9A-F]{40}$' ORDER BY User, Host;

Show password hash lengths for all users

This helps identify which format is being used. Old style is 16 characters; new style is 41 characters (including the leading asterisk).

From the MySQL client:


SELECT User, Host, LENGTH(Password) AS PasswordLength, Password FROM mysql.user;

Locate config files containing passwords (cPanel specific)

This script attempts to find configuration files within cPanel user directories that might contain plain-text passwords for database users who have old-style password hashes. Run as root from the SSH command line (not in the MySQL client).


mysql -Ns -e "SELECT User, Host FROM mysql.user WHERE LENGTH(Password) = 16 AND Password REGEXP '^[0-9a-fA-F]{16}';" | grep "_" | sort | uniq | while read user host; do
    dbuser_noprefix=$(echo "$user" | cut -d'_' -f2-) # Assumes cPanel user_dbuser format
    acct=$(echo "$user" | cut -d'_' -f1)
    if [ -d "/home/$acct/public_html" ]; then
        echo "Searching for user: $user in /home/$acct/public_html"
        # The grep command below is very broad. Be careful with its output.
        # Consider refining the grep pattern for more accuracy, e.g., searching for lines containing the dbuser and "password".
        config_files=$(grep -Rl "$user" "/home/$acct/public_html")
        if [ -n "$config_files" ]; then
            echo "Potential config files for $user ($acct):"
            echo "$config_files"
        else
            echo "No config files found for $user ($acct) in public_html."
        fi
    fi
done

Output: $db_user $/path/to/file. This script is specific to cPanel environments due to its directory structure assumptions (/home/$acct/public_html) and user naming conventions (cpaneluser_dbusername).

Set password in old format (not recommended for newer MySQL)

If you are on a newer MySQL version (that still supports OLD_PASSWORD(), though this is increasingly rare and insecure) and need to set a password in the old format for compatibility with a legacy application:

From the MySQL client (replace $USER, $HOST, and $PASSWORD with actual values):


SET PASSWORD FOR '$USER'@'$HOST' = OLD_PASSWORD('$PASSWORD');
FLUSH PRIVILEGES;

Or, by directly updating the user table (less safe, use SET PASSWORD if possible):


UPDATE mysql.user SET Password = OLD_PASSWORD('$PASSWORD') WHERE User = '$USER' AND Host = '$HOST';
FLUSH PRIVILEGES;

Warning: Using old password formats is a security risk. Update applications to support new password formats whenever possible.

Set password in new format

To set or update a user’s password to the new, secure format:

From the MySQL client (replace $USER, $HOST, and $PASSWORD with actual values):


-- For MySQL 5.7.5 and earlier, or if old_passwords might be enabled globally:
SET SESSION old_passwords = FALSE; 
SET PASSWORD FOR '$USER'@'$HOST' = PASSWORD('$PASSWORD');
FLUSH PRIVILEGES;

Or, by directly updating the user table (ensure old_passwords is not forcing old format):


SET SESSION old_passwords = FALSE;
UPDATE mysql.user SET Password = PASSWORD('$PASSWORD') WHERE User = '$USER' AND Host = '$HOST';
FLUSH PRIVILEGES;

For MySQL 5.7.6+ and MariaDB 10.1.20+, PASSWORD() function may behave differently or be deprecated for direct use in SET PASSWORD or UPDATE statements for password hashing if certain plugins are active. The recommended way is often ALTER USER:


ALTER USER '$USER'@'$HOST' IDENTIFIED BY '$PASSWORD';
FLUSH PRIVILEGES;

If you are encountering issues and need to ensure the mysql_native_password plugin is used (common for new format):


-- Ensure 'old_passwords' system variable is 0 (default for new installations)
-- SHOW VARIABLES LIKE 'old_passwords'; -- should show OFF or 0
UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('$PASSWORD') WHERE User = '$USER' AND Host = '$HOST';
-- Note: In MySQL 8+, the 'Password' column is renamed to 'authentication_string'. Use the correct column name for your MySQL version.
-- For MySQL 8+:
-- UPDATE mysql.user SET plugin='mysql_native_password', authentication_string=PASSWORD('$PASSWORD') WHERE User='$USER' AND Host='$HOST';
-- Or preferably:
-- ALTER USER '$USER'@'$HOST' IDENTIFIED WITH mysql_native_password BY '$PASSWORD';
FLUSH PRIVILEGES;

Common locations for system passwords (cPanel Environment)

If you need to find passwords for system-level MySQL users, they might be stored in these configuration files (primarily on cPanel servers):

MySQL root: /root/.my.cnf
modsec: /var/cpanel/modsec_db_pass (older cPanel) or check ModSecurity configuration.
lwmonitoring: /usr/local/lp/etc/exporters/my.cnf (Liquid Web monitoring)
eximstats: /var/cpanel/eximstatspass (older cPanel) or in Tweak Settings.
cphulkd: /var/cpanel/hulkdpass
roundcube: /usr/local/cpanel/base/3rdparty/roundcube/config/db.inc.php or /opt/cpanel/ea-roundcube/config/config.inc.php (newer EasyApache versions)

Always handle password files with extreme care and ensure proper permissions.

MySQL administration

Commands for server administration, upgrades, and troubleshooting.

Show percentage of used connections

Calculates the current percentage of used connections relative to the maximum allowed connections.

From the MySQL client:


SELECT 
    (SELECT COUNT(*) FROM information_schema.processlist) / 
    (SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE variable_name = 'MAX_CONNECTIONS') * 100 
    AS percentage_used_connections;

Start MySQL server without grant tables (password recovery/emergency)

This allows you to start MySQL without loading the grant tables, effectively bypassing password checks. Useful for password recovery or troubleshooting permission issues. Use with extreme caution and only when necessary.


# Stop MySQL if it's running
# systemctl stop mysql  (or /etc/init.d/mysql stop)

mysqld_safe --skip-grant-tables --skip-networking --socket=/tmp/secure.sock &

Once started, you can connect to MySQL locally without a password using the specified socket:


mysql --socket=/tmp/secure.sock
# mysqladmin --socket=/tmp/secure.sock ...
# mysql_upgrade --socket=/tmp/secure.sock ...

Important:

--skip-networking is crucial to prevent unauthorized network access while grant tables are disabled.
Remember to restart MySQL normally and secure it after you’ve completed your tasks.

Running mysql_upgrade

mysql_upgrade checks all tables in all databases for incompatibilities with the current MySQL server version and repairs them if necessary. It also updates the system tables. This should be run after any MySQL binary upgrade.

On cPanel Servers:

Follow these steps carefully. This assumes you’ve already upgraded the MySQL RPMs/packages.


/usr/local/cpanel/libexec/tailwatchd --stop
# For systemd systems (CentOS 7+, AlmaLinux 8+):
systemctl stop mysql
# For sysvinit systems (CentOS 6):
# /etc/init.d/mysql stop

# Start MySQL without grants for the upgrade process
mysqld_safe --skip-grant-tables --skip-networking --socket=/tmp/mysql_upgrade.sock &
sleep 10 # Wait for the server to start

# Run mysql_upgrade using the temporary socket
mysql_upgrade --socket=/tmp/mysql_upgrade.sock

# Stop the temporarily started MySQL
mysqladmin --socket=/tmp/mysql_upgrade.sock shutdown
sleep 5 # Wait for it to shut down

# Restart MySQL normally
# For systemd systems:
systemctl start mysql
# For sysvinit systems:
# /etc/init.d/mysql start

/usr/local/cpanel/libexec/tailwatchd --start

On core managed servers (Non-cPanel):

The process is similar, omitting cPanel-specific services.


# For systemd systems (CentOS 7+, AlmaLinux 8+):
systemctl stop mysql
# For sysvinit systems (CentOS 6):
# /etc/init.d/mysql stop

# Start MySQL without grants for the upgrade process
mysqld_safe --skip-grant-tables --skip-networking --socket=/tmp/mysql_upgrade.sock &
sleep 10 # Wait for the server to start

# Run mysql_upgrade using the temporary socket
mysql_upgrade --socket=/tmp/mysql_upgrade.sock

# Stop the temporarily started MySQL
mysqladmin --socket=/tmp/mysql_upgrade.sock shutdown
sleep 5 # Wait for it to shut down

# Restart MySQL normally
# For systemd systems:
systemctl start mysql
# For sysvinit systems:
# /etc/init.d/mysql start

MySQL upgrade failure fix

If mysql_upgrade fails, you might need to perform some steps manually. This is an advanced troubleshooting procedure.

Attempt an initial check and repair: Start MySQL normally if possible. If not, you may need to start it with --skip-grant-tables (and --skip-networking) as shown above, but be cautious. mysqlcheck -A --all-databases --check-upgrade --auto-repair Or, if using the secure socket method: mysqlcheck --socket=/tmp/mysql_upgrade.sock -A --all-databases --check-upgrade --auto-repair
Examine mysql_upgrade script contents: mysql_upgrade is often a shell script that executes SQL commands or calls other binaries. You can inspect its contents to understand what it’s trying to do. less $(which mysql_upgrade) # Look for SQL files it might try to execute, or specific mysqlcheck commands. The strings $(which mysql_upgrade) command can also show embedded SQL or paths, but less is usually more readable for scripts.
Manual Intervention: Based on the errors from mysql_upgrade and your examination of the script, you may need to run specific SQL commands or table repair operations manually. This can involve creating or altering system tables in the mysql database. Each case can be highly specific. Consult MySQL error logs and official documentation for the version you are upgrading to.

Reset MySQL root password

On cPanel servers:

Stop services and start MySQL in safe mode: /usr/local/cpanel/libexec/tailwatchd --stop # For systemd systems (CentOS 7+, AlmaLinux 8+): systemctl stop mysql # For sysvinit systems (CentOS 6): # /etc/init.d/mysql stop mysqld_safe --skip-grant-tables --skip-networking --socket=/tmp/secure.sock & sleep 5 # Wait for mysqld_safe to start the server read -s -p "Enter New Root Password: " NEW_ROOT_PASSWORD echo # Add a newline for clarity
Set the new password: Connect to MySQL using the secure socket and update the root password. The exact SQL command can vary slightly by MySQL/MariaDB version, especially the password column name (Password vs authentication_string) and hashing function. For MySQL 5.6 / MariaDB 10.0 and similar (using `Password` column and `PASSWORD()` function): mysql --socket=/tmp/secure.sock -e "UPDATE mysql.user SET Password=PASSWORD('$NEW_ROOT_PASSWORD') WHERE User='root'; FLUSH PRIVILEGES;" For MySQL 5.7+ / MariaDB 10.1+ (using `authentication_string` and `PASSWORD()` or `mysql_native_password`): # Check your MySQL version. For MySQL 8, authentication_string is standard. # mysql --socket=/tmp/secure.sock -e "UPDATE mysql.user SET authentication_string=PASSWORD('$NEW_ROOT_PASSWORD') WHERE User='root'; FLUSH PRIVILEGES;" # Or, if using mysql_native_password plugin explicitly: mysql --socket=/tmp/secure.sock -e "UPDATE mysql.user SET plugin='mysql_native_password', authentication_string=PASSWORD('$NEW_ROOT_PASSWORD') WHERE User='root' AND Host='localhost'; FLUSH PRIVILEGES;" # For MySQL 8, it might be simpler via ALTER USER if grants were not skipped entirely, but for --skip-grant-tables, UPDATE is common. # ALTER USER 'root'@'localhost' IDENTIFIED BY '$NEW_ROOT_PASSWORD'; Clear the password variable: unset NEW_ROOT_PASSWORD
Restart services: # Stop the temporarily started MySQL mysqladmin --socket=/tmp/secure.sock shutdown sleep 5 # For systemd systems: systemctl start mysql # For sysvinit systems: # /etc/init.d/mysql start /usr/local/cpanel/libexec/tailwatchd --start
Update cPanel’s root password cache (if applicable) and /root/.my.cnf: You may need to update the password in /root/.my.cnf for command-line client access. cPanel also has its own mechanisms for MySQL passwords; check WHM’s “SQL Services” > “Manage MySQL Root Password” or related features if the password isn’t syncing. Edit /root/.my.cnf and update the password= line under [client].

On servers without a control panel:

The process is similar but without cPanel-specific steps.

Stop MySQL and restart in safe mode: # For systemd systems (CentOS 7+, AlmaLinux 8+): systemctl stop mysql # For sysvinit systems (CentOS 6): # /etc/init.d/mysql stop mysqld_safe --skip-grant-tables --skip-networking --socket=/tmp/secure.sock & sleep 5 read -s -p "Enter New Root Password: " NEW_ROOT_PASSWORD echo
Set the new password (refer to cPanel examples for appropriate SQL based on version): # Example for MySQL 5.6 / MariaDB 10.0 mysql --socket=/tmp/secure.sock -e "UPDATE mysql.user SET Password=PASSWORD('$NEW_ROOT_PASSWORD') WHERE User='root'; FLUSH PRIVILEGES;" # Adjust SQL for newer versions as shown in the cPanel section. unset NEW_ROOT_PASSWORD
Restart MySQL normally: mysqladmin --socket=/tmp/secure.sock shutdown sleep 5 # For systemd systems: systemctl start mysql # For sysvinit systems: # /etc/init.d/mysql start
Update /root/.my.cnf: Manually edit /root/.my.cnf and update the password= line under [client] section with the new root password.

Enabling a slow query log

The slow query log helps identify queries that take a long time to execute, which can be bottlenecks for performance. Add the appropriate configuration to your MySQL configuration file (usually /etc/my.cnf or /etc/mysql/my.cnf, or a file under /etc/mysql/conf.d/) under the [mysqld] section.

MySQL 5.0 and below

log-slow-queries= /var/lib/mysql/slow.log
long_query_time = 1

MySQL 5.1 or 5.5

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

MySQL 5.6+

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

After enabling the slow query log in the my.cnf, restart mysql:

systemctl restart mysql

Or for Cento6 and older:

/etc/init.d/mysql restart

In order to test the slow query log you can run a simple query:

mysql> SELECT SLEEP(2);

Please be sure you are setting the slow query log intelligently for the server.

Kill all running MySQL queries (use with extreme caution)

This command attempts to kill all currently running queries except for the process running the command itself.

Warning

This can disrupt your applications and lead to data inconsistencies if queries are terminated mid-transaction. Understand the implications before using.


mysql -BNe "SHOW FULL PROCESSLIST;" | awk '$1 !~ /Id/ && $6 !~ /^(Sleep|Binlog Dump)$/ && $8 !~ /FULL PROCESSLIST/ {print "KILL "$1";"}' | mysql
# A simpler version that might be less selective:
# mysql -Bse 'show processlist;' | awk '{print $1}' | grep -v '^Id$' | xargs -L1 mysqladmin kill

The first, more refined command tries to avoid killing sleeping connections, binlog dump threads, or the `SHOW PROCESSLIST` command itself. The second is a more blunt instrument.

List all table sizes (including data, index, and freeable space)

This SQL query lists all tables across all databases, showing their schema, name, and calculated size in MiB.

From the MySQL client:


SELECT 
    TABLE_SCHEMA AS 'Database', 
    TABLE_NAME AS 'Table',  
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'Size (MiB)',
    ROUND((DATA_FREE / 1024 / 1024), 2) AS 'Freeable (MiB)'
FROM 
    information_schema.TABLES
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;

The original command CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MiB") AS Size uses DATA_FREE which is primarily relevant for MyISAM tables or tables that have had many deletions without an OPTIMIZE TABLE. For InnoDB, DATA_FREE might not represent actual freeable disk space in the same way. The revised query above shows total size and potentially freeable separately.

List all database sizes

This SQL query lists all databases and their total size in MB, ordered by size.

From the MySQL client:


SELECT 
    table_schema AS "Database Name", 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "DB Size (MB)" 
FROM 
    information_schema.tables  
GROUP BY 
    table_schema 
ORDER BY 
    SUM(data_length + index_length) DESC;

Restore grants on a cPanel server

This command iterates through all cPanel users and attempts to restore their MySQL grant privileges. This is useful if grants have been lost or corrupted.


\ls /var/cpanel/users/ | xargs -I{} /usr/local/cpanel/bin/restoregrants --cpuser={} --db=mysql --all

Note

This is a cPanel-specific utility (/usr/local/cpanel/bin/restoregrants).

MySQL datadir and tmpdir

Information on configuring MySQL’s temporary directory (tmpdir) and data directory (datadir).

MySQL tmpdir configuration

MySQL uses a temporary directory for operations like sorting large datasets, complex joins, or creating temporary tables. You can specify an alternative tmpdir in your MySQL configuration file (e.g., /etc/my.cnf) under the [mysqld] section.

Common Options:

tmpdir=/tmp (Default on many systems. Often smaller.)
tmpdir=/var/mysql.tmpdir (A common choice for a dedicated tmpdir on a partition with more space. Ensure this directory exists and has correct permissions.)
tmpdir=/dev/shm (Uses a RAM disk, which is very fast but limited by available RAM. Data is volatile.)

Creating and Securing a New tmpdir:

If you choose a custom location like /var/mysql.tmpdir:


mkdir /var/mysql.tmpdir
chmod 1777 /var/mysql.tmpdir # Or chmod 751 and chown mysql:mysql (see below)
chown mysql:mysql /var/mysql.tmpdir

Permissions can be tricky. chmod 1777 (like /tmp) allows any user to write, with the sticky bit preventing users from deleting files they don’t own. Alternatively, chmod 751 (or 700 for more restriction) with chown mysql:mysql makes it private to the MySQL user. Test thoroughly.

A MySQL restart is required for changes to tmpdir to take effect.

Permission errors with new datadir or tmpdir on systemd systems (e.g., CentOS 7+, AlmaLinux 8+)

If you relocate your MySQL datadir or tmpdir to a non-standard location, especially under /home, you might encounter permission errors like:


[ERROR] mysqld: Can't create/write to file '/home/mysqltmp/ib3JaTmd' (Errcode: 13 "Permission denied")
InnoDB: Error: unable to create temporary file; errno: 13

This is often due to Systemd’s ProtectHome=true setting for the MariaDB/MySQL service unit, which restricts access to /home, /root, and /run/user for security.

To resolve this (if you must use a location under /home, though /var/lib/ or other dedicated FS is preferred):

Create a Systemd override file: If an override file doesn’t already exist (e.g., in /etc/systemd/system/mariadb.service.d/ or /etc/systemd/system/mysql.service.d/), create one to disable ProtectHome. For MariaDB: mkdir -p /etc/systemd/system/mariadb.service.d/ echo -e '[Service]\nProtectHome=false' > /etc/systemd/system/mariadb.service.d/protecthome.conf For MySQL (adjust service name if different, e.g., mysqld.service): mkdir -p /etc/systemd/system/mysql.service.d/ echo -e '[Service]\nProtectHome=false' > /etc/systemd/system/mysql.service.d/protecthome.conf
Reload Systemd and restart MySQL: systemctl daemon-reload systemctl restart mariadb # or mysql

Create a new MySQL datadir

If you need to move your MySQL data directory (datadir) to a new location (e.g., a larger partition).

Warning

This is a critical operation. Always back up your data first!

Assume you are creating a new datadir at /var/lib/mysql.new. Adjust paths as necessary.

Stop MySQL: # For systemd: systemctl stop mysql # For sysvinit: # /etc/init.d/mysql stop
Create the new directory and set permissions:
- mkdir /var/lib/mysql.new
- chown -R mysql:mysql /var/lib/mysql.new/
- chmod 700 /var/lib/mysql.new/ # Or 750/751, 700 is most restrictive
Initialize the new datadir (For a completely fresh MySQL instance): If you are setting up a brand new, empty MySQL instance in this new datadir (NOT for migrating existing data): For MySQL 5.7.6+ / MariaDB 10.2+ (mysql_install_db is deprecated, use mysqld --initialize): mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql.new # Or --initialize to generate a random root password (check logs) For older MySQL versions (that use mysql_install_db): mysql_install_db --user=mysql --datadir=/var/lib/mysql.new/
Migrate Existing Data (The More Common Scenario): If you are moving an existing datadir:
1. Ensure MySQL is stopped.
2. Copy data from the old datadir to the new one. `rsync` is recommended: # Example: rsync -avP /var/lib/mysql/ /var/lib/mysql.new/ # Ensure the source path ends with a slash to copy contents. rsync -avP /path/to/old/datadir/ /var/lib/mysql.new/
3. Ensure permissions are correct on the new datadir after copying: chown -R mysql:mysql /var/lib/mysql.new/ chmod 700 /var/lib/mysql.new/ # Or as appropriate
Update MySQL Configuration: Edit your MySQL configuration file (e.g., /etc/my.cnf) and change the datadir setting:

If you use AppArmor (common on Ubuntu/Debian) or SELinux (common on RHEL/CentOS/AlmaLinux), you must update their profiles/contexts to allow MySQL to access the new datadir path. This is a common reason for startup failures after moving the datadir.

SELinux example:


# semanage fcontext -a -t mysqld_db_t "/var/lib/mysql.new(/.*)?"
# restorecon -Rv /var/lib/mysql.new

AppArmor example: Edit /etc/apparmor.d/usr.sbin.mysqld (or similar) to include the new path, then reload AppArmor profiles.Start MySQL:


# For systemd:
systemctl start mysql # or mariadb
# For sysvinit:
# /etc/init.d/mysql start

Check MySQL error logs for any issues.

Note on MySQL 5.6 mysql_install_db Bug:

The original document mentioned a bug in MySQL 5.6 where mysql_install_db might not create certain system tables (e.g., gtid_executed, innodb_index_stats, innodb_table_stats, slave_master_info, slave_relay_log_info, slave_worker_info). If you are initializing a fresh MySQL 5.6 datadir and encounter issues, you might need to manually create these tables. The referenced link http://perboner.com/source/five-tables.sql is no longer active. However, the general solution would be to obtain the CREATE TABLE statements for these system tables from a working MySQL 5.6 instance or its documentation and execute them. After running mysql_install_db, you would connect to the MySQL server and run the necessary SQL. This is less of a concern with newer MySQL versions that use mysqld --initialize.

This document provides a set of common MySQL commands for various operations. Always ensure you understand the implications of a command before running it on a production system. Backups are strongly recommended before performing major data manipulation or administrative tasks. For specific issues or advanced scenarios, consult the official MySQL/MariaDB documentation or seek assistance from qualified support personnel.

Was this article helpful?

Thank you for your input.

Thank you for your feedback.

MySQL Commands

Table of Contents

Extracting data from MySQL

Dump all databases

Dump and compress all databases

Dump all tables from all non-system databases

Dump all tables (alternative for MySQL system instability)

Dump all tables within one specific database

Dump all tables – primary key columns only (schema and primary key data)

Extract SQL for a single database from a full dump file

Extract SQL for a single table from a database dump file

Dump MySQL user privileges (grants)

MySQL database dump script for backups (cron job example)

Importing data into MySQL

Create databases for each SQL dump in a folder

Import all database dumps in a folder

Decompress and import all Gzipped database dumps in a folder

Import a single database from a full dump (containing multiple databases)

Import all table dumps in a folder (Structure: database.table.sql)

Import a single table from a full database dump

MySQL passwords

Old password format vs. new password format

Find users with old-style (16-character) passwords

Find users with new-style (41-character) passwords

Show password hash lengths for all users

Locate config files containing passwords (cPanel specific)

Set password in old format (not recommended for newer MySQL)

Set password in new format

Common locations for system passwords (cPanel Environment)

MySQL administration

Show percentage of used connections

Start MySQL server without grant tables (password recovery/emergency)

Running mysql_upgrade

On cPanel Servers:

On core managed servers (Non-cPanel):

MySQL upgrade failure fix

Reset MySQL root password

On cPanel servers:

On servers without a control panel:

Enabling a slow query log

Kill all running MySQL queries (use with extreme caution)

List all table sizes (including data, index, and freeable space)

List all database sizes

Restore grants on a cPanel server

MySQL datadir and tmpdir

MySQL tmpdir configuration

Permission errors with new datadir or tmpdir on systemd systems (e.g., CentOS 7+, AlmaLinux 8+)

Create a new MySQL datadir