New answers tagged

0

Here are the answers to each of your question 1 How the copying of binary log works? I wrote a detailed list of the steps taken by MySQL Replication in my answer to Is MySQL Replication Affected by a High-Latency Interconnect?. Someone else answered that same post, but from the point of view of DB Client 2 Is it first writes to master binary log ...


0

To answer my own question, the "fix" is to change binlog_format to STATEMENT. It was previously MIXED. Not entirely sure of the reason behind this - MySQL should be intelligent when using MIXED mode about what is replicated as statement and what is replicated as row.


0

Dual-Master, where you write to both masters, is full of problems. You need to carefully set the two auto_increment_* "variables". You need to make sure users cannot insert the same PRIMARY or UNIQUE values into different masters. If 'East' goes down, the Slave is hard to reconnect. If a hurricane hits the East, you lose both the master and slave there. ...


3

We're now looking for a way to monitor the replication; mostly to verify that the slave server is still up-to-date. For monitoring replica lag, there are several ways that give slightly different answers, depending on which version of Postgres you are using. A simple query that can be done directly on the standby is: SELECT (CASE WHEN ...


1

If both the servers showing same server-id, you can set this to some other value and check again. On Master: server-id = 1 On Slave: server-id = 2 Make sure that, while giving change master settings, give the host name as ip address of the master server. This should work, check again and see if the same issue exists. I hope this will help you.


0

I had the same question, and even though I've been a DBA for a while, I haven't really dealt with replication deeply enough to be completely comfortable with it, so I thought the following resources and guides were helpful: This blog, which provided a good outline of the process. It also reminds us that, if you have a large existing publication, and it's ...


0

MHA orchestrates the reconnection of slaves as needed. Orchestrator orchestrates the reconnection of slaves as needed. Galera (PXC, MariaDB) eliminates the need for failover by having all nodes writable, plus automatic repair of nodes that were offline. Fabric is another way. Doing the task yourself is possible, but complex, risky, etc, etc.


0

The error message is the answer. Look at your show binary logs. +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000279 | 1074114047 | | mysql-bin.000280 | 1074004090 | | mysql-bin.000281 | 1074035416 | | mysql-bin.000282 | 1073895128 | There is no mysql-bin.000278 in the display. Unless ...


0

What the number 914166 indicates? The number indicates the row of the table where you are trying to insert data. For example, you are trying to insert some values to 914166th row. Why this error occurs? This error occurs because of duplicate value. You are trying to insert/update 914166 to a table activity as a primary key, but the value 914166 already ...


0

If you have autocommit disabled, you're always in a transaction, which would cause the error you see when trying to manage replication... and could prevent you from seeing recent DML depending on the state of your MVCC read view and isolation level.


1

Did I understand this correctly that your customer has MSSQL Server? Source Database MSSQL or other You have two options: Script your own replication Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration. Source Database Oracle Additionally to the options mentioned in "Source Database MSSQL ...


0

It depends how far behind it fell and what status you have it in your config file at the time of resync. Basically if you've been down so long that it can't catch up with the OpsLog or the OpsLog no longer has the transactions it needs due to it being removed over time you'll want to resync yourself. Check out the docs for your version: Restart the ...


1

OS error 23 is "Data error (cyclic redundancy check)." you can check those by running executing on the command prompt net helpmsg 23, this basically means that you have a serious error on your disk. As the database is running what you need to to is to DONT PANIC Find your backups Script your replication setup Do a tail-of-the-log backup to have a ...


0

I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


1

This issue has been addressed (KB3155209) in SQL Server 2012 SP2 CU12


0

Its never recommended to delete rows from system defined table. Please follow below steps to remove any orphan logreader agent. Create a dummy transactional replication ‘Dummy1’. Add database(select publisher_db from [distribution].[dbo].[MSlogreader_agents]) as publisher and another database(it would be good if you remember the old subscriber) as ...


0

It's better not to skip by hundreds. Just loop one-by-one: until mysql -e "show slave status\G;" | grep -i "Slave_SQL_Running: Yes";do mysql -e "stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;"; sleep 1; done


2

Is it possible to take their suggestions and incorporate them into your main database; or are the additions very client specific? You could ask them for scripts to run post restore. If you go down the replication road then see Chad's answer. Some warnings with transactional replication though are that if your subscriber fails, you might fill your ...


1

You can use Replication Management Objects. You can interrogate them locally or remotely (the better option). The DLLs should be on some systems. If you still have known SQL 2000 instances please test them as it's unclear if modern versions of RMO will still talk down to SQL 2000 instances; 2005 and above should be more reliable. ...


3

I would suggest Transactional Replication. This will be more of a "Real-Time" copy of the data than the one refresh per day that you have now. You can add new tables into the database without affecting the replicated tables.


0

Many thanks to Albe Laurenz from the pgsql-admin mailing list. The server error message means that the client did not send a status update within "wal_sender_timeout" milliseconds, see here. The basebackup needs to complete before this wal_sender_timeout period, else the server resets the connection. Side note, I am running 9.2 so this parameter is called ...


0

One issue with your trigger implementation is that if the reporting DB or DB Server goes down for any reason or stops accepting requests, your prod systems will stop as well. Also if you make schema changes note that you'll need to do that on the reporting table as well. Your solution will work, it just isn't ideal. SSIS won't help with the schema changes ...


0

The answers we got back from one of the senior DBA's at our hosting provider are as follows. I'm sharing in case anyone else runs in to this. It is generally quite critical to have the same settings on the slave as the master. An easy reason recommend this is that you might promote that slave to master some day and do not want surprises. There are ...


1

For trans rep to work one server needs to be able to see the other - and for FTP snapshots to work it has to be that the subscriber can see the publisher and that you set up a pull subscription. A push subscription is set up on the publisher and that's where the jobs run. A pull subscription is set up in the subscriber and that's where (some of) the jobs ...


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 ...



Top 50 recent answers are included