MySQL on Ubuntu: Installing, Resetting the Root Password, and Importing a Database

Beyond the initial install, two MySQL-on-Ubuntu tasks come up often enough to be worth a dedicated reference — recovering from a lost root password, and importing a database dump from the command line.

Installing MySQL

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Logging in as root initially

sudo mysql

A fresh MySQL install on Ubuntu typically authenticates the root user via the auth_socket plugin rather than a password — this is why sudo mysql (using the system's own root privileges) works without a password prompt immediately after install, before a separate MySQL password has been explicitly set.

Resetting a forgotten root password

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

mysql -u root

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_secure_password';
FLUSH PRIVILEGES;
EXIT;

sudo systemctl stop mysql
sudo systemctl start mysql

--skip-grant-tables starts MySQL without enforcing authentication at all — this is what allows logging in and resetting the password without knowing the current one; restarting normally afterward re-enables authentication, so this flag should never be left active longer than the reset itself requires.

Creating a database and a dedicated user

CREATE DATABASE myapp;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;

Creating a dedicated user scoped to just the one database it needs (rather than using root for the application's own database connection) is the safer practice — it limits the damage a compromised application credential could do to just that one database.

Importing a database from a .sql dump file

mysql -u myapp_user -p myapp < backup.sql

This is the standard command-line import pattern — myapp here is the target database, which must already exist (created via CREATE DATABASE beforehand), and backup.sql is redirected in as the source of the SQL commands to execute.

Importing a large compressed dump directly

gunzip < backup.sql.gz | mysql -u myapp_user -p myapp

Piping through gunzip avoids needing to decompress the entire file to disk first — genuinely useful for a large backup file where saving both the compressed and decompressed copies would otherwise use unnecessary disk space.

Exporting a database (the reverse operation)

mysqldump -u myapp_user -p myapp > backup.sql