MYISAM_COMPRESSION(1)User Contributed Perl DocumentationMYISAM_COMPRESSION(1) NAME myisam_compression.pl - a simple perl script to (un)compress MyISAM tables SYNOPSIS myisam_compression.pl --options --dbdir=/data/mysql DESCRIPTION This script can compress and uncompress MyISAM tables as you wish. The aim of this script was to make the life of MySQL administrators easier, as the manual procedure for (un)compressing MyISAM tables can be described by the following commands executed in this order: 1. FLUSH TABLES; 2. LOCK TABLE $tablename; 3. ``compress table'' 4. UNLOCK TABLES; 5. FLUSH TABLES; The strong advantage of this program is to selectively include or exclude some databases or tables which you do or do not want to modify. Hence we offer --omit-(table|database) and --table and --db arguments. The program is clever enough to figure out what tables or databases are present on the system and operate on all of them, if no --db or --table was specified. This program *has to* be run locally on the mysql server machine. It requires full privileges to modify the tables, i.e. should be run under user `mysql' and requires sql connection to the mysql daemon with privileges to LOCK/UNLOCK tables and FLUSH table-cache. COMPRESSION The script detemines status of a table using shell command `myisamchk -dvv $dbdir/$db/$table.MYI'. The possible table formats are PACKED (i.e. UNCOMPRESSED fields) and COMPRESSED. If user specified --backup option, it will run compression command with --backup option. Thus, *.BAK files will be created. You have to delete them manually! After the *.MYD file is COMPRESSED, we must regenerate the index file. In this second step the script executes `myisamchk -rq $dbdir/$db/$table.MYI' command. UNCOMPRESSION In principle uncompressing a table means simply running command `myisamchk --unpack $dbdir/$db/$table.MYI'. However, this scripts takes care of flushing table cache and locking table. As the COMPRESSED table is read-only, we lock the table in READ mode, not in exclusive (i.e. WRITE) mode. ARGUMENTS/OPTIONS --compress Turn this program into the compression mode. Specified databases and specified tables present in these will be COMPRESSED. The algorithm will recognize tables already COMPRESSED and will not touch them. --db=$dbname Specify databases we should (un)compress. This option may be specified more then once and can be combined with --omit-database=$dbname2 options. --db is not compatible with --dbs-in-file. If you do specify neither --db nor --dbs-in-file arguments, the program will create a list of databases from a list of subdirectories present in $dbdir. --dbdir=/data/mysql Full path to the directory where databases are stored. That's because we must change our current working directory to that location. Only then we perform (un)compression as requested. --dbs-in-file=$file Filename (might be specified with full path), which contains line by line list of databases, on which this script should perform (un)compression. If --dbs-in-file=- is pecified, list of databases is read from STDIN. --dry-run The program will run as usually, but will not execute any commands which modify tables. --host=$hostname [default: 127.0.0.1] Hostname of the mysql host. Remember that this script can be run only locally, as we need direct access to the tables. But, as we need to connect to mysql port and issue LOCK/UNLOCK/FLUSH commands, we need mysql connection. If you have shut down mysql daemon, then you obviously don't need this script. ;-) Note that this program will take the advantage of ~/.my.cnf file. --initdb=database [default: mysql] Name of a database to which we connect initially. DBI needs a name of a database to which it should connect. DBI somehow cannot connect just to the server, it has to open a database. We won't do anything with this database, unless is listed in a list of database on which we should operate. --omit-database Enumerate a list of databases we should not modify. --omit-table Enumerate a list of tables we should not modify. This option is used to selectively exclude tables, which were specified using --table option or, more importantly, if --table was left out, the algorithm would perform on all tables present in a given database. --omit-table prevents this program from modifying such tables. --password=$password [default: "] Password to be used during sql connection to LOCK/UNLOCK/FLUSH tables. Note that this program will take the advantage of ~/.my.cnf file. --port=$port [default: 3306] Port number where mysqld is listening to our requests. --table=$tablename Enumerate a list of tables, which we shouuld (un)compress. If no --table option is specified, the script will perform (un)compress command on all tables present in a given database. You can combine --table with --omit- table options. The script will ignore those tables specified via --omit- table although they were also specified using --table. --tmpdir=$dir [default: /var/tmp] Specify name of a directory, where temporary files created during compression should be stored. --uncompress Turn this program into the uncompression mode. Specified databases and specified tables present in these will be UNCOMPRESSED. The algorithm will recognize tables already UNCOMPRESSED and will not touch them. --user=$user [default: "] Username to be used during sql connection to LOCK/UNLOCK/FLUSH tables. Note that this program will take the advantage of ~/.my.cnf file. --verbose Verbose output. Specifying this option more times increases verbosity. Maximum is currently 3, although you may specify it more times. 0 If you do not specify --verbose at all, it is set by default to 0. In such mode, the program will output only Info: about status of tables before/after compression pluse Error: messages. There're also Warning: messages displayed, for example when some table is empty, so there's nothing to compress. 1 If you specified --verbose only once on a commandline, you will get default output plus list of tables or databases on which the program will operate in every step. 2 If you specified --verbose twice, you will get extra messages about SQL commands executed. 3 Is used more for debugging, you will get extra some output lines beginning with Debug:. For example, subroutines listing tables will print some more output. EXAMPLES The examples below show the usage based on the --compress argument. Imagine --uncompress yourself having same syntax. myisam_compression.pl --compress --dbdir=/data/mysql --db=db1 Compress all tables present in db1. Read ~/.my.cnf to make SQL connection. myisam_compression.pl --compress --dbdir=/data/mysql --db=db1 --db=db2 --table=table1 Compress table table1 and table2 in db1 and db2. myisam_compression.pl --compress --dbdir=/data/mysql --db=db1 --omit- table=table3 Compress all tables present in db1 except table3. myisam_compression.pl --compress --dbdir=/data/mysql --dbs-in-file=./db- list.txt --omit-database=db2 --omit-table=table3 Compress all tables present in databases listed in db-list.txt, but ignore database db2 and do not compress any table table3, if present. myisam_compression.pl --compress --dbdir=/data/mysql --dbs-in-file=- --omit-database=db2 --omit-table=table3 < ./db-list.txt Compress all tables present in databases read from STDIN, but ignore database db2 and do not compress any table table3, if present. AUTHOR Martin Mokrejs http://www.natur.cuni.cz/~mmokrejs/mysql-tools