I have an RDS database and need to create a binlog slave on an ordinary MySQL server on an EC2 instance. According to this: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html the way to do this is to:
- Create a manual snapshot, which should also result in an event in the log containing the binlog position.
- Create a new RDS database from the snapshot and use mysqldump to dump data
I have created the snapshot, but I see no binlog position anywhere. Do I need to set some parameter, add some option or …? Without a solution to this, I’m stuck, simply. This is a production database, so I can’t simply stop it until mysqldump finishes (~24 hours).
I have an Azure SQL DB (OLTP), that is under considerable load, let’s call it AppDB. AppDB is transactionally consistent, but new rows are written to it constantly. Now I need to populate ReportingDB based on AppDB state every 30 minutes. Reporting population job runs several moderately big queries against AppDB, those queries, unfortunately, can’t be wrapped in a transaction but still have to all run on consistent data. That is, I can’t have situation
Query 1 runs=>
new rows inserted into AppDB =>
Query 2 runs. All my queries have to see data the way it was when Query 1 started.
I was hoping I can use Snapshots to create a read-only snapshot for Reporting job to use. From description, creation of such a snapshot should be fast and subsequent "copy on write" performance hit should be manageable. Snapshots lifetime would be under 10 minutes on average.
But now it looks like Azure SQL does not support
CREATE DATABASE ... AS SNAPSHOT OF ..., it only supports
CREATE DATABASE ... AS COPY OF ..., which I expect to be a lot slower (probably not meant to be used for reporting snapshot).
What would be a good way to create quick and short-lived read-only snapshots for reporting in Azure SQL DB?
P.S. We have considered replication, but it is not an option for us at the moment due to policy limitations.
We have Primary-secondary PostgreSQL Streaming replication.
I have done some testing.
2.Run some DML on secondary which is in Write mode(another new primary)
3.Down new primary
4.Re-attach with primary using pg_rewind utility.
So, Can we use these steps to create snapshot standby database like we have in Oracle database.
DB snapshot as one of the tasks in
SSIS package. The package works with 20 different DBs. Thus, each DB will have a snapshot with hard-coded
CREATE DATABASE Sales_snap ON ( NAME = SalesData, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData_snap.ss' ), ( NAME = SalesData1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\data\SalesData1_snap.ss' ), ( NAME = SalesData2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\mssql\data\SalesData2_snap.ss' ) AS SNAPSHOT OF Sales
Is there a way to use some default path for
FILENAME instead of copy-pasting the same string 20 x 3 times? Alternatively, is there any way to specify base path only once and then reuse it in other SSIS tasks?
It looks like recommendation questions aren’t explicitly OT, so here goes:
I’m working on some tooling for analyzing a dump of the heap of a running program. The dump is just a list of nodes with associated metadata and references to other nodes (possibly-cyclical).
I don’t have any experience with graph databases, and I’m wondering if I would save myself a lot of time by building tooling around a graph DB. So I’m looking for recommendations and pointers to resources, and advice.
Some specific questions:
- are there any graph databases that have functionality built in for computing a dominator tree? (googling this didn’t seem to get any results)
- are there any DBs that have tooling for visualizing a huge graph?
Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph.
For reference: The left node is a basic insert into the sync_entities table, where the right node is "update sync_entities set A=B, C=D from sync_entities e join sync_revisions r on e.id=r.entity_id where r.id in ()"
Based on my understanding of lock compatibility, an Requesting an S lock when an IX lock exists would block only if Read Committed Snapshot is off, but it is turned on for this database, and has been for a very long time. Am I missing something?
I am trying to set up a Snapshot replication using an Azure SQL Managed Instance. While trying to see the Snapshot Agent status, I see this error. Failed to connect to Azure Storage ” with OS error: 53.
While configuring the Distribution wizard, I had the option to set the Snapshot folder as well as the Storage account connection string.
I got the storage account connection string from the Azure Portal and pasted that in. I am in doubt about the Snapshot folder. What value should I set in there ?
Is it a folder inside Azure Storage account or in the Distributor SQL Server instance ? In the Distribution wizard, it said, that the details of the folder would also be in the Azure portal. Is there a place where I could get it ?
I am getting a feeling that if I have the correct setting for this, my snapshot replication would work just fine.
Can anybody guide me to find out the problem ?
The MySQL doc for
START TRANSACTION says:
The only isolation level that permits a consistent read is REPEATABLE READ. For all other isolation levels, the WITH CONSISTENT SNAPSHOT clause is ignored. A warning is generated when the WITH CONSISTENT SNAPSHOT clause is ignored.
I’m surprised, as I thought that the
SERIALIZABLE isolation level, being above
REPEATABLE READ, would also offer a consistent read.
WITH CONSISTENT SNAPSHOT from working with
I even found this bug report from 2012 where the doc originally said that this worked with both
REPEATABLE READ and
SERIALIZABLE, and they explicitly changed it to
REPEATABLE READ only, so it looks like there is a technical reason for this. I tested it and MySQL does generate a warning when used with
what soft or utils can help make snapshot of production Ubuntu system? With all utils and parameters. Like TimeShift, but without GUI. So in future when it needed i can simply restore this image with all data and statement.
FLUSH TABLES WITH... command has a few flavors. The manual meagerly describes the differences between:
- FLUSH TABLES WITH READ LOCK
- FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT
- FLUSH TABLES … FOR EXPORT
Am I interpreting the manual correctly that if one needs to perform a disk volume snapshot, such as the EBS Snapshot, the preferred way now is to use the
FLUSH TABLES WITH READ LOCK AND DISABLE CHECKPOINT right before initiating the snapshot?