Tag Info

New answers tagged

0

If we are using sql autheticaion for cross domain replication.How the snapshot folder permissions are set .IS it by proxy account


0

The behavior you are seeing is normal for row-based replication. Extra columns are valid at the right-hand "side" of the table, but row-based replication operates at a level, on the slave, where automatic timestamps aren't updated. Statement-based replication has been suggested, but it seems likely to become even more fragile than it already is. ...


1

If they are on the same server, I'd do the table 'copy/replicate' this way: CREATE TABLE detinationDB.new_table like sourceDB.TblName; INSERT INTO detinationDB.new_table SELECT * FROM sourceDB.TblName; RENAME TABLE detinationDB.TblName TO detinationDB.DropMe, detinationDB.new_table TO detinationDB.TblName; DROP TABLE detinationDB.DropMe; This way, you ...


0

I had similar issue, Snapshot that I was trying to apply was in infinite loop. Deleting the record from MSsavedforeignkeys helped. Thanks to Erik.


1

No. However, there are some capabilities supported with log shipping and replication on the primary. https://msdn.microsoft.com/en-us/library/ms151224.aspx As Aaron stated, you could add an additional secondary. If you wanted to use replication to handle subsets of the primary data, or build out a better formatted reporting server (different indexes, ...


1

log-slave-updates=true should be true on both masters. Because if both masters are accepting updates then the other passive master has to be able to update itself. You can take backups of the bin logs whenever you want. As to other options, I use the MIXED option for the binglog_format to allow for ROW based or STATEMENT based updates binlog_format ...


1

I solved this problem by made separate syncs i.e. host1->host2 host2->host3 host3->host1 And, for resurect from "stalled" status, made cron's script that check db status and sync status and activate it.


2

Yes , TDE is in a database level and the encryption is not automatically synced to the subscriber. See : Transparent Data Encryption (TDE) - Transparent Data Encryption and Replication


0

Removed ibdata1? You can* remove iblogfiles but not the ibdata1!! They're system tablespaces storing tables, indexes, metadata, undologs.... *=> Removing iblogs is okay once you stop mysql but not while it's running! Update: Removing iblogs is okay only after shutting down mysql with innodb_fast_shutdown disabled. You might like to review this answer to ...


4

From MSDN: Replication does not automatically replicate data from a TDE-enabled database in an encrypted form. You must separately enable TDE if you want to protect the distribution and subscriber databases. Snapshot replication, as well as the initial distribution of data for transactional and merge replication, can store data in unencrypted ...


0

If you cannot afford more space, another option is to use : START SLAVE .... UNTIL You would execute this command on ALL Slaves which will have them stop at exact same position. You would then check all slave to make sure they all stopped at same position. Then on Master you can run : PURGE BINARY LOGS TO 'mysql-bin.xxx'; PURGE BINARY LOGS BEFORE ...


0

This command may not have run properly on slave while configuring its master CHANGE MASTER TO MASTER_HOST='xx.xx.xx.xx', MASTER_USER='replication', MASTER_PASSWORD='xxxxxxxxxxx', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.00xxxx', MASTER_LOG_POS=xxx


0

See the MySQL documentation or a Step by step replication setup blog if you need to review. If these doesn't help please share the output of show slave status\G (as such it appears connected) and also output of my.cnf of master/slave. Mainly you need to worry about server-ids, replication filters (replicate-to-db/table etc)


0

Master Master should be possible in DR too. Basically what you really have is a Master - Slave with Slave ready to become a master upon failover. So in Location 1: Master Active <--> Macter Passive Location 2: Master Active <--> Macter Passive Master Active in Location 1 should replicate to Master Active in Location 2. Make sure they all have bin ...


1

The first thing I would do is fix the network issue. I would also set up pt-heartbeat to monitor Mysql slave delay. Why do you need to purge the binlog on Master? If you miss the position of a binlog to purge you could affect all your slaves. Mysql allows to set up binlog retentions. If you are running out of space, because your binlog are too large, I ...


0

No way I think. But 1) You could just stop purging if any of the slaves are unreachable(ssh or mysql) from the server where you are running this purging script 2) If the connectivity issue is only with MySQL and not SSH, you could get the positions from master.info files of the slaves Actually I follow this method


1

The easiest method is the following Go to each Slave and run SHOW SLAVE STATUS\G Look for Relay_Master_Log_File on Each Slave Whichever Slave has the oldest Relay_Master_Log_File is the one you purge to on the Master Why Relay_Master_Log_File ? First Look at SHOW SLAVE STATUS\G mysql> show slave status\G *************************** 1. row ...


