MySQL partial import

Here’s the situation. You have a dumpfile called mysql.dmp which contains a whole MySQL database dump. However you only need to import one table (or anything, but only a portion of that dumpfile). Because MySQL dumps are simply SQL scripts, we can use sed in order to extract only the portion of the SQL instructions we want.

Let say the name of your table is mytable and the dumpfile containing the whole database is called mysql.dmp:

$ sed -n -e '/CREATE TABLE.*mytable/,/CREATE TABLE/p' mysql.dmp > mytable.dmp

This will copy in the file mytable.dmp what is located between ‘CREATE TABLE mytable’ and the next ‘CREATE TABLE …’ corresponding to the next table.

Then, you can then proceed and adjust the content of the file mytable.dmp which contains the structure of the table mytable, and the data (a list of INSERT) without having to edit a large MySQL dump file. Suppose you only need the table structure, it’s quite easy to remove all the INSERT lines.

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

MySQL punch user creation DDL

#mysql

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... Read more

20 Jul 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. ```sql mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table... Read more

20 Jun 2016 - 1 minute read

Data export/import on MySQL

#database #mysql #import #export

MySQL provides you with a lot of flexibility when it comes to import or export data between databases. Let’s examine a few possibilities to export/import data: To export a whole database The following command will export a whole MySQL database on a sql file: mysqldump -u user DB_NAME > /tmp/file.sql To export a single table The followin... Read more

20 Feb 2014 - less than 1 minute read