Running/Starting MySQL without installation on Windows

Question

Thanks to Ryan Vincent's comment. I was able to follow the steps in MySQL's reference documentations (For some reason my searches prior to asking this question never found it).

Reference Documentation : 2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive

Simplified Steps

1. Download MySQL Community Server 5.7.17 Windows (x86, 64-bit), ZIP Archive

2. Extract the downloaded MySQL Server Archive to the desired location for MySQL server files (example : D:\mysql\mysql-5.7.17-winx64)

3. Create a directory for MySQL's database's data files (example : D:\mysql\mydb)

4. Create a directory for MySQL's database logging (example D:\mysql\logs)

5. Create MySQL options file (example location : D:\mysql\config.ini)

   # For advice on how to change settings please see
   # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
   
   [mysqld]
   
   # Remove leading # and set to the amount of RAM for the most important data
   # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
   # innodb_buffer_pool_size = 128M
   
   # Remove leading # to turn on a very important data integrity option: logging
   # changes to the binary log between backups.
   # log_bin
   
   # These are commonly set, remove the # and set as required.
   # basedir = .....
   # datadir = .....
   # port = .....
   # server_id = .....
   
   
   # Remove leading # to set options mainly useful for reporting servers.
   # The server defaults are faster for transactions and fast SELECTs.
   # Adjust sizes as needed, experiment to find the optimal values.
   # join_buffer_size = 128M
   # sort_buffer_size = 2M
   # read_rnd_buffer_size = 2M 
   
   sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
   # set basedir to your installation path
   basedir = "D:\\mysql\\mysql-5.7.17-winx64"
   # set datadir to the location of your data directory
   datadir = "D:\\mysql\\mydb"
   # The port number to use when listening for TCP/IP connections. On Unix and Unix-like systems, the port number must be
   # 1024 or higher unless the server is started by the root system user.
   port = "55555"
   # Log errors and startup messages to this file.
   log-error = "D:\\mysql\\logs\\error_log.err"
   
   [mysqladmin]
   
   user = "root"
   port = "55555"
   

   • Selected port is 55555
   • [mysqld] groups options relating to mysqld.exe which will be used when mysql.exe reads this configuration file.
   • [mysqladmin] groups options relating to mysqladmin.exe which will be used when mysqladmin.exe reads this configuration file.

6. Initialize MySQL database files using Windows Batch File/Command Prompt (you might need C++ redistribute if you get an error)

   "D:\mysql\mysql-5.7.17-winx64\bin\mysqld.exe" --defaults-file="D:\\mysql\\config.ini" --initialize-insecure --console
   

• This will create a database files in the location specified in the configuration file.
  • It will have root user with no password
  • Error messages will be printed on current console window.

7. Create a batch file to start the MySQL database server

   "D:\mysql\mysql-5.7.17-winx64\bin\mysqld.exe" --defaults-file="D:\\mysql\\config.ini"
   

   • This will read [mysqld] part/group of the configuration file (D:\mysql\config.ini) and use options specified there to start the MySQL database server.

8. Create a batch file to shutdown the MySQL database server

   "D:\mysql\mysql-5.7.17-winx64\bin\mysqladmin.exe" --defaults-file="D:\\mysql\\config.ini" shutdown
   

   • This will read [mysqladmin] part/group of the configuration file (D:\mysql\config.ini) and use options specified there to specify and shutdown the MySQL database server.

9. You can now start your database and access it, and shut it down when it is not needed.

DISCLAIMER Those steps are supposed to help you get started with MySQL database and are in no way intended or secure for production.(root user doesn't even have a password set yet)

Resources And More Details

1. Reference Documentation : 2.3.5 Installing MySQL on Microsoft Windows Using a noinstall Zip Archive
2. Reference Documentation : 5.2.6 Using Option Files
3. Reference Documentation : 5.2.3 Specifying Program Options
4. Reference Documentation : 6.1.4 Server Command Options
5. [Additional] Reference Documentation : 5.6 Running Multiple MySQL Instances on One Machine
6. Steps to change root password

In addition to that, if you encounter the "mysqld: Could not create or access the registry key needed for the MySQL application to log to the Windows EventLog. Run the application with sufficient privileges once to create the key, add the key manually, or turn off logging for that application." error - Add to the steps 6, 7 the following line: --log_syslog=0

In addition to Sero's answer, if you change the root password following this link, use below commands to start, stop and to connect to server by specifying password

1. change password
    .\bin\mysql -P 55555 -u root --skip-password
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'root123';

2. stop mysql server
    .\bin\mysqladmin --defaults-file=.\config.ini -u root --password=root123 shutdown 

3. start mysql server
    .\bin\mysqld.exe --defaults-file=.\config.ini

4. connecting to mysql
    .\bin\mysql -P 55555 -u root --password=root123

Note: if you just specify --password argument in the command without specifying the password, it will prompt for password in the terminal.