Overview
Install MySQL Server
Debian/Ubuntu
[mitesh@Matrix ~]$ sudo apt-get update && sudo apt-get install -y mysql-serverRedhat/CentOS
[mitesh@Matrix ~]$ sudo yum install -y mysql-serverImportant Files
Configuration File
/etc/mysql/my.cnfThe Main MySQL Configuration File
Log Files
/var/log/mysql/Default Log Directory For MySQL/etc/logrotate.d/mysql-serverLog Rotation Policy For MySQL Logs
MySQL Tricks
Reset MySQL Password
[mitesh@Matrix ~]$ sudo mysqladmin -u root password NEWPASSWORDNOTE!: Add skip-grant-tables in /etc/mysql/my.cnf under the [mysqld] section and restart mysql server.
Create MySQL Database
[mitesh@Matrix ~]$ mysql -u USERNAME -pPASSWORD -e 'create database DB_NAME'Remove MySQL Database
[mitesh@Matrix ~]$ mysql -u USERNAME -pPASSWORD -e 'drop database DB_NAME'Create MySQL USER
[mitesh@Matrix ~]$ create user 'USERNAME'@'localhost' identified by 'PASSWORD';Grant Privileges
[mitesh@Matrix ~]$ grant all privileges on `DB_NAME`.* to 'USERNAME'@'localhost';Grant File Privileges
[mitesh@Matrix ~]$ grant file on . to 'USERNAME'@'localhost';
[mitesh@Matrix ~]$ grant file on *.* to 'USERNAME'@'localhost';Update User Passwords
[mitesh@Matrix ~]$ UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
[mitesh@Matrix ~]$ set password for 'USERNAME'@'localhost' = password('PASSWORD');Check Database Character Sets and Collations
[mitesh@Matrix ~]$ SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;Backup MySQL Database
[mitesh@Matrix ~]$ mysqldump -u USERNAME -h HOSTNAME -pPASSWORD $DB_NAMEBackup All MySQL Database
[mitesh@Matrix ~]$ mysqldump -u USERNAME -h HOSTNAME -pPASSWORD --all-databasesRestore MySQL Database
[mitesh@Matrix ~]$ mysql -u USERNAME -pPASSWORD DB_NAME < Database.sqlExport Specific Tables Only
[mitesh@Matrix ~]$ mysql DB_NAME -u USERNAME -p -e 'show tables like "wp_5%"' | grep -v Tables_in | xargs mysqldump DB_NAME -u root -p > SQLFILE.sql