How to use mysql-mm to realize Mutual Master-Slave replication in mysql 04/02 Update SLTechnology News&Howtos

How to use mysql-mm to realize Mutual Master-Slave replication in mysql

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use mysql-mm to achieve mutual master-slave replication in mysql. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

The principle is described:

AB replication is mainly done through two slave processes (Sql and Imax O processes) and Master's Imax O processes.

The main process of replication is that Slave takes the log from the Master side and then performs the various operations recorded in the log in full sequence on its own.

The replication process trilogy:

1) Slave starts the Iamp O process to connect to Master and requests the log contents from the specified location of the specified log file (or from the beginning of the log)

2) after receiving the request, Master returns the name, location and log information of the bin-log file on Masterside to Slave through the IO process responsible for replication.

3) after receiving the message, Slave adds the received log contents to the end of the relay-log file on the Slave side, and reads the

The file name and location of the bin-log are recorded in the master-info file so that the next time you read it, you can clearly tell Master, "I need to start from

Where does the bin-log start? please send me the contents of the future log. "

After the Sql process of Slave detects a new addition to relay-log, it will immediately parse the content of relay-log into the real execution on the Masterside.

Those executable contents of the, and execute on their own

Environment description: it is better for the two machines to have exactly the same mysql version

A:211.100.97.246 Linux x86_64 mysql5.1.56

B:211.100.97.250 Linux x86_64 mysql5.1.56

Start the mysql process

Both An and B start the mysql process

Modify the security level

Turning off selinux,iptables allows the interconnection of mysql ports between two machines

You can set the parameter selinux= disabled in / etc/sysconfig/selinux.

Add iptables-An INPUT-s SourceIP-p tcp-- dport 3306-j ACCEPT

After modification, test the port:

A: telnet B_IP 3306

B: telnet A_IP 3306

Create an account

A: useradd repl1

B: useradd repl2

After adding and viewing account information

A: id repl1

B: id repl2

A:mysql profile

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

B:mysql profile

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=1

Description:

Server-id represents the serial number of the machine. If it is 1, it generally represents master.

Binlog-do-db indicates which database needs to be backed up. If you want to back up multiple databases, you should add multiple records.

Replicate-do-db indicates the database to be synchronized

Log-bin means to enable the binlog log function. Only when this option is enabled can the log information on the mater be written to the relay-log of Slave through the IBlueO process.

Auto_increment_increment defines the step size of the next AUTO_INCREMENT

Auto_increment_offset defines the starting point value of AUTO_INCREMENT

Authorized users [at least give FILE,SELECT,REPLICATION SLAVE permissions]

A: allow B to synchronize data with A through repl2 account

Mysql > grant replication client on *. * to identified by 'PASSWD'

Mysql > flush privileges

Check out the authorization:

Mysql > select * from mysql.user where

* * 1. Row *

Host: 211.100.97.250

User: repl2

Password: * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

1 row in set (0.00 sec)

B: allow A to synchronize data with B through repl1 account

Mysql > grant replication client on *. * to identified by 'PASSWD'

Mysql > flush privileges

Mysql > select * from mysql.user where

* * 1. Row *

Host: 211.100.97.246

User: repl1

Password: * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

1 row in set (0.00 sec)

Authorization needs to be tested later.

A: / usr/local/mysql/bin/mysql-hacked Bond IP'-urepl1-p

B: / usr/local/mysql/bin/mysql-hacked Aids IP'-urepl2-p

Restart mysql on both machines

Killall mysqld

Ps aux | grep mysql

/ usr/local/mysql/bin/mysqld_safe &

Ps aux | grep mysql

Enter the SHELL of MYSQL

/ usr/local/mysql/bin/mysql-uroot-p

The server locks the table (the mysql process cannot be terminated in the locked table state, otherwise it will fail)

Mysql > flush tables with read lock\ G

Query OK, 0 rows affected (0.01 sec)

