Wednesday, 27 January 2016

The MySQL Query Cache

›
Query caching is one of the important features in MySQL and a vital part of query optimization mysql>show VARIABLES where variable_nam...
Sunday, 24 January 2016

Quickly rebuild indexes

›
mysql> REPAIR TABLE mytable QUICK; +—————-+————+—————+—————+ | Table     | Op     | Msg_type | Msg_text | +—————-+————+—————+—————+ ...

View query results in XML format

›
You can have MySQL return query results to you in well-formed XML shell>mysql --xml mysql> SELECT * FROM test.stories; <?xml v...
Friday, 22 January 2016

Mysql user management

›
# Create user CREATE USER user IDENTIFIED BY 'password'; # Create and user (if doesn´t exist) and grant permissions GRANT ALL O...
Monday, 18 January 2016

MySQL replication - slave is lagging behind master

›
The Seconds_Behind_Master is just like viewing the past via time travel. Just think like this: The Sun is 93,000,000 miles away from th...
Wednesday, 13 January 2016

Enable InnoDB Engine MySQL Database server

›
ERROR 1286 (42000) at line 1: Unknown table engine 'innodb' Stop MySQL service: # /etc/init.d/mysql stop Open the file /etc/my...
Tuesday, 12 January 2016

Solved MySQL replication after slaves's relay log was corrupted

›
mysql>show slave status\G; Note Relay_Master_Log_File: mysql-bin.002045 Exec_Master_Log_Pos: 1038350378 # stop slave mysql> s...
Tuesday, 29 December 2015

Backup and restore MySQL databases

›
Backup and restore MySQL databases mysqldump -u root -p databasename > database.sql Restore the mysql database using the mysql monito...

Allow remote access to a mysql database

›
Allow remote access # mysql -u admin -p mysql>  GRANT ALL PRIVILEGES ON *.* to 'admin'@'%' IDENTIFIED BY 'abc_pas...
Monday, 28 December 2015

Get record counts for all tables in MySQL database

›
mysql>SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' GROUP BY TABLE_NAME;  Th...