MySQL/Linux: Creating and restoring database backups using command line

Most of us have worked on different database technologies. MySQL is one amongst them.This tutorial explains the backup/restore process of db's in mysql.

Mysql provides a tool mysqldump which allows the user to take the backup of current state of the databases and restore them at a later point in time,if required.This tool comes in with the default installation of mysql server and can be used on the command line.

In this tutorial i am using mysql-server-5+ and Linux OS command line to explain the usage of this tool.

For a single database

Suppose you need to backup a single database.This command will do your work.

mysqldump -u root -p[root_password] [database_name] >filename.sql

So to backup a database named 'testing',the command would be

mysqldump -u root -p[root_password] testing > testing.sql

Note:The dump file testing.sqlwill be generated and stored in the directory location where you issue this command.However,you can specify your own directory location.Something like this 

mysqldump -u root -p[root_password] [database_name] > /path/to/file

Now comes the restore part.To restore a dump into a database,the syntax is
mysql -u root -p[root_password] [database_name] < filename.sql

where filename.sql is the dump file generated using mysqldump command.

Suppose you need to restore the dump file generated in the previous step to a database named 'testing1'.The command is

mysql -u root -p[root_password] testing1 < testing.sql

Note: The database into which the dump needs to be restored(in this case testing1) should be created in advance (using the Create database) before dumping the content of the dump into file.Not doing so will throw up an error when the restoration is done.

For multiple databases

Sometimes it is necessary to back up more that one database at once. In this case you can use the --databases option followed by the list of databases you would like to backup. Each database name has to be separated by space.

mysqldump -u root -p[root_password] --databases db1 db2 > content_backup.sql

The above command will dump two databases db1 and db2 into the content_backup.sql file.

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

mysqldump -u root -p[root_password] --all-databases > alldb_backup.sql

Selective backup with mysqldump

The mysqldump utility also allows you to take selective backups in a database.You may want to backup one or more tables in database or,backup only selective data or records(using 'where' conditional clause).

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only blog_template1 and blog_template2 tables from the ‘Templates’ database,use the command below. Each table name has to be separated by space.

mysqldump -u root -p[root_password] \blog_template1 blog_template2  > dbtemplate_backup.sql

For backing up or dumping only selective records in tables we can use the --where or -w option.For example,to backup the records from table t1 in the database db1 where vendor is google, the command will be

mysqldump -u root -p[root_password] db1 \t1 --where "vendor='google'" > t1.sql

This completes the tutorial on backing up/restoring data in mysql.Comments and feedbacks will be appreciated.Cheers!!!

Run a MySQL script file in Linux

We commonly use scripts to automate and ease our work. MySQL provides a feature to create schema structure using scripts.

A MySQL script file(.sql) is as good as a normal text file.Just put your queries in a text file and your script is ready to run.In this post i explain the procedure to run a mysql script through the command line as well as mysql shell

The script file

I created my script using vi editor in Linux(you may use any text editor :) )
Here is my script file

I placed my script file in the root directory.The location of the script is important.Why?? We will get to know it soon.

 

Executing the script

We can execute our script in two ways.

• From the Linux/Unix command line with the help of the redirection operator(<)
• From the mysql shell. 

 To do it the first way,from the command-line execute this command
  mysql -u -p < script  

Note: Execute this command from the location where you placed your script.

Replace the with the mysql username and with the password you use to login into mysql.For instance,if the username is root and password is xyz the script filename is scpt the command would be 
mysql -u root -pxyz < scpt

< is the redirection operator in Unix/linux.

If the command execution is successful,you won't get any confirmation message.The control returns to the next line of the shell prompt.

Now to confirm the changes made by the script,login to mysql and use the show databases and show tables command.

Below is an illustration of this method.

             
To execute your script from the mysql shell.Follow these steps

1. Login to mysql with root privileges(Note:Login into mysql from the same directory where you placed your script)     

   2.  Once in the mysql shell,enter this command to execute your script
         source scriptname
        
Replace scriptname with the script you earlier created and want to execute. 

   3.  Once the mysql shell finds your script file,it will execute it and the results can be instantly seen on your screen.

   

   

 




























Now to confirm the changes made by the script, use the show databases and show tables command.

In case the mysql is unable to locate the file,it will throw up an error something like this

fuser command in Linux

Suppose you want to remove a folder from your system.However,when you try to remove that folder using rm command,it is not able to delete some files in the folder because they are being used by some process.In this situation,the fuser command can come in handy for you.