View A server host status (record binary start file, location)

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000005

Position: 106

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Modify A server configuration

Mysql > change master to

-> master_host='211.100.97.250'

-> master_user='repl2'

-> master_password='123456'

-> master_log_file='mysql-bin.000014'

-> master_log_pos=98

Query OK, 0 rows affected (0.01 sec)

Description:

Master_host indicates that host B250 is the master of A246

Master_user indicates that the account repl1 on A (246) is allowed to connect to master for replication, and it is recommended that the authorized users and passwords of the two hosts are exactly the same.

Master_password indicates the password of the authorized user repl1

Master_log_file represents the name of the log file on the master

Master_log_pos indicates the location of the log file

Mysql > slave stop

Mysql > change master to master_host='B_IP', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106

Then start slave

Mysql > slave start

Check the status of slave after startup

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 211.100.97.250

Master_User: repl1

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 106

Relay_Log_File: XKWB5510-relay-bin.000002

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: test

Replicate_Ignore_DB: mysql

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: 106

Relay_Log_Space: 409

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

View related processes

Mysql > show processlist\ G

* * 1. Row *

Id: 4

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

* 2. Row * *

Id: 18

User: system user

Host:

Db: NULL

Command: Connect

Time: 100

State: Waiting for master to send event

Info: NULL

* 3. Row * *

Id: 19

User: system user

Host:

Db: NULL

Command: Connect

Time: 100

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

* * 4. Row *

Id: 21

User: repl2

Host: 211.100.97.250:34536

Db: NULL

Command: Binlog Dump

Time: 19

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

4 rows in set (0.00 sec)

Synchronize the basic libraries of the two databases

Unlock the server

Mysql > unlock tables

Mysql > use test

Mysql > show tables

Empty set (0.00 sec)

Mysql > create table t11_replicas

-> (id int not null auto_increment primary key

-> str varchar (255) not null) engine myisam

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into t11_replicas (str) values

-> ('This is a master to master test table')

Query OK, 1 row affected (0.00 sec)

Mysql > show tables

Mysql > select * from t11_replicas

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000014

Position: 98

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Mysql > stop slave

Mysql > change master to master_host='A_IP', master_user='repl2', master_password='123456', master_log_file='mysql-bin.000005',master_log_pos=106

Mysql > start slave

Mysql > show processlist\ G

* * 1. Row *

Id: 3

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

* 2. Row * *

Id: 15

User: repl1

Host: 211.100.97.246:51840

Db: NULL

Command: Binlog Dump

Time: 101

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

* 3. Row * *

Id: 16

User: system user

Host:

Db: NULL

Command: Connect

Time: 20

State: Waiting for master to send event

Info: NULL

* * 4. Row *

Id: 17

User: system user

Host:

Db: NULL

Command: Connect

Time: 20

State: Has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

4 rows in set (0.00 sec)

Mysql > show slave status\ G

Mysql > use test

Database changed

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

Reset the log

Mysql > reset master

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000001

Position: 106

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

[root@XKWB5510] # ls-l / var/mysql/database/data/

Total 0

Check the data directory to see if there are any error files:

[root@XKWB5510 data] # ls / var/mysql/database/data/

After re-executing the change master to command, start slave again, and take a look at the status of slave. The slave process starts.

After all the processes are up, monitoring will be implemented.

Error report:

Caused by change master:

Last_IO_Error: error connecting to master-retry-time: 60 retries

Stop the slave process without unlocking it:

Mysql > stop slave

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

Change master syntax error, leaving comma

Mysql > change master to

-> master_host='211.100.97.250'

-> master_user='repl2'

-> master_password='123456'

-> master_log_file='mysql-bin.000002'

-> master_log_pos=106

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_user='repl2'

Master_password='123456'

Master_log_file='mysql-bin.000002' at line 3

Change master without stopping the slave process

Mysql > change master to master_host='211.100.97.246', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=106

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

The server-id of A B is the same:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

