MySQL Administrative / Backup Commands

MySQL contains many useful command line tools to perform administrative task such as connecting, operating on MySQL database, User Management etc.
1) mysqldump : is a command line /shell prompt utility used to take backup     of entire Database.  


syntax: # mysqldump -h hostname -u username -p database_name > file_name.sql

user will be prompted for password.

example: # mysqldump -h localhost -u admin -p College > mybackup.sql

# --> shell prompt

C:\>  Command prompt

Restore entire database from the backup file:


C:\>mysql -h hostname -u username -db databasename  < ‘location of backup file’ C:\>mysql -h localhost -u admin -db College  < ‘c:\college\mybackup.sql’


How to take Backup of Individual Tables syntax:

C:\>backup table tablename to 'location' ;
example: C:\>Backup Table student to 'c:/college';


If you want to get back the table or restore it after taking backup:
syntax:
C:\>restore table tablename from 'location' ;
example:
C:\>restore table student from 'c:/college';
How to take Backup of Individual Table data.
i.e Take backup of student table in text file.
syntax:
SELECT * INTO OUTFILE 'backup filename'   FIELDS TERMINATED BY ','   FROM 'table name';
example:
SELECT * INTO OUTFILE 'c:/test/data.txt'   FIELDS TERMINATED BY ','   FROM student;


2) mysql Command: MySQl provides a command line /shell prompt tool to get a connection to mysql and operate on database.


syntax: # mysql -h hostname -u username -p password
example: # mysql -h localhost -u admin -p nopass

Funny programs in PHP    Free-html-template    Earn money online

mysql>show databases; command gives you a list of databases available / accessible to that user.
Restore table data from text file:
load data infile 'c:/test/records.txt' into table emp fields terminated by ',';


Adding Columns in MySQL:


Alter table emp add joindate datetime;


Adding Columns in MySQL :
Alter table emp drop column joindate;


How to  rename table in MySQL:


Alter table emp rename as myemp;