143
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 ...
35
Error: Last_SQL_Errno: 1594
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry.
This error means that either the master log file is corrupted or the relay log file is corrupted.
Before doing anything backup all your databases, logs, image servers, repeat, several times, and only continue at your own risk.
First run "show ...
32
When a Slave is read-only, it is not 100% shielded from the world.
According to MySQL Documentation on read-only
This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to ...
31
Production ready?
Yes, it's production-ready and widely used. Heroku followers are based on PostgreSQL's built-in async replication for example, as are AWS RDS standbys and read replicas. Streaming replication is used almost universally with PostgreSQL.
Setup of replication isn't exactly lovely, but tools like repmgr help somewhat with that, and it's ...
29
It will be handled immediately on mysql startup.
You do not have to wait for a mysql restart.
First, set expire_logs_days to be 10 in /etc/my.cnf
[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=10
Next, log in to mysql and run this
PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);
Note ...
27
The database name has to be replication as all does not cover replication connections.
host replication postgres 127.0.0.1/0 trust
The PostgreSQL documentation further says:
The value replication specifies that the record matches if a
replication connection is requested (note that replication connections
do not ...
22
AFAIK, running pg_dump on a hot standby is one of the major things standbys are useful for. It's perfectly safe, though it isn't perfectly reliable - the dumps can fail if the standby aborts the transaction when it's falling too far behind the master.
The only thing you really need to watch is to make sure the standby is current and is keeping up. If the ...
22
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 ...
20
With the need of 10TB to be backed up this gets a bit complicated.
Replicas are no replacement for proper backups
While delayed replica set members can provide an relatively easy way to help you with accidental operations, there are no replacement for proper backups, very much like RAID isn't a replacement for file system based backups.
Recommendations
...
19
The Seconds_Behind_Master is really like viewing the past via time travel.
Think of it this way:
The Sun is 93,000,000 miles away from the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where ...
18
It's OK to max out the max_allowed_packet to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?
First you need to know what a MySQL Packet. Page 99 of the Book
explains it in paragraphs 1-3 as follows:
MySQL network communication code was
written under the assumption that
queries are always reasonably short,
...
18
After doing some online search,I found what you needed,hope my answer helps you.
Answer 1
Note:
It Only works with transactional and transactional peer to peer
replication
T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.
Things to be considered before executing the ...
17
Steps to set up the hot standby
First, determine the mount points of the Postgresql data directories and the WAL (Write Ahead Log) directories.
For performance, the $PGDATA and pg_xlog directories should be on separate volumes.
In the examples below, they are defined as follows:
$PGDATA is the "master" cluster, located at /pgdata/9.3/data, at (for example)...
16
According to the MySQL Documentation on SHOW SLAVE STATUS\G
Either SUPER or REPLICATION CLIENT should do it. I would go with the minimum:
GRANT REPLICATION CLIENT ON *.* to user@host;
16
I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
I would like to establish a reporting server (may be 2008 or 2012), that consolidates the rows from selected tables across the 8 source servers into a single instance of those tables on the reporting ...
16
Because like any other optimization, it doesn't fit every workload.
Galera can get overwhelmed by a high rate of transactions, or when transactions update many rows. It can also make your applications experience delays on COMMIT as the cluster is synced up.
Galera doesn't update other nodes synchronously, either. It just transmits worksets synchronously. ...
15
Statement-Based Replication (SBR) will replicate SQL Statements. It is easier to read using mysqlbinlog dump program.
Row based replication (RBR) will replicate actual data changes. Binary logs and relay logs will grow much faster than SBR. You also cannot identify the SQL that generated the changes.
(Please read updated information for MySQL 5.6.2. below)
...
15
@RolandoMySQLDBA has answered the question accurately ... but he also pointed out that his solution was "quick-and-dirty."
And that is a very true statement. :)
The thing that concerns me here is not with that answer, but rather is that the original question appears to make an incorrect assumption:
I can query the two servers and ask which one is a ...
15
This can be a little tricky, but the fact that the MongoDB shell is basically a Javascript interpreter gives us decent options in terms of filtering. Here is the function I use to accomplish this:
// kills long running ops in MongoDB (taking seconds as an arg to define "long")
// attempts to be a bit safer than killing all by excluding replication related ...
15
When you see the Seconds_Behind_Master that high, I look at the following:
Relay_Log_Space: 8179978166
You have 7.6182GB of relay logs to process.
Master_Log_File: mysql-bin.000173
Relay_Master_Log_File: mysql-bin.000093
This tell me that you have read up to mysql-bin.000173, but you are currently processing things from the mysql-bin.000093.
This also ...
14
As long as you do not perform any INSERT/UPDATE/DELETE statements directly on the Slave, your Slave should be just fine. Otherwise, MySQL Replication could break if you INSERT an new row in mydb.mytable on the Slave and, via Replication, the Slave later detects an INSERT of a row to mydb.mytable with the same PRIMARY KEY. This produces error 1062 (Duplicate ...
14
Rolando told you how to do it and his answer is excellent. The truth is, circular replication is an awful, terrible, terrible idea. Don't do it. Ever. "Absolutes are never true," but in this case, it's pretty close.
Instead of having a single point of failure, now you have three.
What if one of the servers goes down? Now replication is broken ...
14
If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this.
Streaming replication reads changes at the block level from the write-ahead logs, so the ...
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 ...
13
You can add the article through SSMS using the GUI and even apply filters to it. As long as you do not change any of the other properties of the article you will not need to generate a full snapshot.
When you hit OK in the publication GUI (after adding the article), it will close without prompting to reinitialize - if it does prompt to reinitialize, then ...
13
The easy, albeit a bit unsecure way
Stop the first secondary
Delete the content of it's dbpath
Restart the secondary
Wait for it to catch up with the primary
Repeat process with the second secondary
This is a bit unsecure as it is unknown why the secondaries entered the Recovering state.
The more secure, but also more intrusive way
As above, but stop ...
12
SLAVE
If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit to /etc/my/.cnf on every Slave
[mysqld]
relay_log_space_limit=4G
and restart mysql
If you cannot set this, at least you should have ...
12
We do backup on standby, it is perfectly fine.
To avoid cancelled statement conflict during backup on standby system, you need to pause replication on standby using SELECT pg_xlog_replay_pause();, then run your backup, once it is finished run SELECT pg_xlog_replay_resume(); to resume replication. Keep in mind that running above commands will cause recovery ...
12
There are two approaches you can try with no or minimal downtime
Given the following:
Master IP is 10.1.20.30
Slave IP is 10.1.20.40
APPROACH #1 : Data is 100% InnoDB
This is very straightforward.
STEP01) If the Master does not have server-id defined in my.cnf you will have to add it
[mysqld]
server-id=100
STEP02) If the Master does not have log-bin ...
Only top voted, non community-wiki answers of a minimum length are eligible
