How to Backup and restore MySQL Databases from CLI

Note: plxxx.ixxx is a given name for the directory

Step1: Creating A Backup
Instead of entering passwords always, change into the web directory

christian@wexx0:~$ sudo -i
[sudo] password for christian:
wexx0# cd /var/www/plxxx.ixxx
wexx0# ls

Note: This is for contao setup in other to get the username and password as well as the database
name for creating the mysql dump and this is different for vtiger files
Contao can be found in /var/www/plxxx.ixxx/web/system/config/
Cat localconfig.php

Ignore for Contoa = (for vtiger it can be found /var/www/
wexx0#Cat config.db.php

Step 2: How to export MySQL database via command line in Linux

The mysql dump command is used to create text file dumps of databases managed by MySQL.
These dumps are just files with
all the SQL commands needed to recreate the database from scratch. The process is quick and easy.

wexx0# mysqldump -u c1xx_cxxtao -p cxxp_cxxtao > /tmp/mysqlnewdump.sql
Enter password:
wxx10# pwd
wxx10# ls

wxx10# scp /tmp/mysqlnewdump.sql’s password:
mysqlnewdump.sql 100% 2903KB 2.8MB/s 00:00

Sometimes you may need to import and export your MySQL databse from command line. Then this maybe could help you out.

#mysqldump -u database_user_name -p database-name > dbname.sql
Behind -u you should put your database user name
-p this is for the database password option
When you have done this command it will ask for the database password,
so you put the password in and press enter.

#mysqldump -u root -p my_db > /home/kudda/my_db_backup.sql
The example above will dump my_db into /home/kudda
and it will land under the name of my_db_backup.sql. Also i am using mysql root to easily dump database,
so when i written -u after that you shall write root.

Step 3: To Access the Dump from another Server where the mysqldump file is copied into

Here i am logging in to an LXC container
root@alxxxa:~# pct enter 167
root@Pxxxro:~# /var/www/pplxxx.ixxx/
bash: /var/www/ppplxxx.ixxx/: No such file or directory
root@Plxxxro:~# ls
root@Plxxxro:~# /var/www/pplxxx.ixxx/
bash: /var/www/pplxxx.ixxx/: No such file or directory
root@Plxxxro:~# cd /var
root@Plxxro:/var# ls
backups cache lib local lock log mail opt run spool tmp www
root@Plxxro:/var# cd www
root@Plxxro:/var/www# ls
pplxxx.ixxx html
root@Plxxro:/var/www# mv
root@Plxxro:/var/www# ls
html pplxx.ixxx
root@Plxro:/var/www# ls
html pplxxx.ixxx
root@Plxxro:/var/www# cd pplxxx.ixxx/
root@Plxxro:/var/www/pplxxx.ixxx# ls



Step 4: Restoring a Backup (Import A MySQL Database)

root@Plxxro:~# mysql -u root -p -h localhost cxxp_cxxtao < /var/www/pplxxx.ixxx/mysqlnewdump.sql
#mysql -u database_user_name -p my_db < /home/kudda/my_db_backup.sql

More info:
Here it should be almost the same as the export.
#mysql -u database_user_name -p my_db < /home/kudda/my_db_backup.sql
Here it is the same as export. after u- you put your database user name
and the -p is used for the password option still.

Here is one example of it.
Example: mysql -u root -p my_db < /home/kudda/my_db_backup.sql
On the example above i am importing my_db_backup.sql script into the database my_db

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s