Before you do an upgrade, you should back up your old databases.
You can always move the MySQL form files and datafiles between different versions on the same architecture as long as you have the same base version of MySQL. The current base version is 4. If you change the character set when running MySQL, you must run myisamchk -r -q -set-character-set=charset on all tables. Otherwise, your indexes may not be ordered correctly, because changing the character set may also change the sort order.
If you are afraid of new versions, you can always rename your old mysqld to something like mysqld-old-version-number. If your new mysqld then does something unexpected, you can simply shut it down and restart with your old mysqld.
If, after an upgrade, you experience problems with recompiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used an old header or library file when compiling your programs. In this case you should check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, please recompile your programs.
If problems occur, such as that the new mysqld server doesn't want to start or that you can't connect without a password, check that you don't have some old my.cnf file from your old installation. You can check this with: program-name -print-defaults. If this outputs anything other than the program name, you have an active my.cnf file that will affect things.
It is a good idea to rebuild and reinstall the Perl DBD-mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the Python MySQLdb module.
Some visible things have changed between MySQL 4.0 and MySQL 4.1 to fix some critical bugs and make MySQL more compatible with the ANSI SQL standard.
Instead of adding options (and a lot of code) to try to make 4.1 behave like 4.0 we have taken another approach:
We have added to the later MySQL 4.0 releases (from 4.0.12 on) the -new startup option for mysqld, which gives you the 4.1 behaviour for the most critical changes. You can also set this behaviour for a given client connection with the SET @@new=1 command.
If you believe that some of the following changes will affect you when you upgrade to 4.1, we recommend that before upgrading to 4.1, you download the latest MySQL 4.0 version and make sure that your applications work in the -new mode. This way you will have a smooth painless upgrade to 4.1 later.
In MySQL 4.1 we have done some things that may affect applications. The following is a list of things that you have to watch out for when upgrading to version 4.1:
The interface to aggregated UDF functions has changed a bit. One must now declare a clear function for each aggregate function.
TIMESTAMP is now returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to have this as a number (like Version 4.0 does) should add +0 to TIMESTAMP columns when you retrieve them. Different TIMESTAMP display widths are no longer supported.
This change was necessary for SQL standards compliance. In a future version, a further change will be made (backward compatible with this change), allowing the timestamp length to indicate the desired number of digits for fractions of a second.
For functions that produce a DATE, DATETIME, or TIME value, the result returned to the client now is fixed up to have a temporal type. For example, in MySQL 4.1, you get this result:
mysql SELECT CAST("2001-1-1" as DATETIME);
- '2001-01-01 00:00:00'
In MySQL 4.0, the result is different:
mysql SELECT CAST("2001-1-1" as DATETIME);
- '2001-01-01'
Binary values such as 0xFFDF now are assumed to be strings instead of numbers. This fixes some problems with character sets where it's convenient to input the string as a binary values. With this change, you should use CAST() if you want to compare binary values numerically as integers:
SELECT CAST(0xFEFF AS UNSIGNED INTEGER) CAST(0xFF AS UNSIGNED INTEGER)
Using binary items in a numeric context or comparing them using the = operator should work as before. (The -new option can be used to make the server behave as 4.1 in this repect from 4.0.13 on.)
AUTO_INCREMENT columns cannot take DEFAULT values. (In 4.0 these were just silently ignored; in 4.1, an error occurs).
SERIALIZE is no longer a valid option value for the sql_mode variable. You should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead. SERIALIZE is no longer valid for the -sql-mode option for mysqld, either. Use -transaction-isolation=SERIALIZABLE instead.
All column and tables now have a character set, which shows up in SHOW CREATE TABLE and mysqldump. (MySQL versions 4.0.6 and above can read the new dump files; older versions cannot.)
If you are running multiple servers on the same Windows machine, you should use a different -shared_memory_base_name option on all machines.
Note: The table definition format used in .frm files has changed slightly in 4.1. MySQL 4.0 versions from 4.0.11 on can read the new .frm format directly, but older versions cannot. If you need to move tables from 4.1 to an earlier MySQL version, you should use mysqldump. Section 4.8.6, “mysqldump, Dumping Table Structure and Data ”.
If you are running MySQL Server on Windows, please also see Section 2.5.8, “Upgrading MySQL under Windows ”.
In general, upgrading to 4.1 from an earlier MySQL version involves the following steps:
Check the changes section if there is some change that may affect your application. Section 2.5.1.1, “Preparing to Upgrade From Version 4.0 to 4.1 ”.
Read the 4.1 news items to see what significant new features you can use in 4.1. Section D.2, “Changes in release 4.1.x (Alpha) ”.
Update the grant tables to generate the new longer Password column that is needed for secure handling of passwords. The procedure uses the mysql_fix_privilege_tables and is described in Section 2.5.6, “Upgrading the Grant Tables ”.
The password hashing mechanism has changed in 4.1 to provide better security, but this may cause compatibility problems if you still have clients that use the client library from 4.0 or earlier. (It is very likely that you will have 4.0 clients in situations where clients connect from remote hosts that have not yet upgraded to 4.1). The following list indicates some possible upgrade strategies. They represent various tradeoffs between the goal of compatibility with old clients and the goal of security.
Don't upgrade to 4.1. No behaviour will change, but of course you cannot use any of the new features provided by the 4.1 client/server protocol, either. (MySQL 4.1 has an extended client/server protocol that offers such features as prepared statements and multiple result sets.) Section 9.1.4, “C API Prepared Statements ”.
Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table so that it can hold long password hashes. But run the server with the -old-passwords option to provide backward compatibility that allows pre-4.1 clients to continue to connect to their short-hash accounts. Eventually, when all your clients are upgraded to 4.1, you can stop using the -old-passwords server option. You can also change the passwords for your MySQL accounts to use the new more secure format.
Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table. If you know that all clients also have been upgraded to 4.1, don't run the server with the -old-passwords option. Instead, change the passwords on all existing accounts so that they have the new format. A pure-4.1 installation is the most secure.
Further background on password hashing with respect to client authentication and password-changing operations may be found in Section 4.2.11, “Password Hashing in MySQL 4.1 ”.
In general, you should do the following when upgrading to 4.0 from an earlier MySQL version:
Update the grant tables to add new privileges and features. The procedure uses the mysql_fix_privilege_tables script and is described in Section 2.5.6, “Upgrading the Grant Tables ”.
Edit any MySQL startup scripts or configure files to not use any of the deprecated options described later in this section.
Convert your old ISAM files to MyISAM files with the mysql_convert_table_format database script. (This is a Perl script; it requires that DBI be installed.) To convert the tables in a given database, use this command:
shell mysql_convert_table_format database db_name
Note that this should only be used if all tables in the given database are ISAM or MyISAM tables. To avoid converting tables of other types to MyISAM, you can explicitly list the names of your ISAM tables after the database name on the command line. You can also issue a ALTER TABLE table_name TYPE=MyISAM statement for each ISAM table to convert it to MyISAM.
Ensure that you don't have any MySQL clients that use shared libraries (like the Perl DBD-mysql mode). If you do, you should recompile them, because the data structures used in libmysqlclient.so have changed. The same applies to other MySQL interfaces as well, such as the Python MySQLdb module.
MySQL 4.0 will work even if you don't do the above, but you will not be able to use the new security privileges that MySQL 4.0 and you may run into problems when upgrading later to MySQL 4.1 or newer. The ISAM file format still works in MySQL 4.0 but it's deprecated and will be disabled in MySQL 5.0.
Old clients should work with a Version 4.0 server without any problems.
Even if you do the above, you can still downgrade to MySQL 3.23.52 or newer if you run into problems with the MySQL 4.0 series. In this case, you must use mysqldump to dump any tables that use full-text indexes and reload the dump file into the 3.23 server. This is necessary because 4.0 uses a new format for full-text indexing.
The following is a more complete list that tells what you must watch out for when upgrading to version 4.0:
MySQL 4.0 has a lot of new privileges in the mysql.user table. Section 4.3.1, “GRANT and REVOKE Syntax ”.
To get these new privileges to work, you must update the grant tables. The procedure is described in Section 2.5.6, “Upgrading the Grant Tables ”. Until you do this, all users have the SHOW DATABASES, CREATE TEMPORARY TABLES, and LOCK TABLES privileges. SUPER and EXECUTE privileges take their value from PROCESS. REPLICATION SLAVE and REPLICATION CLIENT take their values from FILE.
If you have any scripts that create new users, you may want to change them to use the new privileges. If you are not using GRANT commands in the scripts, this is a good time to change your scripts to use GRANT instead of modifying the grant tables directly..
From version 4.0.2 on, the option -safe-show-database is deprecated (and no longer does anything). Section 4.2.3, “Startup Options for mysqld Concerning Security ”.
If you get Access denied errors for new users in version 4.0.2 and up, you should check if you need some of the new grants that you didn't need before. In particular, you will need REPLICATION SLAVE (instead of FILE) for new slaves.
safe_mysqld is renamed to mysqld_safe. For backward compatibility, binary distributions will for some time include safe_mysqld as a symlink to mysqld_safe.
The startup parameters myisam_max_extra_sort_file_size and myisam_max_extra_sort_file_size are now given in bytes (they were given in megabytes before 4.0.3).
External system locking of MyISAM/ISAM files is now turned off by default. Your can turn this on by doing -external-locking. (However, this is never needed for most users.)
The following startup variables/options have been renamed:
| Old Name | New Name |
| myisam_bulk_insert_tree_size | bulk_insert_buffer_size |
| query_cache_startup_type | query_cache_type |
| record_buffer | read_buffer_size |
| record_rnd_buffer | read_rnd_buffer_size |
| sort_buffer | sort_buffer_size |
| warnings | log-warnings |
| -err-log | -log-error (for mysqld_safe) |
The startup options record_buffer, sort_buffer and warnings will still work in MySQL 4.0 but are deprecated.
The following SQL variables have changed name.
| Old Name | New Name |
| SQL_BIG_TABLES | BIG_TABLES |
| SQL_LOW_PRIORITY_UPDATES | LOW_PRIORITY_UPDATES |
| SQL_MAX_JOIN_SIZE | MAX_JOIN_SIZE |
| SQL_QUERY_CACHE_TYPE | QUERY_CACHE_TYPE |
The old names still work in MySQL 4.0 but are deprecated.
You have to use SET GLOBAL SQL_SLAVE_SKIP_COUNTER=# instead of SET SQL_SLAVE_SKIP_COUNTER=#.
The mysqld startup options -skip-locking and -enable-locking were renamed to -skip-external-locking and -external-locking.
SHOW MASTER STATUS now returns an empty set if binary logging is not enabled.
SHOW SLAVE STATUS now returns an empty set if slave is not initialised.
mysqld now has the option -temp-pool enabled by default as this gives better performance with some operating systems (most notably Linux).
DOUBLE and FLOAT columns now honour the UNSIGNED flag on storage (before, UNSIGNED was ignored for these columns).
ORDER BY col_name DESC sorts NULL values last, as of MySQL 4.0.11. In 3.23 and in earlier 4.0 versions, this was not always consistent.
SHOW INDEX has two more columns (Null and Index_type) than it had in 3.23.
CHECK, SIGNED, LOCALTIME and LOCALTIMESTAMP are now reserved words.
The result of all bitwise operators (|, , , , and ~)) is now unsigned. This may cause problems if you are using them in a context where you want a signed result. Section 6.3.5, “Cast Functions ”.
Note: when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned. In other words, before upgrading to MySQL 4.0, you should check your application for cases where you are subtracting a value from an unsigned entity and want a negative answer or subtracting an unsigned value from an integer column. You can disable this behaviour by using the -sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. Section 6.3.5, “Cast Functions ”.
To use MATCH ... AGAINST (... IN BOOLEAN MODE) with your tables, you need to rebuild them with REPAIR TABLE table_name USE_FRM.
LOCATE() and INSTR() are case-sensitive if one of the arguments is a binary string. Otherwise they are case-insensitive.
STRCMP() now uses the current character set when doing comparisons, which means that the default comparison behaviour now is case-insensitive.
HEX(string) now returns the characters in string converted to hexadecimal. If you want to convert a number to hexadecimal, you should ensure that you call HEX() with a numeric argument.
In 3.23, INSERT INTO ... SELECT always had IGNORE enabled. In 4.0.1, MySQL will stop (and possibly roll back) by default in case of an error unless you specify IGNORE.
The old C API functions mysql_drop_db(), mysql_create_db(), and mysql_connect() are no longer supported unless you compile MySQL with CFLAGS=-DUSE_OLD_FUNCTIONS. However, it is preferable to change client programs to use the new 4.0 API instead.
In the MYSQL_FIELD structure, length and max_length have changed from unsigned int to unsigned long. This should not cause any problems, except that they may generate warning messages when used as arguments in the printf() class of functions.
You should use TRUNCATE TABLE when you want to delete all rows from a table and you don't need to obtain a count of the number of rows that were deleted. (DELETE FROM table_name returns a row count in 4.0, and TRUNCATE TABLE is faster.)
You will get an error if you have an active LOCK TABLES or transaction when trying to execute TRUNCATE TABLE or DROP DATABASE.
You should use integers to store values in BIGINT columns (instead of using strings, as you did in MySQL 3.23). Using strings will still work, but using integers is more efficient.
The format of SHOW OPEN TABLES has changed.
Multi-threaded clients should use mysql_thread_init() and mysql_thread_end(). Section 9.1.14, “How to Make a Threaded Client ”.
If you want to recompile the Perl DBD::mysql module, you must get DBD-mysql version 1.2218 or newer because older DBD modules used the deprecated mysql_drop_db() call. Version 2.1022 or newer is recommended.
RAND(seed) returns a different random number series in 4.0 than in 3.23; this was done to further differentiate RAND(seed) and RAND(seed+1).
The default type returned by IFNULL(A,B) is now set to be the more 'general' of the types of A and B. (The general-to-specific order is string, REAL or INTEGER).
If you are running MySQL Server on Windows, please also see Section 2.5.8, “Upgrading MySQL under Windows ”. If you are using replication, please also see Section 4.10.2, “Replication Implementation Overview ”.
MySQL Version 3.23 supports tables of the new MyISAM type and the old ISAM type. You don't have to convert your old tables to use these with Version 3.23. By default, all new tables will be created with type MyISAM (unless you start mysqld with the -default-table-type=isam option). You can convert an ISAM table to MyISAM format with ALTER TABLE table_name TYPE=MyISAM or the Perl script mysql_convert_table_format.
Version 3.22 and 3.21 clients will work without any problems with a Version 3.23 server.
The following list tells what you have to watch out for when upgrading to Version 3.23:
All tables that use the tis620 character set must be fixed with myisamchk -r or REPAIR TABLE.
If you do a DROP DATABASE on a symbolically-linked database, both the link and the original database are deleted. (This didn't happen in 3.22 because configure didn't detect the availability of the readlink() system call.)
OPTIMIZE TABLE now works only for MyISAM tables. For other table types, you can use ALTER TABLE to optimise the table. During OPTIMIZE TABLE, the table is now locked to prevent it from being used by other threads.
The MySQL client mysql is now by default started with the option -no-named-commands (-g). This option can be disabled with -enable-named-commands (-G). This may cause incompatibility problems in some cases--for example, in SQL scripts that use named commands without a semicolon. Long format commands still work from the first line.
Date functions that work on parts of dates (like MONTH()) will now return 0 for 0000-00-00 dates. (In MySQL 3.22, these functions returned NULL.)
If you are using the german character sort order for ISAM tables, you must repair them with isamchk -r, because we have made some changes in the sort order.
The default return type of IF() now depends on both arguments and not only the first argument.
AUTO_INCREMENT columns should not be used to store negative numbers. The reason for this is that negative numbers caused problems when wrapping from -1 to 0. You should not store 0 in AUTO_INCREMENT columns, either; CHECK TABLE will complain about 0 values because they may change if you dump and restore the table. AUTO_INCREMENT for MyISAM tables is now handled at a lower level and is much faster than before. In addition, for MyISAM tables, old numbers are no longer reused, even if you delete rows from the table.
CASE, DELAYED, ELSE, END, FULLTEXT, INNER, RIGHT, THEN, and WHEN are now reserved words.
FLOAT(X) is now a true floating-point type and not a value with a fixed number of decimals.
When declaring columns using a DECIMAL(length,dec) type, the length argument no longer includes a place for the sign or the decimal point.
A TIME string must now be of one of the following formats: [[[DAYS] [H]H:]MM:]SS[.fraction] or [[[[[H]H]H]H]MM]SS[.fraction].
LIKE now compares strings using the same character comparison rules as for the = operator. If you require the old behaviour, you can compile MySQL with the CXXFLAGS=-DLIKE_CMP_TOUPPER flag.
REGEXP is now case-insensitive if neither of the strings are binary strings.
When you check or repair MyISAM (.MYI) tables, you should use the CHECK TABLE statement or the myisamchk command. For ISAM (.ISM) tables, use the isamchk command.
If you want your mysqldump files to be compatible between MySQL Version 3.22 and Version 3.23, you should not use the -opt or -all option to mysqldump.
Check all your calls to DATE_FORMAT() to make sure there is a % before each format character. (MySQL Version 3.22 and later already allowed this syntax.)
mysql_fetch_fields_direct() is now a function (it used to be a macro) and it returns a pointer to a MYSQL_FIELD instead of a MYSQL_FIELD.
mysql_num_fields() can no longer be used on a MYSQL* object (it's now a function that takes a MYSQL_RES* value as an argument). With a MYSQL* object, you should now use mysql_field_count() instead.
In MySQL Version 3.22, the output of SELECT DISTINCT ... was almost always sorted. In Version 3.23, you must use GROUP BY or ORDER BY to obtain sorted output.
SUM() now returns NULL instead of 0 if there are no matching rows. This is required by SQL-99.
An AND or OR with NULL values will now return NULL instead of 0. This mostly affects queries that use NOT on an AND/OR expression as NOT NULL = NULL.
LPAD() and RPAD() now shorten the result string if it's longer than the length argument.
Nothing that affects compatibility has changed between versions 3.21 and 3.22. The only pitfall is that new tables that are created with DATE type columns will use the new way to store the date. You can't access these new columns from an old version of mysqld.
After installing MySQL Version 3.22, you should start the new server and then run the mysql_fix_privilege_tables script. This will add the new privileges that you need to use the GRANT command. If you forget this, you will get Access denied when you try to use ALTER TABLE, CREATE INDEX, or DROP INDEX. The procedure for updating the grant tables is described in Section 2.5.6, “Upgrading the Grant Tables ”.
The C API interface to mysql_real_connect() has changed. If you have an old client program that calls this function, you must place a 0 for the new db argument (or recode the client to send the db element for faster connections). You must also call mysql_init() before calling mysql_real_connect(). This change was done to allow the new mysql_options() function to save options in the MYSQL handler structure.
The mysqld variable key_buffer has been renamed to key_buffer_size, but you can still use the old name in your startup files.
If you are running a version older than Version 3.20.28 and want to switch to Version 3.21, you need to do the following:
You can start the mysqld Version 3.21 server with the -old-protocol option to use it with clients from a Version 3.20 distribution. In this case, the new client function mysql_errno() will not return any server error, only CR_UNKNOWN_ERROR (but it works for client errors), and the server uses the old pre-3.21 password() checking rather than the new method.
If you are not using the -old-protocol option to mysqld, you will need to make the following changes:
All client code must be recompiled. If you are using ODBC, you must get the new MyODBC 2.x driver.
The script scripts/add_long_password must be run to convert the Password field in the mysql.user table to CHAR(16).
All passwords must be reassigned in the mysql.user table (to get 62-bit rather than 31-bit passwords).
The table format hasn't changed, so you don't have to convert any tables.
MySQL Version 3.20.28 and above can handle the new user table format without affecting clients. If you have a MySQL version earlier than Version 3.20.28, passwords will no longer work with it if you convert the user table. So to be safe, you should first upgrade to at least Version 3.20.28 and then upgrade to Version 3.21.
The new client code works with a 3.20.x mysqld server, so if you experience problems with 3.21.x, you can use the old 3.20.x server without having to recompile the clients again.
If you are not using the -old-protocol option to mysqld, old clients will be unable to connect and will issue the following error message:
ERROR: Protocol mismatch. Server Version = 10 Client Version = 9
The new Perl DBI/DBD interface also supports the old mysqlperl interface. The only change you have to make if you use mysqlperl is to change the arguments to the connect() function. The new arguments are: host, database, user, and password (note that the user and password arguments have changed places). Section 9.5.2, “The DBI Interface ”.
The following changes may affect queries in old applications:
HAVING must now be specified before any ORDER BY clause.
The parameters to LOCATE() have been swapped.
There are some new reserved words. The most notable are DATE, TIME, and TIMESTAMP.
Some releases introduce changes to the structure of the grant tables (the tables in the mysql database) to add new privileges or features. To make sure that your grant tables are current when you update to a new version of MySQL, you should update your grant tables as well.
On Unix or Unix-like systems, update the grant tables by running the mysql_fix_privilege_tables script:
shell mysql_fix_privilege_tables
You must run this script while the server is running. It attempts to connect to the server running on the local host as root. If your root account requires a password, indicate the password on the command line. For MySQL 4.1 and up, specify the password like this:
shell mysql_fix_privilege_tables --password=root_password
Prior to MySQL 4.1, specify the password like this:
shell mysql_fix_privilege_tables root_password
The mysql_fix_privilege_tables script performs any actions necessary to convert your grant tables to the current format. You may see some Duplicate column name warnings as it runs; they can be ignored.
After running the script, stop the server and restart it.
On Windows systems, there isn't an easy way to update the grant tables until MySQL 4.0.15. From version 4.0.15 on, MySQL distributions include a mysql_fix_privilege_tables.sql SQL script that you can run using the mysql client. If your MySQL installation is located at C:\mysql, the command looks like this (enter it all on one line):
shell C:\mysql\bin\mysql -f -u root -p mysql
C:\mysql\scripts\mysql_fix_privilege_tables.sql
If your installation is located in some other directory, adjust the pathnames appropriately.
The command will prompt you for the root password; enter it when prompted.
As with the Unix procedure, you may see some Duplicate column name warnings as mysql processes the statements in the mysql_fix_privilege_tables.sql script; they can be ignored.
After running the script, stop the server and restart it.
If you are using MySQL Version 3.23, you can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) Section 7.1, “MyISAM Tables ”.
The MySQL ISAM data and index files (.ISD and *.ISM, respectively) are architecture-dependent and in some cases OS-dependent. If you want to move your applications to another machine that has a different architecture or OS than your current machine, you should not try to move a database by simply copying the files to the other machine. Use mysqldump instead.
By default, mysqldump will create a file containing SQL statements. You can then transfer the file to the other machine and feed it as input to the mysql client.
Try mysqldump -help to see what options are available. If you are moving the data to a newer version of MySQL, you should use mysqldump -opt with the newer version to get a fast, compact dump.
The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database is located:
shell mysqladmin -h 'other hostname' create db_name
shell mysqldump --opt db_name \
| mysql -h 'other hostname' db_name
If you want to copy a database from a remote machine over a slow network, you can use:
shell mysqladmin create db_name
shell mysqldump -h 'other hostname' --opt --compress db_name \
| mysql db_name
You can also store the result in a file, then transfer the file to the target machine and load the file into the database there. For example, you can dump a database to a file on the source machine like this:
shell mysqldump --quick db_name | gzip db_name.contents.gz
(The file created in this example is compressed.) Transfer the file containing the database contents to the target machine and run these commands there:
shell mysqladmin create db_name shell gunzip db_name.contents.gz | mysql db_name
You can also use mysqldump and mysqlimport to transfer the database. For big tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full pathname of the directory you use to store the output from mysqldump.
First, create the directory for the output files and dump the database:
shell mkdir DUMPDIR shell mysqldump --tab=DUMPDIR db_name
Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:
shell mysqladmin create db_name # create database shell cat DUMPDIR/*.sql | mysql db_name # create tables in database shell mysqlimport db_name DUMPDIR/*.txt # load data into tables
Also, don't forget to copy the mysql database because that's where the grant tables (user, db, host) are stored. You may have to run commands as the MySQL root user on the new machine until you have the mysql database in place.
After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.
When upgrading MySQL under Windows, please follow these steps:
Download the latest Windows distribution of MySQL.
Choose a time of day with low usage, where a maintenance break is acceptable.
Alert the users that still are active about the maintenance break.
Stop the running MySQL Server (for example, with NET STOP mysql if you are running MySQL as a service, or with mysqladmin shutdown otherwise).
Exit the WinMySQLadmin program if it is running.
Run the installation script of the Windows distribution, by clicking the "Install" button in WinZip and following the installation steps of the script.
You may either overwrite your old MySQL installation (usually located at C:\mysql), or install it into a different directory, such as C:\mysql4. Overwriting the old installation is recommended.
The version of MySQL that is started as a service is determined by the basedir parameter in the my.ini file of your Windows directory (for example, C:\WINNT).
Restart the server (for example, with NET START mysql if you run MYSQL as a service, or by invoking mysqld directly otherwise).
Update the grant tables. The procedure is described in Section 2.5.6, “Upgrading the Grant Tables ”.
Possible error situations:
A system error has occurred. System error 1067 has occurred. The process terminated unexpectedly.
This cryptic error means that your my.cnf file (by default C:\my.cnf) contains an option that cannot be recognised by MySQL. You can verify that this is the case by trying to restart MySQL with the my.cnf file renamed, for example, to my.cnf.old to prevent the server from using it. Once you have verified it, you need to identify which option is the culprit. Create a new my.cnf file and move parts of the old file to it (restarting the server after you move each part) until you determine which part causes server startup to fail.