MySQL punch user creation DDL

You can get the DDL necessary to duplicate an existing user with the following system command:

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';')
FROM mysql.user
WHERE user<>'' " | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

This might not look like much, but this can prove extremely useful. This method will produce a pure SQL dump of the MySQL grants. All there is left to do is to execute the script on a new server, like so:

mysql -uroot -p -A < MySQLUserGrants.sql

Tags:

Last rowcount in MySQL

#mysql

Suppose you want to count the number of lines returned by the last SQL statement issued. For **select** statements you can use the *FOUND_ROWS* construct: ```sql SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever; SELECT FOUND_ROWS(); ``` This will return the number of rows in the last **select** query (or if the first query ... Read more

20 Sep 2016 - less than 1 minute read

InnoDB table rebuild

#mysql

PROBLEM On a MySQL 5.6 database server, the ibdata1 file includes 5 InnoDB tables in the mysql schema. mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | ... Read more

20 Jun 2016 - 1 minute read