New answers tagged

1

Answer to 2) question - Most probably you had some minor version upgrade for your master 5.5 MySQL server. There is a bug for 5.0 - Bug #50876 and according to my experience, the same bug exists in 5.5


1

By Default cql will convert the table/keyspace/column name in lowercase. If you have table/keyspace/column in uppercase you have to enclose it in double quotes. alter KEYSPACE "HiveMetaStore" with replication = {'class': 'NetworkTopologyStrategy', 'DCassandra1': 1}; Opscenter handled it for you:) CQL UpperCase/lowercase


2

https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html describes the supported variations in table definitions. For one, it appears that this is supported with statement-based replication: When using statement-based replication, a simple rule of thumb to follow is, “If the statement run on the master would also execute ...


0

From your Show slave status \G output, the following things can be understood: Slave_IO_Running: Yes It means, Input Output thread is running - this thread is responsible for downloading data (binlog) from master server to slave server. Slave_SQL_Running: Yes It means, SQL thread is running - this thread is responsible for executing SQL from relay log ...


0

I've done that but I've still sometimes had it prompt to reinitialize the whole thing. The snapshot will only contain the new table that has been added. Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier? Script out ...


2

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates. http://www.postgresql.org/docs/current/static/high-availability.html


0

Use https://github.com/alestic/ec2-consistent-snapshot of your EBS volume using --mysql-master-status-file FILE option. Create a new volume from that snapshot and attach to new instance or another instance you can use to copy to a remote location if needed.. The db server will only be locked for a couple seconds. I just performed this operation on a server ...


1

Typically, you don't. Thinking of your nodes in terms of primary or secondary is the wrong way to approach it. Since standard data bearing nodes should have the same dimensions, it is better to think of them as replica set members, one of which gets elected to primary. Since the drivers are replica set aware and know which the current primary is and you are ...


0

I faced the same issue then looked into Mongos log file and it was saying can't authenticate to hostname as internal user, error: Authentication failed. Please see here: internal user authentication and happens with SecKey file and auth- Enabled. Resolution I opened mongod.conf file and security: authorization: enabled keyFile: ...


0

As for reference, in case somebody bumps here: I've used https://github.com/mrjgreen/db-sync to accomplish similar results. Based on php, dbsync allows you to sync tables/records from host A to host B, including WHERE clauses (for when you need to pass records from one place to another). This directly thru' the command line. I used it to make a gprs based ...


0

For remote replication behind NAT you can use a SSH tunnel so you are not exposing db ports directly to the public network. The replicated traffic is encrypted at that point. Checkout: https://www.digitalocean.com/community/tutorials/how-to-secure-mysql-replication-using-ssh-on-a-vps You could establish a connection from the inside to the remote server or ...


1

Community Wiki answer generated from a question comment by a_horse_with_no_name Create symbolic links on the slave that match the mount points of the tablespaces on the master.


3

tl;dr No. The binlogs contain a set timestamp command before every transaction exactly to prevent this situation. This will only affect the session scope of the SQL thread so running queries meanwhile won't report date 600 seconds in the past. To take a look you can run: $ sudo mysqlbinlog --base64-output=DECODE-ROWS binlog.XXXXX |head -30 #150613 ...


0

I think the answer depends on the SQL skill level of the people who will ultimately be responsible (lose their job) if the data can't be recovered in a DR scenario. Most of the time when DBAs are chit chatting you'll hear horror stories of vendor solutions that didn't work, or stopped working, or weren't monitored. And when the data is lost it's lost. I've ...


3

As for my experience with PostgreSQL it is relatively safe operation unless you have a really big performance impact on that moment. If you have it then it's better to temporary pause writing from all of your clients. I had only one critical case while syncing my master to slave under load and it was caused by OOM killer (yes, you really should COMPLETELY ...


0

I would first try running a below command to see if that fixes the issue. EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1; If not, I will create a publication for a single article and then run the above command again. This should mark all the replication transactions as complete. Once I am able to shrink the ...


1

TLDR: It appears that disabling and re-enabling replication probably fixed the issue: exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false' exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true' I guess this is the equivalent of switching it off and then back on again... ...


1

Restoring the database will break replication, so that's normal. Also most of the other error messages are just follow ons because you haven't been able to remove all the subscriptions (or at least SQL thinks so). You know you have your publisher (the source database), and at least one subscriber (the destination database), and that these are two different ...


1

As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping. I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window. Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance ...


0

You might want to look at the following links: https://bugs.mysql.com/bug.php?id=79272 https://www.percona.com/forums/questions-discussions/mysql-and-percona-server/43922-fatal-error-trying-to-sync-percona-5-7-slave-to-mysql-5-5-master Seems like you need to upgrade to 5.6 prior to replicating to 5.7 from 5.5. Too bad, the documentation is so cryptic " ...


0

Community Wiki answer generated from question comments by Brandon Williams In the tutorial, the repl_distribution account will need to be added to the subscription database as db_owner. In the New Subscription Wizard, on the Distribution Agent Security page, you specify a Process Account, which would be the repl_distribution account that was created on ...


2

