MySQL commands

MySQL commands and troubleshooting of MySQL errors.

Preparation

install MySQL database client apps contains mysql & mysqldump commands

• Linux :
• Win : Install (1) Cygwin, (2) search mysql & install MySQL database clients apps package
• macOS :
  • Install XAMPP. Path of mysql executable file is: /Applications/xampp/xamppfiles/bin/mysql
  • MAMP: path of mysql command: /Applications/MAMP/Library/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
• macOS : 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 of database/table into MySql sql file

mysqldump Options

Default option without any options specified

• drop table & insert new data

Replace (overwrite) the current data

• Run mysqldump with the option --replace --no-create-info: (1) --replace: "Write REPLACE statements rather than INSERT statements." (2) --no-create-info: "Do not write CREATE TABLE statements that create each dumped table." & "Do not add a DROP TABLE statement before each CREATE TABLE statement"

Append the current data

• Run mysqldump with the option --insert-ignore --no-create-info: (1) mysqldump with the option --insert-ignore: "Write INSERT IGNORE statements rather than INSERT statements."

Schema only

• Use mysqldump to get the schema only - Electric Toolbox

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:

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 and press Enter
3. 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 :

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. 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]

Potential failure issue

• insufficient hard-disk space

Importing data from MySql sql file

Notice: existing database will be overwritten

Optional

1. (optional) install pv if the pv was not installed for RHEL / CentOS / SL / Fedora Linux or brew install pv for macOS
2. (optional if the 7Zip was not installed) yum install p7zip for RHEL / CentOS / SL / Fedora Linux

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:

1. Install pv
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 or md5sum command for Linux 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 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 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:

1. Install pv
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)

method 4: Import ZIP file to MySql directly:

1. Install the 7Zip
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

Synchronizing databases

Synchronizing two MySQL databases:

• HeidiSQL - MySQL, MSSQL and PostgreSQL made easy v.9.4.0.5125 for Win , macOS & Linux
  • Steps: Choose the database or table --> Export database as SQL --> Output: Select the mysql server connection
  • Scheduler: Not available . Manually synchronizing databases between two different servers.
• Replication
  • Steps:
  • Scheduler: Auto .
• $ SQLyog v.12.4.0 for Win .
  • Steps: Menu --> Powertools --> Database Synchronization Wizard
  • Scheduler: Available . Save the job file & Schedule it using Windows scheduler.
• $ MySQL GUI Tool - MariaDB and MySQL Front End Client for Windows
  • Steps:
    • Menu: Comparison -> New Data Comparison -> Choose the connection: Next ->
    • Mapping: Select objects that you want to compare ->
    • After hours or minutes ...
    • Menu: Comparison -> Synchronize -> Open the synchronization script in the internal editor -> Synchronize
    • Menu: SQL -> Execute
  • Scheduler:

Transfer date from MS SQL server to MySQL or MariaDB server:

• See on Transfer Data from MySQL to MSSQL

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
   • 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 errors

Troubleshooting of MySQL errors

Further reading

• MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
• 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
• MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server SQL Modes
• MySQL :: MySQL 8.0 Reference Manual :: 4.5.1.1 mysql Client Options

References

Troubleshooting of ...

• Troubleshooting of Excel errors

• PHP, cUrl, Python, selenium, HTTP status code errors

• Database: SQL syntax debug, MySQL errors, MySQLTuner errors or PostgreSQL errors

• Troubleshooting of regular expression

• HTML/Javascript: Troubleshooting of javascript, XPath

• Software: Mediawiki, Docker, FTP problems, online conference software

• Test connectivity for the web service, Web Ping, Network problem, Web user behavior, Web scrape troubleshooting

Template

• Bug report template