It’s hard to know how to respond without knowing more about you, eg. where you live, what kind of music you’re interested in making. But I can say a few things as a shy person who’s been making music for over 20 years.
There are a LOT of musicians that are respected and have built an audience based purely on the quality of their music.
It’s helpful to think about what counts as “success”, because there are a lot of ways to define that. If your only idea of success is international mega-fame, then you’re almost certain to fail, regardless of your personality. If success is being able to make the music you want over a long period, and have an audience that appreciates what you do, I think that’s very possible as a shy person. But it may take a lot of time and work.
There are certain aspects of being a musician that are harder if you’re shy. For example, in order to be covered in magazines or websites, you need to be photographed. I personally don’t like being photographed, but I grit my teeth and do it when necessary. It’s very helpful to make videos, and they will be more useful as promotion if your face is in there (something I can’t really deal with). If you find it hard to talk on the radio, that can be limiting. Again, something you may have to practice and get used to. This stuff is all much easier for people who are socially confident. You can make music and avoid all this stuff, but it will probably limit the size of your audience.
As you’ve already discovered, having bandmates can help a lot, so I would stick to working in groups, at least for now. It becomes harder as you age, because people have less time, having careers/babies etc. Solo work might be an option later on, which is definitely more challenging as a shy person. I work solo, and it can be really rewarding in it’s own way. But it’s more like work, with none of the fun of being in a band.
There are certain things about a life in music that are hard and unpleasant, for sure. Having to hang around in bars is not for everyone, and leads to exposure to one of the main occupational hazards: addiction. Being poor a lot, and seeing friends go on to live more normal lives with steady jobs and money, can affect your self esteem.
For me the rewards of a musical life have been the community, the travel opportunities, and the fulfillment that comes from creativity.