Copy K1000 database to local MySQL server to practic queries
I want to practice some advanced update queries for my ticket rules, but need to get the syntax right. I downloaded MySQL and have a local server running. How do I copy the data from the K1000 to the local MySql without exporting to excel and then reimporting locally.
5 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
h2opolo25
9 years ago
You should be able to install a test copy of a VM KBOX and then restore a backup to that and test out queries that way.
Comments:
-
I was doing that, but when I mess up the data it takes too long to restore the copy of the VM again. That is why I want to do the db copy route. - JordanNolan 9 years ago
Posted by:
nshah
9 years ago
You could try this but it is MS Access
Use MS Access to pull down the KBOX DB, you can also set it to update data or you can do it manually.
Then you can try building update queries. SELECT, UPDATE..the basic commands all work the same when trying things out.
Make a copy of the access db so if you mess up...just over wright it and you are good to go.
Comments:
-
how to do? - a83547 9 years ago
-
The syntax between Access and MySQL are too different. Once I put it in KACE I have to be absolutely correct or else I can mess it up. I really need a copy of MySQL - JordanNolan 9 years ago
Are you using MySQL Workbench? If so you should be able to use MySql workbench and directly connect to the K1000 and test your queries. - KACE_Mary 9 years ago
The syntax for update queries sometimes contains not pure SQL but they may contain some ''macros'' that are interpreted from the K1000 on the fly.
Anyway if you go to settings -> control panel -> backup settings you can download the .gz file under the backups section.
This contain the zipped backup of the DB.
I'd suggest to have a look to the following article that contains some suggestion about the ticket rules:
https://support.software.dell.com/k1000-systems-management-appliance/kb/111161
Kind regards,
Marco = StockTrader - StockTrader 9 years ago
Mary, I do have Flyspeed and MySQL Workbench, but I am trying to make an Update query that is more complex than the built in wizards allow. For this I need a copy of the DB that I can test it on before I implement it in the K1000 - JordanNolan 9 years ago
You will need to grab the latest 2014xxxx_k1_dbdata.gz from your K1000 and unzip on the test machine - KevinG 9 years ago
------------------
ERROR 1071 (42000) Specified key was too long; max key length is 1000 bytes
------------------
I am not a MySQL guy so I have no idea what to do next. - JordanNolan 9 years ago
To bring in DB: log into mysql using ./mysql -u root -p
then execute the following:
source <path_to_backup_file_from_kace>
Works for us. - Jbr32 9 years ago