I'll share my experience with you about recent mysql upgrade I made.
This upgrade was rather tricky for me. It took days of trial and error, before I finally succeeded. The issue here of course is the preservation of existing data.
My initial installation directory was c:\mysql.
Backing up your databases
- Make a new directory c:\mysql40.
- Download mysql-4.0.0a-alpha-win.zip, and unzip it into c:\mysql40.
- Double click on Setup.exe.
- Install it into c:\mysql40.
The first thing you want to do is backup all your databases using MYSQLDUMP.
See a list of your databases in C:\mysql\data, or wherever you have mysql installed. You need to backup all the databases, except the following: mysql, test. I'll explain later why you shouldn't backup these two databases.
- To begin the backup, open DOS console
- Change to c:\mysql\bin, and issue the command
- c:\mysql\bin > mysqldump -u Username -pPassword dbName > c:\dbname.sql
dbname is the name of the database you're backing up. The backup will be saved as dbname.sql, under the C:\ directory.
It's best to make sure the database is not being updated when you do the backup.
Repeat the backup process for every other database you want to backup.
Once all databases have been backed up, if the server is running, shut it down using,
c:\mysql\bin > mysqladmin -u root -pRoot_Password shutdown.
So assuming your root password is JXo3myYLi33c, the command will be mysqladmin -u root -pJXo3myYLi33c shutdown.
Configuration of MySQL 4
Open Notepad and type the following into it.
#slow query log#=
Save this file in C:\ as my.cnf.
The above configuration is a normal mysql server configuration. If you need extended server functionalities like Innodb or bdb database types, you have to include other parameters in my.cnf. More info can be obtained from www.mysql.com.
Change to c:\mysql40\bin, and issue the command mysqld --standalone.
To know if mysql is running, open a dos window and change to c:\mysql40\bin, then type
c:\mysql40\bin> mysqladmin version status proc
If you get errors with the commands, then you have some problem, which you must solve before you continue this tutorial.
SETTING A ROOT PASSWORD
Open a dos window and change to c:\mysql40\bin. Type the command
c:\mysql40\bin> mysql mysql.
Press Enter. You should see something like Welcome to the mysql monitor..., and the prompt should change to mysql>
mysql> delete from user where host='localhost' and user=' ';
You should see something like Query OK, 1 row affected (0.02 sec). If you get an error, check your spellings.
you should see Bye, and your prompt should change back to c:\mysql\bin.
c:\mysql40\bin> mysqladmin reload
c:\mysql40\bin> mysqladmin -u root password your_password. Replace your_password with whatever password you want to use as the root password.
CONNECTING TO MYSQL
Once mysql is running, you have to connect to it before you can begin using it. The general format is
c:\mysql\bin> mysql -h localhost -u root -p
CREATING EMPTY DATABASES
For each database you want to restore, you must create an empty database with the same name in c:\mysql40\data.
Login by changing to c:\mysql40\bin in a dos window. Issue the command
c:\mysql40\bin > mysql -u Username -p.
After logging in, create the databases with
mysql> CREATE DATABASE dbname.
dbname is the name of each database you backed up.
When all the databases have been created, continue with restoring the backups.
RESTORING FROM BACKUPS
To finish this upgrade, you need to restore the databases you backed up earlier.
Make sure the upgraded server is running. If not, double click on c:\mysql40\bin\mysqld.exe.
Open DOS console, and change to
c:\mysql40\bin, then issue the command
c:\mysql40\bin > mysql -u Username -pPassword -e "source c:\dbname.sql" dbName
Change Username, Password, and dbname to the proper values. dbname is the name of the database you want to restore.
When you're done with restoring all the databases, you can start using your upgraded server.
THE mysql.user TABLE
This table contains all usernames and passwords you created in your old server. The upgraded server however does not yet have your users.
In my case, backed up this table using MYSQLDUMP, then restored it the same way I restored other databases. Unfortunately, mysql failed to accept the username/password combos for login.
So I suspect that the encrypted passwords get messed up when they're backed up using MYSQLDUMP. I had to recreate my grant table from scratch, for mysql 4 to accept them.
If somebody has the answer to why the mysql.user table cannot be backed up and restored, please share it with us.
Read the following: http://www.mysql.com/doc/U/p/Upgrading-from-3.23.html