MySQL Main master

A B database installation Just don't say it.

Start A B Database

execute on A B database

Grant Replication Slave on * * to ' repl ' @ ' 127.0.0.1 ' identified by ' 123456 ';

Link Database

Change a Host my.cnf Configuration

Log-bin=mysql-bin

# Binary Logging format-mixed recommended

Binlog_format=mixed

# required Unique ID between 1 and 2^32-1

# defaults to 1 if master-host are not set

# but would not function as a master ifomitted

Server-id = 1

BINLOG-DO-DB = Test

binlog-ignore-db = MySQL

Log-slave-updates

Sync_binlog=1

Auto_increment_offset = 1

Auto_increment_increment= 2

REPLICATE-DO-DB = Test

Replicate-ignore-db =mysql

Restart the database when you are done changing

Change b Host my.cnf Configuration

Log-bin=mysql-bin1

# Binary Logging format-mixed recommended

Binlog_format=mixed

# required Unique ID between 1 and 2^32-1

# defaults to 1 if master-host are not set

# but would not function as a master ifomitted

Server-id = 111

BINLOG-DO-DB = Test

binlog-ignore-db = MySQL

Log-slave-updates

Sync_binlog=1

Auto_increment_offset = 2

Auto_increment_increment= 2

REPLICATE-DO-DB = Test

Replicate-ignore-db =mysql

Restart the database when you are done changing

A Host Login Database

Showmaster Status\g;

Flushtables with read lock;

Slavestop

Changemaster tomaster_host= ' 127.0.0.1 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Mysql-bin1.000002 ', master_log_pos=441,master_port=3306;

Flushprivileges;

Unlocktables

B Host Login Database

Showmaster Status\g;

Flushtables with read lock;

Slavestop

Changemaster tomaster_host= ' 127.0.0.1 ', master_user= ' repl ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=186;

Flushprivileges;

Unlocktables

Complete

A

Server-id

The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. the master_id must be a positive integer value between 1 and232–1, and the slave_id value must be 2 to 232– A positive integer value between 1.

Log-bin

indicates that Binlog is turned on to enable the option to be written to Slave relay-log through I/O and can be replication The premise ;

Binlog-do-db

represents a database that requires logging of the log in. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options

Binlog-ignore-db

represents a database that does not require logging of binary logs. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options

Replicate-do-db

represents a database that needs to be synchronized, if multiple databases can be separated by commas, or multiple replicate-do-db options are used

Replicate-ignore-db=mysql

represents a database that does not need to be synchronized, if multiple databases can be separated by commas, or multiple replicate-ignore-db=mysql options are used

Log-slave-updates

Configure whether the update operation from the library writes to the binaries, and if this is done from the library and other main libraries from the library, then this parameter needs to be hit so that the log can be synchronized from the library from the library

Slave-skip-errors

during the copy process, SQL error in Binlog due to various reasons, by default, the copy is stopped from the library and the user is involved. you can set slave-skip-errors to define the error number, which can be skipped if the error number encountered during the copy process is a defined error number. If the from library is used for backup, setting this parameter will present inconsistent data, do not use. If you are sharing the query pressure of the main library, consider it.

Sync_binlog=1 or N

Sync_binlogThe default value is0, in this mode,Mysqlwill not be synced to the disk. In that case,Mysqlrely on operating system to flush binary logsBinary log, just like the mechanism that the operating system brushes other files. So if the operating system or machine(not justMysqlServer)crash, it's possibleBinlogThe last statement in the list is missing. To prevent this situation, you can use theSync_binlogglobal variables to makeBinlogin everyNTimesBinlogsynchronizes with the hard disk after writing. WhenSync_binlogvariable is set to1is the safest, because inCrashcrashes in case your binary logBinary logonly one statement or one transaction can be lost. However, this is also the slowest way (unless the disk has a cache with battery backup powerCache, which makes synchronization to disk very fast).

even ifSync_binlogset to1,When a crash occurs, it is also possible that the table content andBinlogthere is an inconsistency between the content. If you useInnoDBtable,MysqlServer ProcessingCOMMITstatement, which writes the entire transaction to theBinlogand commits the transaction to theInnoDBthe. If a crash occurs between two operations, the transaction isInnoDBRollback , but still existsBinlogthe. can be used –Innodb-safe-binlogoption to increaseInnoDBtable content andBinlogconsistency between the. (Note: inMySQL 5.1not required in-Innodb-safe-binlog; Due to the introduction ofXatransaction support, which is obsolete), this option provides a greater level of security so that each transaction'sBinlog (Sync_binlog =1)and the(The default condition is True) InnoDBThe log is synchronized with the hard disk, and the effect of this option is to restart after a crash, after rolling back the transaction,MysqlServer fromBinlogshear rollback of theInnoDBtransactions. This will ensure thatBinlogFeedbackInnoDBthe exact data of the table, and keep the slave server in sync with the primary server(do not pick up the rollback statement).

Auto_increment_offset and auto_increment_increment

Auto_increment_increment and auto_increment_offset are used for primary-primary server (master-to-master) replication and can be used to control the operation of the Auto_increment column. All two variables can be set to global or local variables, and each value can be assumed to be an integer value between 1 and 65,535. Setting one of the variables to 0 causes the variable to be 1.

these two variables affect Auto_increment column by:auto_increment_increment the increment value of the value in the control column , Auto_increment_offset determines auto_ The starting point for the INCREMENT column value.

if the value of Auto_increment_offset is greater than the value of Auto_increment_increment, the Auto_increment_offset The value is ignored. For example, if there is some data in the table, it will be used as the initial value of the largest self-increment currently available.

There are self-growing fields in MySQL that need to be set up with two dependent configurations for the primary master synchronization of the database:auto_increment_offset and auto_increment_increment.

Auto_increment_offset indicates that since the growth field starts with that number, his value range is 1. 65535

Auto_increment_increment represents the amount of increment from the growth field each time, the default value is 1, the value range is 1. 65535

in the primary master sync configuration, you need to configure the auto_increment_increment growth of two servers to 2, while the Auto_increment_offset Configured to 1 and 2 respectively.

This article is from the "Learn Linux" blog, so be sure to keep this source http://10265013.blog.51cto.com/10255013/1761855

MySQL Main master