MySQL commands

From LemonWiki共筆
Revision as of 14:06, 4 January 2017 by Planetoid (talk | contribs) (→‎Troubleshooting of MySQL: ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.)
Jump to navigation Jump to search

Preparation

install MySQL database client apps contains mysql & mysqldump commands

  • Linux Os linux.png :
  • Win Os windows.png : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
  • Mac icon_os_mac.png : Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql

(optional) install the Pipe Viewer (pv) package

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';[4]
  3. the db user will be able to execute the mysqldump command

Exporting data of database/table into MySql sql file

Exporting entire data

method1: Good.gif 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][7]:

  1. (optional) install pv if the pv was not installed
  2. (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.gz [8][9] and press Enter
  3. ask to enter password and press Enter

optional: Append the Today's date to file name

  • On Linux Os linux.png : (for InnoDB tables)mysqldump -h localhost -u username -p --force --single-transaction --default-character-set=utf8 DATA_BASE_NAME | pv | gzip -c > database.sql.$(date +"%y%m%d").gz

method2: Bulk export individual tables into the multiple compressed *.sql files 

mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 DB_NAME TABLE_1 | pv | gzip -c > table_1.sql.gz
mysqldump -h localhost -u username -pPASSWORD --force --single-transaction --default-character-set=utf8 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 be executed automatically without the return symbol.

method3: export the *.sql file. It will show a progress bar and estimated time to complete the mysqldump [10]:

  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 --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql and press Enter
  3. enter password and press Enter

method4: export the *.sql file

  1. mysqldump -h localhost -u username -p --single-transaction --default-character-set=utf8 DATA-BASE-NAME > /path/to/database.sql

method5: Using phpmyadmin to export the database. Icon_exclaim.gif But I found the number rows of table after imported was not the same with the number rows of original table once.

Exporting selected data

mysqldump - Append MYSQL dump to a table - Stack Overflow [Last visited: 2016-06-29]

Importing data from MySql sql file

Icon_exclaim.gif Notice: existing database will be overwritten

method 1: Good.gif 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. Check if the file is gzip format (test the compressed file integrity) ex: (1) gunzip -t database.sql.gz If not, it will return the message "gzip: database.sql.gz: not in gzip format." (2) Using WinMD5 Free for Win Os windows.png or md5sum command for Linux Os linux.png to obtain MD5 checksum of files.
  3. pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [11] and press Enter
  4. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 2: 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 [12] and press Enter
  3. enter password and press Enter

method 3: Import the *.sql file (especially for BIG sql file). It will show a progress bar and estimated time to complete the mysql command[13]:

  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 [14] and press Enter
  3. enter password and press Enter The console window will show ETA (Estimated Time of Arrival)

method 4: 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 [15][16] 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)[17]

  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
    • SHOW PROCESSLIST\G to show the brief 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 not found: mysqldump

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

could not access the mysql log for XAMPP on Mac

version: XAMPP 5.6.15-1

cause:

  • the error log only be accessed by the mysql user

solution [18]: 

find the path to the mysql log
$ ls /Applications/XAMPP/xamppfiles/var/mysql/*.local.err

find the file name of mysql log ex: XXXMacBook-Pro.local.err

set the permission of log
$ sudo chmod 774 /Applications/XAMPP/xamppfiles/var/mysql/XXXMacBook-Pro.local.err

ERROR 1005 (HY000) at line xx: Can't create table 'TABLE_NAME' (errno: 28)

Solutions

ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)

Solutions

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

Solution:

  • Check the typo of user name.
  • Check the typo of password.
  • Escape the password if it contains special characters e.g. mysql -u root -p'PASSWORD'[19]


Err 1054 - Unknown column in 'where clause'

Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'

Solution:

  1. check the column name 'xxx' if exists
  2. if the column name 'xxx' was computed by the User-Defined Variables. Enclosed the whole query into another parent derived query.
-- The query which met the error message: [Err] 1054 - Unknown column 'rank' in 'where clause'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
FROM
(
        ...
) semi
WHERE semi.rank <= 10

Enclosed the whole query into another parent derived query. 

SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10

ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.

Wrong sql query as follows: 

CREATE USER 'test'@'localhost' IDENTIFIED BY PASSWORD 'my_password';

Solution: (1) Check if the account was created or not 

SELECT User,Host FROM mysql.user;

(2a) If the account was created 

SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');

(2b) If the account was NOT created 

CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';

references


ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysql.sock'

Condition on Cygwin terminal of Windows: 

$ 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:

  • Change -h localhost to -h 127.0.0.1
$ mysql -h 127.0.0.1 -u root -p

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

Solution:

  • Check if the MySQL service is running or not. If not, start the MySQL service.

Error Code: 2013. Lost connection to MySQL server during query

Condition: After executed the following query contains number of rows which exceed 1,000,000 rows, I met the error message 'Error Code: 2013. Lost connection to MySQL server during query'. 

INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table`;

Solution:

  • Increase the setting of DBMS connection read time out (in seconds) on MySQL Workbench. [20][21]
  • Reduce the number of rows to reduce the execution time (1) by using LIMIT clause (2) or by splitting the query size e.g. MOD(column, 2) = 0 & MOD(column, 2) > 0 if the column is numeric.
INSERT IGNORE INTO `target`.`table` SELECT * FROM `source`.`table` LIMIT 0, 10000;

Navicat error: [Exp] OLE error 800A03EC

error message: 

[Msg] [Exp] Export to - test.xlsx
[Err] [Row1048576] [Exp] OLE error 800A03EC
[Err] [Row1048577] [Exp] OLE error 800A03EC
[Err] [Row1048578] [Exp] OLE error 800A03EC
[Err] [Row1048579] [Exp] OLE error 800A03EC

cause:

solution:

  • LIMIT the rows of MySQL query

resolve insufficient hard disk space where mysql data located

Err: 解決 MySQL 資料庫所在硬碟空間不足的狀況

tools

Transfer date from MS SQL server to MySQL server:

  • $ SQLyog Use a query to specify the data to transfer from MS SQL server into MySQL server.

further reading

references

  1. ↑ Linux / Unix pv Command: Monitor Progress of Data Sent Via a Pipe
  2. ↑ ivarch.com: Pipe Viewer
  3. ↑ Pipe Viewer (pv) in Mac OSX
  4. ↑ security - Minimum permissions for a user to perform a mysqldump? - Server Fault
  5. ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  6. ↑ MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
  7. ↑ backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow
  8. ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
  9. ↑ (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
  10. ↑ MySQL Import/Export Progress Bar - Kevin Warrington
  11. ↑ pv + gzip + mysql — W. Andrew Loe III: Journal
  12. ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
  13. ↑ mysql - How can I monitor the progress of an import of a large .sql file? - Database Administrators Stack Exchange
  14. ↑ Import MySQL Dumpfile, SQL Datafile Into My Database
  15. ↑ database - Importing zipped files in Mysql using command line - Stack Overflow
  16. ↑ Install 7Zip on CentOS 5.5
  17. ↑ MySQL :: MySQL 5.1 Reference Manual :: 13.7.5.31 SHOW PROCESSLIST Syntax
  18. ↑ Using the CHMOD command effectively @ Computer Plumber
  19. ↑ escape characters - Escaping a password using mysqldump console - Super User
  20. ↑ How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow
  21. ↑ Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow
Retrieved from "https://wiki.planetoid.info/index.php?title=MySQL_commands&oldid=17727"