[rt-users] failure restoring backup

[Robert Grasso]

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 at cedrat.com
---
Support service       : mailto:support at cedrat.com
Commercial service : mailto:cedrat at cedrat.com
Web site                  : http://www.cedrat.com



> -----Original Message-----
> From: rt-users-bounces at lists.bestpractical.com
> [mailto:rt-users-bounces at lists.bestpractical.com]On Behalf Of Robert
> Grasso
> Sent: Thursday, November 16, 2006 12:58 PM
> To: Rt-Users
> Subject: [rt-users] 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 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
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com