Shulou(Shulou.com)06/01 Report--

one。 Basic environment

Main library 1: 10.24.65.30 3306 MySQL 5.7.14

Main library 2: 10.24.65.30 3307 mysql 5.7.14

Slave Library: 10.24.65.33 3306 mysql 5.7.14

Replication based on gtid

two。 Considerations for building

1. The DB name cannot be the same on each instance.

two。 The name of the user cannot be the same when creating or copying the user, if you often encounter the same account, if the account is created in order to standardize the account

The number is the same, consider ignoring mysql replication in slave's filtering rules.

[stop slave sql_thread;change replication filter replicate_ignore_db=mysql;]

3307 start and stop:

Mysqld_safe-- defaults-file=/data/mysql/mysql3307/data/my3307.cnf &

Mysqladmin shutdown-S / tmp/mysql3307.sock

Mysql-S / tmp/mysql3307.sock

three。 Main library configuration

1.master1 parameter settin

Gtid-mode=on

Enforce-gtid-consistency=1

Skip_slave_start=1

2.master2 parameter settin

Gtid-mode=on

Enforce-gtid-consistency=1

Skip_slave_start=1

3. Copy account creation

Grant replication slave on *. * to repl@'10.24%' identified by 'replsafe'

Flush privileges

4.slave parameter settin

Gtid-mode=on

Enforce-gtid-consistency=1

Skip_slave_start=1

Master-info-repository=table

Relay-log-info-repository=table

5.slave configuration connects to the main library

Change master to

Master_host='10.24.65.30'

Master_port=3306

Master_user='repl'

Master_password='replsafe'

Master_auto_position=1 for channel 'master-65303306'

Change master to

Master_host='10.24.65.30'

Master_port=3307

Master_user='repl'

Master_password='replsafe'

Master_auto_position=1 for channel 'master-65303307'

four。 Common commands:

1. Switch replication

Start slave io_thread | sql_thread for channel 'master-65303306'

Stop slave io_thread | sql_thread for channel 'master-65303306'

two。 Monitoring replication

Show slave status for channel 'master-65303306'

five。 Check

Master1:

Create database mdb1

Use mdb1

Create table T1 (id int)

Master2:

Create database mdb2

Use mdb2

Create table T2 (id int)

Slave:

(product) root@localhost [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mdb1 |

| | mdb2 |

| | mysql |

| | performance_schema |

| | sys |

+-+

6 rows in set (0.00 sec)

(product) root@localhost [(none)] > use mdb1

Database changed

(product) root@localhost [mdb1] > show tables

+-+

| | Tables_in_mdb1 |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

(product) root@localhost [mdb1] > use mdb2

Database changed

(product) root@localhost [mdb2] > show tables

+-+

| | Tables_in_mdb2 |

+-+

| | T2 |

+-+

1 row in set (0.00 sec)

six。 Related problems

1. One of the multi-source replication processes:

Binlog+position:

Stop slave sql_thread for channel 'master-65303306'

Set sql_slave_skip_counter=n

Start slave sql_thread for channel 'master-65303306'

Gtid:

Stop slave sql_thread for channel 'master-65303306'

Set gtid_next='uuid-N'

Begin;commit

Set gtid_next='automatic'

Start slave sql_thread for channel 'master-65303306'

two。 Replication from multiple sources part II:

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:'A slave with the same server_uuid/server_id as this slave has connected to the master

The first event 'mysql-bin.000181' at 154, the last event read from' / data/mysql/mysql3306/logs/mysql-bin.000181' at 123, the last byte read from'/ data/mysql/mysql3306/logs/mysql-bin.000181' at 154.'

Solution:

a. Since the server-uuid of the two main libraries is the same, change the server-uuid number in one of the auto.cnf

b. And execute reset slave all on the slave library to clear the slave information

c. Re-change master

Add: 1.dump master1 master2 data file (master-data=2) comment out set_gtid--purged2.scp master1.sql to slave, reset master and import master1.sql,master_auto=0 scaffolding replication steps 3.scp master2.sql to slave, reset master and import master2.sql,master_auto=0 scaffolding replication step 4. Build a replication channel

Change master to master_host='127.0.0.1',master_user='repluser',master_password='replpass',master_port=3308,MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=9460 for channel 'master_3308'

5. Check it correctly

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.