If we want to create mysql databases for cadence. Assuming we want 10 shards for cadence, we should create a set of mysql cadences tables for each shard? If we want 5 machine to create mysql database for 10 shards, how should we do?
Assuming we want 10 shards for cadence, we should create a set of mysql cadences tables for each shard
No. You will only need one set of tables for the whole Cadence cluster. The sharding mechanism is implemented within Cadence server. Unless you have a sharded MySQL solution, you don't need to worry about anything about sharding when setting up database schema.
If you do have a sharded MySQL, just make sure to use shardID as partition(sharding) key for the table.
Sharding in Cadence is only needed for History service(that's why it's called numHistoryShards in the config).
More reading about the sharding: https://cadenceworkflow.io/docs/operation-guide/setup/#static-configuration
Typically you will need 2K shards in production if MySQL is the database.