[rt-users] failure restoring backup

[Robert Grasso]

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 at 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 http://wiki.bestpractical.com/index.cgi?PerformanceTuning
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 at cedrat.com
---
Support service       : mailto:support at cedrat.com
Commercial service : mailto:cedrat at cedrat.com
Web site                  : http://www.cedrat.com