Friday, 7 December 2012

Mysql database permissions

Grant all permissions to user "test_user" on the new database "foss"

         mysql >  grant  all  on  foss.*  to  'test_user'@'localhost'   identified  by   'test_user_password';

                   foss  ---> database name   
                   foss.*  ----> [database name] . [table name]                  

Grant all permissions to user "test_user" on all databases
         mysql >  grant  all  on  *.*  to  'test_user'@'localhost'   identified  by   'test_user_password';                       

                      all   ----> all permission
                      *.*  ----> [database name] . [table name]

How To Grant Different User Permissions
            Here is a short list of other common possible permissions that users can enjoy.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)

CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

Sunday, 2 December 2012

Backup and Restore MySQL Database Using mysqldump

How to backup mysql database using "mysqldump"

         mysqldump is an effective tool to backup MySQL database

Backup a single database

     [root@sysadmin ~]#  mysqldump   -u   root   -p   test   >   test.sql

         test --> database name
 test.sql --> dumpfile name

Backup multiple databases

              [root@sysadmin ~]#  mysqldump  -u  root  -p   --databases  test1  test2  >  test1_test2.sql

test1 and test2 --> database name

Backup all the databases

              [root@sysadmin ~]#  mysqldump  -u   root   -p   --all-databases  >  all-database.sql

How to Restore MySQL Database:

      [root@sysadmin ~]#  mysql  -u  root  -p  test  <  test.sql

test --> database name
  test.sql --> dumpfile name