Mysql database restore

From Nuclear Physics Group Documentation Pages
Revision as of 11:13, 2 October 2008 by Maurik (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

MySQL restore.

After Roengen was moved to a Virtual system running RHEL5, I did the following to restore MySQL functionality on Roentgen:

  1. Install mysql-server on Roentgen.
  2. Install MySQL 4.1.x mysql-server on Fermi (a 64 bit RHEL4 machine) so we can read the old roentgen files.
  3. Copy the Mysql database to /net/data/pumpkin1/VM/roentgen_var/mysql
  4. Load this database into Fermi's Mysql: link db directory (step 2) to /var/lib/mysql on Fermi.
  5. Startup mysqld on Fermi (execute mysqld_safe)
  6. Dump database: mysqldump -u root -p --all-databases | gzip -c > /net/data/pumpkin1/VM/roentgen_var/mysql_db.mysql.gz
  7. Initialize the MySQL on roentgen: mysql_install_db
  8. Start server: mysql_safe --user=mysql
  9. Restore old database from dump file: zcat /net/data/pumpkin1/VM/roentgen_var/mysql_db.mysql.gz | mysql
  10. Shutdown the database: mysqladmin shutdown
  11. Startup again: mysql_safe --user=mysql
  12. Check to see if grant tables are now correct:
    • try "mysql" to see if you can get in, no.
    • try "mysql -p" to check super user password.
    • Drop the anonymous and guest users:
      mysql -u root -p
      >> use mysql;
      >> select Host,User,Password,Select_priv,Super_priv from user;
      >> delete from user where User=;
      >> delete from user where User='guest';
  13. Check wiki:
  14. UREKA, do a little victory dance!
  15. Now edit Wiki with update
  16. Finally, make sure MySQL starts automatically: chkconfig --level 35 mysqld on