[rt-users] failure restoring backup

[Robert Grasso]

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 ;-)

---
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 3:13 PM
> To: Rt-Users
> Subject: RE: [rt-users] failure restoring backup
>
>
> 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
>
> _______________________________________________
> 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