Dumping MySQL Stored Procedures, Functions and Triggers

Mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

Assume we want to backup only the stored procedures and triggers
 the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers),
 then we should run something like:

mysqldump -u root -p'password' --routines  --triggers --no-create-info --no-data  --no-create-db --skip-opt db_name > dbfunction.sql

Mysql slow query log not working

Add this in my.cnf and then restart MySQL server:

general_log = 1
log=/var/log/mysql_slowquery.log

You can check whether the general_log is 'ON' or 'OFF' first using query:

mysql>show variables like '%log%';

You can enable it by below commoand without restart

SET GLOBAL slow_query_log = 'ON';

Mysql change table engine MyISAM to InnoDB

 The easiest way is

ALTER TABLE table_name ENGINE = InnoDB;

InnoDB uses row level locks

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