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:
C:\>restore table tablename from 'location' ;
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.
SELECT * INTO OUTFILE 'backup filename'   FIELDS TERMINATED BY ','   FROM 'table name';
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

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;

No comments:

Post a Comment

Thanks for visiting my Blog!

Have a Question ? Need Help in College Assignments, Need Code that is not available here? Just leave a comment & get your code instantly.

Tips to Enhance Your Blog