… slow on larger schemas. One of the downsides to mysqldump is the need to scan the full tables and in turn, load…/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 IMPORT… INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF mysql -N -B <<'EOF' > import-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE;’) AS…
Post: Experiences with the McAfee MySQL Audit Plugin
May 23, 2013 By 4 Comments
… someone more knowledgeable in MySQL internals can enlighten me here. – audit_record_objs : List of database objects (tables, according to the docs) for which you want events written to the log. – audit_whitelist_users : This one is undocumented… audit trail support has been an important missing feature on the server, which has made using MySQL in PCI or SOX compliant…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…concurrency SSB tests when Peter blogged about the importance of performance at low concurrency. The SSB …cold and hot. SSB Flight #4 There is one query in this flight, Q4.3, which …ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the… the `kind_types` table by primary key, the result was a query that took half the time. An important conclusion of my… America and Europe. Join Percona and the MySQL community at our events. The next one is in London on November 11-12…
Post: Percona XtraBackup 2.0.7 for MySQL available for download
May 6, 2013 By 1 Comment
…, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs Fixed… backup with partitioned tables. Bug fixed #1169169. Tables that were dropped between taking a full backup and an incremental one were present in…
Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download
May 7, 2013 By Leave a Comment
…, that can be imported by ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables guide. Bugs Fixed… backup with partitioned tables. Bug fixed #1169169. Tables that were dropped between taking a full backup and an incremental one were present in…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… flow control because it is important to understand. What is flow control? One of the things that people… mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack… into the certification index, which is a hash table. A small fc_limit does however keep the…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… out an important use case for PARTITIONing (“uneven reads”, Re: #1); let me rephrase the use case — * The entire table is too big to be cached, but * One partition is small enough to be cached, and * Your accesses are to that one… single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (Please provide specific references if you…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
…some data from one table. In this case we’re going to delete the salary information from the user 10008: mysql> SELECT * FROM…lib/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld…Full scan 0% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% no In …an understanding how much your queries (at least most important ones) cost you in terms of CPU and IO. …
