… identifier. We will need the table id to find indexes of the table. mysql> select * from SYS_TABLES WHERE NAME=’sakila/actor’; +————–+—–+——–+——+——–+———+————–+——-+ | NAME…/lib/mysql/ibdata1 3. Recover SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS from indexes 0-1, 0-3, 0-2 and 0-4…> 4. Load dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE constraints_parser generates mysql>LOAD DATA INFILE ‘/path/to/SYS…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
…PRIMARY N_FIELDS: 1 TYPE: 3 SPACE: 15 PAGE_NO: 3 *************************** 2. row *************************** TABLE_ID: … TYPE: 0 SPACE: 15 PAGE_NO: 4 2 rows in set (0.00 …to be sure ibdata1 has valid checksums: #./innochecksum /var/lib/mysql/ibdata1 # 5. Now you can start MySQL and take a dump from…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…size : 4096 KB physical id : 1 siblings : 4 core id : 3 cpu cores : 4 fpu : yes fpu_exception : yes cpuid level :… addition, at the tail it has information on core dumps and so on. It’s possible that it has…’t do to just assume there’s a single instance in /var/lib/mysql and it’s reading from /etc/my…
Post: MySQL Installation and upgrade scripts.
May 21, 2007 By 3 Comments
…recently. In MySQL 3.22->3.23 upgrades or 3.23->4.0 upgrades …dump your data and reload. Little tip to mention – upgrading to the new major version make sure you upgrade to…to Upgrade RedHat RPM binaries to MySQL Community RPM. Of course you may argue MySQL is not responsible for upgrade process from…
Post: InnoDB in self-compiled MySQL 5.1
September 13, 2007 By 9 Comments
…to compile MySQL from…to/mysql make make install and then, for example, load the dump of InnoDB from previous version: mysql testdatabase < dump…
Post: How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
… in this example we used MySQL binary log file which …to compress all data you can read from single drive. The file decompresses in 3.7 seconds which gives 105MB/sec read speed from…298 seconds which is just 3.4 MB/sec though compressed …it takes longer to dump and much longer to load to the database …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…3); Query OK, 4194304 rows affected (3 min 4.87 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SHOW PROFILE; +——————————+————+ | Status | Duration | +——————————+————+ . . . | copy to… restoring the data from a dump would actually make …
Comment: Upgrading MySQL
January 5, 2010 By 24 Comments
… retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a… regards to unique indexes. “If you perform a binary (in-place) upgrade without dumping and reloading tables, you cannot upgrade directly from MySQL 4.1 to 5.1. This occurs due to an incompatible change in…
Comment: Slow Query Log analyzes tools
June 10, 2008 By 84 Comments
…mysql from source ourselves anyway, so this is still a fair performance test). Get the database on it current up to…to be benchmarked. It’s worth taking a database dump here, in case you want to… to unix epoch – makes date arithmetic dead easy. $datetime = Time::Local::timelocal($6, $5, $4, $3, …
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…date from 2012. We considered a few ideas to try to diagnose the issue, one of which being to try to force MySQL to …with the 0024-06-21 date. I then proceeded to dump a few thousand random datetimes into this table …| 2 | 0024-06-21 10:35:55 | | 3 | 0024-06-21 10:35:55 | | 4 | 0024-06-21 10:35:55 | …
