Linux commands: Difference between revisions

Jump to navigation Jump to search

Linux commands (edit)

Revision as of 17:22, 3 December 2015

7,516 bytes removed ,  3 December 2015
→‎mysql operation
mNo edit summary
Line 146: Line 146:


== mysql operation ==
== mysql operation ==
=== Exporting data into MySql sql file ===
[[MySQL command]]
* {{Gd}} export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref><ref>[https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program]</ref>:
*# (optional if the {{kbd | key =pv}} was not installed)
*#* {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux<ref>[http://www.cyberciti.biz/open-source/command-line-hacks/pv-command-examples/ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe]</ref>;
*#* {{kbd | key =apt-get install pv}} for Debian / Ubuntu<ref>[http://www.ivarch.com/programs/pv.shtml ivarch.com: Pipe Viewer]</ref>;
*#* Install (1) [https://www.cygwin.com/ Cygwin], (2) pv package and (3) MySQL database clients apps package  for {{Win}}
*#* Install (1) {{kbd | key =brew install pv}} to get the latest version of pv package<ref>[http://ashleyangell.com/2013/11/pipe-viewer-pv-in-mac-osx/ Pipe Viewer (pv) in Mac OSX]</ref> and (2) MySQL database clients from [https://www.apachefriends.org/zh_tw/download.html XAMPP]  for {{Mac}}. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql
*# (for InnoDB tables){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref><ref>(if have permission to LOCK TABLES){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} (if have no permission to LOCK TABLES){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p --lock-tables=false DATA_BASE_NAME | pv | gzip -c > database.sql.gz</nowiki>}} via [http://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables mysql - Run MySQLDump without Locking Tables - Stack Overflow]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# ask to enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
 
 
* Bulk export multiple tables into the compressed *.sql files
<pre>
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction DB_NAME TABLE_2 | pv | gzip -c > table_2.sql.gz
 
</pre>
note: the last line of above part is '''return symbol'''. The last command will not executed automatically without the return symbol.
 
* export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump <ref>[http://dubbs.github.io/blog/2013/09/05/mysql-import-slash-export-progress-bar/ MySQL Import/Export Progress Bar - Kevin Warrington]</ref>:
*# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux
*# (for InnoDB tables){{kbd | key =  <nowiki>mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv > database.sql</nowiki>}}  and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
* export the *.sql file
*# {{kbd | key =  <nowiki>mysqldump -h localhost -u username -p DATA-BASE-NAME > /path/to/database.sql </nowiki>}}
 
=== Importing data from MySql sql file ===
{{exclaim}} Notice: existing database will be overwritten
* {{Gd}} Import the compressed *.sql file with gzip (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command:
*# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux
*# {{kbd | key =  <nowiki> pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://journal.andrewloe.com/2009/05/12/pv-gzip-mysql/ pv + gzip + mysql — W. Andrew Loe III: Journal]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)
 
* Import *.sql file:
*# (optional if the .sql file was compressed) {{kbd | key =unzip data.zip}} or {{kbd | key =gzip -d data.gz}} if the file was compressed by gzip
*# {{kbd | key =  <nowiki>mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
 
* Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command<ref>[http://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange]</ref>:
*# (optional if the {{kbd | key =pv}} was not installed) {{kbd | key =yum install pv}} for RHEL / CentOS / SL / Fedora Linux
*# {{kbd | key =  <nowiki>pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://www.cyberciti.biz/faq/import-mysql-dumpfile-sql-datafile-into-my-database/ Import MySQL Dumpfile, SQL Datafile Into My Database]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }} The console window will show ETA (Estimated Time of Arrival)
 
* Import ZIP file to MySql directly:
*# (optional if the 7Zip was not installed) {{kbd | key =yum install p7zip}}
*# {{kbd | key = <nowiki>7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME</nowiki>}} <ref>[http://stackoverflow.com/questions/11267309/importing-zipped-files-in-mysql-using-command-line database - Importing zipped files in Mysql using command line - Stack Overflow]</ref><ref>[http://needs-be.blogspot.tw/2011/01/install-7zip-on-centos-55.html Install 7Zip on CentOS 5.5]</ref> and press {{kbd | key =  <nowiki>Enter</nowiki> }}
*# enter {{kbd | key =  <nowiki>password</nowiki> }} and press {{kbd | key =  <nowiki>Enter</nowiki> }}
 
=== Show the MySQL process list & kill the process ===
Show the MySQL process list & kill the process (especial for SLOW query command)<ref>[http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax]</ref>
# {{kbd | key=mysql -h HOST -u ACCOUNT -p }}
# And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to ''mysql>'' from ''$'' or ''#''
# mysql> {{kbd | key=SHOW FULL PROCESSLIST\G}} to show the process list
# mysql> {{kbd | key=kill 101;}} to kill the process with Id number: 101
# mysql> {{kbd | key=exit;}} to leave the MySQL command.


== system operation ==
== system operation ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/15851"

Navigation menu