Mysql Database Backup / Restore Syntax

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

Tips

Export data from local database to production site.

Some time we need to export a database or a table from local to production. hear a small tip.

Export via mysqldump or PhpMyAdmin

mysqldump -uroot -proot drupal8 my_table| gzip > file.sql.gz

Import via drush

gunzip < PATH/file.sql.gz| drush sqlc