MySQL: Backup and Restore

In the past I have always tried to take database backup and followed by restoration process using the various client tools like SQLYog or HeidiSQL. As I have worked on community version of these tools thus always faced restrictions or got errors while working with larger database.

With experience I switched to MYSQL in built library function which came to my rescue and never let me down: Below are the details for the same:

For Windows:

• Open a command prompt DOS window by typing in "cmd" in Start>>Run Menu
• Now navigate to the bin directory of the MySQL installation say
  cd c:/Program Files/MySQL/MySQL Server 5.0/bin
• For backup
  mysqldump.exe -u root -pabc123 se_alfresco > d:\backup.sql
• For restore
  mysql.exe -u root -pabc123 se_alfresco < d:\backup.sql

For Linux/Unix:

• Go to linux shell prompt
• Use the below command for back up and restoration
• For backup
  mysqldump -u root -pabc123 se_alfresco > d:\backup.sql
• For restore
  mysql -u root -pabc123 se_alfresco < d:\backup.sql

MySQL: Grant permission to a user

Create a new user

 CREATE USER 'c'@'localhost' IDENTIFIED BY 'mypass';

Grant all permission on all database

grant all privileges on *.* to 'v'@'localhost' identified by 'mypass' with grant option;

Grant all permission on a database

grant all privileges on appDB.* to 'v'@'localhost' identified by 'mypass' with grant option;

References:

For complete details see MySQL Manual.