How to save Steem blockchain to MySQL?

in sbds •  2 years ago

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:[email protected]_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

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  

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

·

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.

Good, but do you think the currency is high steem in the future?

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

why now esteem dont give me upvotes.?

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!

Thanks. Hope to see python or R Api for steem.

thank info @good-karma
vote and resteem ok

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.

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

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.

Amazing information. Thanks for share @good-karma.

I resteem now :

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!

This is amazing......

That's it? Thanks for sharing, I'll try that later when I got to my pc :D

Thanks for sharing ,

Upvoted !

By the way ,I started following you and I would appreciate your follow back too .
please visit my blog and vote me too

Its so exciting to be on the ground floor. I think most people don't realize how lucky we are to be a part of this as a community. Things of this magnitude are usually only available to the elite. If the power of the community could be harnessed the growth could be exponential

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

good your post and sharing

Good information, thanks.....

Nice info

Great blog @good-karma

Okay. You just became amazing.

Always love your updates !! Never disappointed

Congratulations @good-karma!
Your post was mentioned in the hit parade in the following category:

  • Pending payout - Ranked 7 with $ 252,28

You are wonderful! Thank you!

Hey, I just wanted to mention that I appreciate your work on Steem very much. I notice your votes on my posts. I think you bring great value to the community!

I hope you well, and I will vote you as witness!

Very nice bruv. thanks for this. it will be used. upvoted followed and resteemed!

Thanks @good-karma for this interesting and useful information.

I often learn about sql or mysql .. because I often use it when I create programs and web ,, because SQL is a sub-programming language specifically used to manipulate the database.
SQL is one of the most popular DBMS (Database Management System) for database and embedded operations (embedded) in almost all programming languages that support relational databases.
Great post @good-karma l like this your post..

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

Award for the number of comments received

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!

great post as always, and thank you for all that you do on Steemit to make it a great place.

articles that are very useful, it can be stored in mysql databases, thanks for sharing knowledge, I will try mysql test and my configuration..

Awesome buddy.... Good revolution.

Great @good-karma...i ama learning...thanks

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!

Thank you for the knowledge, very useful @good-karma

wow !!! awesome!!!!!!!!!!!!

Thanks for this post, @good-karma. I tried out building and running sbds in a container as you did here (but with the built-in sqlite), but hit an issue I reported in https://github.com/steemit/sbds/issues/76. I'm commenting here in case you or others reading this noticed the same problem, so we can figure out how to fix it more easily.

nice post
i like it

I LIKE APLICASI ESTEEM U..... THANKS YA...

Vielen Dank für Steemit 🙏🇹🇭

Thanks for sharing the nice info

Thank you!

This is nice

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 👍

Great post! I gave you a follow and upvote

Thanks for sharing this :) Followed for more news

Nice integration. Resteemed.

Nice post

very nice sharing, thanks for sharing with us i am following with pleasure ...

Great!
Voted you as witness :-)

smart posting. thank you

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

With the recent changes in Steemit's framework, the following line should be changed in the Dockerfile from:
ENV STEEMD_HTTP_URL https://steemd.steemitdev.com
to
ENV STEEMD_HTTP_URL https://api.steemit.com

Otherwise it will spit a load of RPCError and not synchronize.

·

Arrrrggghhh!
I am replying to this @drakos comment as a marker as I will be setting this up this weekend.
Glad you posted this Matey!

·

you just saved me hours of confusion. Thanks !

·
·

Great. Have fun with SBDS :)

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

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

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

Thankyou for information

@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~

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.

Hello good post my name is Yudexis Jaume and I am from venezuela can you give a vote to my last post? @good-karma

thanks for sharing your knowledge and clear easy-to-follow information with us all.

@good-karma
thanks for all...i mentioned you on my post

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

Test post, please ignore *:)