{"id":23251,"date":"2021-08-13T04:52:31","date_gmt":"2021-08-13T04:52:31","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=23251"},"modified":"2021-10-22T12:27:23","modified_gmt":"2021-10-22T12:27:23","slug":"how-to-migrate-artiafactory-derby-database-to-mysql-database","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/how-to-migrate-artiafactory-derby-database-to-mysql-database\/","title":{"rendered":"How to migrate artiafactory derby database to mysql database?"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\">Step 1 &#8211; Check mysql version supported by artiafactory 7.x<\/h1>\n\n\n\n<p>Artifactory supports MySQL v5.5, 5.6, 5.7 and 8.x with InnoDB engine which is the default provided.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Step 2 &#8211; Take a full import of System<\/h1>\n\n\n\n<p>System Export from the currently running instance by opening from Artifactory UI -&gt; Admin -&gt; Import &amp; Export -&gt; System. Select the Exclude Content checkbox. By selecting this, we export only the configurations from Artifactory but not the binary data.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"845\" height=\"586\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-import-export-system.jpg\" alt=\"\" class=\"wp-image-23253\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-import-export-system.jpg 845w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-import-export-system-300x208.jpg 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-import-export-system-768x533.jpg 768w\" sizes=\"auto, (max-width: 845px) 100vw, 845px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Step 3 &#8211; Setup a mysql server, database and permission<\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">$ docker run --name mysql8 -v \/tmp\/database:<span class=\"hljs-regexp\">\/var\/<\/span>lib\/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=artifactory -e MYSQL_USER=artifactory -e MYSQL_PASSWORD=artifact123 -d mysql<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"900\" height=\"546\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-mysql-check.jpg\" alt=\"\" class=\"wp-image-23258\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-mysql-check.jpg 900w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-mysql-check-300x182.jpg 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-mysql-check-768x466.jpg 768w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Step 4 &#8211; Stop artiafactory server<\/h1>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">$ cd \/opt\/artifactory-pro-7.23.3\/app\/bin\n$ .\/artifactory.sh stop<\/code><\/span><\/pre>\n\n\n<h1 class=\"wp-block-heading\">Step 5 &#8211; Make databases changes in property file of artiafactory<\/h1>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Copy a following section of mysql from system.full-template.yaml<\/strong><\/li><\/ul>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$ more \/opt\/artifactory-pro<span class=\"hljs-number\">-7.23<\/span><span class=\"hljs-number\">.3<\/span>\/<span class=\"hljs-keyword\">var<\/span>\/etc\/system.full-template.yaml\n\n    <span class=\"hljs-comment\">## Example for mysql<\/span>\n    <span class=\"hljs-comment\">## uncomment below to use database type<\/span>\n    <span class=\"hljs-comment\">##type: mysql<\/span>\n    <span class=\"hljs-comment\">##driver: com.mysql.jdbc.Driver<\/span>\n    <span class=\"hljs-comment\">##url: \"jdbc:mysql:\/\/&lt;your db url, for example: localhost:3306&gt;\/artdb?characterEncoding=UTF-8&amp;elideSetAutoCommits=true&amp;useSSL=false\"<\/span>\n    <span class=\"hljs-comment\">##username: artifactory<\/span>\n    <span class=\"hljs-comment\">##password: password<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<ul class=\"wp-block-list\"><li>Make a changes in \/opt\/artifactory-pro-7.23.3\/var\/etc\/system.ya<strong>ml with following content<\/strong><\/li><\/ul>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">   <span class=\"hljs-comment\">## Example for mysql<\/span>\n    <span class=\"hljs-comment\">## uncomment below to use database type<\/span>\n    type: mysql\n    driver: com.mysql.jdbc.Driver\n    url: <span class=\"hljs-string\">\"jdbc:mysql:\/\/172.17.0.2:3306\/artifactory?characterEncoding=UTF-8&amp;elideSetAutoCommits=true&amp;useSSL=false\"<\/span>\n    username: artifactory\n    password: artifact123<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"321\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-system.yaml_-1024x321.jpg\" alt=\"\" class=\"wp-image-23260\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-system.yaml_-1024x321.jpg 1024w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-system.yaml_-300x94.jpg 300w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-system.yaml_-768x241.jpg 768w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-system.yaml_.jpg 1189w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">shared:\n  database:\n    type: mysql\n    <span class=\"hljs-attr\">driver<\/span>: com.mysql.jdbc.Driver\n    <span class=\"hljs-attr\">url<\/span>: jdbc:mysql:<span class=\"hljs-comment\">\/\/&lt;your db url, for example: localhost:3306&gt;\/artdb?characterEncoding=UTF-8&amp;elideSetAutoCommits=true&amp;useSSL=false<\/span>\n    username: artifactory\n    <span class=\"hljs-attr\">password<\/span>: password<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<h1 class=\"wp-block-heading\">Step 6 &#8211; Copy a mysql database driver to required location<\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li>Download the MySQL JDBC driver (available from the MySQL website) and copy the mysql-connector-java-.jar file into $JFROG_HOME\/artifactory\/var\/bootstrap\/artifactory\/tomcat\/lib directory.<\/li><\/ul>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">$ cd \/tmp\n$ wget https:<span class=\"hljs-comment\">\/\/cdn.mysql.com\/\/Downloads\/Connector-J\/mysql-connector-java-8.0.26.tar.gz<\/span>\n$ tar -zxvf mysql-connector-java<span class=\"hljs-number\">-8.0<\/span><span class=\"hljs-number\">.26<\/span>.tar.gz\n$ cp mysql-connector-java<span class=\"hljs-number\">-8.0<\/span><span class=\"hljs-number\">.26<\/span>.jar \/opt\/artifactory-pro<span class=\"hljs-number\">-7.23<\/span><span class=\"hljs-number\">.3<\/span>\/<span class=\"hljs-keyword\">var<\/span>\/bootstrap\/artifactory\/tomcat\/lib\/<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h1 class=\"wp-block-heading\">Step 7 &#8211; Clean all logs so if errors are there &#8211; can be troubleshoot easily<\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">$ cd \/opt\/artifactory-pro<span class=\"hljs-number\">-7.23<\/span><span class=\"hljs-number\">.3<\/span>\/<span class=\"hljs-keyword\">var<\/span>\/log\n$ rm -rf *<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h1 class=\"wp-block-heading\">Step 8 &#8211; Start a artiafactory<\/h1>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$ cd \/opt\/artifactory-pro<span class=\"hljs-number\">-7.23<\/span><span class=\"hljs-number\">.3<\/span>\/app\/bin\n$ .\/artifactory.sh start\n\n$ .\/artifactory.sh status\nUsing <span class=\"hljs-keyword\">default<\/span> router<span class=\"hljs-string\">'s certificate and private key\nrouter is running (PID: 28404)\nmetadata is running (PID: 28550)\nevent is running (PID: 28662)\nfrontend is running (PID: 28778)\nArtifactory is running, on pid=28856<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h1 class=\"wp-block-heading\">Step 9 &#8211; Import all the files which we got as part of Step2.<\/h1>\n\n\n\n<p>To migrate your existing database:<\/p>\n\n\n\n<p>Import the metadata using Full Import and Export (optional for migration, not required for a new installation). Changing the database does not automatically transfer your data to the new database.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"686\" height=\"572\" src=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-import.jpg\" alt=\"\" class=\"wp-image-23256\" srcset=\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-import.jpg 686w, https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2021\/08\/artifactory-database-import-300x250.jpg 300w\" sizes=\"auto, (max-width: 686px) 100vw, 686px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Verify the Migration from Derby to Mysql<\/h1>\n\n\n\n<h3 class=\"wp-block-heading\">Check Artifactory running or not<\/h3>\n\n\n\n<p>$ .\/artifactory.sh status<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Chec a Browsers if its Functioning well or not. Wait 2-3 mins.<\/h3>\n\n\n\n<h3 class=\"wp-block-heading\">Come inside a container<\/h3>\n\n\n\n<p>$ docker exec -it 916877c4a82c \/bin\/bash<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Login to mysql using root<\/h3>\n\n\n\n<p>$ mysql -h localhost -u root -p<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Validate Tables of Artifcatory Migration<\/h3>\n\n\n\n<p>show databases;<br>use artdb;<br>show tables;<br>exit<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">How to troubleshoot?<\/h1>\n\n\n\n<p>$ .\/artifactory.sh status<br>$ cd \/opt\/artifactory-pro-7.23.3\/var\/log<br>Check a log for errors.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Reference<\/h1>\n\n\n\n<p><a href=\"https:\/\/www.jfrog.com\/confluence\/display\/JFROG\/Configuring+the+Database#ConfiguringtheDatabase-ChoosingtheRightDatabase\" target=\"_blank\" rel=\"noopener\">https:\/\/www.jfrog.com\/confluence\/display\/JFROG\/Configuring+the+Database#ConfiguringtheDatabase-ChoosingtheRightDatabase<\/a><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-scmgalaxy wp-block-embed-scmgalaxy\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"ffRkuLeyhf\"><a href=\"https:\/\/www.scmgalaxy.com\/tutorials\/artifactory-error-error-1071-specified-key-was-too-long-max-key-length-is-3072-bytes\/\" target=\"_blank\" rel=\"noopener\">Artifactory Error &#8211; Error 1071: Specified key was too long; max key length is 3072 bytes<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Artifactory Error &#8211; Error 1071: Specified key was too long; max key length is 3072 bytes&#8221; &#8212; scmGalaxy\" src=\"https:\/\/www.scmgalaxy.com\/tutorials\/artifactory-error-error-1071-specified-key-was-too-long-max-key-length-is-3072-bytes\/embed\/#?secret=ffRkuLeyhf\" data-secret=\"ffRkuLeyhf\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n<div class=\"epyt-gallery\" data-currpage=\"1\" id=\"epyt_gallery_51489\"><iframe loading=\"lazy\"  id=\"_ytid_24898\"  width=\"760\" height=\"427\"  data-origwidth=\"760\" data-origheight=\"427\" src=\"https:\/\/www.youtube.com\/embed\/?enablejsapi=1&#038;autoplay=0&#038;cc_load_policy=0&#038;cc_lang_pref=&#038;iv_load_policy=1&#038;loop=0&#038;rel=1&#038;fs=1&#038;playsinline=0&#038;autohide=2&#038;theme=dark&#038;color=red&#038;controls=1&#038;disablekb=0&#038;\" class=\"__youtube_prefs__  no-lazyload\" title=\"YouTube player\"  data-epytgalleryid=\"epyt_gallery_51489\"  allow=\"fullscreen; accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen data-no-lazy=\"1\" data-skipgform_ajax_framebjll=\"\"><\/iframe><div class=\"epyt-gallery-list\"><div>Sorry, there was a YouTube error.<\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>Step 1 &#8211; Check mysql version supported by artiafactory 7.x Artifactory supports MySQL v5.5, 5.6, 5.7 and 8.x with InnoDB engine which is the default provided. Step 2 &#8211; Take&#8230; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-23251","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/23251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=23251"}],"version-history":[{"count":9,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/23251\/revisions"}],"predecessor-version":[{"id":24222,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/23251\/revisions\/24222"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=23251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=23251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=23251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}