Run Multiple Instances

GarryChapple posted 1 year ago in General

HI, can you advise if it is possible to run multiple instances of HeidiSQL on the same server. I have a backup of a database that was created in a different instance which had a different root password. What I would like to do is to be able to load the older backup to a new instance but I do not seem to be able to do this unless the new instance is created using the same root credentials as the old instance. Hope hat makes sense?

OR, is it possible to load the old backup to a new instance of HeidiSQL and still access the old backup somehow? I tried restoring the SQL file to a new instance but it doesn't appear to work, the database appears to be empty?

GarryChapple posted 1 year ago

Oh, and to follow-up on that, when I do a backup I select create for both the database and the tables, is this correct? The saved file is very small compared to the size of the database shown in the session manager? Sorry I am new to MySQL and Heidi so am learning. Thanks.

ansgar posted 1 year ago

Yes, you can create another session with different credentials. If you have fun you can create 10 sessions with the same credentials, connect to them in the same HeidiSQL instance, or in another instance.

And yes, that makes sense, if the both "root" users have a different host/ip. For example you can have one "root@localhost" user, and one "root@", or also with a wildcard: "root@%". The latter means you can connect from all host names, not only from the server's local ip.

I guess I would check if I can extend the privileges of the one "root" user. In HeidiSQL, click Tools > User manager. Then find and select the current user@host name on the left panel. Then you make sure he can see all databases: the right privileges panel should have checked the "Global" node, plus at least all green sub nodes (select, show databases,...).

1 attachment(s):
  • user-manager-globalprivs
GarryChapple posted 1 year ago

Thanks for the assistance Ansgar this is good to know. How do I actually run them if I wanted to have multiple instances of a specific application in iiS 7.5? What I want to do is have two web applications running on different ports in iiS, connect them to two different DB's, but use the same DB name and root passwords so I can cross-restore for training and testing purposes. i.e. backup DB001 from instance001 and restore it to instance002 DB002 and still be able to fire it up with the same root credentials.

I have worked with MS SQL in the past and you can run multiple SQL instances on the same server but have to run the installer multiple times to do that. Is it the same with HeidiSQL do you just run the installer again and choose different settings?

Thank very much for the advice. Garry

GarryChapple posted 1 year ago

Sorry one other thing. When I backup my database of say 4MB, the backup SQL file is like 1600KB - is this normal in HeidiSQL? Or is there a second file that needs to stay with the SQL file to restore all of the data?

Seems like one hell of a compression ratio if not :-)

Sorry for my ignorance!


ansgar posted 12 months ago

What I wrote above is just valid for MySQL and MariaDB. For MSSQL I'm unsure about the privileges you will need.

For the "compression" issue - in MariaDB/MySQL that's a common case. The indexes often take much disk space, but are just included as structure in the export file, without the indexed data (which is created when you import that file)

GarryChapple posted 12 months ago

Sorry I wasn't asking about SQL that was just a reference. What do I do in Heidi SQL to actually create another instance of a database. I don't understand how you do that with Heidi, do I need to run the installer multiple times to create multiple new instances?


ansgar posted 12 months ago

For cloning a database, you normally create a full backup and then import that file in some empty new database.

GarryChapple posted 12 months ago

Sorry ansgar I am not very good at expressing my questions, that's why I used SQL as an example. The best way I can explain it is this:

With MS SQL you can create multiple named SQL instances which creates multiple SQL services with the instance name appended to it. So they are effectively multiple installations of SQL on the same server, but are totally independent of each other. With MS SQL you just run the SQL installer multiple times giving each new instance a different name.

What I would like to know is if this can be achieved in the same way with Maria database using HeidiSQL? Sorry that I am not very good at explaining myself :-) Thanks, Garry.

ansgar posted 12 months ago

Ah ok, you want to install multiple MariaDB servers. I was confused about the word "SQL", when you meant "MS SQL". Anyway.

HeidiSQL is just a client program, connecting to one or multiple servers (MariaDB, MySQL, MS SQL or PostgreSQL). HeidiSQL cannot install a server, neither for MS SQL nor MariaDB.

I see no use case for having multiple MariaDB services running on the same machine, but of course if you have fun you can do that. You will have to use different TCP/IP ports for them, where the default one is mostly 3306.

I would probably do that in the following way:

  • stop the MariaDB service (admin command prompt: net stop mysql)
  • copy the folder where you installed MariaDB into, to a second one
  • edit the my.ini (or my.cnf) in the new folder with a text editor
  • change the port value from 3306 to 3307
  • install the second service via admin command prompt: c:\mariadb-second\bin\mysqld --install mariadb2
  • start the first service: net start mysql
  • start the second one: net start mariadb2
GarryChapple posted 12 months ago

OK that's great thanks. I want to have two SQL services so I can mirror the databases and iis web sites to create a testing and production environment. The production site and DB are critical and backed up regularly, but still I need to test our Project Management application bug fixes and patches before pushing them into production.

This setup will allow me to just restore a good current DB to the test services and make sure I don't break things when patching the app. Thanks for your help very much appreciated.

One other thing, can I setup a basic script to run scheduled backups to a SAN on the network? Currently I do this manually but a script would be useful, are there any examples to that I can modify to suit my Windows 2012 server scheduler?

Thanks again.

Garry Chapple

ansgar posted 12 months ago

One other thing, can I setup a basic script to run scheduled backups to a SAN on the network?

Not via HeidiSQL I'm afraid.

Please login to leave a reply, or register at first.