MySQL user DB does not have password columns - Installing MySQL5.7

In MySQL 5.7, the password field in mysql.user table field was removed, now the field name is 'authentication_string'.
First use the database
mysql>use mysql;
mysql>show tables;
You will find the user table, now let's see its fields:
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

update user set authentication_string=PASSWORD('new_pass') where User='root';

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');