These ids must be different for replication to work (or the-- replicate-same-server-id option must be used on

Slave but this does not always make sense; please check the manual before using it).

View server-id

Mysql > show variables like 'server_id'

Manually modify server-id

Mysql > set global server_id=2; # the values here are the same as those set in my.cnf

Mysql > slave start

6) after change master, check the status of slave and find that slave_IO_running is NO

It should be noted that after doing the above, the mysql process is finally restarted.

Synchronize data situation

A: insert data on A

Mysql > create table aniya (id int not null auto_increment primary key, str varchar (255) not null)

Mysql > insert into aniya (str) values

-> ('This is a master to master test table')

Mysql > select * from aniya

+-+-

| | id | str |

+-+-

| | 1 | This is a master to master test table |

+-+-

1 row in set (0.00 sec)

View B's log:

[root@XKWB5705 var] # ls-lrth XKWB5705-relay-bin.000003

-rw-rw---- 1 mysql mysql 576 Sep 26 12:29 XKWB5705-relay-bin.000003

[root@XKWB5705 var] # more XKWB5705-relay-bin.000003

.in.N

(id int not null auto_increment primary key

Str varchar (255) not null) 3

('This is a master to master test table')

B A master-slave synchronous test

Create table lian on B and insert data

Mysql > create table lian (an int,b char (10))

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into lian (a ~ (b)) values (22 ~ ())

ERROR 1054 (42S22): Unknown column 'h' in' field list'

