Archive

Posts Tagged ‘database’

Logging to debug MySQL

October 30, 2013 No comments

It’s common, when using more or less complex stored procedures, to make mistakes that make us waste much time, and sometimes we would like to see the value of a variable or the result of a query because it could tell us what’s going wrong.

This is a little help, an easy piece of code we can insert into our database and will let is write in a table what’s happening.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
DROP SCHEMA IF EXISTS `PBUtils` ;
CREATE SCHEMA IF NOT EXISTS `PBUtils` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE PBUtils;

DROP TABLE IF EXISTS `PBUtils`.`LOG`;
CREATE TABLE IF NOT EXISTS `PBUtils`.`LOG` (
   `Log_id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
   `Log_date` DATETIME NOT NULL,
   `Log_description` varchar(128) NOT NULL,
   `Log_text` TEXT NULL,
   `Log_number` bigint NULL,
   `Log_float` double NULL,
   PRIMARY KEY(Log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logText` $$
CREATE PROCEDURE `PBUtils`.`logText`(
    IN  In_description VARCHAR(128),
    IN  In_text TEXT
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_text`) VALUES (NOW(), In_description, In_text);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logNumber` $$
CREATE PROCEDURE `PBUtils`.`logNumber`(
    IN  In_description VARCHAR(128),
    IN  In_number bigint
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_number`) VALUES (NOW(), In_description, In_number);
END $$
DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `PBUtils`.`logFloat` $$
CREATE PROCEDURE `PBUtils`.`logFloat`(
    IN  In_description VARCHAR(128),
    IN  In_float double
)
BEGIN
    INSERT INTO PBUtils.LOG (`Log_date`, `Log_description`, `Log_float`) VALUES (NOW(), In_description, In_float);
END $$
DELIMITER ;

Now, what we have to do is insert our procedure to debug values, anywhere in our code:

1
PBUtils.logText('Text to describe', variable);
1
PBUtils.logNumber('Text to describe', variable);
1
PBUtils.logFloat('Text to describe', variable);

depending on what we want to record, a text, number or float

To see what has been written in our log:

1
SELECT * FROM PBUtils.LOG ORDER BY Log_data DESC

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:

1
2
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:

1
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:

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

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:

1
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’;
mysql> FLUSH PRIVILEGES;

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

Top