1

You should rather monitor your slaves for replication lag or if it's actually catching-up / connected with master or not! You may simply write a shell script to look at io_thread and sql_thread values and alert if they're NO. There are already many scripts available for reference. You can use Percona Monitoring Tools's pmp-check-mysql-replication-delay / ...


1

Active-Passive master-master is a good setup but I have seen SUPER (humans)users writing on slave without setting sql_log_bin. (Though super_read_only in 5.7 will change things around this.) Anyways, following is possible and works. ProdActiveMaster<---->ProdPassiveMaster | | | \/ DRmaster----->DRSlave So you know now that another master-master ...


0

1 Stop the slave on the first master mysql> STOP SLAVE; If the second master becomes a read only then the first master should no longer replicate from it. 2 Change the 2nd master's configuration to read only. mysql> SET GLOBAL READ_ONLY=1; Add read_only=1 to my.cnf for the next restart Read Only variable 3 Unlock the tables on the second ...


0

As you already mentioned documentation that you cannot use table level filtering on replication. You might like to try another approach instead. Replicate whole database and change all tables except those you need to use to BLACKHOLE engine. alter table X engine=BLACKHOLE;


0

I have a more direct way to upgrade from MySQL 5.1 to 5.6 The idea is to do the following: Dump the mysql schema from MySQL 5.1 as pure SQL into a grants file Use pt-show-grants Emulate pt-show-grants Dump the data from MySQL 5.1 without the mysql schema into a data file Uninstall MySQL 5.1 Install MySQL 5.6 Load grants file into MySQL 5.6 Load data ...


0

