Upgrading/Downgrading MySQL
===========================
Before you do an upgrade, you should back up your old databases.
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.
Upgrading From Version 4.0 to 4.1
---------------------------------
gives
you the 4.1 behaviour for the most critical changes. You can also
enable these behaviours for a given client connection with the `SET
@@new=1' command, or turn them off if they are on with `SET @@new=0'.
If you believe that some of the 4.1 changes will affect you, we
recommend that before upgrading to 4.1, you download the latest MySQL
4.0 version and run it with the `--new' option. That way you can test
the new behaviors in 4.0 to make sure that your applications work with
them. This will help you have a smooth painless transition when you
perform a full upgrade to 4.1 later.
The following list describes changes that may affect applications and
that you should watch out for when upgrading to version 4.1:
* `TIMESTAMP' is now returned as a string in `'YYYY-MM-DD HH:MM:SS''
format. (The `--new' option can be used from 4.0.12 on to make a
4.0 server behave as 4.1 in this respect.) If you want to have
the value returned as a number (like Version 4.0 does) you should
add +0 to `TIMESTAMP' columns when you retrieve them:
mysql> SELECT ts_col + 0 FROM tbl_name;
Display widths for `TIMESTAMP' columns are no longer supported.
For example, if you declare a column as `TIMESTAMP(10)', the `(10)'
is ignored.
These changes were 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.
* 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 a string as a binary value. With
this change, you should use `CAST()' if you want to compare binary
values numerically as integers:
mysql> SELECT CAST(0xFEFF AS UNSIGNED INTEGER) < CAST(0xFF AS UNSIGNED INTEGER);
-> 0
If you don't use `CAST()', a lexical string comparison will be
done:
mysql> SELECT 0xFEFF < 0xFF;
-> 1
Using binary items in a numeric context or comparing them using the
`=' operator should work as before. (The `--new' option can be
used from 4.0.13 on to make a 4.0 server behave as 4.1 in this
respect.)
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'
* `DEFAULT' values no longer can be specified for `AUTO_INCREMENT'
columns. (In 4.0, a `DEFAULT' value is silently ignored; in 4.1,
an error occurs).
`--sql-mode' option for `mysqld', either. Use
`--transaction-isolation=SERIALIZABLE' instead.
can read
the new dump files; older versions cannot.)
need to move tables from 4.1 to a version earlier than 4.0.11, you
should use `mysqldump'. *Note `mysqldump': mysqldump.
* If you are running multiple servers on the same Windows machine,
you should use a different `--shared_memory_base_name' option on
all machines.
* The interface to aggregated UDF functions has changed a bit. You
must now declare a `clear()' function for each aggregate function.
In general, upgrading to 4.1 from an earlier MySQL version involves the
following steps:
* Check the changes listed earlier in this section to see if there
are any that may affect your applications.
* Read the 4.1 news items to see what significant new features you
can use in 4.1. *Note News-4.1.x::.
* If you are running MySQL Server on Windows, please also see *Note
Windows upgrading::.
* After upgrading, update the grant tables to generate the new
longer `Password' column that is needed for secure handling of
passwords. The procedure uses `mysql_fix_privilege_tables' and is
described in *Note Upgrading-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.) *Note 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.
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 *Note
Password hashing::.
Upgrading From Version 3.23 to 4.0
----------------------------------
In general, you should do the following when upgrading to 4.0 from an
earlier MySQL version:
* After upgrading, update the grant tables to add new privileges and
features. The procedure uses the `mysql_fix_privilege_tables'
script and is described in *Note Upgrading-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'.
To find out the table type for a given table, use this statement:
mysql> SHOW TABLE STATUS LIKE 'tbl_name';
* 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 (not compiled in by default) in MySQL 4.1. `MyISAM' tables
should be used instead.
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.
*Note `GRANT': GRANT.
To get these new privileges to work, you must update the grant
tables. The procedure is described in *Note
Upgrading-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). *Note Privileges
options::.
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'.
* InnoDB support is now included by default in binary distributions.
If you build MySQL from source, InnoDB is configured in by default.
If you do not use InnoDB and want to save memory when running a
server that has InnoDB support enabled, use the `--skip-innodb'
server startop option. To compile MySQL without InnoDB support,
run `configure' with the `--without-innodb' option.
* 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.
`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. *Note 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'. *Note 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.
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'.
* The format of `SHOW OPEN TABLES' has changed.
* Multi-threaded clients should use `mysql_thread_init()' and
`mysql_thread_end()'. *Note Threaded clients::.
* 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 *Note
Windows upgrading::. If you are using replication, please also see
*Note Replication Implementation::.
Upgrading From Version 3.22 to 3.23
-----------------------------------
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.
Upgrading from Version 3.21 to 3.22
-----------------------------------
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 *Note Upgrading-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.
Upgrading from Version 3.20 to 3.21
-----------------------------------
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.
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). *Note Perl `DBI' Class:
Perl DBI Class.
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'.
Upgrading the Grant Tables
--------------------------
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
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
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.
Upgrading to Another Architecture
---------------------------------
of any
byte-swapping issues.) *Note `MyISAM' Tables: MyISAM.
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
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
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.
Upgrading MySQL under Windows
-----------------------------
When upgrading MySQL under Windows, please follow these steps:
1. Download the latest Windows distribution of MySQL.
2. Choose a time of day with low usage, where a maintenance break is
acceptable.
3. Alert the users that still are active about the maintenance break.
4. Stop the running MySQL Server (for example, with `NET STOP mysql'
if you are running MySQL as a service, or with `mysqladmin
shutdown' otherwise).
5. Exit the `WinMySQLadmin' program if it is running.
6. Run the installation script of the Windows distribution, by
clicking the "Install" button in WinZip and following the
installation steps of the script.
recommended.
8. 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').
9. Restart the server (for example, with `NET START mysql' if you run
MYSQL as a service, or by invoking `mysqld' directly otherwise).
10. Update the grant tables. The procedure is described in *Note
Upgrading-grant-tables::.
Possible error situations:
A system error has occurred.
System error 1067 has occurred.
The process terminated unexpectedly.
`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.
[Назад] [Содержание] [Вперед]
| Главная |