Helpful MySQL Commands, tips and tricks

I’m pretty much brain dumping now so I apologize if I leave open any questions. Post to the comments if you would like for me to go into further detail on any of these…

I would argue that all of these commands and tips need to be mastered to be an effective SysAdmin DBA.


The configuration formula… The estimated amount of memory that will be allocated to MySQL. This formula should be used when initially configuring a server while still leaving some for the OS (assuming that this is a dedicated MySQL database server).

KBS + IBP + (RBS + SBS + BLCS) * MC = Physical RAM – OS requirements

KBS -> Key Buffer Size
IBP -> InnoDB Buffer Pool size
RBS -> Read Buffer Size
SBS -> Sort Buffer Size
BLCS -> Binlog Cache Size
MC -> Max connections
If a DB server begins to use a lot of SWAP memory, this is very bad. The disk thrashing that is occurring as a result of the SWAP being used has a reputation of bringing the entire db to a halt. I have witnessed this in several environments. If a high transaction MySQL db is using SWAP memory, immediate action should be taken before it gets worse.


When this command is issued, statements won’t be logged to the binlog (or replicated) in this session.

mysql> SET SQL_LOG_BIN = 0;

The skip current slave query statement. BE CAREFUL! Skipping a slave statement without a FULL, in-depth investigation can be VERY damaging and cause inconsistencies that may lead to a full rebuild of that data set. If you’re skipping a statement on the slave be sure that you know what you’re doing!
With that said, this definitely can come in handy if a bad statement gets replicated and breaks a slave in your replication tree.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

These are the commands to start up new slave. I’m assuming that you have the correct data in place. I will have a separate post on how to build a new slave with full details later.

CHANGE MASTER TO MASTER_HOST = '';
CHANGE MASTER TO MASTER_PORT = ;
CHANGE MASTER TO MASTER_USER = '';
CHANGE MASTER TO MASTER_PASSWORD = '';
CHANGE MASTER TO MASTER_LOG_FILE = '';
CHANGE MASTER TO MASTER_LOG_POS = ;

#Optional – using SSL to secure replication

CHANGE MASTER TO MASTER_SSL=1;
CHANGE MASTER TO MASTER_SSL_CA = '/ca-cert.pem';
CHANGE MASTER TO MASTER_SSL_CAPATH = '/ssl/';
CHANGE MASTER TO MASTER_SSL_CERT = '/client-cert.pem';
CHANGE MASTER TO MASTER_SSL_KEY = '/client-key.pem';
START SLAVE;

Issue a mysqldump only of the stored procedures

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt id > triggers.id.sql

Issue a mysqldump only of the table layout and schema (NO DATA)

mysqldump --all-databases --no-data > db_layout.sql

If you have a mysqldump file and want to insert it into a database, you can just pipe it like this:

mysql -h  -u  -p < file.sql

Issue a mysqldump for specific WHERE clause to output.sql file

mysqldump --where="id > 1" > output.sql

Drop partition then immediately re-add it

mysql> ALTER TABLE email_queue DROP PARTITION p01;
mysql> ALTER TABLE email_queue ADD PARTITION (PARTITION p07 VALUES IN (01));

Change the query cache size on the fly

mysql>set global query_cache_size=10*1024*1024;

Then flush the active query cache

mysql> flush query cache;reset query cache;

Decode row based relay logs. I’ll have another post on how to debug row based replication errors. They’re definitely not as straight-forward as statement based replication issues.

mysqlbinlog mysql_server-relay-bin.000005 --base64-output=decode-rows -v > decoded.log

Other Configuration Notes

  • innodb_log_buffer_size -> InnoDB only keeps data round log buggers for ~1 second
  • possibility to decrease disk use dramatically by innodb_flush_method=O_DIRECT and innodb_double_write=OFF
  • innodb_thread_concurrency = twice # of CPU’s. could save some for MyISAM if it’s used
  • innodb_data_file_path -> if not big enough, it could create too many fragments
  • mysqldumpslow slow_query.log -> full report of SQL statements in slow query log
  • sync_binlog = 1 will send the binlogs to slaves more frequently. potential to dramatically improve slave performance
  • OPTIMIZE = defragment
  • ANALYZE = rebuild index (sorting)
  • normalization=deduplication
  • myisam_sort_buffer_size -> not only for myisam. can be set at session, buffer is released when finished
  • max_heap_table_size -> same or more than tmp_table size
  • max_heap_table_size ->if using BLOB or TEXT datatypes, don’t go above 64Mb. start at
  • 16Mb. if no BLOB or TEXT, created_temp_disk_table should be equal to 0
  • ‘heap’ never goes to the SWAP. heap covers memory tables and tmp memory tables
  • key_buffer_size in mysql 5.0 doesn’t use more than 4Gb even if it’s assigned it
  • myisam_sort_buffer_size -> keep # high when doing analyze. can set this on the fly
  • convert TEXT to varchar to avoid tmp tables
  • FLUSH TABLES; will help to decrease the time for innodb to shutdown

 

Leave a Reply

Your email address will not be published.