Failure restoring backup

Hello,

(I am posting this request on this list, as it may be RT-related - maybe I should post it only on a
Mysql list ? I don’t know)

I am configuring RT 3.4.5 on CentOS 4.4 - I am almost finished, it runs nicely, adding RTFM, the
shredder …

I wanted to setup a Mysql incremental backup - it seemed it worked. Then I copied my /var/lib/mysql
in a safe place, and destroyed it in order to test the restoration. Alas, after restoration I was
not able to connect into RT any more, Apache replying

“Internal Server Error”

Displaying mysql.log, I got many messages like this one :

“061115 15:41:46 InnoDB: ERROR: the age of the last checkpoint is 14151694,
InnoDB: which exceeds the log group capacity 14150247.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.”

each of them during the restoration.

This is our support database, ibdata1 is 5 GB large, there are many large attachments. I imported
this database from RT2. BTW, on RT2, when the database reached 4GB, it failed, and I applied
(probably without great accuracy, but this fixed the failure) :

ALTER TABLE Attachments AVG_ROW_LENGTH=1000000, MAX_ROWS=8000;

In the Apache log, I am getting :

Access denied for user ‘rt_user’@‘localhost’

when I try to reset rt_user here is what I get :

mysql> GRANT ALL PRIVILEGES ON rt3.* TO rt_user@localhost IDENTIFIED BY “xxxyyy”;
ERROR 1133 (42000): Can’t find any matching row in the user table

I ran the full backup with the following command :

mysqldump -u
root -p --single-transaction --flush-logs --delete-master-logs --master-data=2 --all-databases |
gzip > /backup_path/full_backup_test.sql.gz

and twice the following command (just for testing):

mysqladmin -u root -p flush-log

and tried to restore using :

mysqladmin -u root -p “mypasswd”
gunzip < /backup_path/full_backup_test.sql.gz | mysql -u root -p

mysqlbinlog /backup_path/rt3_bin.000001 | mysql -u root -p

and again for two other logs.

Finally, here is my my.cnf : I copied the InnoDB settings from

http://wiki.bestpractical.com/index.cgi?PerformanceTuning

my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).

old_passwords=1
max_allowed_packet=100M

suggested by Request Tracker Wiki

set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
set-variable = sort_buffer=2M

preparing for incremental backup

log-bin = /mnt/hgfs/backup_rt3_test/rt3_bin
#set-variable = have_compress=YES

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Can anybody help ?

Best regards

Robert GRASSO
System Engineer

CEDRAT
15, Chemin de Malacher - Inovall�e - 38246 MEYLAN Cedex - FRANCE
Tel: +33 (0)4 76 90 50 45 Fax: +33 (0)4 76 90 16 09
mailto:Robert.Grasso@cedrat.com
Support service : mailto:support@cedrat.com
Commercial service : mailto:cedrat@cedrat.com
Web site : http://www.cedrat.com

Ah, solved the Innodb checkpoint error, increasing the innodb log file size to 200 MB fixed the
problem

I still have the rt_user issue …

Robert GRASSO
System Engineer

CEDRAT
15, Chemin de Malacher - Inovall�e - 38246 MEYLAN Cedex - FRANCE
Tel: +33 (0)4 76 90 50 45 Fax: +33 (0)4 76 90 16 09
mailto:Robert.Grasso@cedrat.com
Support service : mailto:support@cedrat.com
Commercial service : mailto:cedrat@cedrat.com
Web site : http://www.cedrat.com

found myself once more : it seems that the table mysql.users is not loaded into memory after
restoring the backup ? I was able to GRANT a new user, but not to change the rt_user password,
rt_user was unavailable; BUT listing the users table I could see it, in quite a normal shape. Thus I
just stopped and restarted the mysql server, and suddenly RT3 was available !

Ticket closed :wink:

Robert GRASSO
System Engineer

CEDRAT
15, Chemin de Malacher - Inovall�e - 38246 MEYLAN Cedex - FRANCE
Tel: +33 (0)4 76 90 50 45 Fax: +33 (0)4 76 90 16 09
mailto:Robert.Grasso@cedrat.com
Support service : mailto:support@cedrat.com
Commercial service : mailto:cedrat@cedrat.com
Web site : http://www.cedrat.com

Robert Grasso wrote:

Ah, solved the Innodb checkpoint error, increasing the innodb log file size to 200 MB fixed the
problem

I still have the rt_user issue …

try this

sh: mysql -u root -p (if passwd)
mysql> use mysql;
mysql>GRANT ALL PRIVILEGES ON rt3.* TO rt_user@localhost IDENTIFIED BY
“xxxyyy”;
reload the privileges and try to bring up rt then.

eats the blues for breakfast,
does unix for rent,
plays harp for food,
will play the flute for kicks
rides for the freedom

www.up-south.com