May 162013
 
Article MySQL

In a production server, a periodic backup of  the existing MySQL databases is usually done using mysqldump.

But there may be one of several users with different access permissions to those databases, the tables they hold, or even specific fields in the tables, and also to the stored procedures. This information also needs to be saved, to be able to recover all the functionality of the database.

This post presents a series of SQL sentences that can be used to save this information into backup files, and to recover it from the files into the database.

1. Save users with privileges to access tables and stored procedures

In the example below we will save in a file “users.txt” all the records in the users table (mysql.user) that hold users with access privileges for tables and stored procedures in databases ‘db1’ and ‘db2’:

2. Save privileges to access tables and stored procedures

In the example below we will save in a file ‘db_privileges.txt’ the records in the table “mysql.db” that hold access privileges for databases  ‘db1’ and ‘db2’:

Next, we will save in a file ‘table_privileges.txt’ the records in the table “mysql.tables_priv” that hold access privileges for tables in the same databases:

Finally, we will save in a file ‘stored_proc_privileges.txt’ the records in the table “mysql.procs_priv” that hold access privileges for stored procedures in the same databases:

3. Restoring user privileges

Having stored the four files above together with the backup of the databases, it is easy to restore the user privileges by loading the files into the database using sentences “LOAD DATA INFILE”:

 Posted by at 7:05 pm

 Leave a Reply

(required)

(required)