Published
Jul 24 2019 05:41 PM
4,682
Views
Often, users are reporting point in time restores are showing recent(current) data in the restored server.
Solution: When you run point in time restore (PITR) this operation will create a new server and restore your server to a point of time that you choose. When the restore operation is successful please remember that you need to update the connection string to connect to the newly created server in two places, server name and username.
It's important to change the username in the new connection string to reflect the new server name. In Azure Database for MySQL and Azure Database for PostgreSQL usernames consists of two parts separated by the '@' symbol in the following format: username@servername.
Now when you connect to the database server and if you see the data as in current this means that your connection string has the server name correct but the username part is still set to the original (source) server name as the following "source-server-db.mysql.database.azure.com" which is incorrect, alternatively set it here also to connect to the newly created server.
Example: If i need to restore server "source-server-db" point in time 5 days ago, I will go to the portal and navigate to the restore blade and set the restored server to be called "destination-server-db", as in the following screenshot:
After the restore operation is successful and when you want to connect to the database you will need to use the following connection string
server: destination-server-db.mysql.database.azure.com
username: bahusse@destination-server-db
Now why this happens?
In Azure Database for MySQL and azure database for PostgreSQL, each region shares a common endpoints or gateway to start your connection process. When your connection is initially started, it will resolve your full server name, in this case, destination-server-db.mysql.database.azure.com. This gateway is then able to review the connection details to determine the specifics of your server and where it resides in that region. It will then provide that information to the client to proceed to connect to your specific instance. In a scenario such as with MySQL Workbench or PgAdmin or any other client, this is determined by looking at the user name and then if necessary, the server name.
As the username in the above example is username@destination-server-db, it will initially speak to the gateway which is resolved from destination-server-db.mysql.database.azure.com and ask for the details of mydomain.com as that is part of the user name provided after the @ symbol. By adding the @server to the end of my overall user, it is able to essentially 'override' what client thinks is the server provided in the user name by explicitly stating the server as well. You will notice that all the provided connection strings in the portal for a server use the user@server syntax by default for the user due this same of behavior.