July 13, 2021 • ––– views
MySQL Linux commands
Overview
This page covers MySQL specific commands using Linux. some of them related to the MySQL CLI, others could be used from another program. You can use it as a reference in case you want to do some task quickly, or you might find something new and beneficial when using MySQL.
Exporting data
Export MySQL database to a directory with mysqldump
-
Full database structure and data
mysqldump -uroot -proot --databases mydb_name \ --skip-comments > /Documents/db.sql -
Specified table structure and data
mysqldump -uroot -proot mydb_name mytable_name > /Documents/db.sql -
A specific table structure and data that matches a where clause
mysqldump -uroot -proot mydb_name --tables mytable_name \ --where="id>5 limit 5000000" > mytable_name.sql
Changing CLI pager
This Changes MySQL CLI pager to less. Using less helps readability when viewing large sets of data from queries in the terminal. Use this after you login into the MySQL shell
pager less -R -S
Using Profiling
In your current MySQL session you can enable profiling to debug and tune queries performance
SET profiling = 1;
select * from table_name where field_name like '%omar%';
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1; ## 1 is the ID from SHOW PROFILES
SHOW PROFILE CPU FOR QUERY 2;