Running/Starting MySQL without installation on Windows

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

The above answers are quite outdated and give a bit too much information which may confuse.

These are even more simplified steps for 8.0.26+ version users who just want to run the DB as quick as possible on Windows:

1. Extract the main archive to the desired install directory (C:\mysql is the safe default choice)

2. Initialize the data directory. The recent MySQL distributions do not contain it. Go to C:\mysql\bin and type:

mysqld --initialize --console

3. and save the password from the line:

2021-11-03T21:33:13.455063Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: yIQ_4Ra2Q9g)

4. Run the db from bin directory

mysqld --console

5. Change the assigned random password. Open separate cmd windows and connect

mysql -u root -p

Enter the temporary password from point 3. The root is default MySQL user name. Then type:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';

The "root-password" should be replaced with your own password.

You can now try mysql -u root -p again to check if new password works or just connect with any other client.

I generally followed steps described HERE but not everything is consistent there.

Answer 2

For all who are still looking for all the steps explained in a simple way, here is the answer:

Here is the ZIP file that I had downloaded: mysql-8.0.30-winx64.zip

Here are the steps to start MySQL Server (mysql-8.0.30-winx64), without installation, for the first time on Windows:

1. Create a new folder named "data" in MySQL installation directory (i.e. in the same location as where "bin" directory is located. For me it is: C:\programs\mysql-8.0.30-winx64. This location will vary as per the location where you have extracted the MySQL zip file)
2. From here I will use my MySQL folder location as reference. Go to: C:\programs\mysql-8.0.30-winx64\bin and execute the command: mysqld --initialize-insecure or mysqld --initialize depending on whether you want the server to not generate (or) generate a random initial password for the 'root'@'localhost' account.
3. To start the DB, go to: C:\programs\mysql-8.0.30-winx64\bin and execute mysqld --console You can see the start-up logs being printed.
4. To connect to DB, go to: C:\programs\mysql-8.0.30-winx64\bin and execute mysql -u root -p. When prompted, Enter password if it has been set or else just hit "Enter" button to connect

These steps work on Windows 10 and Windows 7.

I have also written these steps and apps using MySQL database with working code on GitHub in my Blog posts. Please check: https://softwaredevelopercentral.blogspot.com/p/mysql-issues-and-resolution-1.html

Answer 3

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

Answer 4

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.

Answer 5

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