Caveat: There are many details left out of these steps. Read the docs; find more forum entries; etc. Assuming you are starting with A<->B, meaning replication is established in both directions. Also assuming you are currently writing to only A. Plan A, just 2 servers Change to A->B: That is, STOP SLAVE on A. Upgrade in place B to 5.5. (Keep the ...


0

Move server-id=3 from under [mysqld_safe] and put under [mysqld] [mysqld] datadir=/data/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 server-id=3 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #max_connections max_connections=400 symbolic-links=0 #skip-slave-start log-bin=/data/mysql/mysql-bin ...


0

You can use ETL tools like Talend,pentaho,informatica and other tools..my suggestion is that use Talend ETL tool it provides lots of features and its a open source tool.


1

I'm not sure why you are getting that error. I have 2 solutions. The solutions will give you an error if your data truly can't be converted to unsigned bigint. ALTER TABLE `order_item_units` ADD COLUMN `parent_id2` BIGINT(255) UNSIGNED AFTER `parent_id`; UPDATE `order_item_units` SET `parent_id2` = `parent_id`; SELECT * FROM `order_item_units` WHERE ...


0

The tutorial you followed was mine and was the accepted answer. However, the other answer given by AaronBrown had a very strong caveat. Instead of having a single point of failure, now you have three. What if one of the servers goes down? Now replication is broken everywhere: How do you replace it easily and align the binary log positions? ...


0

Having 4 application servers pointed at the same DB is not a problem. You may want to monitor the various metrics such as connection counts on each server. In theory, there may be a few more connections than previously but that is ok. You may also want to start planning on sharding. A cheap and easy way to do this is to have auto increment offsets being ...


0

Found issue with different MySQL server version. Master server was running on MySQL version 5.7.8 and slave MySQL version was 5.6.10. Synced both Master and Slave versions to 5.6.26. Replication is working fine without slave server being stopped.


0

Even this question is too old but as there can be one more scenario so I am posting this answer here so that some can get help from here. Suppose there are bulk DML (insert/update/delete) statements associated with single pointer (normally one statement should be associated with any possition in binlog but due to some faulty script/app by developer I got ...


1

MySQL doesn't reduce/reclaim the size of previously deleted data. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space. Source: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html To verify MySQL replication integrity, you may want ...


1

With the changing of the clock times on the Master there might be loss of data for the slave a slave may replay a query it already processed Rather than risk either of these extremes, you should schedule a brief downtime. Here is what you should do: STEP 01: Stop all writes to the Master SET GLOBAL read_only = 1; FLUSH TABLES WITH READ LOCK; STEP ...


0

There is one thing that cannot work with MySQL Replication from 5.5 to 5.6 : DATETIME. Why ? MySQL 5.6 DATETIME has a very different byte format because it accommodates microseconds. When the binlog_format is ROW, or when MIXED switches from STATEMENT to ROW, only a 5.6 Slave can unpack the row change from the relay logs. A slave running MySQL 5.5 cannot ...


2

Establish SSL connections. Don't worry about the speed -- your 500K-row refresh will take minute(s), with or without SSL.


0

Yes, it's safe. B is an identical copy of A that must always be at or behind it in time, so it is safe to switch replication from B to A. Personally, though, I'd use WAL shipping from A as my fallback. I'd leave streaming replication configured to use B. If it's unreachable, C will try the WAL archives, and it'll find the WAL archives from A and replay from ...


0

You can send rs.stepDown(300) to the replicas on the SSD disks that you want as Secondary. Then they won't get chosen for the next 5 minutes. The replica on the HDD should be Primary when all the others step down. Also make sure that you can read from your secondaries with rs.slaveOK(). In case that your primary has problems, one of your secondaries will ...


0

Your best bet to get some well researched guidance on this topic is to look up "Extract, Transform, and Load" processing, abbreviated ETL. ETL processing has been extensively studied, especially as it relates to data warehouses and data marts. Your reporting database is a little like a data mart. There are tools for this that you can buy and use off the ...


0

You can replicate Transaction to Reporting without any issues are it's "independent" but Reporting to Transactional may cause issues depending on your foreign-key settings. You can easily replicate single table with the help of replication filters (in your case replicate-do-table or replicate-wild-do-table). There are plenty of blogs for replication setup ...


2

You are over-thinking this. Stopping the slave doesn't cause you to lose your binlog position, and changing the replication username and password doesn't, either, as long as that is all you change. On the slave: mysql> STOP SLAVE; This won't stop in the middle of a binlog read. Create the new account in the master, then reconfigure the slave to use ...


0

You can setup replication using SSL. With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and look at the data being sent or received, or even change the data while it is in transit between client and server. When you must move information over a network in a secure ...


1

Seconds_Behind_Master is the difference between the timestamps a statement was most recently downloaded by the IO_THREAD and when it was most recently executed. I'd highly recommend pt-heartbeat for monitoring your replication status. pt-heartbeat is a two-part MySQL and PostgreSQL replication delay monitoring system that measures delay by looking at ...


0

A database in the realm of 1TB, growing by > 300GB/year strongly suggests a backup strategy using rsync. Similarly pgBackRest achieves high efficiency by copying new or modified files. In he scenario you describe I would strongly recommend implementing delayed replication by setting recovery_min_apply_delay on one of the standby servers. This gives you the ...


1

Are you trying to run a command on the Master and have it not replicate to the slave? http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_log_bin SET sql_log_bin = 0; turns off replication of your session.


3

On slave: stop slave; Note down Relay_Master_Log_File & Exec_Master_Log_Pos On Master: Do your changes on master. On slave: Issue "CHANGE MASTER TO MASTER_HOST=..." on slave using above noted co-ordinates (Relay_Master_Log_File & Exec_Master_Log_Pos). Your slave should be okay!


1

Has anything been written to the Master in the last 72K seconds (about a day)? Sometimes this helps: STOP SLAVE; START SLAVE; I have seen the need for this on and off over the last decade; never figured out why it might be needed. Another mystery is that the Seconds_behind_master sometimes mysteriously decides to flap between zero and some large value. ...


2

There are several ways of doing what you're wanting. I would recommend using a specific solution such as WAL-E, barman, or OmniPITR to make the logistics of managing basebackups and WAL archives easier on yourself, or using pg_basebackup. Some of these tools, depending on the versions, will allow you to back up off your replica, which will reduce load on ...


1

The best way would be to enable binary logging on the Slave. Why ? With binary logging enabled on the Slave, each INSERT, UPDATE, DELETE recorded in the binary logs comes with the server_id where the SQL statement originated. What if log-slave-updates was enabled ? If you have log-slave-updates enabled on the Slave, every INSERT, UPDATE, DELETE from the ...


4

in relation to your comment, I suspect a Ops related issue here instead of a SQL Server engine issue. These SAN devices usually work on the block layer and some manage transaction log/data file sync better than others, as well as other areas. You can show the ops team that no, SQL Server doesn't randomly corrupt data like this. You can restore backups ...


0

1) Backups: You can verify if your backup is successful by tailing the mysqldump for "dump completed successfully" to verify the success of a mysqldump. Invalid backup is as-good-as (or as-bad-as) no backup. It's a good idea employ a backup-validation process by restoring it to a provisioned location as possible. Do it. If you want to stick to logical ...


-1

If the data in the reporting database does not have to be real-time, the I suggest you do not implement replication but instead go with log shipping.



Top 50 recent answers are included