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_name regexp 'query_cache';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

6 rows in set (0.00 sec)

mysql>SHOW GLOBAL STATUS WHERE variable_name REGEXP 'Qcache';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_free_blocks      | 0     |
| Qcache_free_memory      | 0     |
| Qcache_hits             | 0     |
| Qcache_inserts          | 0     |
| Qcache_lowmem_prunes    | 0     |
| Qcache_not_cached       | 0     |
| Qcache_queries_in_cache | 0     |
| Qcache_total_blocks     | 0     |
+-------------------------+-------+
8 rows in set (0.00 sec)

Quickly rebuild indexes

mysql> REPAIR TABLE mytable QUICK;
+—————-+————+—————+—————+
| Table     | Op     | Msg_type | Msg_text |
+—————-+————+—————+—————+
| mytable   | repair | status   | OK       |
+—————-+————+—————+—————+
1 row in set (0.05 sec)

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 version="1.0"?>

<resultset statement="SELECT * FROM test.stories">
  <row>
        <id>1</id>
        <headline>This is a test</headline>
        <tstamp>2005-07-28 00:14:57</tstamp>
  </row>

  <row>
        <id>2</id>
        <headline>This is the second test</headline>
        <tstamp>2005-07-28 00:15:11</tstamp>
  </row>
</resultset>
2 rows in set (0.11 sec)

Mysql user management

# Create user
CREATE USER user IDENTIFIED BY 'password';

# Create and user (if doesn´t exist) and grant permissions
GRANT ALL ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

# Update
UPDATE [table] SET [table_field]=[new_value] WHERE [id]=[id];

# Change user password
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');

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 the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where it was 8 min 20 sec ago.
In same manner, it seems that the Master is processing a lot of queries at the same time.

You look back at the Slave,
run SHOW SLAVE STATUS\G and it says 200 for Seconds_Behind_Master.
How is that number calculated?
Slave's Clock Time (UNIX_TIMESTAMP(NOW()) - TIMESTAMP of the Query
when it was completed and recorded in the Master's Binary Log.

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.cnf (
Find the skip-innodb parameter and either remove it or comment the line by adding # at the beginning of the line:

#skip-innodb
Insert the following parameters into the [mysqld] and [mysqld_safe] sections in /etc/my.cnf:
innodb_buffer_pool_size=16M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

Save the file and start MySQL:
# /etc/init.d/mysql start

In case still innoDB not showing
# /etc/init.d/mysql stop
move ibdata1 and ib_logfile from mysql data directory
# /etc/init.d/mysql start

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> stop slave;

# make slave forget its replication position in the master's binary log
mysql> reset slave;

# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.002045', master_log_pos=1038350378;

# start slave
mysql> start slave;

Backup and restore MySQL databases

Backup and restore MySQL databases
mysqldump -u root -p databasename > database.sql

Restore the mysql database using the mysql monitor
mysql -u  user_root -p db_name < sqldump.sql

Allow remote access to a mysql database

Allow remote access

# mysql -u admin -p
mysql>  GRANT ALL PRIVILEGES ON *.* to 'admin'@'%' IDENTIFIED BY 'abc_password'
mysql>  quit

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;

 The COUNT(*) function returns the number of records in a table:

mysql> SELECT COUNT(*) FROM tiki_user_preferences;