Tag Info

Hot answers tagged

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


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!


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


2

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


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


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


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


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


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


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


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


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.


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


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. pt-heartbeat is a two-part MySQL and PostgreSQL replication delay monitoring system that measures delay by looking at actual replicated data. This avoids ...


1

what will be the effect of this on fields of type "timestamp with timezone"? They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will ...



Only top voted, non community-wiki answers of a minimum length are eligible