Removing DEFINER from MySQL < 5.7 dump files


You are using the dump files from MySQL (earlier version than 5.7 – that has -skip-definer option in mysqlpump), that outputs the DEFINER keyword in Functions and Views, which causes en error when trying to import in a different database that the users do not exist.


Use the following in your database dump to remove these keywords:


sed -i 's/DEFINER=\S*\sFUNCTION/FUNCTION/' mysql_dump


sed -i '/50013\sDEFINER/d' mysql_dump

Adding missing timestamp column in MySQL table


You have a MySQL table that does not have any timestamp information (creating, updating) and you want to add a column that automatically adds a timestamp every time the record is created.


Add the column for timestamp that creates a timestamp every time a new record is created (INSERT) by using the following:

More information here

Find the table sizes in MySQL database


You would like to have a list with all the tables in an MySQL database, sorted with their sizes (actual table and indices).


You could run the following query to give you a list of all the tables sorted by size:

Take from the answer here

Exporting SQL structure commands from MySQL Workbench


You would like to export the structure of some specific MySQL tables using MySQL Workbench (ie to create same tables in another database).


Go to the table name, right click, select ‘Copy To Clipboard’, then ‘Create Statement’ and finally go to a new file or editor and paste the command from the clipboard.
Just have in mind that you may need to delete the AUTO_INCREMENT from the SQL.
Solution was described here

Using a primary key not named id with Ecto and Mysql


You would like to use a legacy or a database you don’t have control over, with Phoenix, but the primary key of the table it is not named ‘id’.


You can define the primary key using the @primary_key as in:

Taken from here

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement


You want to export a MySQL database table as a csv with something like:

but you get the error that MySQL cannot execute this command.


Find out the location that MySQL can use to export files by running the following:

and then use the path to change the outfile path (ie /var/lib/mysql-files/table_name.csv).

Taken from the answer here