You may want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication. The step is optional. See Section 15.1.1.1, “Creating a User For Replication”.

You must configure the master to support the binary log and configure a unique ID. See Section 15.1.1.2, “Setting the Replication Master Configuration”.

You must configure a unique ID for each slave that you want to connect to the Master. See Section 15.1.1.3, “Setting the Replication Slave Configuration”.

Before starting a data snapshot or the replication process, you should record the position of the binary log on the master. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events. See Section 15.1.1.4, “Obtaining the Master Replication Information”.

If you already have data on your Master and you want to synchronize your slave with this base data, then you will need to create a data snapshot of your database. You can create a snapshot using mysqldump (see Section 15.1.1.5, “Creating a Data Snapshot using mysqldump”) or by copying the data files directly (see Section 15.1.1.6, “Creating a Data Snapshot Using Raw Data Files”).

You will need to configure the slave with the Master settings, such as the hostname, login credentials and binary log name and positions. See Section 15.1.1.10, “Setting the Master Configuration on the Slave”.

If you are setting up a new MySQL master and one or more slaves, then you need only setup up the configuration, as you have no data to exchange. For guidance on setting up replication in this situation, see Section 15.1.1.7, “Setting up Replication with new Master and Slaves”.

If you are already running a MySQL server, and therefore already have data that will need to be transferred to your slaves before replication starts, have not previously configured the binary log and are able to shutdown your MySQL server for a short period during the process, see Section 15.1.1.8, “Setting up replication with existing data”.

If you are setting up additional slaves to an existing replication environment then you can setup the slaves without affecting the master. See Section 15.1.1.9, “Introducing Additional Slaves to an Existing Replication Environment”.

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (SBR) and this correlates to the standard statement-based binary logging format. Binary logging and replication in MySQL 5.1.4 and earlier, and all previous versions of MySQL, used this format.

Row-based binary logging logs the physical changes to individual table rows. In replication terms this is row-based replication (RBR), the master writes events to the binary log that indicate how individual table rows are affected. Support for RBR was added in MySQL 5.1.5.

As of MySQL 5.1.8, the binary logging format can be altered on the fly according the event being logged. With mixed-based logging (and the associated mixed-based replication (MBR)), statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described below. See Section 5.11.4.3, “Mixed Binary Logging (MBL) Format”.

SQL_MODE

FOREIGN_KEY_CHECKS

UNIQUE_CHECKS

CHARACTER_SET_CLIENT

COLLATION_CONNECTION

COLLATION_DATABASE

COLLATION_SERVER

SQL_AUTO_IS_NULL

--master-host

--master-user

--master-password

--master-port

--master-connect-retry

--master-ssl

--master-ssl-ca

--master-ssl-capath

--master-ssl-cert

--master-ssl-cipher

--master-ssl-key

--log-slave-updates

Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

A -> B -> C

Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

--log-warnings[=level]

This option causes a server to print more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started. This option is enabled by default; to disable it, use --skip-log-warnings. Aborted connections are not logged to the error log unless the value is greater than 1.

--master-connect-retry=seconds

The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.

--master-host=host_name

The hostname or IP number of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.

--master-info-file=file_name

The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory.

--master-password=password

The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

--master-port=port_number

The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).

--master-retry-count=count

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by --master-connect-retry and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400.

--master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name

These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 5.8.7.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

--master-user=user_name

The username of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. The value in the master.info file takes precedence if it can be read. If the master username is not set, the name test is assumed.

--max-relay-log-size=size

The size at which the server rotates relay log files automatically. For more information, see Section 15.4.2, “Replication Relay and Status Files”. The default size is 1GB.

--read-only

Cause the slave to allow no updates except from slave threads or from users having the SUPER privilege. This enables you to ensure that a slave server accepts no updates from clients. This option does not apply to TEMPORARY tables.

--relay-log=file_name

