Re: Mysql backup for large databases
Hello, Just one more suggestion to do full backups in large databases: - Dedicated slave (either physical machine, a disk cabinet using iscsi connections from a machine just with a bunch of RAM etc) - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends on your workload) using Percona Toolkit ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. - Make sure the master keeps the necessary logs for a point in time recovery if needed. If something goes wrong you have a full copy of your database, just delayed a few hours. If you want to recover from a drop database (or any big fail executed from MySQL CLI) in the master, you can just sync up your delayed slave to that specific statment - go thru your master binlog, locate the drop database statement and set START SLAVE SQL_THREAD UNTIL master_log_file = 'mysql-bin.XXX', master_log_pos=XXX; to the position right before the bad one. That way you'd have your database as it was just before the wrong statement execution. As Rick said - if you're thinking about doing snapshots, make sure you stop your mysql daemon in the delayed slave to make sure everything is committed to disk. Otherwise can end up having a corrupted DB which won't boot when you need it. And lastly, but probably most importanttest your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian a...@apple.com Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of discrete. Cheers Karen On 01.11.2012, at 8:53, machiel.richa...@gmail.com wrote: Well, the biggest problem we have to answer for the clients is the following: 1. Backup method that doesn't take long and don't impact system 2. Restore needs to be done on a quick as possible way in order to minimize downtime. The one client is running master - master replication with master server in usa, and slave in south africa. They need master backup to be done in the states. Sent via my BlackBerry from Vodacom - let your email find you! -Original Message- From: Reindl Harald h.rei...@thelounge.net Date: Thu, 01 Nov 2012 16:49:45 To: mysql@lists.mysql.commysql@lists.mysql.com Subject: Re: Mysql backup for large databases good luck i would call snapshots on a running system much more dumb than innodb_flush_log_at_trx_commit = 2 on systems with 100% stable power instead waste IOPS on shared storages Am 01.11.2012 16:45, schrieb Singer Wang: Assuming you're not doing dumb stuff like innodb_flush_log_at_tx=0 or 2 and etc, you should be fine. We have been using the trio: flush tables with read lock, xfs_freeze, snapshot for months now without any issues. And we test the backups (we load the backup into a staging once a day, and dev once a week) On Thu, Nov 1, 2012 at 11:41 AM, Reindl Harald h.rei...@thelounge.netmailto: h.rei...@thelounge.net wrote: Why do you need downtime? because mysqld has many buffers in memory and there is no atomic flush buffers in daemon and freeze backend FS short ago there was a guy on this list which had to realize this the hard way with a corrupt slave taken from a snapshot that's why i would ALWAYS do master/slave what means ONE time down (rsync; stop master; rsync; start master) for a small timewindow and after that you can stop the slave, take a 100% consistent backup of it's whole datadir and start the slave
Re: Mysql backup for large databases
Hi... Quite often, they create a replica for the purposes of switching to it immediately if the master completely fails. I like the idea of taking backups on that replica, and not create another replica just for doing backups. One reason why I like it is that if I am to recover a database, I will want to do the following: a) restore the database backup; b) apply binary logs starting from the exact position as of which that backup is a snapshot of my database. If I have to use the binary logs taken on another database (f.e. backups are taken on a replica and binary logs on a master), then I will need to match the contents of one database binary logs with the time the backup was taken, which will make my recovery approximate and error prone. From this, it kind of follows that I will want to set log-slave-updates and, for recovery, use backups of a replica and the binary logs generated by that replica. If the backups are on the third system, and I also have another replica for failovers, I will not only use an additional server and storage, but also my recovery plan will become messy. On a failure of a primary, if I will be switching to a replica, I will have to make it a master to the replica I am taking backups on, which can be done, well, approximately. If I do not want to deal with manually finding a position to synchronize them together, I will end up destroying a second replica for backups and rebuilding it from the replica I just switched to, for which in turn I will have to take another backup. On a failure of primary in which I will be restoring the database from the replica I designated for backups, I will also have to restore the replica I use for failover protection. All this tells me that it will be substantially simpler if the replica for backups and replica for failovers are the same thing. Peace Karen On 02.11.2012, at 0:55, Manuel Arostegui wrote: Hello, Just one more suggestion to do full backups in large databases: - Dedicated slave (either physical machine, a disk cabinet using iscsi connections from a machine just with a bunch of RAM etc) - Get the slave delayed a certain time (ie: 1 hour, 2 hours...depends on your workload) using Percona Toolkit ( http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html) - Make sure that slave has (*log*-*slave*-*updates) *in my.cnf. - Make sure the master keeps the necessary logs for a point in time recovery if needed. If something goes wrong you have a full copy of your database, just delayed a few hours. If you want to recover from a drop database (or any big fail executed from MySQL CLI) in the master, you can just sync up your delayed slave to that specific statment - go thru your master binlog, locate the drop database statement and set START SLAVE SQL_THREAD UNTIL master_log_file = 'mysql-bin.XXX', master_log_pos=XXX; to the position right before the bad one. That way you'd have your database as it was just before the wrong statement execution. As Rick said - if you're thinking about doing snapshots, make sure you stop your mysql daemon in the delayed slave to make sure everything is committed to disk. Otherwise can end up having a corrupted DB which won't boot when you need it. And lastly, but probably most importanttest your backups periodically!! Hope this helps Manuel. 2012/11/1 Karen Abgarian a...@apple.com Hi, For doing backups on the primary database, I know nothing better than have your tables in InnoDB and use Innobackup (or MySQL Enterprise backup). This, however, still has the possibility of hanging as it is using FLUSH TABLES WITH READ LOCK for taking backups of MyISAM tables.One may want to script it to kill the backup if the wait exceeds some threshold. The backup taken this way has incremental backups feature which may reduce the impact. For offloading the backups to a replica, there exist more options because the replica can be frozen and/or shut down. For an InnoDB database, it has to be shut down for taking a consistent backup. If it is not, it will result in cute little inconsistencies unless a DBA is one lucky guy and always wins playing roulette. Combining the two, I like the idea of doing EM backup on a replica and having all tables in InnoDB. After a backup has been taken, it will eventually need to be restored unless someone just likes taking them. For this reason, it will have to be brought to the recovered system. Unless somebody knows in advance when the database would need to be recovered (f.e. it is known that a bad guy always corrupts it on Monday mornings), the backup will need to be available for restore always. These considerations usually imply things like shared filesystems between primary and replica, rejecting backups for recoveries across datacenters and the like. Backing up binary logs allows providing continuous coverage for recovery instead of
RE: MySQL Thread Pool Plugin + Percona Server?
(I'm not using such.) Would you explain what your application is doing that would push the limits of thread concurrency? -Original Message- From: Singer Wang [mailto:w...@singerwang.com] Sent: Wednesday, October 31, 2012 2:08 PM To: mysql@lists.mysql.com Subject: MySQL Thread Pool Plugin + Percona Server? Hey, I'm looking to combine the benefits of the MySQL Thread Pool Plugin and Percona-Server. All the benchmarks show that the Thread Pool Plugin handles higher concurrency much better then just setting innodb_thread_concurrency. Alas Percona server has many tweaks that improve performance. Does anyone have any experience running Percona Server (obviously 5.5) with the Thread Pool Plugin? I got it running but was wondering if there's any caveats.. S
