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

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