The basename for the relay log. The default basename is host_name-relay-bin. The server creates relay log files in sequence by adding a numeric suffix to the basename. You can specify the option to create hostname-independent relay log names, or if your relay logs tend to be big (and you don't want to decrease max_relay_log_size) and you need to put them in some area different from the data directory, or if you want to increase speed by balancing load between disks.

--relay-log-index=file_name

The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

--relay-log-info-file=file_name

The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.

--relay-log-purge={0|1}

Disable or enable automatic purging of relay logs as soon as they are not needed any more. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N.

--relay-log-space-limit=size

This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means “no limit.” This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs, because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to temporarily ignore --relay-log-space-limit.

--replicate-do-db=db_name

Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database.

An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

USE prices;
UPDATE sales.january SET amount=amount+1000;

The main reason for this “just check the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

--replicate-do-table=db_name.tbl_name

Tell the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-do-db. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

--replicate-ignore-db=db_name

Tells the slave to not replicate any statement where the default database (that is, the one selected by USE) is db_name. To specify more than one database to ignore, use this option multiple times, once for each database. You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

An example of what does not work as you might expect: If the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master, the UPDATE statement is replicated:

USE prices;
UPDATE sales.january SET amount=amount+1000;

If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

--replicate-ignore-table=db_name.tbl_name

Tells the slave thread to not replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

--replicate-rewrite-db=from_name->to_name

Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. This does not work for cross-database updates. The database name translation is done before the --replicate-* rules are tested.

If you use this option on the command line and the ‘>’ character is special to your command interpreter, quote the option value. For example:

shell> mysqld --replicate-rewrite-db="olddb->newdb"

--replicate-same-server-id

To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. Note that by default the slave I/O thread does not even write binary log events to the relay log if they have the slave's server id (this optimization helps save disk usage). So if you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.

--replicate-wild-do-table=db_name.tbl_name

Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the ‘%’ and ‘_’ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.

To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own%db, but not replicate tables from the my1ownAABCdb database, you should escape the ‘_’ and ‘%’ characters like this: --replicate-wild-do-table=my\_own\%db. If you're using the option on the command line, you might need to double the backslashes or quote the option value, depending on your command interpreter. For example, with the bash shell, you would need to type --replicate-wild-do-table=my\\_own\\%db.

--replicate-wild-ignore-table=db_name.tbl_name

Tells the slave thread not to replicate a statement where any table matches the given wildcard pattern. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates. See Section 15.4.3, “How Servers Evaluate Replication Rules”.

Example: --replicate-wild-ignore-table=foo%.bar% does not replicate updates that use a table where the database name starts with foo and the table name starts with bar.

For information about how matching works, see the description of the --replicate-wild-do-table option. The rules for including literal wildcard characters in the option value are the same as for --replicate-wild-ignore-table as well.

--report-host=slave_name

The hostname or IP number of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server. Leave the value unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP number of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.

--report-port=slave_port_num

The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a non-default port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.

--report-password=password

The account password of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

--report-user=user_name

The account username of the slave to be reported to the master during slave registration. This value appears in the output of SHOW SLAVE HOSTS on the master server if the --show-slave-auth-info option is given.

--show-slave-auth-info

Display slave usernames and passwords in the output of SHOW SLAVE HOSTS on the master server for slaves started with the --report-user and --report-password options.

--skip-slave-start

Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a START SLAVE statement.

--slave_compressed_protocol={0|1}

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

--slave-load-tmpdir=file_name

The name of the directory where the slave creates temporary files. This option is by default equal to the value of the tmpdir system variable. When the slave SQL thread replicates a LOAD DATA INFILE statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some filesystem that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the --relay-log option to place the relay logs in that filesystem.

The directory specified by this option should be located in a disk-based filesystem (not a memory-based filesystem) because the temporary files used to replicate LOAD DATA INFILE must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process.

--slave-net-timeout=seconds

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the --master-connect-retry option and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

--slave-skip-errors=[err_code1,err_code2,...|all]

Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

For error codes, you should use the numbers provided by the error message in your slave error log and in the output of SHOW SLAVE STATUS. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.

You can also (but should not) use the very non-recommended value of all to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you use all, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.

Examples:

--slave-skip-errors=1062,1053
--slave-skip-errors=all