Posts Tagged ‘privileges’

Backup MySQL users

October 28, 2013 No comments

When we are dumping a MySQL database, is normal to ignore “mysql”, “test” and “information_schema” databases. We could have undesirable problems when restoring, for example if we change MySQL version.
But sometimes, when backing up the database we want to save also user table information. If we have several applications using the same database, we should have a user for each application and we don’t want to lose them.

So we could use the query SHOW GRANTS FOR [email protected] in MySQL. We could automate it to give us all user permissions with a little bash script:

USERINFO="-uroot -ppasswd"
mysql $USERINFO -BNe "select concat('SHOW GRANTS FOR \'',user,'\'@\'',host,'\'; ') from mysql.user where user != 'root'" | mysql $USERINFO -BN | sed 's/$/;/g' > users_backup.sql

In the USERINFO variable, we store login data for MySQL, we can use root user for this.

With the first call, we generate the queries we are going to make to the database to get user privileges generation SQL. We look who are the users, their hosts and we generate for each row the following string:

SHOW GRANTS FOR ‘user’@'host’;

In the second call, we make all queries, and it should return for each one of them something like this:

GRANT USAGE ON *.* TOuser’@'localhost’ IDENTIFIED BY PASSWORD ‘*1C3116A0C895332DE9EE63FFF08863CC9397ED8E’
GRANT ALL PRIVILEGES ON `database`.* TO ‘user’@'

If the user has privileges on more tables or databases, they will appear here too.

The third call is to finish each line with ; (semicolon), so MySQL won’t give us problems when restoring them.

To restore the users just do:

mysql -uroot -ppaswwd < users_backup.sql

Restore root password in MySQL server

October 17, 2013 No comments

Sometimes, especially in our test server (or even production, who knows), we can lose our MySQL root password. This days we think about deleting everything, lose everything we’ve been working on, but we must be patient, we can’t destroy everything freely…

So, I leave here some easy steps (not only for you, but for me, I’m so absent-minded). We must have root privileges in the server we have MySQL installed:

1. First, stop MySQL service, depending the distribution, you may use one of these (or not, there can be more ways to do this)

1.1. $ service mysqld stop
1.2. $ /etc/init.d/mysqld stop
1.3. $ /etc/rc.d/mysqld stop

2. As root, we start MySQL service in a special way, ignoring privileges tables:

root # mysqld –skip-grant-tables

(you can use sudo on Ubuntu, for example)

Depending on your MySQL version (or distribution), it may not let you start as root, so we will specify the user, it will use:

root # mysqld -u mysql –skip-grant-tables &

In most systems the user will be mysql.

3. Access as superuser (as we are not checking privileges tables, we will have easy access). It can be done with a normal user. Use mysql database (or schema), that’s where the privileges tables are in.

$ mysql -u root mysql

4. From MySQL client we will update the password:

mysql> UPDATE mysql.user SET Password=PASSWORD(‘new password’) WHERE User=’root’;

5. Exit mysql client

mysql> quit

6. Close mysqld (as root), it will take some seconds, we must be patient

$ killall mysqld

7. Restart MySQL server as we are used to, the same way we stopped it in step 1:

7.1. $ service mysqld start
7.2. $ /etc/init.d/mysqld start
7.3. $ /etc/rc.d/mysqld start

Let’s carry on working with our new root password