{"id":1713,"date":"2018-11-10T13:00:04","date_gmt":"2018-11-10T13:00:04","guid":{"rendered":"http:\/\/chewett.co.uk\/blog\/?p=1713"},"modified":"2018-11-10T17:02:26","modified_gmt":"2018-11-10T17:02:26","slug":"running-two-mysql-databases-on-windows","status":"publish","type":"post","link":"https:\/\/chewett.co.uk\/blog\/1713\/running-two-mysql-databases-on-windows\/","title":{"rendered":"Running two MySQL databases on Windows"},"content":{"rendered":"

\"\"<\/p>\n

This post describes the process you need to follow to set up a second MySQL database on Windows.<\/p>\n

<\/p>\n

Preparation\u00a0for configuring a secondary database<\/h2>\n

The base of this guide assumes that you have installed MySQL server for windows already and it is running as normally.<\/p>\n

It is assumed that the location of mysqld.exe<\/code> is\u00a0C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin\\mysqld.exe<\/code>. Any places that refer to this command will need it to be replaced with the location of your mysqld.exe<\/code> executable.<\/p>\n

Setting up the database data and files<\/h2>\n

To start with you will need to pick a location to set the new database files up. For this example I have created a folder under C:\\dev\\mysql_database_2\\<\/code>\u00a0to store the configuration files and C:\\dev\\mysql_database_2\\data\\<\/code> to store the data directory.<\/p>\n

In the main directory, you will need to create a my.ini file to start your database. This will be the full configuration file the database server will use. In my case I am copying the my.ini file used by the main MySQL server, for me this is located in C:\\ProgramData\\MySQL\\MySQL Server 5.7\\<\/code>.<\/p>\n

Once I have copied the file there are a few lines that need to be changed. I have modified the file to change these lines to the below contents.<\/p>\n

port=3307\r\ndatadir=C:\\dev\\mysql_database_2\\data\\\r\nsecure-file-priv=C:\\dev\\mysql_database_2\\Uploads\\\r\nloose_keyring_file_data=C:\\dev\\mysql_database_2\\keyring\\keyring<\/pre>\n
The port is the port that MySQL server will run on and must be free to use. Which means it needs to at least be changed from port 3306 which the current MySQL server runs on. I have changed the data dir parameter to the location\u00a0I want to store the data in. The secure-file-priv<\/code>\u00a0and loose_keyring_file_data<\/code> fields are internal folders used for housekeeping and we should make sure they have a valid folder.<\/p>\n
Initializing the database data directory<\/h2>\n
Before I can start using the database it needs to be properly initialized and the data directory configured. This adds a set of default tables that MySQL will use to operate. To do this we need to run the initialization command.<\/p>\n
\"C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin\\mysqld.exe\" --defaults-file=C:\\dev\\mysql_database_2\\my.ini --initialize --console<\/pre>\n
Here we specify our ini file using the defaults-file<\/code> command line parameter. --initialize<\/code> tells MySQL to set up the new data directory and --console<\/code> ensures that the temporary password is printed to the console.<\/p>\n
Once this has been run, the final line should be something along the lines of:<\/p>\n
2018-11-10T09:55:17.422082Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxxxxx<\/pre>\n
Save this password as you will need it to log into your database and configure the root password.<\/p>\n
Setting up a service to run the new database<\/h2>\n
Now the database has been initialized we can configure a new windows service to run the database automatically. To install a service you must run the following command in an administrator\u00a0command terminal.<\/p>\n
\"C:\\Program Files\\MySQL\\MySQL Server 5.7\\bin\\mysqld.exe\" --install MySQL57_2 --defaults-file=C:\\dev\\mysql_database_2\\my.ini<\/pre>\n
The install<\/code> parameter takes a single parameter, the name of the service. Here I have called the service MySQL57_2<\/code>. Passing in the defaults file will create the service with the newly created my.ini<\/p>\n
Starting the service and changing the root passwordRunning two MySQL databases on Windows<\/h2>\n
Now that the service has been created it can be started using the windows services panel. By default, the services created will be started automatically on boot.<\/p>\n
The first time the database is logged into using the temporary root password it will request you change it. Once this is done you will be able to start using it.<\/p>\n
\"\"<\/p>\n
Above is an image of MySQL workbench asking me to change the root password after logging in using the temporary one.<\/p>\n
Summary<\/h2>\n
In the above steps we have created a location to store the data and created our my.ini configuration function. Once this was created we initialized the data and configured it to start on boot using a Windows service. Finally to start using MySQL workbench we changed the current temporary password to a permanent one.<\/p>\n","protected":false},"excerpt":{"rendered":"
This post describes the process you need to follow to set up a second MySQL database on Windows.<\/p>\n","protected":false},"author":1,"featured_media":1715,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Today I wrote about how to install a second #MySQL #Database on windows.","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[98],"tags":[60,286,317,29],"class_list":["post-1713","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software","tag-mysql","tag-mysql-database","tag-mysql-server","tag-windows"],"wppr_data":{"cwp_meta_box_check":"No"},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/11\/running_multiple_databases.fw_.png?fit=800%2C800&ssl=1","jetpack_shortlink":"https:\/\/wp.me\/p2toWX-rD","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1321,"url":"https:\/\/chewett.co.uk\/blog\/1321\/how-to-install-the-official-mysql-community-server-on-fedora-28\/","url_meta":{"origin":1713,"position":0},"title":"How to Install the official MySQL community server on Fedora 28","author":"Chewett","date":"July 25, 2018","format":false,"excerpt":"Today I talk about how you can install the official\u00a0MySQL community server on Fedora 28. Why you cant just use DNF to install mysql-server In the last couple versions of Fedora the default MySQL\u00a0distribution was changed to mariadb. While this is, for the most part, a drop in replacement this\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2018\/07\/installing_offical_mysql.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":151,"url":"https:\/\/chewett.co.uk\/blog\/151\/access-network-location-hewlett-packard\/","url_meta":{"origin":1713,"position":1},"title":"Could not access network location \\Hewlett-Packard\\\\","author":"Chewett","date":"September 2, 2013","format":false,"excerpt":"When trying to install MySQL Server it failed with the message \"Could not access network location \\Hewlett-Packard\\\\\" After some searching it turns out this is a common issue where HP have inserted a registry entry with the install path of \"\\Hewlett-Packard\\\\\" which isnt a valid location. This can be resolved\u2026","rel":"","context":"In "Fixes"","block_context":{"text":"Fixes","link":"https:\/\/chewett.co.uk\/blog\/category\/fixes\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2783,"url":"https:\/\/chewett.co.uk\/blog\/2783\/how-to-install-a-mysql-server-on-a-raspberry-pi\/","url_meta":{"origin":1713,"position":2},"title":"How to install a MySQL Server on a Raspberry Pi","author":"Chewett","date":"January 30, 2021","format":false,"excerpt":"This blog post explains the steps you need to perform to install a MySQL server on a Raspberry Pi. Ensuring the Raspberry Pi is up to date Before installing any programs it is recommended to make sure your Raspberry Pi is up to date. This reduces the change of a\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/install_mysql_raspi_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":2247,"url":"https:\/\/chewett.co.uk\/blog\/2247\/how-to-remove-a-mysql-service-in-windows\/","url_meta":{"origin":1713,"position":3},"title":"How to remove a MySQL Service in Windows","author":"Chewett","date":"June 22, 2019","format":false,"excerpt":"This post details the command to remove a MySQL Service in Windows. Removing a MySQL service To remove the default MySQL service you can run the following command. mysqld.exe --remove The mysqld.exe may not already be in your path so you may need to locate it. It is typically found\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/removing_mysql_service-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/removing_mysql_service-1.jpg?fit=800%2C800&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/removing_mysql_service-1.jpg?fit=800%2C800&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2019\/06\/removing_mysql_service-1.jpg?fit=800%2C800&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":194,"url":"https:\/\/chewett.co.uk\/blog\/194\/mysql-workbench-working-with-key-based-exchange-mechanisms\/","url_meta":{"origin":1713,"position":4},"title":"Mysql workbench working with key based exchange mechanisms","author":"Chewett","date":"June 3, 2015","format":false,"excerpt":"It appears newer linux sshd configs\u00a0(fedora 22) by default include a smaller set of key exchange based mechanisms that they have turned on. This means that it gives errors saying it is unable to connect to the server. To fix this you need to enable some of the older key\u2026","rel":"","context":"In "Fixes"","block_context":{"text":"Fixes","link":"https:\/\/chewett.co.uk\/blog\/category\/fixes\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2786,"url":"https:\/\/chewett.co.uk\/blog\/2786\/fixing-raspberry-pi-mysql-access-denied-for-user-root-with-non-root-user\/","url_meta":{"origin":1713,"position":5},"title":"Fixing Raspberry Pi MySQL Access Denied for user root with non root user","author":"Chewett","date":"February 6, 2021","format":false,"excerpt":"This blog post talks about how you can log in as root to your MySQL database with a non-root user on your Raspberry Pi. The \"Access denied for user 'root'@'localhost'\" error After setting up your database you might have changed the root password using mysql_secure_installation however it will still give\u2026","rel":"","context":"In "Informational"","block_context":{"text":"Informational","link":"https:\/\/chewett.co.uk\/blog\/category\/informational\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/chewett.co.uk\/blog\/wp-content\/uploads\/2021\/01\/fixing_raspi_mysql_root_login_posticon_OUTPUT.png?fit=1200%2C628&ssl=1&resize=1050%2C600 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1713","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=1713"}],"version-history":[{"count":3,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1713\/revisions"}],"predecessor-version":[{"id":1723,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/posts\/1713\/revisions\/1723"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media\/1715"}],"wp:attachment":[{"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=1713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=1713"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/chewett.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=1713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}