Enable slow query log on MySQL server

Set it temporarily, by running the following commands on mysql console: set global slow_query_log = 1; set global slow_query_log_file = ‘/var/log/mysql-slow.log’; the changes will be undone when mysql is restarted. Set it permanently, by adding the following in “my.cnf” file: slow-query-log=1 slow-query-log-file=/var/log/mysql-slow.log The location of my.cnf varies by OS, but is often found in /etc/my.cnf,

Read More →

Symfony2 Doctrine debug query

Show doctrine constructed query and parameters print $query->getSQL(); foreach ($query->getParameters() as $param) print_r($param);

Mysqldump only tables with certain prefix

Mysqldump only tables with certain prefix: mysqldump DBNAME $(mysql -D DBNAME -Bse “show tables like ‘wp_%'”) > FILENAME.sql

Exporting and Importing Compressed MySQL Databases

This command will dump a MySQL database, compress it and save it to a file (replace userName and databaseName with your data): mysqldump -u userName -p databaseName | gzip > databaseName.sql.gz The import command takes a compressed MySQL dump, decompresses it and adds it to the database (replace userName and databaseName with your data): gzip

Read More →

MySQL show tables from a database

MySQL command to view all tables from a database: SHOW TABLES;

MySQL command to view databases from server

MySQL command to view databases from server: SHOW DATABASES;

MySQL – show user privileges

MySQL command to view user privileges: SHOW GRANTS FOR ‘root’@’localhost’;

MySQL – create user and grant all privileges

Create user with mysql command: CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘password’; Add all privilleges to databases for user: GRANT ALL ON *.* TO ‘username’@’localhost’;

MySQL – import / export database from / into file

Export database into a file with shell command: mysqldump -u USERNAME -p PASSWORD database > filename.ext; Import database from a file with shell command: mysql -u USERNAME -p PASSWORD database < filename.ext;