Replication Lag is usually measured by finding the difference between the following TIMESTAMP#1 at (Relay_Master_Log_File,Exec_Master_Log_Pos) TIMESTAMP#2 at (Master_Log_File,Read_Master_Log_Pos) From what I am seeing in the SHOW SLAVE STATUS\G you are 98230 seconds (27 hours 17 minutes 10 seconds) behind you have 329 binlogs (278337 - 278008) to ...


1

I can set up the subscription if I add the server name and ip into the hosts file. Not pretty, but it works.


0

You can replicate data from one server to others using Replication feature in MongoDB. The replica set ensures high availability and redundancy of you mongodb data in case if node failure. In your case, you can create a replica set of 3 nodes (PRIMARY, SECONDARY, ARBITER). When one of the server goes down, Secondary would be promoted as PRIMARY which ...


1

Not with the technology you use - this is not what replication is intended to do. A filter is indeed an active filter, also removing objects. You CAN add a specific field for that that you never reset.


0

Finally, I had to change the approach of the solution. In the original server, I have created several triggers over the "private data" table when it changes (in inserts/deletes/updates), which store "insert/update/delete" custom queries in other table. Through a demon (developed in .net), which is launched each 5 minutes, it opens a connection with ...


4

The DBMS can be left to deal with fault detection and recovery (when configured correctly). It would be most unusual to manually implement such behaviour in the application. Indeed, that is what a software stack is for - to remove from applications those features which are common and frequently required, much as an OS looks after memory management and thread ...


0

A VIEW is a shorthand for accessing the underlying table(s). If the table(s) are not there, the VIEW won't work. You can reach into other servers via FEDERATED. See also Spider (a 3rd party product). Each has limitations. Master-Slave is predicated that the Slave always contains an exact copy of the Master. (There are exceptions, but your question does ...


0

NO. Do not attempt to have a single copy of the data with multiple copies of mysqld accessing it. I don't know if that ever worked; if it did, I suspect it has atrophied over the years as changes have been made. Think about it... Each copy of the server has lots of stuff cached in RAM. To coordinate that with other servers would require writing things ...


1

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group. Why? Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the ...


2

Connecting to a replica set Most drivers will show the following behavior, as far as I know If only a single node is given, the driver will simply connect to this node. If the replicaSet option is given in the connection string, the driver will start to explore said replica set to identify all non-hidden members, regardless of the number of nodes given in ...


1

The valid and live members from replica set will automatically detect the arbiter which is in your replica set but not specified in connection. The driver will redirect the request to only Primary and secondaries knowing that arbiter will not serve any requests. It helps in voting in case of failover. However, its always good idea to add all members in ...


-1

Simply put, if the slave can be started up, both contents of master and slave are perfectly same. I'll explain the details. The slave is restored from the base backup which is the physical database back up of the master by replaying WAL log (transaction log). So, the slave is a perfect replica of master and the difference between master and slave never ...


1

Start by splitting your confidential info out of the table. Make a 'parallel' table ('vertical partitioning'). The new table would have the same PRIMARY KEY as the original, so they can easily be JOINed together. To avoid replicating the new table, either use replicate_ignore_wild=... on the slave or put the table in a different database and use ...


4

On a basic level, yes. Just set up the current Master like you would any normal slave database. However, there are some things to consider first. Is the second slave set to READ-ONLY, as you want to avoid writing to both masters at once. You probably want to set auto_increment_increment=2 and auto_increment_offset=1 on the first master, and ...


4

The reason you were able to issue a single skip slave statement and it caught up, is because the SQL thread stopped, but the IO thread continues. As long as you see: Slave_IO_Running: Yes when you run SHOW SLAVE STATUS, then the slave is able to continually pull the binary logs from the master and store them into the relay logs. Regardless of ...


1

Like you found yourself already, the fastest way to copy a complete database within the same DB cluster is CREATE DATABASE with a TEMPLATE. This just copies a whole DB on the file level. CREATE DATABASE master_db_copy TEMPLATE master_db; The original (the "template", master_db in the example) cannot have any concurrent connections to it. You can use ...


0

A sliding time scale is very efficient by using PARTITIONs. Implement 14 hourly partitions using PARTITION BY RANGE(..). Use REORGANIZE PARTITION to create a new partition every hour. Use "transportable tablespaces" (assuming you are using 5.6, or preferably 5.7, and InnoDB) to move the 'old' partition away from the main table to the archive table. More ...


0

if you want the records that have been changed you could use the OUTPUT Clause (Transact-SQL) you can also see an example of this here Regarding your update statement, you definitely need an index on the ID column in both tables. I would even consider creating it as clustered, but I don't know your tables structure and your application. you could for ...


1

for replicating an specific set of data, you can create a view and replicate it. here you can see what data you can publish there is a small detail though: All types of replication allow you to replicate views. The view (and its accompanying index, if it is an indexed view) can be copied to the Subscriber, but the base table must also be ...


2

Transactional replication works well in this scenario for replicating your data to your reporting databases. It's cheaper than availability groups as you can replicate back to your single server instance to create a reporting database, rather than having an availability group where you have to pay for a second licence if you are reading the secondary data. ...



Top 50 recent answers are included