Reinstalling MySQL 8 on Ubuntu Safely

MySQL is a popular open-source database management system. Sometimes you might need to completely reinstall MySQL due to configuration issues or corrupted installations. This guide walks you through the process safely and systematically.

Step 1: Remove Existing MySQL Installation

First, stop MySQL and remove existing packages:

sudo systemctl stop mysql
sudo apt-get remove --purge mysql-*
sudo apt-get autoremove
sudo apt-get autoclean

Step 2: Clean Up MySQL Files

Remove MySQL directories safely, focusing only on standard MySQL locations:

# Remove main MySQL directories
sudo rm -rf /etc/mysql
sudo rm -rf /var/lib/mysql
sudo rm -rf /var/log/mysql

# Optional: Remove specific MySQL configuration files
sudo rm -f /etc/my.cnf
sudo rm -f /etc/my.cnf.d/*
sudo rm -f /etc/mysql/my.cnf

# Clean up package management MySQL files
sudo rm -f /var/cache/apt/archives/mysql*
sudo rm -f /var/cache/apt/archives/mariadb*

❗ Important Safety Note:

• Avoid using broad recursive find and delete commands
• Such commands can be dangerous as they:
  • Can delete files from mounted external drives
  • Might remove essential system files
  • Could affect other applications that have "mysql" in their name
  • Risk deleting user data unintentionally

Step 3: Install MySQL 8

Now let's install a fresh copy of MySQL:

sudo apt update
sudo apt install mysql-server

Step 4: Start MySQL Service

Start and verify MySQL service:

sudo systemctl start mysql
sudo systemctl status mysql

Step 5: Secure MySQL Access

Access MySQL and set up root password:

sudo mysql

Once inside MySQL, set the root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_new_password';
FLUSH PRIVILEGES;
exit;

Step 6: Verify Installation

Now you can log in using:

sudo mysql -u root -p

Enter your password when prompted.

Step 7: Useful Verification Commands

Once logged in, you can verify your installation using these MySQL commands:

-- List all databases
SHOW DATABASES;

-- Select a specific database
USE database_name;

-- Show all tables in current database
SHOW TABLES;

-- Show tables from specific database
SHOW TABLES FROM database_name;

-- Get table details
DESCRIBE table_name;

-- Show table structure
SHOW COLUMNS FROM table_name;

-- View table creation statement
SHOW CREATE TABLE table_name;

-- Check table sizes
SELECT 
    table_schema as 'Database',
    table_name as 'Table',
    round(((data_length + index_length) / 1024 / 1024), 2) as 'Size (MB)'
FROM information_schema.TABLES
ORDER BY table_schema, table_name;

-- View table statistics
SHOW TABLE STATUS;

Common Issues and Solutions

1. Access Denied Error:

   • If you can't access MySQL with mysql -u root -p, but can access it with sudo mysql -u root -p, this is normal in MySQL 8
   • It's part of MySQL's improved security model
   • Always use sudo when accessing MySQL as root

2. Service Won't Start:

   • Check logs: sudo tail -f /var/log/mysql/error.log
   • Verify permissions: sudo chown -R mysql:mysql /var/lib/mysql
   • Ensure ports are free: sudo netstat -tuln | grep 3306

Best Practices

1. User Management:

   • Create separate users for your applications instead of using root
   • Grant only necessary permissions to each user
   • Use strong passwords for all MySQL users

2. Backup and Security:

   • Regularly backup your databases
   • Keep MySQL updated with security patches
   • Monitor MySQL logs for unusual activity

3. Performance:

   • Regularly check table sizes and optimization
   • Monitor system resources (CPU, memory, disk usage)
   • Consider using connection pooling for applications

Before Reinstalling MySQL

Before following this guide, make sure to:

1. Backup all important databases
2. Document any custom configurations
3. Save any important user permissions
4. Note down any critical database credentials

Following these steps will give you a fresh, working, and secure installation of MySQL 8 on your Ubuntu system. Remember to replace 'your_new_password' with a strong password of your choice.

Additional Resources

• MySQL Official Documentation
• Ubuntu MySQL Community Documentation
• MySQL Security Best Practices Guide

Remember to always test your MySQL installation in a development environment before applying changes to production systems.