Backup and restore MySQL – mysqldump a database – Grant Privileges

Back up a MySQL database

If you want to backup a MySQL database and you have access to shell, then you can easily do your job.

You can use mysqldump to create a simple backup of your database using the following syntax. 

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

after executing this command the result will be the backupfile.sql which is the backup file for your database. I shouldn’t pass the password argument [password] in the command because it would be available to other users of the system. If you leave it blank mysql client will ask you to enter your password. If you want to connect to a remote host to make a backup then the only thing you should do is to add -h [hostname | ip address] parameters like the syntax below.

mysqldump -h [hostname] -u [username] -p [password] [databasename] > [backupfile.sql]

This will work supposing you have privileges as the user you want to connect from the remote site.

There are a lot of combinations to dump a MySQL database and you can explore all of them here.

Restore a MySQL database from a dump

To restore a database from a file produced by mysqldump there is an easy walkthrough. Its the reverse way you resulted the dump file. Get a look at the following syntax.

mysql -u [username] -p [password] [databasename ] < [backupfile.sql]

you can restore the dump to a different database  or to a different database server and this is a good option if you want to make a replica of your data to make some tests.

Grant Privileges on a user in MySQL

To grant the privileges to user you have to connect to mysql as user that have privileges to grant privileges to other user as root user do.

As you may already know, to connect to the MySQL server as root you have to execute this command:

mysql -u root -p

once again do not provide your password to the command line. Now to grant privileges to a user you must keep in mind that you should give privileges to user from a host to some database table(s). Keeping this in mind is easy to remember the syntax of the command.

GRANT ALL PRIVILEGES ON 'databasename'.'databasetable'  TO 'username'@'host';
and then do
FLUSH PRIVILEGES

If you want to grant privileges on all tables or all databases just use the asterisk (* ) as follows:

GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'host';

If you want this user to have access from every host / ip address then:

GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'%';

and after each statement that affects the privileges of a user on a database you have to FLUSH PRIVILEGES.

In case you want to give this user the privilege to grant privileges then you have to use on of the above, using extra: “with GRANT option” . For example use:

  GRANT ALL PRIVILEGES ON 'databasename'.* TO 'username'@'%' WITH GRANT OPTION;

this will result a super user on this database and this user can connect from every site and he is able to grant privileges to other users for this database. Do not forget to FLUSH PRIVILEGES.