Command to optimize all MySQL tables one by one automatically

 use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators


 mysqlcheck -uroot -p --optimize --databases myDatabase

Optimizing a Table

It's simple and straightforward to optimize a table in MySQL

Optimize table TABLE_NAME

Speedly MySQL InnoDB Shutdown

Depending on the size of the databases you have in mysql innodb the time it takes mysql to restart can be very slow.Some tricks that can speed this up


mysql> set global innodb_max_dirty_pages_pct = 0;

You can check the number of dirty pages with the command

mysqladmin ext -i10 | grep dirty

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a built-in function.

mysql> select 1+1;

+-----+

| 1+1 |

+-----+

|   2 |

+-----+

1 row in set (0.00 sec)

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2016-05-03 12:40:41 |

+---------------------+

1 row in set (0.00 sec)

mysql> select now(), 1+2;

+---------------------+-----+

| now()               | 1+2 |

+---------------------+-----+

| 2016-05-03 12:40:55 |   3 |

+---------------------+-----+

1 row in set (0.00 sec)

Recover a MySQL root password

Simple and smart reset mysql root password

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Save the Select Output to a File

Using SELECT INTO, we can save the output of a select command into a file.Instead of displaying the output on the screen

mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee;

We can also store the output into a comma delimited file by specifying the “FIELDS TERMINATED BY”

mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt'   FIELDS TERMINATED BY ',' FROM employee;

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)