Skip to content

Portable MySQL setup

Portable MySQL setup refers to the configuration and running of the MySQL database (specifically version 8.0.x on Windows) without performing a system-wide installation. Instead, the database server is executed manually via batch scripts and initialized for standalone use, typically on a developer's local machine^[600-developer-database-mysql-mysql8-portable.md].

Configuration (my.ini)

Running MySQL portably requires a configuration file, typically named my.ini, which defines paths and server behavior^[600-developer-database-mysql-mysql8-portable.md].

Key configuration requirements include:

  • Directory Paths: The basedir (installation directory) and datadir (data storage directory) must be explicitly defined to point to the portable folders^[600-developer-database-mysql8-portable.md].
  • Authentication: To ensure compatibility with older clients or connection methods, the default_authentication_plugin is often set to mysql_native_password^[600-developer-database-mysql8-portable.md].
  • Logging: The configuration typically enables slow-query-log and defines paths for log-error to facilitate debugging^[600-developer-database-mysql8-portable.md].
  • Performance Settings: The configuration file allows for the tuning of InnoDB parameters, such as innodb_buffer_pool_size and innodb_log_file_size^[600-developer-database-mysql8-portable.md].

Initialization Process

Before the server can be run for the first time, the database directory must be initialized^[600-developer-database-mysql-mysql8-portable.md].

  1. Execute Initialization: Run the mysqld executable with the --initialize-insecure flag, pointing to the configuration file^[600-developer-database-mysql-mysql8-portable.md].
    • Example command: mysqld --defaults-file=d:/my.ini --initialize-insecure --console^[600-developer-database-mysql-mysql8-portable.md].
    • This sets the root password to blank (insecure mode)^[600-developer-database-mysql-mysql8-portable.md].

Startup Script (run.bat)

A batch file (e.g., run.bat) is used to start the server manually from the command line^[600-developer-database-mysql-mysql8-portable.md].

  • Structure: The script usually navigates to the current directory and executes mysqld using the --console flag to display output directly in the terminal^[600-developer-database-mysql-mysql8-portable.md].
  • Command: bin/mysqld --defaults-file=my.ini --console^[600-developer-database-mysql-mysql8-portable.md].

Post-Setup Configuration

Once the server is running, access must be configured for remote or developer connections^[600-developer-database-mysql-mysql8-portable.md].

  1. Login: Access the database via the command line using mysql -uroot -p (blank password)^[600-developer-database-mysql-mysql8-portable.md].
  2. Update Host: Change the root user's host from localhost to % to allow connections from other hosts^[600-developer-database-mysql-mysql8-portable.md].
    • Command: UPDATE mysql.user SET host='%' WHERE User='root' AND Host='localhost';^[600-developer-database-mysql-mysql8-portable.md].
  3. Set Password: Alter the root user to use mysql_native_password and set a specific password^[600-developer-database-mysql-mysql8-portable.md].
    • Command: ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';^[600-developer-database-mysql-mysql8-portable.md].
  4. Apply Changes: Run FLUSH PRIVILEGES; to apply the user modifications^[600-developer-database-mysql-mysql8-portable.md].

Sources

^[600-developer-database-mysql-mysql8-portable.md]