Essential MySQL Server Management Commands

Updated for 2025

If you’re running a MySQL server on Ubuntu, especially for PHP or Laravel applications, getting comfortable with server-level management is critical. This guide covers the most useful MySQL commands for system administrators and developers who need to manage databases, users, backups, and service status from the command line.

Whether you’re deploying a Laravel app, creating automated backups, or restoring a production database, these are the commands you’ll use again and again.

Start, Stop, and Check the MySQL Service

Use these commands to manage the MySQL service on Ubuntu or Debian systems:

sudo systemctl status mysql     # Check if MySQL is running
sudo systemctl start mysql      # Start MySQL
sudo systemctl stop mysql       # Stop MySQL
sudo systemctl restart mysql    # Restart MySQL
sudo systemctl enable mysql     # Start MySQL on boot

If you ever see “MySQL server has gone away,” start troubleshooting here.

Secure Your MySQL Installation

Immediately after installing MySQL, run the secure installation script:

sudo mysql_secure_installation

This script will guide you through:

• Setting the root password
• Removing anonymous users
• Disabling remote root login
• Removing the test database
• Reloading privilege tables

This step is critical to harden your server against unauthorized access.

Logging Into the MySQL Shell

You can log into the MySQL shell in two main ways:

If MySQL is using the auth_socket plugin (common on Ubuntu):

sudo mysql

If your root user has a password configured:

mysql -u root -p

Inside the shell, you can manage databases, users, and view or change configurations.

Manage Users and Privileges Inside the MySQL Shell

Once logged into the MySQL shell, here are essential administrative commands:

View All MySQL Users

SELECT user, host FROM mysql.user;

Create a New MySQL User

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Replace localhost with % to allow remote connections (not recommended unless using a firewall and SSL).

Change a User’s Password

ALTER USER 'myuser'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

Delete a User

DROP USER 'myuser'@'localhost';

Working With Databases and Tables

These commands help you inspect and manage database contents from inside the MySQL shell.

List All Databases

SHOW DATABASES;

Select a Database and List Its Tables

USE mydatabase;
SHOW TABLES;

Describe a Table’s Structure

DESCRIBE users;

This shows each column’s name, type, whether it allows NULLs, keys, and default values.

Exporting (Backing Up) a MySQL Database

Use mysqldump to create a complete backup of a database.

Backup a Single Database

mysqldump -u root -p mydatabase > mydatabase_backup.sql

Avoid table locking (recommended for production):

mysqldump -u root -p --lock-tables=false mydatabase > mydatabase_backup.sql

Backup All Databases

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

To compress the backup for long-term storage:

mysqldump -u root -p mydatabase | gzip > mydatabase_$(date +%F).sql.gz

Importing (Restoring) a MySQL Database

To restore a previously backed-up database:

Import a Single Database

mysql -u root -p mydatabase < mydatabase_backup.sql

Make sure the target database already exists, or create it first.

Restore All Databases

mysql -u root -p < full_backup.sql

Run MySQL Queries from the Linux Shell

You can run queries directly from your terminal without opening the MySQL shell:

mysql -u root -p -e "SELECT COUNT(*) FROM users;" mydatabase

This is useful in scripts, cron jobs, or monitoring tools.

Bash Script for Backup and Restore

Create a reusable script to automate your backup and restore tasks:

#!/usr/bin/env bash

# mysql-backup-restore.sh
# Automate MySQL backups and restores

set -euo pipefail

USER="root"
DB="${2:-}"
FILE="${3:-}"

case "${1:-}" in
  backup)
    if [ -z "$DB" ]; then
      echo "Usage: $0 backup db_name [output_file.sql]"
      exit 1
    fi
    OUT="${FILE:-${DB}_backup_$(date +%F).sql}"
    echo "Backing up $DB to $OUT"
    mysqldump -u "$USER" -p --lock-tables=false "$DB" > "$OUT"
    echo "Backup complete"
    ;;
  restore)
    if [ -z "$DB" ] || [ -z "$FILE" ]; then
      echo "Usage: $0 restore db_name input_file.sql"
      exit 1
    fi
    echo "Restoring $FILE into $DB"
    mysql -u "$USER" -p "$DB" < "$FILE"
    echo "Restore complete"
    ;;
  *)
    echo "Usage: $0 backup|restore db_name [file.sql]"
    exit 1
    ;;
esac

Make the script executable:

chmod +x mysql-backup-restore.sh

Run it like this:

./mysql-backup-restore.sh backup mydatabase
./mysql-backup-restore.sh restore mydatabase mydatabase_backup.sql

Conclusion

Managing MySQL from the command line gives you full control over users, databases, backups, and restores. Whether you’re hosting WordPress, Laravel, or any other MySQL-based application, these server management commands will help you keep your environment stable and secure.

Bookmark this guide or drop it into your dev notes. If you’re working on automated deployments, CI/CD, or staging-to-production workflows, these commands are your foundation.

If you’d like to see follow-ups on automated cron backups, remote replication, or performance tuning, leave a comment or message me directly.