Note: This applies to MySql version 5.7. See the comments section of this post for version 8.0.
I have been looking for a portable version MySql database which I can just copy on my machine and start using with installation. It took a little bit of effort to make it work.
Here are the steps I performed for running MySql without going through installation process (noinstall):
- Download Zip Archive (Community Edition) from http://dev.mysql.com/downloads/mysql/
- Unzip the archive and go to the bin folder.
- Initialize MySql database: Run the following on Command Prompt (cmd) while being in the bin folder. This creates data folder with database files.
mysqld --initialize
- Start server with unrestricted access to the full database.
mysqld --console --skip-grant-tables
- Reset password for root user
- Run mysql.exe (MySql client) from the bin folder. This will take you to a prompt (mysql>).
- Tell the server to load the grant tables so that account management statements work (They are not loaded due –skip-grant-tables parameter to server).
mysql> FLUSH PRIVILEGES;
- On mysql prompt (mysql>), run the following queries to reset the root password (one of the following will work depending on your version)
-
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
-
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass'); -
UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass') WHERE User = 'root' AND Host = 'localhost'; FLUSH PRIVILEGES;
-
- Restart server in normal mode.
mysqld --console
- Connect using client
mysql -u root -p
Enter the password (MyNewPass) when prompted
- Execute your queries on mysql prompt (mysql>).
Please note that this works for version 5.7, but may or may not apply to other versions.
In order to connect to MySql from the application, download the relevant connector from MySql Connectors. For instance, JDBC driver (connector) for connecting from Java application is available at Connector/J.
I’ve followed each step in your doc. FLUSH PRIVILEGES throws the error :
access denied you need (at least one of) the reload privilege(s) for this operation
I then tried : GRANT reload on *.* to ‘root’@’localhost’. This gives the error :
Access denied for user ”@localhost;
Am not able to execute any query. Any help is really appreciated.
I git an error when executing
mysqld –console –skip-grant-tables
I had to replace this command with
mysqld –console –skip-grant-tables –shared-memory
https://dev.mysql.com/downloads/mysql/ only executable MSI files are seen. From where to get ZIP file to extract ?
See the ‘Other Downloads’ section on the page you shared.
With the latest version of MySql, which is 8.0, I have to do the following instead:
– Follow up to step # 3 from the above post.
– Afterwards, create a text file with the below query for changing the root password to MyNewPass:
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;
– Move to MySQL bin folder
C:\> cd “C:\Program Files\MySQL\MySQL Server 8.0\bin”
– Execute the command referring to the saved file to reset password
C:\> mysqld –init-file=C:\\mysql-init.txt
– Now, the database setup is complete and we can log in with root credentials
C:\> mysql -u root -p
Enter the password (MyNewPass) when prompted