Fix the error “Mysql Server has gone away (error 2006)”?

Server timed out and closed the connection. To fix, check that “wait_timeout” mysql variable in your my.cnf configuration file is large enough

 Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, assumes that something has gone wrong and closes the connection.
 To fix, increase the maximal packet size limit “max_allowed_packet” in my.cnf file,
e.g. set max_allowed_packet = 128M, then  /etc/init.d/mysql restart.

Using SELECT statement find the version of the server you are running and print the name of the current database

 The below MySQL command will show server version and currently selected Database.

 mysql> SELECT VERSION(), DATABASE();

Repair all tables in one-shot

Rather than typing the query repair table ''tablename''; for all the tables one by one.

# mysqlcheck -u root --password=thepass --auto-repair --check --all-databases

Check All Tables in a Database

 To check all the tables in a particular database, don’t specify the table name. Just specify the database name.
 The following example checks all the tables in the db_name database.

#mysqlcheck -c db_name  -u root -p
Enter password:
db_name.JBPM_ACTION                               OK
db_name.JBPM_BYTEARRAY                            OK
db_name.JBPM_BYTEBLOCK                            OK
db_name.JBPM_COMMENT                              OK
db_name.JBPM_DECISIONCONDITIONS                   OK
db_name.JBPM_DELEGATION                           OK
db_name.JBPM_EVENT                                OK


Check a Specific Table in a Database

If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.

The following example checks employee table in db_name database.

# mysqlcheck -c db_name table_name -u root -p
Enter password:
db_name.table_name    OK

Top 10 Mysql Slow Query

MySQL has a nice feature: MySQL slow query log, which allows you to log all queries that exceed a predefined about of time to execute.
Go or define mysql slow query file path and run

mysqldumpslow -s c -t 10 

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.