155
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 '...
36
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 enable ...
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 ...
22
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
...
21
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 ...
19
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 ...
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,
...
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)...
17
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. ...
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
@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
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 ...
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
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
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 ...
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
Version 10 and above
There is a very convenient view for this called pg_hba_file_rules.
table pg_hba_file_rules ;
line_number │ type │ database │ user_name │ address │ netmask │ auth_method │ options │ error
─────────────┼───────────┼───────────────┼─────────────┼───────────┼──────────────────────────────────...
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 ...
12
Using mirroring alone won't be sufficient since the mirrored secondary is not available for querying. You have to create and maintain snapshots, which can be annoying.
Your options are, in no particular order:
Mirroring with snapshots : effective but has management overhead.
Backup and restore : Are you restoring from full backups rather than differential ...
12
Failover - automatic or otherwise - isn't provided directly by PostgreSQL.
You'll need external tools like repmgr.
The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer.
If you do choose to use autofailover, you ...
12
In reality, this DMV (Dynamic Management View) won't be very helpful for a DBA or Developer and is geared to SQL server Product Support and Product Group. You can ignore this DMV.
Hash tables work well when there are a small number of items that fit into a bucket and this is an example of a DMV that support might use to check certain internal structures ...
answered Aug 30 '19 at 14:28
Sean Gallardy - Retired User
20.6k2 gold badges32 silver badges62 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
