Running/Starting MySQL without installation on Win

Question

Normally, I would download MySQL msi installer for windows and install then configure and created the database within the installation steps. Then just connect using whatever application/language and go from there.

However
I want to reach the same result without using the msi installer, instead i want to use the provided MySQL archive. So,

• I have downloaded (MySQL Community Server => Windows (x86, 64-bit), ZIP Archive mysql-5.7.17-winx64.zip)
• Extracted the archive.

I want to know how to create and manage a database using those downloaded server files through windows command line.

Most of the search attempts yield results that either assume that msi installation have taken place or are far too complex for someone who is still attempting to learn MySQL basics.

TL;DR : How can i create and manage a database using MySQL server archive files on windows through command line?

Answer 1

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

Answer 2

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

   "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