MySQL commands: Difference between revisions
Jump to navigation
Jump to search
Line 5: | Line 5: | ||
* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package | * {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search mysql & install MySQL database clients apps package | ||
* {{Mac}}: Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd | key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql | * {{Mac}}: Install [https://www.apachefriends.org/zh_tw/download.html XAMPP]. Path of {{kbd | key=mysql}} executable file is: /Applications/xampp/xamppfiles/bin/mysql | ||
(optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package | (optional) install the [http://www.ivarch.com/programs/pv.shtml Pipe Viewer] (pv) package | ||
Line 17: | Line 12: | ||
* {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search pv & install pv package | * {{Win}}: Install (1) [https://www.cygwin.com/ Cygwin], (2) search pv & install pv package | ||
* {{Mac}}: 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]. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql | * {{Mac}}: 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]. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql | ||
create the db user | |||
# create the db user | |||
# grant the minimum permission: {{kbd | key=SELECT}}, {{kbd | key=LOCK TABLES}} to the db user. SQL query: {{kbd | key=<nowiki>GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';</nowiki>}}<ref>[http://serverfault.com/questions/22712/minimum-permissions-for-a-user-to-perform-a-mysqldump security - Minimum permissions for a user to perform a mysqldump? - Server Fault]</ref> | |||
# the db user will be able to execute the mysqldump command | |||
=== Exporting data into MySql sql file === | === Exporting data into MySql sql file === |
Revision as of 21:56, 1 January 2016
Preparation
install MySQL database client apps ex: mysql & mysqldump commands
- Linux :
- Win : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
- Mac : Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql
(optional) install the Pipe Viewer (pv) package
- Linux :
- How to Enable RPMForge Repository in RHEL/CentOS 7.x/6.x/5.x/4.x
- (1)yum install pv for RHEL / CentOS / SL / Fedora Linux[1]; (2) apt-get install pv for Debian / Ubuntu[2];
- Win : Install (1) Cygwin, (2) search pv & install pv package
- Mac : Install (1) brew install pv to get the latest version of pv package[3] and (2) MySQL database clients from XAMPP. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql
create the db user
- create the db user
- grant the minimum permission: SELECT, LOCK TABLES to the db user. SQL query: GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';[4]
- the db user will be able to execute the mysqldump command
Exporting data into MySql sql file
- export the compressed *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysqldump[5][6]:
- Bulk export multiple tables into the compressed *.sql files
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
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 [9]:
- (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
- (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv > database.sql and press Enter
- enter password and press Enter
- export the *.sql file
- mysqldump -h localhost -u username -p DATA-BASE-NAME > /path/to/database.sql
Importing data from MySql sql file
Notice: existing database will be overwritten
- 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) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux
- pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [10] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
- Import *.sql file:
- (optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
- mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql [11] and press Enter
- enter password and press Enter
- Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command[12]:
- (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
- pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [13] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
- Import ZIP file to MySql directly:
Show the MySQL process list & kill the process
Show the MySQL process list & kill the process (especial for SLOW query command)[16]
- 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> SHOW FULL PROCESSLIST\G to show the process list
- mysql> kill 101; to kill the process with Id number: 101
- mysql> exit; to leave the MySQL command.
Troubleshooting of MySQL command
command not found: mysqldump
- locate the mysqldump command
- sudo find / -iname mysqldump for Mac or Linux
- And find /Applications/XAMPP/xamppfiles/bin/mysqldump from XAMPP for Mac
- input the complete path of mysqldump command
- old command which caused error mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz
- new command /Applications/XAMPP/xamppfiles/bin/mysqldump -h 127.0.0.1 -u root -p --force --single-transaction DATABASE_NAME | pv | gzip -c > DATABASE_NAME.sql.gz
references
- ↑ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
- ↑ ivarch.com: Pipe Viewer
- ↑ Pipe Viewer (pv) in Mac OSX
- ↑ security - Minimum permissions for a user to perform a mysqldump? - Server Fault
- ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
- ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
- ↑ (if have permission to LOCK TABLES)mysqldump -h localhost -u username -p DATA_BASE_NAME | pv | gzip -c > database.sql.gz (if have no permission to LOCK TABLES)mysqldump -h localhost -u username -p --lock-tables=false DATA_BASE_NAME | pv | gzip -c > database.sql.gz via mysql - Run MySQLDump without Locking Tables - Stack Overflow
- ↑ MySQL Import/Export Progress Bar - Kevin Warrington
- ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
- ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
- ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
- ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
- ↑ database - Importing zipped files in Mysql using command line - Stack Overflow
- ↑ Install 7Zip on CentOS 5.5
- ↑ MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax
further reading
- Forcing a mysqldump to always be nice to CPU and I/O | eosrei.net: using nice or ionice commands
- ionice command is not exists in cygwin