Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities… MASTER TO to instruct all servers to use GTIDs Disable read-only mode This procedure will switch all your servers from regular… tricky if your application does not easily tolerate downtime or read-only mode, especially if you have a lot of servers to…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… in a master/slave cluster might be: mysql> set global wsrep_provider_options=”gcs.fc_limit=500… have applying cluster transactions. Therefore, it keeps reads more up to date without needing to …. EDIT: actually the cost of certification depends only the size of the transactions, which translates …
Post: How to create/restore a slave using GTID replication in MySQL 5.6
February 8, 2013 By 7 Comments
… is the master and set it on the slave. MySQL keeps two global variables with GTID numbers on it: gtid_executed: it…> set global GTID_EXECUTED=”9a511b7b-7059-11e2-9a24-08002762b8af:1-14″ ERROR 1238 (HY000): Variable ‘gtid_executed’ is a read only variable Error… read only variable. The only way to change it is with reset master (yes, on a slave server): slave1> reset master; slave1 > show global…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… TABLES WITH READ LOCK to temporary make MySQL read only. In many cases the period for which server has to be made read only is very short, just few seconds, yet the impact of FLUSH TABLES WITH READ LOCK can be… flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost | dumptest | Query | 74 | Waiting for global read lock | insert into C…
Post: Percona Replication Manager, a solution for MySQL high availability with replication using Pacemaker
November 29, 2011 By 102 Comments
… demoted master to avoid replication conflicts All slaves are in read_only mode Simple administrative commands can remove master role from a… ms_MySQL p_mysql \ meta master-max=”1″ master-node-max=”1″ clone-max=”3″ clone-node-max=”1″ notify=”true” globally-unique… the read_only setting on the slave so, make sure the application database use doesn’t have the SUPER privilege which overrides read_only…
Post: Can we improve MySQL variable handling ?
February 18, 2012 By 8 Comments
… read it: mysql> set global sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.sort_buffer_size; +—————————+ | @@global…. would be extending INFORMATION_SCHEMA.GLOBAL_VARIABLES Currently as of MySQL 5.5 it contains only variable name and value. Yet…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
…read-only…read and written. The first write query to each table will immediately block against the requested global read lock, and subsequent read…mysql> show processlist; …
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
… need much introduction, especially to the MySQL server developers. The Valgrind Memcheck …is perfect though: the manual is only partially correct in how to…is when we read the waiters field next, the read must be serialized… The locks in question are LOCK_global_system_variables and LOCK_system_…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…READ for read-only SELECT, but it behaves as if you use READ …global variable that affects all sessions and needs a server restart to change its value. Both options cause phantom reads (non repeatable reads…mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…| | wsrep_local_index | 1 | | wsrep_ready | ON | only wsrep_local_index defers …mysql server: [root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL… restart both nodes: set global wsrep_provider_options=”pc.ignore…:42:50.237 INFO: Read config: daemon: 0 address:…