The fuser command is a powerful tool provided by the unix/linux developers.It gives information about the file user or the process(or executable) that is currently using the file.The file may be a regular file or a directory or even an executable.

Who is using my file/directory

Using the fuser command you can identify which processes are using a particular file/directory.

At the console type this command
fuser .

Include a space between the command and the dot.The dot indicates the current working directory.

We can see a number of process IDs followed by a character 'c'. This indicates the type of access.The type of access can be any one of the following:

c - indicates current directory
e - executable being run
f - Open file.f is omitted in default display mode
F - Open file for writing.F is omitted in the default display mode
r - root directory
m - mapped file or shared library

So 'c' in the output means that these processes are using this directory as their current directory.
  

Services running on a port

The fuser command can also find out which service(s) are running on a particular port of your system.The port might be a tcp/udp port.For a tcp port (say 3306-the mysql default port) use this command

fuser -n tcp 3306


Next,run the following command to find out the service name from PID you got

ps -ef|grep <Pid obtained from the previous command>

Alternatively,you may use the -v flag to obtain a detailed info
fuser -v -n tcp <port number>

Tutorial:Simplifying vi (editor) in Linux

The aim of this tutorial is to simplify the use of vi editor in unix/linux and to unleash its power with a combination of few keystrokes and commands.

The vi editor(or visual editor) is a screen editor which is available on almost all the unix systems.Its a fast and a powerful editor. vi has no menus but instead uses a combination of keystrokes in order to accomplish commands.A beginner might find it difficult to edit files in vi editor,but at the same time it is fun to learn and use.One needs to remember the keystrokes.

Getting into vi

To begin with,on the unix/linux console type vi followed by a space and the filename.If you want to edit an existing file,type in its name or if you are creating a new file,type in the name you wish to give to the new file.Next,hit enter

vi modes

vi has two modes: the command mode and the insert mode.When in the command mode,letters of the keyboard will be interpreted as commands.Wen you are in insert mode the same letters of the keyboard will type or edit texts.The vi editor always starts in the command mode.Switching between the two modes is easy.Once you enter the vi editor,hit the letter i on the keyboard.You enter the insert mode.If you wish to leave the insert mode and return to the command mode,hit the ESC key on the keyboard.If you are not sure where you are,hit ESC a couple of times and you will be back to the command mode.

Please note that the vi editor is case sensitive.Thus, a i and I will be interpreted as two different commands.

Also note that the keystrokes in the command mode are not displayed on the screen.

 

Entering text

Suppose you created a new file using the vi command.Initially, you are in command mode.To enter some text in the file you need to first switch to the insert mode.Hit i.This brings you to the insert mode.When you start typing now,anything that you type will be displayed on screen and will be entered into the file.At the end of each line,vi will break the line and texts will move to the next line.

Cursor movement in a file

You must be in the command mode if you want to move the cursor to different positions in the file.If you are finished entering text into the file,its time to return back to the command mode.Hit ESC.This will bring you back to the command mode once again.

Moving one character at a time
The arrow keys can help you move one character at a time in the file.Alternatively,use these keys
h: one space to the left (equivalent of left arrow key)
l : one space to the right (equivalent of right arrow key)
j : one space down the current position (equivalent of down arrow key)
k : one space upward (equivalent to up arrow key)

Moving among words and lines
To make navigation quicker there are several 'key' commands which allow you to move word by word in a file.
w: moves the cursor forward one word.

b: moves the cursor backward one word.

