MySQL Basics

Install MySQL Server

Debian/Ubuntu

[mitesh@Matrix ~]$ sudo apt-get update && sudo apt-get install -y mysql-server

Redhat/CentOS

[mitesh@Matrix ~]$ sudo yum install -y mysql-server

Important Files

Configuration File

• /etc/mysql/my.cnf The Main MySQL Configuration File

Log Files

• /var/log/mysql/ Default Log Directory For MySQL
• /etc/logrotate.d/mysql-server Log Rotation Policy For MySQL Logs

MySQL Tricks

Reset MySQL Password

[mitesh@Matrix ~]$ sudo mysqladmin -u root password NEWPASSWORD

NOTE!: 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');

Backup MySQL Database

[mitesh@Matrix ~]$ mysqldump -u USERNAME -h HOSTNAME -pPASSWORD $DB_NAME

Backup All MySQL Database

[mitesh@Matrix ~]$ mysqldump -u USERNAME -h HOSTNAME -pPASSWORD --all-databases

Restore MySQL Database

[mitesh@Matrix ~]$ mysql -u USERNAME -pPASSWORD DB_NAME < Database.sql

Export 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