New answers tagged

0

Have two instances of MySQL on that one server. Each has its own data directory, its own port (3307 is typically used for the second one), each has its own my.cnf and logs. But, since they share the RAM, you need to cut back many tunables relating to RAM. Why? What do you gain? Certainly not HA. You may be able to better use lots of Cores. But you are ...


3

With merge replication, identity ranges must be allocated to the publisher and each subscriber. Identity range management can be either manual or automatic. As you already have found out, when a range is exhausted, a new range must be allocated. With automatic range management, the merge agent takes care of allocating a new range, with manual range ...


0

@Brandon Williams - it's easy to pass this error. Just recreate procedures on the publication database. Execute this on the publication database: EXEC sp_scriptpublicationcustomprocs 'PublicationName'; Take the script and run it on the subscription databases. The errors will be gone.


1

That slave info is in RAM. If you are running MySQL 5.6, run RESET SLAVE ALL; If you are running MySQL 5.5, restart MySQL If you are running MySQL 5.0/5.1, run the following: STOP SLAVE; CHANGE MASTER TO master_host=''; These are the ways to make the SHOW SLAVE STATUS\G disappear from view GIVE IT A TRY !!!


0

One solution proposed in https://forums.aws.amazon.com/thread.jspa?threadID=45258 requires setting backup retention period to zero. Which should mean no automatic backup! But you can still take manual snapshots.


0

If you had made a backup of the old master's pg_xlog content that was not applied on the new master after the point of divergence, as they call it, you can use pg_xlogdump (included in the PG distribution since 9.3) to examine the contents of transaction logs on the old master: $ pg_xlogdump -p <pg_xlog_dir> <segment_file_name> Unfortunately, ...


1

While the specifics will depend on the database system itself this could be handled with pretty much any of the RDBMS platforms out there. Permissions will pretty much handle what you want. The process that puts the data in would need to have insert permissions and all other access would be select only. As far as tracking the modifications - most of the ...


0

How long does the backup take? How much does it "lock up" the Slave? What time of day is the backup done? What time of day are analytics performed? The answers to those questions will tell you "Will the backup interfere too much with the analytics queries?" The answer to that is the answer to your original question.


0

When running this command mysqlbinlog --verbose --base64-output=decode-rows mysql-bin.000004 | tail -n 5 | head -n 1 it gives an error. After investigation we found that last week there was power failure, because of which replication isn't happening. So the workaround is to take a new backup of master and restore the replication.


0

It is a good idea to use a slave for backups, especially if backups cause problems with operations on the master. Reading from the slave shouldn't present any issues - a lot of people do it as a way to lighten the load on the master. Check that the slave is set to read-only and be aware that long-running SELECTs may cause replication lag and possibly ...


0

Not a proper slave because you are not using mysql replication but anyway it's very common to have an slave or more than one to reduce the load in the master server. Make really sure that its only used for read queries.


2

Was query_cache_type OFF (0) in my.cnf, not merely turned off later? (This seems to be critical for having it really off.) Another possible common thread in the bugs -- MyISAM. If you are using MyISAM, you really should convert to InnoDB. There are many reasons for converting. Here are some tips on doing the conversion: ...


0

You'll need relational tables with joins; having 142 tables for what you're suggesting is not using the relational aspect of RDBMS systems to their fullest potential. There are a few patterns and practices it will help to learn, and one of them is a many-to-many relationship, which you will probably need. The basics of the schema design can be seen in this ...


0

IMO you should think about how you are going to use the data when you design the database. You shouldn't be scared of joins, they are a fundamental part of how a relational database works. You say items have attributes which can vary with the type of item. In some applications this could be a reason to split the items into different tables but in this ...


-1

Table name: Categories Columns: category_id number(5) not null - primary key category_desc varchar(100) not null Table name: Items Columns: item_id number(5) not null - primary key item_desc varchar(100) not null item_details varchar(2000) not null item_contact_phone varchar(20) not null etc... Table: item_categories item_id number(5) not null ...


4

I would heavily advise you not make 142 tables, it will be a nightmare. But don't necessarily use JSON, either. Instead, you should have one table containing categories, and another table containing the items. Link items to their respective categories by using a foreign key column. Your items table could have a category_id column, for example. Do not store ...


14

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc. Storing JSON blobs in the database will kill performance when you're performing searches, so I ...


0

Considering all the slaves were in-sync with master while you issued reset-master, it is safe to issue change master to on slaves with newly generated first bin-log.


1

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events). The function code would be something like this: CREATE OR REPLACE FUNCTION synchronize() RETURNS trigger AS $$ ...


0

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format: CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER... That's not specific to replication. It happens even if you would want to restore it to an empty db. ...


0

From looking at SQLServer Transactional Replication, it sounds like Londiste from Skytools3 would work well for this. As I understand it, one of the uses that Skype had for Londiste was to push data from their PL/Proxy OLTP setup into their data warehouse. This was set up to push data from multiple databases that have tables with the same table structure to ...


2

