MySQL commands: Difference between revisions
Line 206: | Line 206: | ||
Solution: | Solution: | ||
* Check if the MySQL service is running or not. If not, start the MySQL service. | * Check if the MySQL service is running or not. If not, start the MySQL service. | ||
==== ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out") ==== | |||
Solution: | |||
* Check the firewall rules. More on [https://devops.profitbricks.com/tutorials/install-mysql-on-centos-7/#firewall-rules Install MySQL on CentOS 7 | ProfitBricks DevOps Central] | |||
==== Error Code: 2013. Lost connection to MySQL server during query ==== | ==== Error Code: 2013. Lost connection to MySQL server during query ==== |
Revision as of 17:05, 24 January 2017
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 :
- 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 of database/table into MySql sql file
Exporting entire data
method1: 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]:
- (optional) install pv if the pv was not installed
- (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
- ask to enter password and press Enter
optional: Append the Today's date to file name
- On Linux : (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]:
- (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 --default-character-set=utf8 DATA_BASE_NAME | pv > database.sql and press Enter
- enter password and press Enter
method4: export the *.sql file
- 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. 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
Notice: existing database will be overwritten
method 1: 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
- 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 or md5sum command for Linux to obtain MD5 checksum of files.
- pv database.sql.gz | gunzip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [11] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
method 2: 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 [12] and press Enter
- 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]:
- (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 [14] and press Enter
- enter password and press Enter The console window will show ETA (Estimated Time of Arrival)
method 4: Import ZIP file to MySql directly:
- (optional if the 7Zip was not installed) yum install p7zip
- 7za x -so data.zip | mysql -u username -p -h localhost --default_character_set utf8 --force DATA_BASE_NAME [15][16] and press Enter
- 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]
- 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
- SHOW PROCESSLIST\G to show the brief process list
- mysql> kill 101; to kill the process with Id number: 101
- mysql> exit; to leave the MySQL command.
Troubleshooting of MySQL
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
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
- Check if the disk space where mysql data folder located is enough. e.g. Input df -h on Linux
- More on mysql - ERROR 1005 (HY000): Can't create table (errno: 150) - Stack Overflow.
ERROR 1006 (HY000): Can't create database 'DATABASE_NAME' (errno: 28)
Solutions
- Check if the disk space where mysql data folder located is enough. e.g. Input df -h on Linux
- More on ERROR 1006 (HY000) Can't create database (errno: 13) MySQL 5.6.12 - Stack Overflow.
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:
- check the column name 'xxx' if exists
- 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 the password for the account.
SET PASSWORD FOR 'test'@'localhost' = PASSWORD('my_password');
(2b) If the account was NOT created, re-create the account.
CREATE USER 'test'@'localhost' IDENTIFIED BY 'my_password';
references
- MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.2 CREATE USER Syntax
- MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.7 SET PASSWORD Syntax
- MySQL :: MySQL 5.7 Reference Manual :: 14.7.1.3 DROP USER Syntax
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 2003 (HY000): Can't connect to MySQL server on 'IP' (116 "Connection timed out")
Solution:
- Check the firewall rules. More on Install MySQL on CentOS 7 | ProfitBricks DevOps Central
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;
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:
- exceeds the Microsoft Excel Worksheet size: 1,048,576 rows
solution:
- LIMIT the rows of MySQL query
resolve insufficient hard disk space where mysql data located
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
- MySQL :: MySQL 5.7 Reference Manual :: B Errors, Error Codes, and Common Problems
- 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]
- MySQL :: MySQL 5.5 Reference Manual :: 7.2 Database Backup Methods [Last visited: 2016-06-29]
- SQL syntax debug
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
- ↑ backup - How can I slow down a MySQL dump as to not affect current load on the server? - Stack Overflow
- ↑ 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
- ↑ Using the CHMOD command effectively @ Computer Plumber
- ↑ escape characters - Escaping a password using mysqldump console - Super User
- ↑ How can I execute SQL queries that take longer 99,999 seconds on MySQL Workbench? - Stack Overflow
- ↑ Error Code: 2013. Lost connection to MySQL server during query - Stack Overflow