W: moves the cursor forward one word(but ignores space or punctuations between words.To make out the difference use w and W seperately for navigating in your file.

B: moves the cursor backward one word(ignores punctuations)

0(zero): moves the cursor to the beginning of the current line.

$: moves the cursor to the end of the current line.

Screen movement
To move the cursor to a line within your current screen use these keys

H: moves the cursor to the top line of the current screen
M: moves the cursor to the middle line of the current screen
L: moves the cursor to the last line of the current screen

In case the file is a large one (say 1000 lines),it is not possible to display the whole file on a single screen.In this case we have to scroll through multiple screen to view the whole file.In such a situation use these keystrokes

ctrl+f : scrolls down one screen

ctrl+b : scrolls up one screen  

ctrl+u : scrolls up half a screen

ctrl+d : scrolls down half a screen

For moving to the beginning or end of the file, use the G command

G : to move the cursor at the end of the file.

1G : to move the cursor at the first line of the file.In general, if G is preceded by a number,it moves the cursor to the specified line in the file.For instance, 5G moves the cursor to the fifth line in the file, 17G moves the cursor to the 17th line in the file and so on.
 
Search as you move
A very efficient method to get to a specific position in a file is by searching for a particular text.In command mode,type a / followed by the text you want to search.When you hit enter, the cursor is placed at the first match in the file.Hit n to point the cursor to the next instance(if any,present in the file).

Editing a file

The file editing commands work in the command name.As mentioned earlier,the commands are case-sensitive.Also, some editing commands can be preceded by a number to indicate the number of repetitions of the command.

Deleting(or cutting) letters/words/lines 
To delete a character in the file,position the cursor to that particular character and use these commands

x : deletes the character under the cursor.
X : deletes the character to the left of the cursor.
dd : deletes the current line.
D : deletes from the current position of cursor to the end of that particular line where the cursor is placed.

Preceding the command with a number will repeat the command that number of times.For instance, 4x will delete the character on which the cursor has been placed followed by 3 more characters immediately succeeding it.Similarly, 4dd will delete four lines.

Pasting text using Put 
In case you want to paste or insert the deleted text to a specific location in file,you can use the Put command to do so.To use the command,place the cursor where you wish the deleted text to be inserted.Then use the p command to reinsert the text.If you are inserting a line or a paragraph use the lower case p to insert on the line below the cursor and use P to insert on the line above the cursor.

Copying or 'Yanking' text
If you want to copy an existing text in the file,you can use the yank command to do so.Yank copies the selected text into a buffer and holds it until another yank or deletion occurs.As usual,we can use it in command mode.The commands below will help you yank word(s) or line(s) in a file.

yw : copies the word(based on the current location of the cursor) to the buffer.
yy : copies the entire line to the buffer.

When a number precedes these commands say, 7yy ,it will copy 7 lines into the buffer(the current cursor position line and 6 lines following it).

The yanked text can be easily placed at some other position using the p(or P) command.

Replacing characters,words or lines
The following commands can help you replace characters,words or lines in your file.

r : replaces the current character(highlighted by the cursor) with the next character you type in.After the character has been typed,the control returns to the command mode.

R : puts you in the overwrite mode where things are overwritten as you type.Hit  ESC to exit this mode.

cw : changes and replaces the current word with the text you type in.The $(dollar) sign at the end of the typed text marks the end of the replacement text.

Appending text 
The append command can help you add text or content to your file.Append works much like insert except that it enters text after the cursor rather than before it.To use this command,place the cursor to the position where you want to add text and press a.Then continue typing in the needed content.Once done,hit the ESC key to return back to the command mode.


The Undo command
As the name suggests,the undo command can undo things for you. DO NOT move the cursor from the line where you made a mistake.(Before that hit ESC if you are in the insert mode).Then try these commands

u : undoes the last change you made anywhere in the file.Using u again will "undo the undo"

U : undoes all recent changes to the current line.

Some file handling

Saving and quitting
The command ZZ (uppercase) will allow you to quit the vi editor and save changes made by you to the file.Below are some of the commands which allow file handling in vi editor.Remember that you should be in command mode while executing these commands.Also,use the key combination shift+: before typing in any of these commands.

 wq : alternative to ZZ

e! : return to the last saved version of the current file.
 
q or q! : quit vi without saving the file.

w : write to the current file without exiting the vi editor.(use w! to force write)

 

Some Cool stuff!!  

 

Use these commands in the command mode to see how awesome is this vi stuff.Remember to use the key combination shift+: before using any of these commands.

sh : temporarily escape to the shell

^d : return back to vi from shell(read as ctrl+d)

![command] : execute a linux command without exiting the vi editor.Use ctrl+d to return back to the vi editor after executing the command.

r![command] : read output of the command to vi.

r[filename] : read a file to vi

n : open next file

^g : lists the current line number.

set number : show line numbers

set showinsert : show the flag "I" at bottom of screen when in insert mode.Can help you to identify in which mode you are working at any instant.

set tabstop=n : sets default tab space to number n.

1,15w filename : write lines 1 to 15 to any filename mentioned in the command.

15,$w >>file : writes the content from line 15 to the end of the file and appends it to the file mentioned.


That completes the tutorial folks.I might have missed out some things.However,i have embedded a 'cheat-sheet' of vi for quick reference.Check it out below...

VI Help Sheet 011 by Siddharth Deo

Viewing huge files in Linux/Unix

In Linux there are several system generated files which might be very large in size (say upto thousands of lines).An example of this is the system generated logs(syslogs).A system may generate thousands of logs in a day.This makes it very difficult for the user to read the portion of the file which he wants.'Catting' the file is no solution for the problem.Using grep is useful if you know what pattern to search for.

There are several tools/commands available in unix/linux systems which can make life easier for the user.The head and tail command are two such commands which facilitate convenient viewing of log files.

Below are some examples on how to use these commands effectively.

Display the first N lines of a file using head command 

Syntax: head -n N filename(or file path)

Example: head -n 15 /var/log/syslog

This will display the first 15 lines of the syslog file.

Display the last N lines of a file using tail command

Syntax: tail -n N filename(or file path)

Example: tail -n 50 /var/log/syslog

This will display the last 50 lines of the syslog file.

Ignore the first N-1 lines of a file using tail command  

Syntax: tail -n +N filename(or file path)

Example: tail -n +10 /var/log/syslog

This will ignore the first 9 lines of the syslog file and will print from the line number 10th.   

Ignore the last N lines of a file using head command  

Synatx: tail -n -N filename(or file path)

Example: tail -n -10 /var/log/syslog

This will ignore the last 10 lines of the syslog file

View growing log file in real time using tail command  

Syntax: tail -f filename(or file path)

Example: tail -f /var/log/syslog

View the syslogs written in real time.

Display specific portion(based on line number) of a file using head and tail command  

The following example will display line numbers 50 to 60 of the syslog file.

Note: M(=50) indicates the starting line number and N(=60) indicates the ending line number.

Syntax: cat filename|tail -n +M|head -n (N-M+1)   

Example: cat /var/log/syslog|tail -n +50|head -n 11

In this example

cat -> prints the whole file to stdout

tail -n +50 -> ignores lines upto the given line number(i.e till line 49),and then start printing from the given number(from line no.50)

head -n 11 -> prints the first 11 lines ,that is from line number 50 to line number 60 and ignores the remaining lines.

Viewing compressed log file 

By virtue of log rotation log files are generally compressed and archived on the systems.A compressed log file is generally a .gz(gzip) format file.It is therefore,not possible to view the file contents in human readable form using the cat command. To view a compressed log file we have zcat command.

The general syntax of zcat command is 
zcat [options] [filename(or file path)]

Using head and tail command with zcat  

The output of the zcat command can be piped for the head and tail command to view specific portions of the log file.

To display the first N lines of a compressed file use this command syntax
zcat filename.gz |head -N

To display the last N lines of a compressed file use this command syntax 
zcat filename.gz |tail -N

To ignore the last N lines of a compressed file use this command syntax
zcat filename.gz|head -n -N

To ignore the first N-1 lines of a compressed file use this command syntax
zcat filename.gz|tail -n +N 

 

cat command in Unix/Linux

The cat command is one of the most frequently used command in unix/linux.Many people use it to view the contents of the file or to concatenate two files.However,there is much more you can do with this command 

Basics

 Displaying the contents of a file

The most basic use for the cat command is to display the contents of a file.The syntax is
cat filename(or filepath)

The above command will display the contents of the file on screen.

 

 

 

 

Concatenating multiple files

The cat command can also concatenate the contents of multiple files.This does not affect the original file(s).In this example,cat will concatenate the contents of three files demo_file,demo_file1 and demo_file2 and the result will be redirected to a new file named test.

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Create a file

cat can also create a text file.To create a file test.txt use this command
cat > test.txt
You can then whatever you want.To save the file and exit use CTRL+D

 

 

 

 

 

 

 

Copy a file

The cat command can also be used to create a new file and transfer to it the data from an existing file.
cat oldfile > newfile

To output file1's contents, then standard input, then file2's contents,use

cat file1 - file2

To see the effect of the above command we redirect the output of the above command to a new file
cat file1 -file2 >foo1.txt

Enter some text(standard input) and then use CTRL+D to save and exit.Catting the file will show you the result.

A hyphen indicates that input is taken from the keyboard. In this example, to create a new file file2 that consists of text typed in from the keyboard followed by the contents of file1, enter: 
cat - file1 > file2

 

Some useful options

To number non-blank output lines use -b option
cat -b filename

To number all output lines use -n option
cat -n filename

To squeeze multiple adjacent blank lines use -s option
cat -s filename