开发进行时...

Mike Peters
http://www.softwareprojects.com/resources/programming/t-how-to-move-copy-a-live-mysql-database-1257.html

The challeneg:

把一个MySQL数据库移到新机器上,数据库处理联机状态，一直高负载运行，80G数据量，5000多个表。 不可能选择关机以“定期维护”；我们不能出现一点差错和丢失一点心数据，要保证正在处理的金融交易且跟踪整个操作。

可采用的方案：

* MySQLHotCopy - 一个Perl脚本,能够锁住MySQL表然后使用系统级别的 "cp" 或 "scp"来进行快速的数据库复制。 完成后，MySQLHotCopy 刷新数据表释放锁。最快的方法只是只能用在源与目标库都在同一台机器上。
不适合这个案例并且我们也不清楚这个脚本的用处，因为你要建立一个容灾数据库备份，你最好把它备份在远程机器上。

* rsync - a utility that provides fast incremental file transfer.

RSync-ing directories on two remote machines is as simple as:

rsync --progress --stats --compress --rsh=/usr/local/bin/ssh /www/* webserver:simple_path_name

能够非常好的处理日常文件但不适合数据库，因为当你同步联机数据库时，你不会取到一个数据DNS记录的“安全”点，保证那个点的所有数据都是交易完成的且能够转换完(否则登上新机器产生很多新问题)
根据不同的负载，将会一定程序的数据丢失。

* MySQLDump - 我们的托管提供商给的建议。MySQLDump 建立一个MySQL 脚本文件并能够在目标机器上运行以安全的重新建立该数据库。

mysqldump -hREMOTE_HOST -uroot -pREMOTEpwd --opt --compress REMOTEdb | mysql -uroot -pLOCALpwd LOCALdb

这个命令能够执行在目标数据库上并能拉出所有源数据库上的内容，重建所有的表与数据。
不幸的是这个方法必须要求断开所有数据库连接以避免丢失数据。在我们案例中，几分钟的关机都是不可能的，只能是联机状态。

选的方法 - Replication:

MySQL Replication, 我们住地的数据库大师建议的方案，是一个在两个数据库服务器之间建立主从关系的处理过程，通过跨机器的复制，从机能够不断地、自动地获得了应用在主机的操作结果。
一旦配置和激活了复制，从机将像一个联机数据库在运行，从主机获取一个所有数据的最新快照。
我们喜欢这种没有“漏洞”的方案。没有潜在的数据丢失也不需要关机。
从机一旦准备好，就可以通一个开关将所有的数据库请求指向新的机器，然后断开主/从机关系并开始运行新机器。

下面是具体的操作步聚：

Step 1: 在master mysql上创建一个能在slave机器上访问的帐号. 这个帐号必须具有REPLICATION SLAVE 权限。

mysql> use mysql;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Step 2: 打开MySQL交易二进制日志。只有启用二进制日志才能使服务器成为一个复制主机。找到master服务器上的my.cnf文件在[mysqld]选项卡下加入现面两行：

[mysqld]
log-bin=mysql-bin
server-id=1

如果这些选项不存在（我们的案例中有这个选项），那么将不得不重启MySQL服务。

Step 3: Flush all the tables and block write statements by executing a FLUSH TABLES WITH READ LOCK statement - this is important so that we can safely copy the database files, with no fear of the files being changed during the copy operation. Make sure you keep the shell window where you issue the command below open throughout the process, until the tar finishes. Once this window closes, the lock will be automatically released.

mysql> SET GLOBAL WAIT_TIMEOUT=600000; SET WAIT_TIMEOUT = 600000; FLUSH TABLES WITH READ LOCK;

Step 4: Take a snapshot of all data on the master database server. The easiest way to create a snapshot is to use tar. (Make sure you have sufficient storage space on your master server to hold the tar. If unsure, calculate the current size of your database and plan for an additional 70% of available space)

cd /var/db/mysql
tar -cvf /tmp/mysql-snapshot.tar ./ --exclude mysql &

On our system this operation took a little over four hours to complete. Notice the "&" operand - this causes tar to run as a background process. Useful if your shell gets disconnected. You'll know it's done by periodically viewing the process-list using "ps -ax"

Step 5: While the read lock placed by FLUSH TABLES WITH READ LOCK is in effect, read the value of the current binary log name and offset on the master:

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+

The File column shows the name of the log and Position shows the offset within the file. In this example, the binary log file is mysql-bin.003 and the offset is 73. Record these values. You need them later when you are setting up the slave. They represent the replication coordinates at which the slave should begin processing new updates from the master.

Step 6: Now that you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master:

mysql> UNLOCK TABLES;

Step 7: Hop on the new database server machine (slave). Shutdown the database, then locate the my.cnf file and add these lines to the file:

[mysqld]
server-id=2

Step 8: Copy and extract master database snapshot to the slave server under /var/db

cd /var/db

ftp
open masterdb.com
cd /tmp
get mysql-snapshot.tar
exit

tar --extract --file=mysql-snapshot.tar

Step 9: Start the slave database and issue these commands, replacing the option values with the actual values relevant to your system:

mysql> CHANGE MASTER TO
MASTER_HOST='master_host_name',
MASTER_USER='replication_user_name',
MASTER_PASSWORD='replication_password',
MASTER_LOG_FILE='recorded_log_file_name',
MASTER_LOG_POS=recorded_log_position;

Step 10: Start the slave

mysql> START SLAVE;

If everything went well, your slave database will now connect to the master database, downloading all transactions following the time you took the snapshot. From this moment on, the slave database will continually feed itself with data from the master database, always staying in sync.

Confirm the slave server is up-to-date by running a:

mysql> SHOW SLAVE STATUS;

and comparing the position markers with what the master database reports under:

mysql> SHOW MASTER STATUS;

Step 11: Update database-connect functions

Once the slave database is up-to-date, it is now time to update your application code, routing all calls to the new server.

Assuming you have all database access functions encapsulated in a database.php / database.c layer, locate the function that establishes a connection to the database and update the IP address or domain name to point to the slave database.

Voila! There you have it -- migration of MySQL from machine A to machine B without ever taking the database down.

Step 12: Test

Run a tail on the master database MySQL log file, or run SHOW PROCESSLIST and make sure no one is accessing the master database any more. Once confirmed, shutdown the master database and test for any strange side effects.

I recommend you keep the old database server up for a few days until you're absolutely certain the transfer went through smoothly.

Running a mysqlcheck --all-databases on the new database is highly recommended.

Any comments, tips or insight are welcome!
Mike Peters, 04-16-2007
Update:

If you don't have enough storage space on the master server to hold the tar file, you could tar across ssh using this command:

tar -czpf - ./ --exclude mysql | ssh -lUSERNAME NEWHOST.COM tar -xzpf - -C /var/db/mysql

Replace USERNAME with a login on the slave machine
Replace NEWHOST.COM with the domain of the slave machine