… at the MySQL Performance Blog, we’ve been discussing the several new features that MySQL 5.6 brought: GTID-based replication, InnoDB Fulltext… warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test…
Post: Migrating between MySQL schemas with Percona Xtrabackup
June 10, 2013 By 2 Comments
…lib/mysql/orig_old Make sure that the files are owned by mysql: chown mysql:mysql /var/lib/mysql/orig_old/* For each table, run: ALTER TABLE tblname…name) using –include=”^orig[.]“ (i.e. functional partitioning) Combine with –replicate-rewrite-db Daily backups from production to staging with …
Post: Why MySQL Performance at Low Concurrency is Important
March 27, 2013 By 8 Comments
… to be single-threaded. MySQL replication is single-thread too, and MySQL 5.6 brings some abilities of parallel replication but these apply only to some use cases. Many database maintenance operations such as “alter table” are run in…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… account possible query changes, replication etc. Q: Is it possible to move MyISAM to Innodb through plain ALTER TABLE statements? Yes. This is exactly how it is done in many cases. There is really no gain in dumping table… of specific issues might be making MySQL Replication to work. You might need to inject MySQL 5.0 in between so it will…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… just a slave somewhere (just be careful not to break replication!). mysql> create schema index_analysis; If our index_statistics are collecting… you use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However…
Post: Is your MySQL Application having Busy IO by Oracle Measures ?
January 8, 2012 By 1 Comment
… believe is ability of MySQL to use powerful system (including powerful storage) – Is quite limited with single thread replication as well as… with large table and data sizes in MySQL can be painful, lacking online operations to alter table, defragmentation etc. Yes there are replication based and…
Post: Finding your MySQL High-Availability solution – Replication
November 13, 2009 By 6 Comments
…MySQL, replication. High Availability solution for MySQL: Replication This HA solution is the easiest to implement and to manage. You basically need to setup MySQL replication…that with MySQL, altering a table means recreating the whole table and it is a blocking operations. Altering a large table …
Post: Converting Character Sets
March 17, 2009 By 22 Comments
… TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE… force a reload of data on every server in the replication chain (very time/bandwidth/disk space consuming). Except for Approach… they need to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
… fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough gotchas with replication itself and getting things right… add more tables to be replicated to the slaves. Another issue is of course creating and altering tables. If you have Innodb tables you’re…
