Table of Contents

MySQL Conf

.my.cnf
[client]
host=127.0.0.1
database=db_name
user=db_user
password=db_passwd

MySQL Dump

Dump ALL databases 

mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 --master-data=2 -r $(date '+%y%m%d-%H%M%S')-all-databases.sql --all-databases

Dump database without create db 

mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 -r $(date '+%y%m%d-%H%M%S')-database.sql database

Dump database with create db 

mysqldump -ER --triggers --tz-utc -e --default-character-set=utf8 -r $(date '+%y%m%d-%H%M%S')-database.sql -B database

Edit Dump

Cut single database from dump 

sed -n '/^-- Current Database: `database`/,/^-- Current Database: `/p' all-databases.sql > database.sql

Cut single table from dump 

sed -n '/^-- Table structure for table `licenses`/,/^--Table structure for table /p' database.sql > table.sql

Useful commands

Purge Binlog 

BINLOG_FILE=$(mysql -e "SHOW MASTER STATUS"|tail -n1|awk '{print $1}')
mysql -e "PURGE BINARY LOGS TO '${BINLOG_FILE}'"

DB Size 

mysql -e "SELECT table_schema, SUM(data_length+index_length)/1024/1024 FROM information_schema.TABLES GROUP BY table_schema"

Secure Installation 

mysql -e "UPDATE mysql.user SET Password = PASSWORD('NEWPASSWORD') WHERE User = 'root'"
mysql -e "DROP USER ''@'localhost'"
mysql -e "DROP USER ''@'$(hostname)'"
mysql -e "DROP DATABASE test"
mysql -e "FLUSH PRIVILEGES"