MySQL commands

Preparation

install MySQL database client apps contains 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 :
  1. How to Enable RPMForge Repository in RHEL/CentOS 7.x/6.x/5.x/4.x
  2. (1)yum install pv for RHEL / CentOS / SL / Fedora Linux; (2) apt-get install pv for Debian / Ubuntu;
• Win : Install (1) Cygwin, (2) search pv & install pv package
• Mac : Install (1) brew install pv to get the latest version of pv package and (2) MySQL database clients from XAMPP. Path of mysql is: /Applications/xampp/xamppfiles/bin/mysql

create the db user

1. create the db user
2. grant the minimum permission: SELECT, LOCK TABLES to the db user. SQL query: GRANT SELECT, LOCK TABLES ON `DATA\_BASE\_NAME`.* TO 'dbuser'@'localhost';
3. 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:
  1. (optional) install pv if the pv was not installed
  2. (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv | gzip -c > database.sql.gz and press Enter
  3. ask to enter password and press Enter

• 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 :
  1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
  2. (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction DATA_BASE_NAME | pv > database.sql and press Enter
  3. enter password and press Enter
• export the *.sql file
  1. 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:
  1. (optional) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux
  2. pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME and press Enter
  3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

• Import *.sql file:
  1. (optional if the .sql file was compressed) unzip data.zip or gzip -d data.gz if the file was compressed by gzip
  2. mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME < data.sql and press Enter
  3. 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:
  1. (optional if the pv was not installed) yum install pv for RHEL / CentOS / SL / Fedora Linux
  2. pv data.sql | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME and press Enter
  3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

• Import ZIP file to MySql directly:
  1. (optional if the 7Zip was not installed) yum install p7zip
  2. 7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME and press Enter
  3. enter password and press Enter

Show the MySQL process list & kill the process

Show the MySQL process list & kill the process (especial for SLOW query command)

1. mysql -h HOST -u ACCOUNT -p
2. And keyin the PASSWORD of your MySQL ACCOUNT. The first character will be changed to mysql> from $ or #
3. mysql> SHOW FULL PROCESSLIST\G to show the process list
4. mysql> kill 101; to kill the process with Id number: 101
5. mysql> exit; to leave the MySQL command.

Troubleshooting of MySQL command

command not found: mysqldump

1. locate the mysqldump command
   • sudo find / -iname mysqldump for Mac or Linux
   • And find /Applications/XAMPP/xamppfiles/bin/mysqldump from XAMPP for Mac
2. 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

ERROR 2002 (HY000): Can't connect to local MySQL server

condition on Windows Cygwin terminal:

$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)

$ mysql -h localhost -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2)

solution:

• -h localhost 變數值改成指定 -h 127.0.0.1

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
• DBAStuff: Mysql Dump and partitions [Last visited: 2016-01-01]