Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 29

Displaying posts with tag: slave (reset)

Prewarm your EBS backed EC2 MySQL slaves
+1 Vote Up -0Vote Down

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why.

Most of the clients that we support run on the Amazon cloud using either RDS or running MySQL on plain EC2 instances using (Provisioned IOPS) PIOPS EBS for data storage.

As expected the common architecture is running a master with one or more slaves handling the read traffic.

A common problem is that after the slaves are provisioned (normally created from an EBS snapshot) they lag badly due to slow IO performance.

Unfortunately what tends to be lost in the “speed of provisioning new resources” fetish is some limitations in terms of data persistence layer (EBS).

If you are using EBS and you have created the EBS volume from snapshot or created a new volume you have to pre-warm the EBS volume

  [Read more...]
Cloning a slave using Mysql Enterprise Backup on a GTID enabled server
Employee +0 Vote Up -0Vote Down

MySQL 5.6 introduced a new feature called GTID (Global Transaction IDentifier) support in Replication. For every transaction that is committed on to the server, a GTID of the format :

server_uuid:transaction_id is written into the master's binary log.

This offers the following advantages:

  • Very helpful to set up a slave and create a replication setup.

  • User need not worry about fetching the master's binlog filename and position in the “CHANGE MASTER TO” command which is used to synchronise the slave with the master.

  • Applying GTIDs on slaves ensures consistency – since GTIDs are unique, it cannot be applied more than once on the server.

For a gtid enabled server, the following properties

  [Read more...]
MySQL requires an authoritative master to build slaves
+0 Vote Up -0Vote Down

Read the original article at MySQL requires an authoritative master to build slaves