Mysql > insert into lian (a ~ (b)) values (22 ~ ()

Query OK, 1 row affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | lian |

+-+

1 row in set (0.00 sec)

Mysql > select * from lian

+-+ +

| | a | b | |

+-+ +

| | 22 | h |

+-+ +

1 row in set (0.00 sec)

Check B's master log to prove that the above operation is successful:

Cat mysql-bin.000002

.? Nh?@stdtestcreate table lian (an int,b char (10))?? Nl > @ stdtestinsert into lian (ameme b) values (22pr.

Now look at the relay log from server An and find that the log has been synchronized

[root@XKWB5510 var] # cat XKWB5510-relay-bin.000003

.? Nh?@stdtestcreate table lian (an int,b char (10))?? Nl > @ stdtestinsert into lian (ameme b) values (22pr.

Then check from server A to see if the lian table exists in the database:

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | aniya |

| | lian |

+-+

2 rows in set (0.00 sec)

Now it shows that the data B A master-slave synchronization is successful.

Test A B master and slave

Create table From246 on An and insert data

Mysql > use test

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | A246 |

| | aniya |

| | lian |

+-+

3 rows in set (0.00 sec)

Mysql > create table From246 (Name varchar, Sex varchar, Age int)

Query OK, 0 rows affected (0.00 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | A246 |

| | From246 |

| | aniya |

| | lian |

+-+

4 rows in set (0.00 sec)

Mysql > insert into From246 (Name,Sex,Age) values ('Zhaoyj','Girl',24)

Query OK, 1 row affected (0.00 sec)

Mysql > select * from From246

+-+

| | Name | Sex | Age | |

+-+

+-+

1 row in set (0.00 sec)

Check A's master log to prove that the above operation is successful

[root@XKWB5510 var] # tail-1 mysql-bin.000002

Testcreate table From246 (Name varchar, Sex varchar, Age int)? N?R@stdtestinsert into From246 (Name,Sex,Age) values ('Zhaoyj','Girl',24)

View the master log status of A

[root@XKWB5510 var] # / usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 | tail-15

/ *! * /

# at 702

# 110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0

SET timestamp 1317016911

Create table From246 (Name varchar, Sex varchar, Age int)

/ *! * /

# at 838

# 110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0

SET timestamp 1317016925

Insert into From246 (Name,Sex,Age) values ('Zhaoyj','Girl',24)

/ *! * /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET

Check the relay log of B and synchronize the log successfully.

Testcreate table From246 (Name varchar, Sex varchar, Age int)? N?R@stdtestinsert into From246 (Name,Sex,Age) values ('Zhaoyj','Girl',24) [root@XKWB5705 var]

Check the relay log status of B

[root@XKWB5705 var] # / usr/local/mysql/bin/mysqlbinlog XKWB5705-relay-bin.000005 | tail-13

/ usr/local/mysql/bin/mysqlbinlog: Character set'# 28' is not a compiled character set and is not specified in the'/ usr/local/mysql/share/mysql/charsets/Index.xml' file

# 110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0

SET timestamp 1317016911

Create table From246 (Name varchar, Sex varchar, Age int)

/ *! * /

# at 853

# 110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0

SET timestamp 1317016925

Insert into From246 (Name,Sex,Age) values ('Zhaoyj','Girl',24)

/ *! * /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET

But the data is not inserted.

> show tables

+-+

| | Tables_in_test |

+-+

| | lian |

+-+

1 row in set (0.00 sec)

When I delete the table on A, B's relay log is also synchronized

[root@XKWB5705 var] # tail-4 XKWB5705-relay-bin.000005

?? NS?@stdtestdrop table A246??NT@stdtestdrop table aniya??NSd@stdtestdrop table lian??NV?@stdtestdrop table From246

Troubleshooting:

First, use it on Master.

Show processlist; checks to see if the process has too much Sleep. It turns out it's normal.

Show master status; is also normal.

It is normal to check on Slave again.

Show slave status

A problem was found:

When I manually import B data from A

Mysql > load table From246 from master

ERROR 1115 (42000): Unknown character set: 'gbk'

Doubt: is it because of the string problem that AB master-slave replication failed?

You can see through the show character set command

A has the gbk character set while B does not.

Mysql > show character set

+-- +

+-- +

| | dec8 | DEC West European | dec8_swedish_ci | 1 |

| | cp850 | DOS West European | cp850_general_ci | 1 |

| | hp8 | HP West European | hp8_english_ci | 1 |

| | swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| | cp1250 | Windows Central European | cp1250_general_ci | 1 |

| | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| | utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| | cp866 | DOS Russian | cp866_general_ci | 1 |

| | cp852 | DOS Central European | cp852_general_ci | 1 |

| | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| | cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| | cp1257 | Windows Baltic | cp1257_general_ci | 1 |

+-- +

27 rows in set (0.00 sec)

Now it should be time to unify their character set when starting mysql.

A: [root@XKWB5510 var] # / usr/local/mysql/bin/mysqld_safe-- default-character-set=latin1 &

B: [root@XKWB5705 var] # / usr/local/mysql/bin/mysqld_safe-- default-character-set=latin1 &

Import data from An on B:

Mysql > show tables

Empty set (0.00 sec)

Mysql > load table From246 from master

Query OK, 0 rows affected (0.01 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | From246 |

+-+

1 row in set (0.00 sec)

Now the problem of the character set is solved.

Now manually start the "apply logs to database" thread: SLAVE start SQL_THREAD

And the thread that writes the log of the master segment locally: SLAVE start IO_THREAD

It is found that the synchronization of data still fails, which means it is not the problem of the thread.

If you find that Seconds_Behind_Master is (null)

Resolve:

Stop slave

Set global sql_slave_skip_counter = 1

Start slave

After that, Slave will synchronize with Master mainly to see if Seconds_Behind_Master is 0, until it is 0.

Whether the master.info information on slave B machine is synchronized with the information on master A

Mater A:

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000004

Position: 808

Binlog_Do_DB: test

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

Slave B:

[root@XKWB5705 var] # cat master.info

fifteen

Mysql-bin.000004

eight hundred and eight

211.100.97.246

Repl2

123456

3306

sixty

From the above, we can see that it is synchronized.

Flush master

Flush slave

On how to use mysql-mm in mysql to achieve mutual host from replication to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.