Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.
How to dump an MySQL/InnoDB database
Answer:
To do a point-in-time SQL dump of a InnoDB database, use the mysqldump command with the --single-transaction flag
# mysqldump --master-data=2 --single-transaction huge_db > backup.sql
The binlog position will be written in the backup.sql, which is very useful for database recovery if needed.
How to format MySQL output as HTML
Answer:
If you want MySQL to return query result as HTML format, you can use the -H option
# mysql -H -e 'select now()' -u root
<TABLE BORDER=1><TR><TH>now()</TH></TR><TR><TD>2010-01-16 20:30:45
</TD></TR></TABLE>
How to limit query results scrolling off the screen
Answer:
If you have a query which return a very large result, and you want to use a pager such as less to control, you can use the following command in the mysql client.
mysql> pager less;
PAGER set to 'less'
mysql> select * from very_large_table;
To turn off the pager
mysql> nopager;
PAGER set to stdout
How to execute SQL using command line
Answer:
Use the mysql client and -e option to execute query in shell
# mysql -e 'select now()' -u root -p
+---------------------+
| now() |
+---------------------+
| 2010-01-16 20:22:38 |
+---------------------+
How to execute SQL file with MySQL
Answer:
If you already inside the mysql client, type
mysql> source /tmp/data.sql
Or you can pipe the file to mysql client
cat /tmp/data.sql | mysql -u root
