Lets pull apart your first paragraph.
I went to the final round of interview of two companies X and Y. I
like company Y much more than X. Company X gave me an offer and I
accepted. I have started the hiring process with them.
I assume that the offer from X is a written offer without contingencies, and not an verbal offer, or an offer that requires a successful background check which could take weeks.
I informed Y about the offer. Then Y rejected me,
Telling Y about the offer from X before signing the offer from X can be used to spur Y to make your an offer. Telling them after the fact either tells then you are off the market, or even though you signed with X you are willing to break your promise and go with them. Unless your telling them about X didn't mentioned that you signed the offer from X.
but told me that I was their 2nd choice when I asked them for feedback.
This tells me that either before you told them about the offer from X, or after you told them they decided that you weren't number 1. You could have been 2nd, 3rd, 4th. They told you 2nd so that if they need to make an offer to you after other candidates reject them, they can do so.
Y asked me if I would be open to joining them if things don't work out with the 1st choice.
They want to get an idea on how likely you are to jump to them if they contact you.
Now to the 2nd paragraph.
I'd like to take up company Y's "offer", but I don't want to sound
desperate. But, I am not sure if I should even consider Y's "offer" in
the first place, especially since I am their backup.
Tell them that you are open to joining them. They may never call. They may make an offer that is acceptable. They may not call for weeks or months. Telling them you are open to joining them if X doesn't workout is not committing you or them to anything.
When I accept a job offer, I don't call everyplace I submitted an application and pull that application. I don't waste the companies time, and I don't give them false hopes. But I have no problem keeping a few irons in the fire just in case the job falls through.
Neither side in your question have committed anything going forward. You might never call them, they might never call you.
Another question is what is the impact of leaving company X before or
shortly after my start date? Is it okay to leave so soon like this?
We have many questions about this. X will be mad. They may have invested time and money. They may have lost a good candidate they told was number 2, who had an offer from another company and called to say they weren't open to joining them if the other company didn't work out...