Page 1 of 1

Beginner: How to integrate existing MySQL

PostPosted: 24. May 2015 19:35
by Billaboard
I see the question has been asked before, but I really hope for more detailed answer. I am trying to bring together quite a large number of databases built over many years and am well down the path of converting them to MySQL. I am doing this on a spare Windows 7 64-bit machine with a view to possibly moving all to a "server" on the workgroup.

Now I have installed XAMPP. It installs its own instance of MySQL in C:\xampp. The existing install is in C:\Program Files\MySQL Both are 5.6.24, I believe.

I have done a mysqldump of the databases in my existing MySQL and then tried to stop its service, but get "Access is denied". To get round this I stopped the service via a program " SysInternals' Autoruns".

I have (I think) changed the port in XAMPP from 3306 to 3308 in the control panel and in My.ini in the appropriate directory, but then seem to have MySQL Workbench pointing at the newer server, but still looking at the older my.ini file.

In other words, it is all a mess.

Maybe the best approach will be to uninstall XAMPP and start again, pointing it to the directory where MySQL is already installed? If I do this will it pick up my existing schemas, or will I have to manually intervene in some way. I am rather worried that I will lose the work I have already done in the database conversions if I go ahead blindly.

I have never used Perl or PHP and only embarked on this in an attempt to be able to create a smooth way of printing out results of queries in simply formatted formats.

Re: Beginner: How to integrate existing MySQL

PostPosted: 25. May 2015 16:35
by mark.mcdonald
Sounds like something to worry about but also sounds like you managed to create a net in case you fall. So down to business:
Billaboard wrote:I have done a mysqldump of the databases in my existing MySQL

From your dump I am assuming this created a .sql file with your databases and data. This is your net to catch you if you fall as this can be easily imported into xampp.
Make an exact copy of what you have now from "C:\Program Files\MySQL" to "C:\Program Files\MySQL - copy" (can't get worse as this is your secondary net. If anything happens and things get worse, just delete the bad mysql folder and make a new one from the " C:\Program Files\MySQL - copy")
Try stopping each and every service. I'm assuming your install of xampp is blank as I would get rid of this installation for now and focus on fixing the original C:\Program Files\MySQL (stopping the pull for either mysql instance and let it all go to one spot for now)
Focus on one port (either 3306 or 3308 whichever it is using), and make sure everything points to the one installation as it did before. Fix this first before trying to integrate xampp again.
Are you the administrator or have administrator privileges on your win 7 machine to be stopping the service? "Access is denied". The fact you went around and "cut the black and yellow wire" vs finding out why you aren't allowed to stop the service is a red flag as this was the start of your problems.

Once you have this fixed. Stop all services.
Install xampp fresh and upload the .sql file of your databases and data.

Re: Beginner: How to integrate existing MySQL

PostPosted: 26. May 2015 20:30
by Billaboard
Thanks, Mark.
Not going well so far....... Some of the previous info may have been a little confused, and I still am.

The old database that I want to keep seems to have mySQL directories within c:\Program Files, C:\Program Files (x86) and C:\ProgramData. The actual data seems to be kept within the last of these.
I have been using MySQL Workbench and occasionally some of the connectors to gather the databases together. They were all set up so as to appear and be accessible via one "Model" in Workbench. All this batch of MySQL programs were installed at the same time and are in the same directory group.
Via Workbench, I have made 2 dumps of schemas into dump directories and on combined dump of the schemas into one .sql file.

I have stopped this MySQL service, uninstalled and reinstalled XAMPP to C:\XAMPP.

I can then open Workbench and configure it to see the schemas that come with XAMPP. I then go to import the schemas either individually or all together and in every case get a message "ERROR 1046 (3D000) at line 22: No database selected Operation failed with exitcode 1".

I am running an Administrator account. I realise this is now probably a problem for a different forum.

I am now going to try stopping the XAMPP MySQL server and restarting the "older" server to try the dump and retrieve process there by making a small schema, dumping, deleting and trying to retrieve.

I now have many, many backups.

Re: Beginner: How to integrate existing MySQL

PostPosted: 26. May 2015 21:01
by mark.mcdonald
Are you only dumping the tables? or the tables and the database? It sounds like you are almost there!!
It's like cleaning a fish bowl. You are moving a fish from one bowl to the other except you are missing the second bowl, but you have the fish and water going everywhere!
Xampp is complaining about not having a place to put your tables aka a database. If you had a dump of the database and imported the database it should be fine. Otherwise create a database and the dump the tables within it.

Re: Beginner: How to integrate existing MySQL

PostPosted: 27. May 2015 00:08
by Billaboard
The fish are still flapping about on the table, I'm afraid. I think I've dumped the tables and database. I have selected "Dump Structure and data" in Workbench.

Maybe the first problem to sort out is that, having started and stopped and, I thought, set up both databases, when I open MysqL Workbench pointing at the XAMPP instance of the database and go to "Server Status", it shows the socket as C:\xampp\mysql\mysql.sock and the ini file as C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

So I still have a situation where the ini file of the older instance is being used by the newly installed instance, and the two my.ini's are different.

I'm beginning to think that complete re-installation and build all from scratch might be the best way forward, but I'll keep poking at this for a day or so more. I'm borderline ancient so memory is not my strongpoint hence reliance on the gui assistance where possible.

Re: Beginner: How to integrate existing MySQL

PostPosted: 27. May 2015 18:07
by mark.mcdonald
Your Server instance is registered wrong.
When you create new server instance for server administration in workbench, there is a selected option, e.g.:
Mysql Installation Type: Window(MySQL5.0 x64 Installer Package)
If you selected a wrong one with your current OS and Server installation, it would result an incorrect base path mapping.
To solve your issue, you can change the base directory in Server Administration-> Logging the instance -> Configuration Menu -> Configuration -> General:
1) Click the base directory checkbox
2) Input the path e.g. "C:/Program Files/MySQL/MySQL Server 5.1/"
3) Click the data directory checkbox
4) Input the path e.g. "C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"

Re: Beginner: How to integrate existing MySQL

PostPosted: 29. May 2015 20:42
by Billaboard
I'm going to have to disappear for some time and read a great deal. As I say, I have messed ( the operative word) with databases for many years and the current interfaces with MySQL seem to be where I get most confused. In the above instructions, I can't even find the base and data directory checkboxes.

I have been and still am confused by the MySQL Workbench interface, which now seems completely different from the version shown in most of the training materials I can find.
However, I believe I can set the relevant directories when I get that far.

Meanwhile, I've booted up and done a fresh install on another machine and looked at the mysqldump files, which I understand should start with instructions to create the databases before adding the tables. This first instruction seems to be missing, which agrees with an error message I was getting early on.
I will therefore go away and spend some time sorting out what I'm doing.

Many thanks for the help. I will come back to the thread when I have rebooted my understanding.