How to save Steem blockchain to MySQL?

in #sbds7 years ago (edited)

Ever wanted to access Steem data easily. Steem developers got you covered, there are already services to make your life easy and get you started with developing applications on top of Steem.

SteemData, SteemSQL, many more services that offer you tools and services to get started.

Get your own

If you want to build and host your own database for your application you can do that as well. SBDS (Steem Blockchain Data Service) which is being developed by Steemit and it is already fully functional.

Instruction on github straight-forward and uses SQLite by default because sbds has api routes built-in to query data. And you want more custom queries and have data in mysql database, you can try below steps. I have couple instances running for eSteem users and upcoming features.

All you need is virtual machine or local one with docker installed.

Steps

Here are the steps I did on my sbds setups:

a) docker run -d --name steem_mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=steem mysql

This will run mysql docker instance which we can use to hook up our sbds docker.

--:--

b) docker inspect --format '{{ .NetworkSettings.IPAddress }}' steem_mysql

This will show you ip address of mysql docker instance which you can use below

--:--

c) git clone https://github.com/steemit/sbds

Clone SBDS source code from github

--:--

d) cd sbds && nano Dockerfile

Edit docker file by adding mysql connection url

--:--

e) ENV DATABASE_URL mysql://root:password@ip_of_mysql_docker:3306/steem

Change line 3 in Dockerfile to above, you can add/setup your custom user credentials instead of root for your mysql instance if you prefer safer storage.

--:--

f) docker build -t sbds .

This will build sbds

--:--

g) docker run -p 8080:8080 -p 9191:9191 --name sbds-instance --link steem_mysql:mysql sbds

This will run sbds and link to mysql docker instance.

--:--

That's it!


Now you can wait sbds to sync up and save all blockchain data to mysql for you.

You can connect to mysql instance via your virtual/local machine IP, mysql credentials because instance exposed 3306 port. SBDS ports are also exposed so you can use SBDS json-rpc requests as well to query the data you need.

If you are developing website, web application, business or any application, I am sure local copy of blockchain helps you build all sort of stuff and get stats easily. Read previous post to learn why you should build on top of Steem.

Steem On!


vote, reblog, follow, vote witness good-karma

Sort:  
There are 2 pages
Pages

Man, you sure deserve the title you are wearing on Steem, "@goodkarma"! thanks for sharing your knowledge and clear easy-to-follow information with us all, namaste :)

Agreed! I found this post to he very informative! I always wondered about this steemit development topic

 7 years ago  Reveal Comment

Great! Thanks 🙏. How many records are there already? Would it be better to use MongoDb instead ?

As I mentioned there are SQL, Mysql and Mongodb options, all possible and depends on developer preference.

Appreciate your services @good-karma, voted you as a witness already, but I guess it's a bit difficult if you are not a developer, wht you say dude?

I have never used docker before. Why do I need it ? Can't I simply use my local mysql ?

Of course you can, above is my setup. You can use sbds and mysql without docker as well. I have one instance without docker, I ended up doing a lot of fiddling and custom changes.

Ok, thank you.

That is awesome, since my day one on steemit I was trying to find a way to put the blockchain on mysql. How big is the mysql database?

I got one server synced up in 8 hours which is taking 134 GB.

Do you know if there is available mysql instances to connect to? I am learning programming might try few things or do some testing... Thanks!

Great job, but still for the techy people, since i am just a writer, but still I know the importance of having this set up correctly. Keep up the good work, my friend

Thank you for the lovely post, wishing you all the best.:) information is sure to come in handy, but more than that, it gives me great confidence to know your hard work is being the useful.

Hello @good-karma 🙂

Give information only

why is the end of this app esteem is often difficult to open? Any open page esteem not showing?

This is good and shows the ingenuity of steem blockchain . Thanks for sharing , upped.

Even though the code is all Greek to me, I wish to learn it at some point.

One word for you..."The Boss". Nice piece

this is a great idea sir ,,thanks

Thanks. I generally avoid Docker, is there a walkthrough for a non docker approach for MySql?

Hello fraind i follow u and upvote your top 3 post so you also follow back and upvote my top 3 post plez fraind...

Congratulations @good-karma! You have completed some achievement on Steemit and have been rewarded with new badge(s) :

Award for the number of upvotes

Click on any badge to view your own Board of Honor on SteemitBoard.
For more information about SteemitBoard, click here

If you no longer want to receive notifications, reply to this comment with the word STOP

By upvoting this notification, you can help all Steemit users. Learn how here!

Hello!, Kindly visit my new created profile and Bless me with your best wishes. @fotographi

Thankyou for sharing.

seems cool Im not great with computers but learning more everyday thanks for the tips I just installed The Steemit More Info 1.4 - Chrome Extension by @armandocat .

Have you checked it out and if so what are your opinions on it for someone new like me?

Also I am not really great with tech talk but I work for a service industry and we have this simple website https://www.sflodorremoval.com/ what do you think we could do with blockchain all we use the site for is to advertise on google and receive phone calls to get work.

thank you for sharing your knowledge and information easily followed by us all, namaste :) @goodkarma
mere nam @Scotting

extraordinary applications that you describe, a very wide range ,, thanks @good-karma good luck always with you

Great job! Thank you for sharing , my friend @good-karma ! Have a wonderful day!

Always love your updates !! Never disappointed

Thank you for sharing @good-karma science. this is very useful for us

Thanks for sharing this useful article @good-karma
You are my inspirator

@good-karma,
Very useful article! I was trying to find out how to get data from STEEM Block Chain and you solved it just now! Great work and thank you very much for sharing such useful guideline with us!

Cheers~

thank info @good-karma
vote and resteem ok

Great Post, I always wait ever your posting @good-karma

Amazing information. Thanks for share @good-karma.

I resteem now :

This is amazing......

Wow!
It's good to know that so much more is done to make life easier on steemit
Thanks

Thank you!

This is a good information to developers. There are so many things to learn on steemit and about steem. This is awesome. Thanks for sharing 👍

smart posting. thank you

It looks complicated in my eyes... Lol, i need to start learning more about computers, website developers and all...

This new feature really helps my ms @good-karma in monitoring my steemit account, this will increase my spirits in managing steemit accounts

Thankyou for information

Wow thanks for sharing this. People like you are the best!

thanks my best friend @good-karma.
has been sharing information that is very useful for us eSteem user.
hopefully just eSteem growing all over the world.

Pretty awesome update explained very nicely and easily thanks for that :)

Thank you so much for this very informative tutorial. I am also a developer but i am just starting to learn on how to program in steem blockchain. This is very helpful for me.

Very helpful to all users in steemit, keep thinking @ good-karma for the development of all members steemit, hopefully your day is always fun, because your kindness can be felt by others, that good person is useful for others.

Thank you very much.

Good post! I'm going to follow you to see more post like this and for support us!

awesome post :) i followed you

Very good information. Thank you very much

damn man thats awesome ! :) ur a great writer too :D

-

im gonna follow you maybe @good-karma does come around :)

-

Wow..... its amazing post ,,,,,,,,, thanks for sharing this. People like you are the best!

Wow........amazing post my dear friend ,,,,,,,,,, thanks for sharing this. People like you are the best!

Love your work @good-karma! Resteemed!

There are 2 pages
Pages