Mysql Database Backup / Restore Syntax

Submitted by editor on Sat, 01/30/2016 - 12:23
Question

Mysql Database Backup / Restore syntax using mysql and mysqldump

How to Restore Multiple files or splited sql file ?

Syatax (Usually used)
mysqldump --host=IP-OR-HOST --user=USERNAME --password=PASSWORD database_name > database_name.sql
mysqldump -h IP-OR-HOST -u USERNAME -pPASSWORD database_name > database_name.sql
mysqldump -h IP-OR-HOST -P 3310 -u USERNAME -pPASSWORD database_name > database_name.sql;
// -P 3310 = PORT 3310

Backup Local Database without password
mysqldump db_name > db_file.sql

Backup / Local / Remote Database With Password
mysqldump -u USERNAME -p dico db_name > db_file.sql

Backup A Table
mysqldump -uUSERNAME -p db_name tab_name > tab_file.sql

Backup and compress using gzip
mysqldump -uUSERNAME -p db_name  | gzip > db_file.sql.gz

Restore a Database
mysql -uUSERNAME -pPASSWORD db_name < db_name.sql

Restore a gzip compressed file using gunzip
gunzip < db_file.sql.gz | mysql -uUSERNAME -pPASSWORD db_name

Restore Multipl efiles or splites sql file

for i in files.*
do
  echo "file=$i"
  mysql -uusername -ppassword database_name < $i
done

You can also try:
cat *.sql | mysql -uusername -ppassword database_name

Force Import (--force) ignore errors.
mysql -uUSERNAME -pPASSWORD -f db_name < db_name.sql

 

Mysql Data location

In general on linux mysql datas are located in : /var/lib/mysql/
But you can find all data locations using this query.
On PhpMyadmin (or another tool)
SHOW VARIABLES WHERE Variable_Name LIKE "%dir"
On command line mysql (mysql cli)
mysql -uroot -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

root = THE ROOT USER NAME

Add new comment

CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.