Mysql query result into HTML or XML

mysql can do that for you. Use mysql -H or mysql-X.

mysql generates an HTML table from each query result set if you use the -H (or --html) option mysql creates an XML document from the result of a statement if you use the -X (or --xml) option:

mysql  db_name -H -e "select * from table_name";
mysql  db_name -X -e "select * from table_name";

What is the default root pasword for MySQL 5.7

After you installed MySQL-community-server 5.7 from fresh on linux

grep 'temporary password' /var/log/mysqld.log

Upgrade From MySQL 5.1 to 5.7 checklilst

Find mysql upgrade checklist

1.Dump all databases/schemas from the existing mysql server by using mysqldump command
2.intall/Initialize a new MySQL 5.7 server instance
3.Load the dump file into the new MySQL 5.7 server
4.Run mysql_upgrade (all the system tables upgraded)
5.Keep in mind for mysql users and grants

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Mysql 5.7 password verification plugin (and impacts to PASSWORD() function)

mysql>  SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set, 1 warning (0.00 sec)

Workaround is setting /etc/my.conf using validate-password = off
and reload mysql server

view a list of MySQL users and their privileges

You are running a multi-user MySQL database, some useful commands that show a list of all existing MySQL users
and their privileges. To find out all MySQL users and the permissions granted to each user, log in to your MySQL server,
and run the below MySQL commands.

mysql>select user,host from mysql.user;

+-----------+-------------+
| user      | host        |
+-----------+-------------+
| dbb        | %           |
| root      | 127.0.0.1   |
| dbb        | 192.168.1.% |
| root      | 192.168.1.% |
| dbb        | localhost   |
| mysql.sys | localhost   |
| root      | localhost   |
+-----------+-------------+
7 rows in set (0.00 sec)



mysql>  show grants for 'root'@'192.168.1.%';
+-----------------------------------------------------+
| Grants for root@192.168.1.%                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

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