MySQL Installation and Environment Configuration: A Step-by-Step Guide to Setting Up a Local Database

What is MySQL?¶

MySQL is an open-source relational database management system (RDBMS) renowned for its stability, ease of use, and free availability. It is widely used in website development, data analysis, and other fields. For beginners, installing MySQL locally allows you to practice SQL statements and build databases for small projects anytime without relying on external servers.

Pre-Installation Preparation¶

Before installing MySQL, confirm the following information:
- Operating System: Windows (recommended for beginners) or Linux (e.g., Ubuntu, CentOS).
- Installation Package Source: Visit the MySQL Official Website to download the Community Edition, which is free and includes all necessary features for learning.
- Hardware Requirements: Minimum configuration (1GB RAM, 100MB disk space). No special requirements for general learning scenarios.

Installation Steps for Windows¶

1. Download the Installation Package¶

After entering the official website, click the “Download” button and select “MySQL Installer for Windows”.
Choose “Community Edition” and click “Download”. Download the installer (.msi file) without registration.

2. Run the Installer¶

Double-click the downloaded package and select “Custom” (Custom installation) to easily choose installation paths and components later (newcomers can also directly select “Typical” for quick setup).
Installation Type Selection:
Typical: Automatically installs core components (server, client, tools), ideal for quick start for beginners.
Custom: Manually select the installation path (e.g., D:\MySQL) and components (e.g., install only Server, Client, and Tools).

3. Installation Process¶

The installer will automatically detect and install dependent libraries (e.g., Microsoft Visual C++ runtime). If prompted “Missing VC++ components”, click “Execute” to automatically fix.
After installation, proceed to the “Product Configuration” interface and click “Next”. The default port is 3306 (no modification needed unless there is a conflict).

4. Configuration and Initialization¶

Password Setup: Strongly recommend setting a password for the root user (avoid empty password by default). The password must be at least 8 characters (e.g., 123456) and remember it for later use.
Server Type: Select “Developer Machine” (uses fewer resources, suitable for local development).
Character Set: Ensure “utf8mb4” is selected (supports Chinese and emojis) to avoid Chinese garbled issues later.
Network Configuration: Keep default “TCP/IP” protocol with port 3306; no changes needed.

5. Verify Installation Success¶

Check Version: Press Win+R, type cmd to open Command Prompt, then input mysql -V (uppercase V). The version number indicates success.
Log in to MySQL: Input mysql -u root -p, enter the password set earlier. If you see the mysql> prompt, the installation is complete.

6. Environment Variable Configuration (Optional but Recommended)¶

Locate MySQL’s bin Directory: e.g., D:\MySQL\bin (adjust based on actual installation path).
Add to System Environment Variables:
Right-click “This PC” → “Properties” → “Advanced System Settings” → “Environment Variables” → “Path” → “Edit” → “New”, then paste the bin directory path.
Verify Environment Variables: Close Command Prompt and reopen, then input mysql -u root -p. No need to switch directories to log in.

Installation Steps for Linux (Ubuntu Example)¶

1. Install MySQL¶

Open the terminal and execute:

  sudo apt update  # Update software sources
  sudo apt install mysql-server  # Install MySQL

During installation, you will be prompted to set the root password (different from Windows; Linux requires manual confirmation).

2. Start and Verify¶

Start the service: sudo systemctl start mysql
Enable auto-start: sudo systemctl enable mysql
Verify installation: sudo mysql -u root -p, enter the password to log in.

3. Security Configuration (Mandatory)¶

Run the command sudo mysql_secure_installation. Follow prompts to modify the root password, delete anonymous users, disable remote root login, etc. (For beginners, press Enter to skip all options except setting the root password).

Common Issues and Solutions¶

1. Installation Failure: Port Conflict¶

Check if another database service (e.g., MariaDB) is using port 3306. Stop the conflicting service and retry installation.

To reset the root password (Windows example):
1. Stop the MySQL service (via Services Manager or command line net stop mysql).
2. Open Command Prompt as Administrator, input mysqld --skip-grant-tables (starts without permission check).
3. Reopen Command Prompt, input mysql -u root (no password needed), then execute ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';.

3. Chinese Garbled Text¶

Check the configuration file: On Windows, modify my.ini (in the MySQL installation directory). Add under [mysqld]:

  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci

Restart the service and execute show variables like '%character%'; to ensure character_set_server is utf8mb4.

Summary and Recommendations¶

Key Steps: Download → Install → Configure Password → Verify → Environment Variables (for Windows).
Learning Tools: Recommended to use GUI tools like Navicat or SQLyog to connect to local MySQL, or practice basic SQL statements (e.g., SELECT, CREATE) directly via command line.
Notes: Use a secure password and avoid exposing it publicly. Regularly back up the database using the mysqldump command.

After completing the above steps, you have successfully set up a local MySQL database. Now you can start learning SQL syntax and database design!