In MySQL database operations, you often need to rebuild slaves. They fail for a lot of different reasons, fall out of sync or crash. When this happens you may find you need to reclone and start fresh. This is normally done by finding your authoritative master database, and doing a hotbackup. Click through to the [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:
  • Limitations of MySQL row-based replication
  • Why does MySQL replication
  •   [Read more...]
    Automatically restarting MySQL slaves the easy way.
    +2 Vote Up -0Vote Down
    MySQL Replication is something that is used by many, many MySQL users, and here at Recorded Future we are no exception. In our case, the slaves are used for different purposes, and as we develop our system so much and so fast, so sometimes things happen which could have been avoided. Like something getting executed in the master that really should not end up in a slave at all, and which would cause all sorts of problems on the slave.

    Also, some things that work fine on the master can sometimes cause things to break on the slave, a typical such issue is a big operation on the master that when executed on the slave would cause a lock timeout. And you could argue that the lock timeout should be increased, but the question is how much, and frankly, do you really want those locks hanging around? And in some

      [Read more...]
    Memory tuning fast paced ETL
    +3 Vote Up -0Vote Down

    Dear Kettle friends,

    on occasion we need to support environments where not only a lot of data needs to be processed but also in frequent batches.  For example, a new data file with hundreds of thousands of rows arrives in a folder every few seconds.

    In this setting we want to use clustering to use “commodity” computing resources in parallel.  In this blog post I’ll detail how the general architecture would look like and how to tune memory usage in this environment.

    Clustering was first created around the end of 2006.  Back then it looked like this.

    The master

    This is the most important part of our cluster.  It takes care of administrating network configuration and topology.  It also keeps track of the state of dynamically added slave servers.

    The master is started

      [Read more...]
    Slavereadahead 1.3 available
    +1 Vote Up -0Vote Down
    Version 1.3 of Slave read-Ahead is available for download. If you don't know what this tool is about, it is a tool that runs in the background, reads the incoming replication log on a slave and converts INSERT, UPDATE and INSERT ... SELECT statements into SELECT statements and executes these before the statement in question is executed on the server, the idea being that this will "prewarm" the MySQL caches for this date, for example the rows that an UPDATE is affecting will already be in the cache when the UPDATE arrives on the slave. Because of the way replication data is read, this tool only works with MySQL5.5 and up.

    Version 1.3 introduces the auto-reconnect feature. This will reconnect to the MySQL server if the connection fails. To be sure that we restart, all existing connectings are released

      [Read more...]
    Slave Readahead 1.2 available
    +1 Vote Up -0Vote Down
    Version 1.2 of Slave Readahead is now available for download here. If you don't know what this little project is about, it is used to pre-warm the MySQL Cache for the Replication thread on MySQL slaves.

    It is built to support MySQL 5.5 and up only, as it uses some new commands in MySQL (like the SHOW RELAYLOG EVENTS admin command). For more information regarding this little project, either read this blogpost or download the documentation for the project.

    MySQL replication for demanding users
    +2 Vote Up -0Vote Down
    I have been working with MySQL replication for quite a while. I have dealt with simple replication setups and I have experimented with complex ones. Five years ago I wrote an article about advanced MySQL replication, which was mostly a dream on what you could do with imagination and skill, but the matter from that article is still not even remotely ready for production. Yet, since that article, I have been approached by dozens of people who wanted to know how to make the multiple master dream become reality. To all of them, I had to say, "sorry, this is just a proof of concept.Come back in a few years, it may become possible". It still isn't.
    Despite its latest great technological advance, MySQL native replication is is very poor of topologies. What you can do with MySQL native
      [Read more...]
    Setting up Master-Slave Replication with MySQL
    +1 Vote Up -1Vote Down
    Replication enables data from one MySQL server to be replicated on one or more other MySQL servers. Replication is mostly used as scale-out solution. In such a solution, all writes and updates take place on the master server, while reads take place on one or more slaves. This model is actually known as master-slave replication and this is the kind of replication that I will be setting up in this post.
    A first look at delayed replication in MySQL 5.6
    +9 Vote Up -0Vote Down
    If you like fresh features, you should not miss this one. MySQL 5.6.2 includes, among other improvements, the implementation of Time delayed replication, a feature that lets you tell the slave not to apply changes from the master immediately, but to wait N seconds.The feature is documented in WL#344. (There was a manual online as well together with the binaries for MySQL 5.6.0, but they were removed after a few days for a good reason. I am confident that both the manual and some binaries will eventually show up soon).
    Since as of today there are no binaries
      [Read more...]
    Statement-based vs Row-based Replication
    +2 Vote Up -0Vote Down
    Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
    Cache pre-loading on mysqld startup
    +5 Vote Up -0Vote Down
    The following quirky dynamic SQL will scan each index of each table so that they’re loaded into the key_buffer (MyISAM) or innodb_buffer_pool (InnoDB). If you also use the PBXT engine which does have a row cache but no clustered primary key, you could also incorporate some full table scans. To make mysqld execute this on startup, create /var/lib/mysql/initfile.sql and make it be owned by mysql:mysql
    SET SESSION group_concat_max_len=100*1024*1024;
    SELECT GROUP_CONCAT(CONCAT('SELECT COUNT(`',column_name,'`) FROM `',table_schema,'`.`',table_name,'` FORCE INDEX (`',index_name,'`)') SEPARATOR ' UNION ALL ') INTO @sql FROM information_schema.statistics WHERE table_schema NOT IN ('information_schema','mysql') AND seq_in_index = 1;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    SET SESSION group_concat_max_len=@@group_concat_max_len;
    and in my.cnf add a line  [Read more...]
    Setting up slave, stripping indexes and changing engines, on the fly
    +4 Vote Up -0Vote Down

    Warning, the following is quite ugly, but does the job :)

    A while back I needed to create an archive slave database from a half a terabyte myisam master and had space restrictions. I could not dump the db, load it, then drop keys (archive doesn’t support keys apart from a primary key on one column as of 5.1), alter engine etc (would take even longer than it took either way). So an ugly single liner came to mind and worked nicely too.

    mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname --master-data=1 | sed 's/ENGINE=MyISAM/ENGINE=archive/g' | grep -v '^ UNIQUE KEY' | grep -v '^ KEY' | perl -p0777i -e 's/,\n^\)/\n\)/mg' | mysql -uuser -ppassword -h127.0.0.1 -P3307 dbname

    So what is it doing?
    Broken down:
    mysqldump -uuser -ppassword -h127.0.0.1 -P3306 dbname

      [Read more...]
    Lock wait timeout on slaves
    +1 Vote Up -0Vote Down

    We had a one of our slave servers frequently stop replicating with the “Innodb Lock Wait Timeout” error. The slave IO thread would continue to fetch the binlogs while the slave SQL thread kept stopping with  the above mentioned error. The teams initial inclination was to change the innodb lock wait timeout variable from 50 secs to a higher value. It was a read-only slave. Our expectation was there would be no competing writes. Then we started listing what are the next steps possible and what could be wrong.

  • There could be a user with “super” privilege in the system that was running updates directly on the slave
  • A backup script that could be locking the tables out for backup
  • Increase the “innodb lock wait timeout variable
  • Enable the innodb lock monitor
  • While we were

      [Read more...]
    Video: Building a MySQL Slave and Keeping it in Sync
    +5 Vote Up -0Vote Down

    Last night at the Boston MySQL User Group I presented on how to get a consistent snapshot to build a slave, how to use mk-table-checksum to check for differences between masters and slaves on an ongoing basis, and how to use tools such as mk-table-sync and mysqldump to sync the data if there are any discrepancies.

    The slides are online at http://technocation.org/files/doc/slave_sync.pdf.

    The video can be watched on youtube at http://www.youtube.com/watch?v=Un0wqYKmbWY or directly in your browser with the embedded player below:

    Trivia: identify this replication failure
    +0 Vote Up -0Vote Down

    We got good responses to the “identify this query profile” question. Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”. See the responses and my last note on it below the original post.

    Got a new one for you!

    You find a system with broken replication, could be a slave or one in a dual master setup. the IO thread is still running. but the SQL thread is not and the last error is (yes the error string is exactly this, very long – sorry I did not paste this string into the original post – updated later):

    “Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by

      [Read more...]
    MySQL Error: error reconnecting to master
    +1 Vote Up -0Vote Down
    Error message:Slave I/O thread: error reconnecting to masterLast_IO_Error: error connecting to masterDiagnosis:Check that the slave can connect to the master instance, using the following steps:Use ping to check the master is reachable. eg ping master.yourdomain.comUse ping with ip address to check that DNS isn't broken. eg. ping mysql client to connect from slave to master. eg
    MySQL Error: error reconnecting to master
    +0 Vote Up -0Vote Down

    Error message:

    Slave I/O thread: error reconnecting to master
    Last_IO_Error: error connecting to master


    Check that the slave can connect to the master instance, using the following steps:

  • Use ping to check the master is reachable. eg ping master.yourdomain.com
  • Use ping with ip address to check that DNS isn’t broken. eg. ping
  • Use mysql client to connect from slave to master. eg mysql -u repluser -pREPLPASS –host=master.yourdomain.com –port=3306 (substitute whatever port you are connecting to the master on)
  • If all steps work, then check that the repluser (the SLAVE replication user has the REPLICATION SLAVE privilege). eg. show grants for ‘repl’@’slave.yourdomain.com';
  • Resolution:

    • If step 1 and 2 fail, you have a network or firewall

      [Read more...]
    [MySQL] Deleting/Updating Rows Common To 2 Tables – Speed And Slave Lag Considerations
    +1 Vote Up -0Vote Down


    A question I recently saw on Stack Overflow titled Faster way to delete matching [database] rows? prompted me to organize my thoughts and observations on the subject and quickly jot them down here.

    Here is the brief description of the task: say, you have 2 MySQL tables a and b. The tables contain the same type of data, for example log entries. Now you want to delete all or a subset of the entries in table a that exist in table b.

    Solutions Suggested By Others

    DELETE a FROM a INNER JOIN b on a.id=b.id;

    The Problem With  [Read more...]

    Quiz: Enabling an application for MySQL Replication
    +1 Vote Up -1Vote Down

    A little challenge for you… given an existing app that does not know about separate master/slave connections, and you want to enable working in a replicated infrastructure. Simply redirecting all SELECTs to the slave connection will not work. Why?

    Hint: there are at least two reasons, depending on other factors. There may be more.

    Comments are set to be moderated so providing answers will not spoil it for others. I’ll leave it run for a bit and then approve all comments.

    Feature Preview: Multi-threaded Slave
    Employee +0 Vote Up -0Vote Down
    We have just published Andrei's first version of the multi-threaded slave as a preview release.

    Currently, the master produce a load by concurrent multiple client connections while the single slave thread execute replication events one by one. In some scenarios, this causes the slave to lag behind the master.

    With the multi-threaded slave work, the replication slave will scale on multi-core machines.

    This is a very early preview with serious limitations. Even so, please feel free to try it out and let us know what you think.

    On Slave Usage
    +0 Vote Up -0Vote Down

    Slaves can be used for:

  • Horizontal read scalability — take the load off a master database by spreading reads to a replicated slave.

  • Disaster recovery — some disasters, such as a hardware failure, can be solved by having a slave ready to propagate to a master. This technique also works to make offline changes to a master/slave pair without having database downtime (see below).

  • Consistent Backups — without disrupting production usage, a slave can be used to take a consistent backup by stopping the database and copying the database files. This is a free and uncomplicated way to get consistent backups (innodb hot backup is not free, and using a snapshotting tool (such as LVM’s snapshot capability) can be complex. Not everyone wants to manage snapshots.)
  • Be careful when using a slave for more than one purpose. Using a

      [Read more...]
    MySQL: RENAME TABLE on Transactional Tables Can Jeopardize Slave Data
    +0 Vote Up -0Vote Down
    Do you have a master-slave MySQL set up?  Ever do DDL changes on the master?  You may be hit with a serious data integrity bug.  Read on. One of our clients does a regular rename tables on the master to keep the current table small and archive off old data.  We’d occasionally be hit by [...]
    MySQL Slave Lag (Delay) Explained And 7 Ways To Battle It
    +0 Vote Up -0Vote Down

    Slave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series – one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:

    • Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
    • A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a big WHERE clause that doesn't use an index). In this case, the query runs on the master for an
      [Read more...]
    An Interesting Replication Tip
    +0 Vote Up -0Vote Down
    We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.All went well, until I found a peculiar error:Error 'Table 'db101.table101' doesn't exist' on
    Relay binlog corrupt
    +0 Vote Up -0Vote Down

    The slave failed with the error that the relay binlog is corrupt. It had copied close to 12 binlogs from the master and they were yet to be applied. Unfortunately those binlogs have been purged on the master. Now to sync up cleanly we might have to refresh data from the master which can be costly since it was a 290 GB database. We had the option of shutting down the server. We thought we can try our luck with a crazy hack. We shutdown the server. Tried reading the binlog using mysqlbinlog utility from the corrupt position. It failed as expected. Then we tried reading from the next immediate position and it went through fine. Now we had a proof that our hack might work. We opened the relay-log.info and incremented the second row by a value of one. Then we started the server. Boom, the slave started running and we were saved from a great pain of resyncing the slave.

    PS : We might

      [Read more...]
    Stopping the slave exactly at a specified binlog position
    Employee +0 Vote Up -0Vote Down
    Catching up on some articles on the Planet MySQL feed, I just read the post by Dathan on how to promote a slave to be master by using MASTER_POS_WAIT(). The MASTER_POS_WAIT() is an excellent function that allows you to wait until the slave reaches a point at or after the given binlog position. Observe that after the statement issuing a MASTER_POS_WAIT() returns, the slave threads are still running, so this means that even if a STOP SLAVE is issued immediately after the statement with MASTER_POS_WAIT(), it is bound to move a little more before actually stopping. For Dathan's situation, this is not necessary, but wouldn't it be great if you could stop a slave at  [Read more...]
    Why MySQL says the server is not configured as a slave
    +0 Vote Up -0Vote Down

    Is MySQL giving you the error message "ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO" when you try to run START SLAVE? There are a few simple troubleshooting steps to take, but I always forget what to do. This article is to help me remember in the future!

    The invisible I/O thread failures are no more
    Employee +0 Vote Up -0Vote Down
    To get the status of the replication slave, it is possible to check the Last_Error and Last_Errno fields from SHOW SLAVE STATUS. Unfortunately, they only give information about the status of the SQL thread (and not always that either). If the I/O thread fails, for example, because the server configuration is not correctly set up, or if the connection to the master is lost due to a network outage, it is necessary to dig through the error log to find out the reason. This might be possible, although annoying, for a DBA to do since he has access to the files on the machine where the server is running, but when using automatic recovery applications that watch the status of the replication, this is not practical. It is also easier to see the status  [Read more...]
    Showing entries 1 to 29

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.