New answers tagged replication
1
In general, you can safely read from a slave but you cannot safely write to a slave. If you insert data into a table in the slave, the master data will not overwrite it, it will fail to insert due to a duplicate key.
However, with MySQL you can setup a master-master (a.k.a dual master) configuration that will allow both the Trailer and the Office read/write ...
1
If you can afford to lose up to a few hours of configuration data, and the configuration part of the database isn't big, maybe replication isn't quite the right answer for you. As @michael-sqlbot mentioned, replication options have a lot of caveats.
Consider using mysqldump and scripting something up using shell scripts and your server's crontab.
...
1
@RolandoMySQLDBA's answer is accurate, but I would advise against using the replicate-*-table and replicate-*-db options because of the complexity of how MySQL evaluates replication rules.
When these aren't used, everything is replicated, and I would contend that replicating everything the most reliable configuration. As a rule, I never do anything else.
...
0
For zabbix you need replicate all tables, but you don't replicate the history*, the trends* and the events tables.
More about zabbix, http://puppetlinux.blogspot.com.es/search/label/zabbix
0
DISCLAIMER : I know nothing about Zabbix
There are some options for replication you must start using.
Suppose the tables in the zabbix database are as follows:
configdata
sysdata
thisdata
thatdata
someotherdata
I want those five tables to replicate
You want to use the replicate-do-table option
On the Slave, please add the following options to ...
1
Postgres-XC does not run on Windows:
Postgres-XC currently runs on the Linux Operating Systems running on
64-bit Intel(R) processors.
The development team tested this with Cent-OS 5.3 and 5.4.
You might be able to build the data nodes and possibly the coordinators on Windows, but the GTM will take major work to port to Windows.
0
http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/0b8bac44-da87-4115-90f6-b8bf1e1d0bd8
Both cmd values relate to the number of commands written. Since last stats - cmds are all cmds written since the last entry and stats- cmd are an accumulation of all cmds written since the last comment with a state of 1.
2
The output you presented is very confusing. On one hand, the master shows:
mysql-bin.000001 639495
The slave's PROCESSLIST indicates that replication works (the IO thread is connected to the master, the SQL thread is waiting).
The slave's SHOW SLAVE STATUS again claims all is well, but the master's position is 106.
Are all these outputs issued at ...
1
(I'm putting this in an answer, as it's way too long for a comment.)
We have a scenario similar to yours for our bug tracking system. We use it internally, of course, but customers can also submit issues through a page we created on our customer SharePoint site.
What we decided to do was host the database and website only at the office and provide external ...
-3
You didn't tell the slave which DB to replicate: the variable Replicate_Do_DB is empty.
Try to add the line
replicate-do-db=name_of_db (where "name_of_db" is the name of the database to replicate)
in your my.cnf (on slave) and restart the slave-server.
EDIT:
I don't think my answer is the right solution, meanwhile ;-)
Perhaps have a look a this page ...
1
I wrote about something similar on MySQL replication: most important config parameters for performance on slave server?
Basically you should focus on the follow variables on slave (see the other post for more information):
innodb_buffer_pool_size
innodb_log_file_size
innodb_flush_log_at_trx_commit
innodb_doublewrite
innodb_flush_method
...
2
Can you check the article property -->Statement Delivery --> Update delivery format -- what is it set to ?
One more thing to add -- please verify if any column that is part of a unique constraint is updated. If this is true, this means SQL Server is implementing the Update as a Deferred Update, which means as a pair of DELETE/INSERT operations. This ...
1
As per Microsoft,
If you restore a backup of a replicated database to another server or
database, replication settings cannot be preserved. In this case, you
must re-create all publications and subscriptions after backups are
restored.
0
The quickest and dirtiest solution there is has to do with
The binary log format
the SQL statement you must be using
EXAMPLE
DELETE FROM tblname WHERE ... LIMIT 1;
UPDATE tblname SET ... LIMIT 1;
Statements like these do not replicate well and warnings usually spew out. In a MySQL Replication topology, there is no decent way to guarantee the order of ...
3
Only ORACLE and IBM-DB2 are officially supported as subscribers. Refer here and here for more details.
Also, from sql server 2012, this is being depreciated as well.
Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and SSIS.
Caution :
This ...
1
Changing the IP addresses is no problem. You'll just need to force AD replication after the machine comes up with the new IP then do an IPCONFIG /flushdns on the other machines so that they can all see each other. Depending on which machine has it's IP changed and if you are doing push or pull replication you may not even need to do that.
Moving the ...
0
The "Arbitrary Topology" on page 35 seems to be referring to the fact that the masters and slaves can cascade each other in an arbitrary order (a process that is simplified by GTIDs) but each slave subtends only one master... there is no fan-in in MySQL 5.6.
There is, however, such a feature, Multi-Source Replication, in MariaDB 10.0.2 (Alpha) which is ...
2
At present, MySQL does not support a native Multiple Master, Single Slave topology.
The phrase "Arbitrary Topology" is exact what it says. It is a topology of your making.
If you really want to create a Multiple Master, Single Slave topology, you came to the right place.
You will have to construct the topology based on the book
Pages 373-375 has two ...
0
By default, pt-table-checksum displays as result all tables even if there is no error or diffs.
If you want to display just errors and diffs you can add the --quiet to the invocation of pt-table-checksum
Print only the most important information (disables --progress). Specifying this option once causes the tool to print only errors, warnings, and tables ...
2
As your main concern is to have an incremental backup solution, you can change the follow variables:
Changes:
innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.
innodb_doublewrite - Disable the innodb double write
sync_binlog - Disable syn binlog
long_query_time - increase the long query time or ...
1
You can try to comment out these two variables on your slave to use default values - they may cause high disk I/O:
max_binlog_size
sync_binlog
3
The reason for this is pretty straightforward:
if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
In periods of moderate to low traffic, it's easy for the "last thing that ...
1
If you concern is about performance and you don't care about loose a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables:
innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb)
innodb_log_file_size - choise a good value in here to optimize the i/o in your slave
...
0
Well I am not saying we know why this happened, but DDL instruction was properly distributed once we stopped\restarted the SQL service on the publisher ...
4
Here are two really efficient solutions to this, since you're only moving data. These are efficient because they don't actually move data at all: they simply manipulate the metadata to present the data in the desired location. This means not only will they be fast, but the amount of logging required will be minimal.
If you're using Enterprise edition, take ...
0
you can use the BCP and BULK INSERT command and then truncate your source table
1
In SQL Server you can do it this way:
SELECT *
INTO output
FROM source;
TRUNCATE TABLE source;
1
You just need to optimize for InnoDB environment. innodb_buffer_pool_size is still the key for performance.
And beacuse your slave server is weak than master, you might need to deal with replication lag. (i.e. Write to master, but cannot read data from slave immediately)
3
This really depends on your backup strategy. One of the main reasons to keep the binary logs around is to restore your database to a 'point-in-time'. If your database crashes and requires restoration, you would restore the latest full backup, and then play back the binary logs starting with the position of the full backup.
So, if you do a full backup every ...
2
Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.index keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.index are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE ...
0
As we suspected, you are making the changes on 4306 and connecting on default port which is the server running on 3306. use these :
mysql -u user -p --port 4306
3
1. Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.
Many factors that it depends --
On the activity on the primary server which will be the Publisher server. Also, if the distribution agent on the same or different server matters a lot as it will be doing the heavy work of ...
4
1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out.
There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small. One big problem that can become an issue is if you have a local distributor, especially ...
2
DISCLAIMER : Not a user of mha4mysql.
SUGGESTION #1
Hang the Lag Slave off from the Standby Server. How?
Step 01: Stop all Writes to the Master
Step 02: Run SHOW MASTER STATUS; on the StandByMaster
Step 03: Run mysqlbinlog against the binlog from Step 02
Step 04: Acquire timestamp from given position
Step 05: Subtract 43200 sec (12 hrs) from the ...
2
Since we're using streaming replication, writes to the master will no longer go through when the standby fails.
Not necessarily the case. This is only the case when you are setting up synchronous streaming replication. Normally the master will return immediately after it writes its own log files to disk and not wait for the slave. It is then ...
1
The reason for the discrepancy is obvious.
When you run
SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
(SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='proc') B;
you get
mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
-> (SELECT COUNT(1) MySQLProcColumns FROM ...
2
There does not exist a STOP MASTER; command nor is there a manual mechanism from the Master to stop Replication. You would have to go to each Slave and run on of the following:
STOP SLAVE; (Kills the IO Thread and SQL Thread)
STOP SLAVE IO_THREAD; (Kills the IO Thread only)
Running either of these will get you the following:
Clean Recording of the ...
1
The easiest way to do this online would be to revoke all REPLICATION SLAVE privileges on the master for any users who have it.
This offers the additional benefit of allowing you to be selective about which slaves you remove.
Once that's done, check for any existing replication sessions with SHOW PROCESSLIST and kill them off with the KILL command (these ...
Top 50 recent answers are included
