MySQL Tips

From Wiki
Jump to: navigation, search

Skip replication error. This will skip the next N events.

stop slave;
SET GLOBAL sql_slave_skip_counter = N;
start slave;

Select based on date range:

select * from comments where create_dt BETWEEN '2012-04-25 00:00:00' AND NOW();

Copying a MySQL databases:

Usually you run mysqldump to create database copy:

$ mysqldump -u user -p db-name > db-name.out

Copy db-name.out file using sftp/ssh to remote MySQL server:

$ scp db-name.out user@remote.box.com:/backup

Restore database at remote server (login over ssh):

$ mysql -u user -p db-name < db-name.out

This can also be done over ssh:

$ mysqldump db-name | ssh user@remote.box.com mysql db-name
$ ssh oldserver mysqldump --opt <db> | mysql <db>

Dumping a single table:

mysqldump --opt db-name <table>

Find size of data used by each storage engine.

SELECT engine, count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC ;

For MB:

SELECT engine, count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024),2),'M') data, concat(round(sum(index_length)/(1024*1024),2),'M') idx, concat(round(sum(data_length+index_length)/(1024*1024),2),'M') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length) DESC ;

Show size of *all* tables in MB:

SELECT table_name AS 'Tables', table_schema, round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES ;

Show size of tables in a specific database:

SET @db = 'database';
SELECT table_name AS 'Tables', table_schema, round(((data_length+index_length)/1024/1024),2) 'Size in MB' FROM information_schema.TABLES where table_schema = @db order by data_length DESC;

Restore single table from SQL dump

If you want to restore a single table for a database you can extract the table info from the database dump using sed.

db="database"
table="tablename"

#Export table from sql file:
sed -n '/-- Table structure for table `'$table'`/,/-- Table/{ /^--.*$/d;p }' $db.sql > ${table}.sql

#Import table to current database
mysql $db < $table.sql

Start 2nd mysql instance

Be *very* careful with this command.

mysqld --no-defaults --datadir=/backup/mysqldatadirbackup --port=3307 --socket=/tmp/mysql_3307.sock &

Once the instance is started you can connect using the socket file.

mysql -S /tmp/mysql_3307.sock

Counting unique values

If you want to count the number of times that a value occurs in a column you can use the GROUP BY and COUNT functions. For example:

SELECT ip,COUNT(*) as count FROM modsec GROUP BY ip ORDER BY count DESC LIMIT 20;