… employees.employees table: mysql [localhost] {msandbox} (employees) > EXPLAIN SELECT * INTO OUTFILE ‘/home/viiin/sandboxes/msb_5_1_58p/outfile.txt’ -> FROM ( -> SELECT * FROM employees -> WHERE… bear, otherwise SELECT .. UNION SELECT INTO OUTFILE should be convenient. Lastly to demonstrate the purpose of this post. mysql [localhost] {msandbox} (employees) > SELECT * -> FROM employees…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… data – what if you run out of space or SELECT INFO OUTFILE fails for other reason and you proceed with deletion you… instead: mysql> rename table rep to rep_maint; Query OK, 0 rows affected (0.01 sec) mysql> select * from rep_maint into outfile ‘/tmp/rep.txt’; Query OK, 0 rows affected (0.01 sec) mysql> create table rep…
Post: Mass killing of MySQL Connections
May 21, 2009 By 30 Comments
… also a way to do it just using MySQL with a few commands: mysql> select concat(‘KILL ‘,id,’;') from information_schema.processlist where… in set (0.00 sec) mysql> select concat(‘KILL ‘,id,’;') from information_schema.processlist where user=’root’ into outfile ‘/tmp/a.txt’; Query OK, 2 rows affected (0.00 sec) mysql> source /tmp/a.txt…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… be done fore replication to work right in MySQL before 5.1. In MySQL 5.1 this as well as few other…: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO OUTFILE does not have to set extra locks. If…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
… it easily runable ? Well just use INTO OUTFILE to create very simple shell script: mysql> select concat(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql”) from tables where engine=’innodb’ into outfile ‘/tmp/dump…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the…: select table_schema, table_name, page_type, count(*) from information_schema.innodb_buffer_pool_content group by 1, 2, 3 into outfile… passive server, with both a mixed workload from my mirrored SELECT statements and with the “pure” replication workload. I totaled the…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… accurate information about table usage I will use Percona Patches: mysql> select * from information_schema.table_statistics where table_schema=’test’ and… can surely use different formula if you like. mysql> select t.table_name,index_length/(select sum(index_length) from information_schema.tables… min 31.68 sec) Pass this via SELECT INTO OUTFILE or pipe it to mysql directly as explained here to create key caches…
Post: Announcing Percona Server 5.5.25a-27.1
July 21, 2012 By Leave a Comment
…. The LOAD DATA INFILE and SELECT INTO OUTFILE statements will fail with the following error: “The MySQL server is running with the –secure… #1000221 (Alexey Kopytov). Server started with skip-innodb crashes on SELECT * FROM INNODB_TABLE_STATS or INNODB_INDEX_STATS. Bug fixed… bzr branch migration. Bug fixed #988383 (Stewart Smith). Fixed upstream mysql bug #60743, typo in cmake/dtrace.cmake that was making…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
…. We got number of tables reporting wrong checksums while running SELECT INTO OUTFILE and diffing these files reported no changes. It turns… being trivial. This was the best choice as with New MySQL version upgrade involves new Operating System and hardware and any… especially makes sense. The team, even if it includes skilled MySQL DBA typically does not need to go through major version…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… log can result in a different order of execution. The MySQL manual actually says this, but not in the clearest way… inserts for CREATE … SELECT or INSERT … SELECT statements. If you use mysqladmin debug, you’ll see an ordinary SELECT gets a lock on… workarounds, though. You can use the old trick of SELECT INTO OUTFILE followed by LOAD DATA INFILE. You can use InnoDB instead…
