MySQL Mutual master from replication configuration notes

mysql-master1:192.168.72.128

mysql-master2:192.168.72.129

OS Version: CentOS 5.4
MySQL version: 5.5.9 (master and master copy are the same as the MySQL version in the slave server or the master version is higher than the slave version)

First, MySQL master-master configuration

1. Modify the MySQL configuration file

Both MySQL to turn on the Binlog log function, open method: In the Mysql-master1 configuration file/etc/my.cnf

The [MYSQLD] paragraph adds

user = MySQL
Log-bin=mysql-bin
Server-id = 1
Binlog-do-db=test
Binlog-ignore-db=mysql
Replicate-do-db=test
Replicate-ignore-db=mysql
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
Auto_increment_offset=1

In the Mysql-master2 configuration file/etc/my.cnf

The [MYSQLD] paragraph adds

user = MySQL
Log-bin=mysql-bin
Server-id= 2
Binlog-do-db=test
Binlog-ignore-db=mysql
Replicate-do-db=test
Replicate-ignore-db=mysql
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2

Save two configuration files and restart MySQL server separately

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

Binlog-do-db=database is the database to log;

Synchronizing multiple database Repeat settings options binlog-do-db=test and Replicate-do-db=test

For example

Binlog-do-db=test1

Replicate-do-db=test1

Binlog-do-db=test2

Replicate-do-db=test2

BINLOG-IGNORE-DB is not to log the database name, the middle of multiple databases separated by commas (,);

MySQL config file my.cnf log-slave-updates means that if one master hangs out, the other one takes over immediately.

Sync_binlog=1
auto_increment_increment=2
Auto_increment_offset=1 refers to the server's frequent refresh log. This ensures that when one of them hangs up, the log is refreshed to another one. Thus ensuring the synchronization of data.

Auto_increment_offset = 1
Auto_increment_increment = 2
So the Auto_increment field of a has the following values: 1, 3, 5, 7, ... Wait for the odd ID.

Auto_increment_offset = 2
Auto_increment_increment = 2
The value of the Auto_increment field of B is: 2, 4, 6, 8, ... Equal even ID.

Your auto_increment field will never be duplicated between different servers, so there is no problem with the master-master structure. Of course, you can also use 3, 4, or n servers, just ensure that auto_increment_increment = N and then set the Auto_increment_offset as the appropriate initial value, then, Our MySQL can have dozens of master servers at the same time without the duplication of self-growth IDs.

2. Set 192.168.72.128 as the primary server for 192.168.72.129

Create a new authorized user on 192.168.72.128

mysql> grant replication Slave on * * to ' replication ' @ ' percent ' identified by ' replication ';
Query OK, 0 rows Affected (0.00 sec)

Mysql>flush privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000003 |              374 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

------------------------------------------------------------------------------
192.168.72.129 will 192.168.72.128 as its own primary server

mysql> Change Master to master_host= ' 192.168.72.128 ', master_user= ' replication ', master_password= ' replication ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=374;
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave status/g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.72.128
Master_user:replication
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:374
relay_log_file:mysql-master2-relay-bin.000002
relay_log_pos:235
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:374
relay_log_space:235
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
1 row in Set (0.00 sec)

The Red Callout section is a file postion two value in the results of the show Master Status command in the 192.168.72.128 host



3. Set 192.168.72.129 as the primary server for 192.168.72.128

Create a new authorized user on 192.168.72.129

mysql> grant replication Slave on * * to ' replication ' @ ' percent ' identified by ' replication ';
Query OK, 0 rows Affected (0.00 sec)

Mysql>flush privileges;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000003 |              374 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

----------------------------------------------------------------------------------
192.168.72.128 will 192.168.72.129 as its own primary server

mysql> Change Master to master_host= ' 192.168.72.129 ', master_user= ' replication ', master_password= ' replication ', Master_log_file= ' mysql-bin.000003 ', master_log_pos=374;
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Slave status/g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.72.129
Master_user:replication
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:374
relay_log_file:mysql-master2-relay-bin.000002
relay_log_pos:235
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:374
relay_log_space:235
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
1 row in Set (0.00 sec)

If the primary database has data.

A database lock table operation that does not allow data to be written again. Mysql> FLUSH TABLES with READ LOCK;

Mysqldump with command

Back up the database.

The master server executes

shell> mysqldump-uroot-p123456--master-data--opt test1 > Backup-file.sql

See the status of the primary database mysql> show master status;

Record values for file and position entries

Note: Because the primary server is not locked, the position value of the primary server binary log recorded here may be greater than the value when doing mysqldump, which will cause the update to be lost from the server during this period. If you can guarantee that the primary server will not have an update to create a new table during this time, the loss has little impact; otherwise, the replication thread from the server fails, and the primary server must be locked when mysqldump is done.

-----------------------------------------------

To add an option from the My.cnf file in the server
Skip-slave-start
Skip-slave-start means that the synchronization thread is not started when booting from the MySQL server, it is necessary to manually start the synchronization thread after starting the slave server, and run "start slave" at the mysql> prompt to

After saving my.cnf

Perform

Shell> mysqladmin-uroot-p123456 Create Test1

Shell> mysql-uroot-p123456 Test1 < Backup-file.sql

Start a thread from the server

Mysql>start slave;

Cancel the primary database lock Mysql>unlock TABLES;

4. Other commands

1. View Replication Progress
Need to run on the main library
Mysql>show processlist \g;

2. Related commands on the primary server:
Show Processlist;
Show Master Status
Show slave hosts
Show {master|binary} logs
Show Binlog Events
Purge {master|binary} logs to ' Log_name '
Purge {master|binary} logs before ' date '
Reset Master (old version flush Master)
Set Sql_log_bin={0|1}

3. Related commands from the server:
Slave start
Slave stop
Slave stop io_thread//This thread writes the log of the master segment to a local
Slave Start Io_thread
Slave stop sql_thread//This thread applies the logs written locally to the database
Slave Start Sql_thread
Reset Slave
Set Global Sql_slave_skip_counter
Load data from Master
Show slave status (Super,replication CLIENT)
Change Master to master_host=, master_port=,master_user=, master_password=//changing Master information dynamically

PURGE Master [before ' date '] Delete logs that have been synchronized on the master side

=========================================================================
Skip-slave-start
Server-id = 1

Log-bin=c:/program Files/mysql/mysql Server 5.0/00/repbinlog
Log-error=c:/program Files/mysql/mysql Server 5.0/rep/rep.err
Relay-log=c:/program Files/mysql/mysql Server 5.0/rep/beltal_relay_log
Sync_binlog=1
Log-slave-updates
Innodb_flush_log_at_trx_commit=1
Binlog-do-db=sync

#slave
Master-host = 192.168.1.144
Master-user = Replicate
Master-password = Replicate
Master-port = 3306
Master-connect-retry=60
Replicate-do-db = Sync
report-host=192.168.1.80

MySQL Mutual master from replication configuration notes