does anyone know if it's possible to use Change Tracking on a replication target? When you say replication target, do you mean subscriber ? Irrespective, of it, CT will work with replication. Things to consider when implementing Change Tracking (CT): Make sure you have retention & clean up of the data in the hidden CT tables. Enable CT on the ...


0

It might be in the manual: Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system ...


1

Which one was built first? How did you copy the data to the other one? Have you ever done OPTIMIZE TABLE or ALTER TABLE on one machine but not the other? ROW or STATEMENT based replication? Same indexes? I ask about the order of the dual-master because I would sort of expect the Master to be smaller. RBR vs SBR impacts how the inserts/updates are ...


1

WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially. Which means you have to have a BASE to start off of before you can apply any WAL Files. If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your ...


2

Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...


2

When you setup a Slave by loading a mysqldump from a Master, there is a strong likelihood that many of the the new Slave's tables will be bigger than the Master because of the order BTREE indexes are being loaded as the corresponding tables are being loaded. Usually that order is dictated by PRIMARY KEYs. If you talking about InnoDB, data and PRIMARY KEY ...


2

From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different. Two potential reasons for this difference is: Schema difference on the master vs slave. Does the slave have utf8 instead of latin1 on a column or three? Does the slave have additional indexes? ...


2

I see at least two options: if you don't need near-real-time replication, but just once a day (or maybe every hour) is enough for you, and the amount of data is small, you can quite simply create a script that will run in each remote location: connect to the local database do a dump of the local data (pg_dump --data-only should be fine) connect to the ...


0

20 commits/second is not very fast. A Master can have any number of Slaves. Those Slaves can be local to the same datacenter or remote, in the other datacenters. You can have "relay" servers that act like a Slave to the Master, but then act like a Master to 'downstream' Slaves. With this kind of topology, you can have an unlimited number of Slaves. (I ...


1

Looking at you config, it seems you are missing log-slave-updates in your my.cnf files. You will need to add this in to both masters my.cnf files and restart them. This tells the slave database to take anything it receives from it's master, and copy from the relay log to the binary log so it can be replicated onwards. e.g. Master 1 Updates a table. It ...


0

Slow ALTER in Master-Master In Master-Master, each server is acting both as a Master and a Slave to the other server. STOP SLAVE -- When you later do START SLAVE, the stuff that was performed on the Master will be applied to the Slave. If you did, say, a big ALTER on a big table, that ALTER will run, taking a long time. This is probably the "downtime" ...


0

Perhaps the GRANTs on the Slave are not consistent with those on the Master? On Master, do SHOW GRANTS FOR 'dbadmin'@'%';. Then apply the grant(s) it gives you to the Slave. Then do START SLAVE;


0

Not sure this is what you are looking for, but I think that the major players in the RDBMS field will add support for JSON the same way they added support for XML a couple of years ago. You can for example have a look at: http://www.ibm.com/developerworks/data/library/techarticle/dm-1306nosqlforjson1/ Another approach is to add some kind of product that ...


2

WordPress WordPress was not designed with scaling in mind. (Caveat: What I say here is based on looking at innards several years ago; things may have changed.) There is no "read-write" separation, so scaling reads via Replication would be a major undertaking. Each "user" has his own database with 13+ tables. For large installations, this leads to OS ...


1

Sounds like you were attempting to perform the delete operation in a single transaction. This being the case, the answer is "Yes, it will most likely break." Even without replication, it's a bad idea to do such a large operation in a single batch. You would be better off looping a smaller number of deletes - it's faster and less of a performance issue. ...


0

You can read more about Apache Phonix https://phoenix.apache.org/ which is Relational DB over HBase (NoSQL) Regards


1

There are 4 possible explanations mentioned in the error message, and a fifth one that isn't mentioned. the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or ...


0

Developers often work on only one machine, and have their whole development environment on that machine. Testing database replication before deploying changes in this kind of a development environment can be a challenging task. In this article, Toptal engineer Ivan Bojovic guides us through a step-by-step tutorial on how to implement MySQL Master-Slave ...


1

Once and for all: The database repair command is potentially harmful and is advised against You should not use repairDatabase for data recovery unless you have no other option. And This command obtains a global write lock and will block other operations until it has completed. Hardly what you want in a production system. I already answered a ...


0

Add new articles in your Publication property window (uncheck the Show only checked articles in the list) right click the same Publication node and go to "View Snapshot Agent Status" click start and just note the log in the same windows which shows this new article is only synced after a short time the new articles will be synced in subscribers without ...


3

Is it possible to have all the distribution tables use In-Memory OLTP (formerly known as Hekaton) for enhancing performance of the distribution database? This is a good question and made me thinking. But .... the answer is NO as of SQL Server 2016 CTP 3.0. The documentation says : Replication tables on the distributor cannot be configured as ...


0

Use a search engine, DuckDuckGo is great. Use following keywords: master slave database replication multi master database replication Personally I use PostgreSQL a lot, so I recommend starting with their docs on replication solutions. Have fun!


0

Some additional information to go along with the other answers that relates to your question (although not to your specific issue). When you can't build a fast refresh-able materialized view and sometimes even when you can but the same values are being updated in the same rows many times, you need a different technique to keep the replication current while ...



Top 50 recent answers are included