Go to the first, previous, next, last section, table of contents.
One way replication can be used both to increase robustness and
speed. For robustness you have two systems and switch to the backup if
you get problems you witch to the backup. The extra speed is achieved by
sending a part of the non updating queries to the replica server. Of
course this only works if non updating queries dominate, but that is the
One way replication is planned for the near future. This will be
implemented so that slave servers will be synchronized with low priority
updates and delayed inserts up to date (this will give readers higher
priority than writers).
MySQL doesn't (yet) have database replication, but here are
some info on how do to it.
The most general way to replicate a database is to use the update
log. See section 9.2 The update log. This requires one database that acts as a master
(to which data changes are made) and one or more other databases that act
as slaves. To update a slave, just run
mysql < update_log.
Supply host, user and password options that are appropriate for the slave
database, and use the update log from the master database as input.
If you never delete anything from a table, you can use a
column to find out which rows have been inserted or changed in the table
since the last replication (by comparing to the time when you did the
replication last time) and only copy these rows to the mirror.
It is possible to make a two-way updating system using both the update
log (for deletes) and timestamps (on both sides). But in that case you
must be able to handle conflicts when the same data have been changed in
both ends. You probably want to keep the old version to help with
deciding what has been updated.
Because replication in this case is done with SQL statements, you should not
use the following functions in statements that update the database; they may
not return the same value as in the original database:
All time functions are safe to use, as the timestamp is sent to the
mirror if needed.
LAST_INSERT_ID() is also safe to use.
Since MySQL tables are stored as files, it is easy to do a
backup. To get a consistent backup, do a
LOCK TABLES on the
relevant tables. See section 7.24
LOCK TABLES/UNLOCK TABLES syntax. You only need a
read lock; this allows other threads to continue to query the tables while
you are making a copy of the files in the database directory. If you want to
make a SQL level backup, you can use
SELECT INTO OUTFILE.
Another way to backup a database is to use the
Do a full backup of your databases:
shell> mysqldump --tab=/path/to/some/dir --opt --full
You can also simply copy all table files (`*.frm', `*.MYD' and
`*.MYI' files), as long as the server isn't updating anything.
mysqld if it's running, then start it with the
--log-update option. You will get log files with names of the
form `hostname.n', where
n is a number that is
incremented each time you execute
mysqladmin refresh or
mysqladmin flush-logs, the
FLUSH LOGS statement, or
restart the server. These log files provide you with the information
you need to replicate changes to the database that are made subsequent
to the point at which you executed
If you have to restore something, try to recover your tables using
myisamchk -r first. That should work in 99.9% of all cases. If
myisamchk fails, try the following procedure:
Restore the original
Execute the following command to re-run the updates in the update logs:
shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql
ls is used to get all the log files in the right order.
You can also do selective backups with
SELECT * INTO OUTFILE 'file_name'
FROM tbl_name and restore with
LOAD DATA INFILE 'file_name' REPLACE
... To avoid duplicate records, you need a
PRIMARY KEY or a
UNIQUE key in the table. The
REPLACE keyword causes old records
to be replaced with new ones when a new record duplicates an old record on
a unique key value.
There are circumstances when you might want to run multiple servers on the same
machine. For example, you might want to test a new MySQL release
while leaving your existing production setup undisturbed. Or you might
be an Internet service provider that wants to provide independent
MySQL installations for different customers.
If you want to run multiple servers, the easiest way is to compile the servers
with different TCP/IP ports and socket files so they are not
both listening to the same TCP/IP port or socket file.
Assume an existing server is configured for the default port number and
socket file. Then configure the new server with a
something like this:
shell> ./configure --with-tcp-port=port_number
file_name should be different than the
default port number and socket file pathname, and the
should specify an installation directory different than the one under which
the existing MySQL installation is located.
You can check the socket and port used by any currently-executing
MySQL server with this command:
shell> mysqladmin -h hostname --port=port_number variables
If you have a MySQL server running on the port you used, you will
get a list of some of the most important configurable variables in
MySQL, including the socket name.
You should also edit the initialization script for your machine (probably
`mysql.server') to start and kill multiple
You don't have to recompile a new MySQL server just to start with
a different port and socket. You can change the port and socket to be used
by specifying them at runtime as options to
shell> /path/to/safe_mysqld --socket=file_name --port=port_number
If you run the new server on the same database directory as another server
with logging enabled, you should also specify the name of the log files
both servers may be trying to write to the same log file.
Warning: Normally you should never have two servers that update
data in the same database! If your OS doesn't support fault-free system
locking, this may lead to unpleasant surprises!
If you want to use another database directory for the second server, you
can use the
--datadir=path option to
When you want to connect to a MySQL server that is running with
a different port than the port that is compiled into your client, you
can use one of the following methods:
Start the client with
--host 'hostname' --port=port_numer or
[--host localhost] --socket=file_name.
In your C or Perl programs, you can give the port and socket arguments
when connecting to the MySQL server.
MYSQL_TCP_PORT environment variables
to point to the Unix socket and TCP/IP port before you start your clients.
If you normally use a specific socket or port, you should place commands
to set these environment variables in your `.login' file.
See section 12.1 Overview of the different MySQL programs.
Specify the default socket and TCP/IP port in the `.my.cnf' file in your
home directory. See section 4.15.4 Option files.
Go to the first, previous, next, last section, table of contents.