Upgrade MySQL to a new version

I. upgrade from version 3.23 to 4.10
4.10 new features: Support for transaction processing and stored procedures

Precautions for the upgrade process:

1: The upgrade uses the method of directly copying the installation file, which is simple and practical, but you must pay attention to the relevant configuration changes; 2: the database that directly copies the backup cannot be restored through the copy method, therefore, the mysqldump method must be used for backup. 3: The user management of mysql4.1 is different from that of mysql 3.23. max_questions, max_updates, and max_connections fields are added to the user table, which can be set differently for different mysql users, for backup/recovery, you can set max_questions and max_updates to a higher value. The specific setting value depends on the actual situation of the database.

Upgrade procedure:

1: stop the current MySQL Service

#/Www/mysql/bin/mysqladmin shutdown

2. Back up data files

# Cp-R/www/mysql // www/backup/

Back up data using the mysqldump command

# Mysqldump mvnforum>/www/backup/mvnforum. SQL # mysqldump demo>/www/backup/demo. SQL

 

Delete the original MySQL installation # rm-R/www/mysql

 

3: Install MySQL4.1

Create a user:

#/Usr/sbin/groupadd-g 201 mysql

Create user

#/Usr/sbin/useradd-u 1001-g mysql-d/www/mysql-m-s/bin/tcsh-c "MySQL Administrator" mysql

 

 

Set User Password: # passwd mysql # Set to mysql

 

Decompress the installation package # tar-zxvf mysql-standard -4.1.4-gamma-pc-linux-i686.tar.gz

 

Copy files to MySQL installation location # cp mysql-standard-4.1.4-gamma-pc-linux-i686/usr/local/mysql

 

Modify directory permissions # chown mysql. mysql/usr/local/mysql-R

 

Test whether it can be started #/usr/local/mysql/bin/mysqld_safe &

 

Modify the Startup File rc. local/usr/local/mysql/bin/mysqld_safe &

 

Modify the profile file to facilitate mysql Command Execution PATH = $ PATH:/usr/local/mysql/bin

 

Set the mysql password. If you leave the password blank, run the # mysqladmin-u root password command.

Add a MySQL user # mysql-u root-p

 

Create a user for local management #> grant usage on *. * to identified by; #> update user set Select_priv = Y, Insert_priv = Y, Update_priv = Y, Delete_priv = Y, Create_priv = Y, Drop_priv = Y, Reload_priv = Y, _ priv = Y, Process_priv = Y, File_priv = Y, Grant_priv = Y, References_priv = Y, Index_priv = Y, Alter_priv = Y, Show_db_priv = Y, Super_priv = Y, keys = Y, Lock_tables_priv = Y, Execute_priv = Y, Repl_slave_priv = Y, Repl_client_priv = Y where Host = 172.16.98.2 and User = root; #> update user set max_questions = 30000, max_updates = 30000 where User = "root ";

 

 

Create a user for Backup Recovery #> grant usage on *. * to identified by; #> update user set Select_priv = Y, Insert_priv = Y, Update_priv = Y, Delete_priv = Y, Create_priv = Y, Drop_priv = Y, Reload_priv = Y, shutdown_priv = Y, Process_priv = Y, File_priv = Y, Grant_priv = Y, region = Y, Index_priv = Y, Alter_priv = Y, Show_db_priv = Y, Super_priv = Y, region = Y, lock_tables_priv = Y, Execute_priv = Y, Repl_slave_priv = Y, Repl_client_priv = Y where Host = localhost and User = mysql; #> update user set max_questions = 3000000, max_updates = 3000000 where user = "mysql"; // mysql as backup/recovery user

 

Refresh table # mysqladmin-u root-p reload # mysqladmin-u root-p flush-privileges

 

4. Restore the backup database

Create databases songliDB and demo respectively. use mysql database users to recover backups.

#./Mysql-u mysql-p mvnforum </www/backup/mvnforum. SQL

#./Mysql-u mysql-p demo </www/backup/demo. SQL