MySQL manual version 5.0.20-mysql sync (ii)

Mysql

6.4 Setting Sync

The following describes how to quickly set up a MySQL synchronization server. Suppose you intend to synchronize all the databases and have not been set before. The master server needs to be shut down to complete all the steps.

The procedures described in this chapter can be used for a slave situation or for multiple slave situations.

This is just one of the most direct ways to set up slave, not just one. For example, there is already a master's data snapshot (snapshot), Master has set the server number ID (server_id) and enabled binary logging, which eliminates the need to shut down master or prevent data from being updated on master. Please see "6.9 Replication FAQ" for details.

To fully master the MySQL sync settings, it is best to read all of this chapter and test the 14.6.1 SQL statements for controlling Master Servers and 14.6.2 SQL statements for All the statements mentioned in controlling Slave Servers. And be familiar with the various sync setup options, please see "6.8 Replication Startup Options" for details.

Note that this process, along with some subsequent synchronous SQL statements, requires SUPER privileges. MySQL 4.0.2 before, it is PROCESS permission.

Make sure that the MySQL version is installed on both master and slave, and that these versions are compatible and are listed in the "6.5 Replication compatibility Between MySQL versions". Verify that there is a problem with the latest version, or do not report the bug.

A new account is added to master and slave can be used to connect to it. This account must be granted REPLICATION SLAVE permissions. If this account is for synchronization only (recommended), there is no need to grant additional permissions. Setting your domain is mydomain.com, you want to authorize an account REPL use password slavepass, allowing it to connect to master on any host in the domain. To create an account with the GRANT statement:

Mysql> GRANT REPLICATION SLAVE on *.*

-> to ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';

Before MySQL 4.0.2, replace REPLICATION SLAVE with FILE permissions:

Mysql> GRANT FILE on *.*

-> to ' repl ' @ '%.mydomain.com ' identified by ' slavepass ';

If you intend to execute the load TABLE from master or load DATA from master statement on Slave, you must grant additional permissions to the account:

Grant Global SUPER and RELOAD permissions.

Grant SELECT permissions on all tables that you want to load. Any table with no SELECT permission on Master will be skipped by LOAD DATA from MASTER.

If you use only the MyISAM table, execute the FLUSH tables with READ LOCK statement to refresh all tables and block other writes:

Mysql> FLUSH TABLES with READ LOCK;

Do not exit the client that executes the FLUSH TABLES statement to keep the read lock valid (the read lock is released if you exit). The data snapshot is then obtained from master. The simpler way is to pack the data directories and compress them. For example, on Unix, tar, PowerArchiver, WinRAR, WINZIP, or similar programs on Windows. To create a compressed package with tar, including all databases, simply execute the following command (change the directory to your Real path):

Shell> Tar-cvf/tmp/mysql-snapshot.tar.

If you only want to package a database this_db, just execute the command:

Shell> Tar-cvf/tmp/mysql-snapshot.tar./this_db

The file is then copied to the slave '/tmp ' directory. On the slave, perform the following command to unlock the compressed package (change the directory to your Real path):

Shell> Tar-xvf/tmp/mysql-snapshot.tar

The MySQL database may not need to be synchronized because the permissions table on the slave is not the same as master. At this point, you need to remove the compressed package when you unpack it. Also, do not include any log files, or ' master.info~ ' or ' relay-log.info ' files, in the compressed package. When the FLUSH TABLES with READ LOCK statement on Master is still in effect, read the file name and offset of the current binary on master:

MySQL > Show MASTER STATUS;

+---------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73 | Test | Manual,mysql |+---------------+----------+--------------+------------------+