Portable MySQL Server: a quick setup guide without installation

October 27, 2024

Running a MySQL server on your machine can be challenging, especially if you are working with a laptop with restricted access rights controlled by your admin team. Or, may be you don't want to install but just want to use it temporarily for local testing.

The following is the steps on where to download, setup, run and how to create a short-cut for later use.

Download MySQL Portable Server

Head to https://dev.mysql.com/downloads/mysql/ and download the "Windows (x86, 64-bit), ZIP Archive" version.

Extract the downloaded ZIP archive to a desired location.

For example C:\mysql_portable

Configure MySQL as a Portable Server

Inside your extracted MySQL directory, C:\mysql_portable,

create data and logs folders and my.ini file for configuration.

Add the following config to my.ini.


[mysqld]

basedir = "C:/mysql_portable"
datadir = "C:/mysql_portable/data"
tmpdir = "C:/mysql_portable/logs"
symbolic-links=0
log-error = "C:/mysql_portable/mysql-server.log"

To initialize the Data Directory, open Command Prompt and navigate to the MySQL bin directory:

cd C:\mysql_portable\bin

PS: The follwing command will print a temporary root password on the console, which is important to take note.

mysqld --initialize --console

Run the MySQL Server

You are now ready to run your newly set portable MySQL server. The following command will start the server.

mysqld --console

Leave the first Command Prompt open and open a new termianl to log in to the server.

Navigate to the bin directory:

cd C:\mysql_portable\bin

Run the MySQL client to log in as the root user and enter the temporary root password you noted earlier.

mysql -u root -p

Changing the root password to something more memorable is always a good idea.

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

Create a Batch File to Simplify Startup

You can create a batch file to make it easier to start your portable MySQL server.

Open Notepad, paste the following content, and save it as start_mysql.bat somewhere in your hard drive.

@echo off
cd C:\mysql_portable\bin
start mysqld --console

Now, you can just double-click on start_mysql.bat to start the server.

Conclusion

Running a portable MySQL server is a convenient way to manage MySQL without needing a full installation. This setup is particularly useful for developers who need to work on different machines or need a lightweight MySQL server for testing purposes.

Happy Coding!