MySQL / SQLite

Get all indicies size:

SELECT database_name, table_name, index_name,
round(stat_value*@@innodb_page_size/1024/1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size' AND index_name != 'PRIMARY'
ORDER BY 4 DESC;

use DBNAME;

# select rows older than 6 months, show only 5 entries ('created' is timestamp)
SELECT * FROM `logs` WHERE `created` < DATE_SUB(NOW(), INTERVAL 6 MONTH) LIMIT 5;

# delete rows older than 6 months ('created' is timestamp)
DELETE FROM `logs` WHERE `created` < DATE_SUB(NOW(), INTERVAL 6 MONTH);

# get DATE: now - 6 months
SELECT DATE_SUB(NOW(), INTERVAL 6 MONTH);

# check
SHOW VARIABLES LIKE '%innodb_file_per_table%';

# if ON you can reclaim free space
OPTIMIZE TABLE logs;

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)

CREATE TABLE `users` (
`id` int(7) NOT NULL auto_increment,
`full_name` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`email` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`type` varchar(12) collate utf8_unicode_ci NOT NULL default '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Check tables DB engine:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '<database_name>';

Backup/restore mysql dump:

# create dump
mysqldump -u USER -pPASSWORD DATABASE > dump.sql

# create dump of several tables (not all database)
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > dump_tables.sql

# create dump and archive it on fly
mysqldump -u USER -pPASSWORD DATABASE | gzip > dump.sql.gz

# with timestamp
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +dump.sql.%Y%m%d.%H%M%S.gz`


# IMPORTANT: Assuming you are using InnoDB tables, you should add '--single-transaction' option
mysqldump --skip-lock-tables --single-transaction some_database > dump.sql

# restore from dump
mysql -u USER -p DATABASE < dump.sql

# restore from gz dump
zcat dump.sql.gz | mysql -h HOST -u USER -p DATABASE

New ROOT password (tested on - mysql Ver 14.14 Distrib 5.5.52, for linux2.6 (x86_64) using readline 5.1):

mysqladmin -p -u root password
mysqladmin -u root password NEWPASSWORD

# show slow queries
select * from mysql.slow_log\G

# show databases
mysqlshow -p

# connect to remote DB
mysql -u <user>
          -p                # connect with pass
          -h <ip>

# create DB
CREATE DATABASE users;

CONNECT mysqldb;    # connect to db
USE <dbname>;               # change db

SHOW DATABASES;     # show db

SHOW TABLES FROM <db>;

SHOW COLUMNS FROM <table>;

INSERT users VALUES ("1", "vasya", "email@mail.ru", "admin");

SELECT * FROM <table>;

SELECT host, user, password from mysql.user;        # mysql users

Create user and add privileges:

CREATE DATABASE db_name;

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db_name.* TO 'newuser'@'localhost';                 # db.table
FLUSH PRIVILEGES;


# Allow only select requests. % - means from everywhere
GRANT SELECT ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

# Allow see everything but not change (inluding system info)
GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
# "Reading" the definition of views is the SHOW VIEW privilege.
# "Reading" the list of currently-executing queries by other users is the PROCESS privilege.
# "Reading" the current replication state is the REPLICATION CLIENT privilege.

SHOW GRANTS FOR 'username'@'%';

Delete mysql user:

DROP USER ‘demo’@‘localhost’;

Update table cell:

UPDATE <table> SET <key>='<value>' WHERE <key>='<value>';

Clear mysql command history:

> ~/.mysql_history

Show size of databases:

SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

Show size of all tables:

SELECT  table_schema, table_name, (data_length + index_length + data_free)/1024/1024 AS total_mb, (data_length)/1024/1024 AS data_mb, (index_length)/1024/1024 AS index_mb, (data_free)/1024/1024 AS free_mb, CURDATE() AS today FROM information_schema.tables ORDER BY 3 ASC;

Repair crashed table:

# ERROR 144 - Table 'table_name' is marked as crashed and last (automatic?) repair failed
repair table <table_name>;

Rename table:

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

SQLite

Show all tables:

SELECT name FROM sqlite_master WHERE type='table'

Show column list of the table:

PRAGMA table_info(table_name);