Simple configuration of master-slave synchronization of Mysql database

1. Master-slave synchronization: (Amurmuri-> B)

Master:192.168.71.128

Slave:192.168.71.138

1. Master configuration:

Vi / etc/my.cnf

Server-id = 1

Log-bin=mysql-bin enables binary logging

Binlog-ignore-db = mysql out of sync mysql library

Authorize slave database 192.168.71.138 to use account rsync password bobo365 to synchronize all data in the master database

Mysql > grant replication slave on *. * to rsync@'192.168.71.138' identified by 'bobo365'

Query OK, 0 rows affected (0.01 sec)

Refresh permissions

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Set the table to read-only

Mysql > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

Record the file and position values, which are used by the server.

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000025 | 541 |

+-+

1 row in set (0.00 sec)

Unlock the database read-only.

Mysql > unlock tables

Query OK, 0 rows affected (0.00 sec)

2. Slave configuration:

Vim / etc/my.cnf

Server-id = 2

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

Mysql > change master to master_host='192.168.71.128',master_user='rsync',master_

Password='bobo365',master_log_file='mysql-bin.000025',master_log_pos=541

Query OK, 0 rows affected (0.01 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.71.128

Master_User: rsync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000025

Read_Master_Log_Pos: 886

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 598

Relay_Master_Log_File: mysql-bin.000025

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

Relay_Log_Space: 758

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

3. Test:

Master:

Mysql > create database M2s

Query OK, 1 row affected (0.00 sec)

Mysql > use M2s

Database changed

Mysql > create table m2s_tb (id int (2), name char (10))

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into m2s_tb values (001)

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | bb |

| | db_nagiosql_v3 |

| | M2s |

| | mysql |

| | performance_schema |

| | testcopy |

+-+

7 rows in set (0.00 sec)

Slave:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | cactidb |

| | collabtive |

| | db_nagiosql_v3 |

| | M2s |

| | my_wiki |

| | mysql |

| | performance_schema |

| | test |

| | testcopy |

+-+

10 rows in set (0.00 sec)

Mysql > use M2s

Database changed

Mysql > select * from m2s_tb

+-+ +

| | id | name |

+-+ +

| | 1 | todu |

+-+ +

1 row in set (0.00 sec)

2. Two-way synchronization: (AB)

Use the primary primary premise:

A. the primary key of the table is self-increasing.

B. Specify ID for the program writing library

Two masters are principal and subordinate to each other:

Resolve primary key self-growth variable conflicts:

Master1:

Auto_increment_increment = 2 # the interval of self-increasing ID, for example, the interval of 135 is 2.

Auto_increment_offset = 1

Master2:

Auto_increment_increment = 2 # the interval of self-increasing ID, for example, the interval of 246 is 2.

Auto_increment_offset = 2

There is an ID discontiguity problem:

-> 13 5 6 8 10 11 13 15

Mutual master and slave parameters:

Log-bin = / data/3307/mysql-bin

Log-slave-updates # enable binlog log from the library

Third, secondary learning content supplement (contrastive learning)

Environment:

3306 master

3307 from

Main library operation:

Turn on binlog:log-bin = / data/3306/mysql-bin

Change server id:server-id = 1

Add a replication user:

Grant replication slave on *. * to rep@'192.168.1.%' identified by 'bobo365'

Add read lock:

Flush table with read lock

Show master status

Mysql-bin.000011 | 43275

Show master logs

If the window is not closed, reopen the window dump data:

Mysqldump-S / data/3306/mysql.sock-uroot-pbobo365-A-B-E-- master-data=2 > / home/rep.sql

Or lock the table directly:

Mysqldump-S / data/3306/mysql.sock-uroot-pbobo365-A-B-E-X-- master-data=2 > / home/rep.sql

Or-- master-data=1

The following parameters are not used from the library:

MASTER_LOG_FILE='mysql-bin.000011'

MASTER_LOG_POS=43275

Unlock:

Unlock tables

From the library:

# log-bin = / data/3307/mysql-bin

Server-id = 3

Inject the backup file rep.sql of the main library into the main library:

Source / home/rep.sql

Log in to the slave library execution:

CHANGE MASTER TO

MASTER_HOST='192.168.1.61'

MASTER_PORT=3306

MASTER_USER='rep'

MASTER_PASSWORD='bobo365'

MASTER_LOG_FILE='mysql-bin.000011'

MASTER_LOG_POS=43275

View the mster info file:

[root@zhong-61 data] # more master.info

twenty-three

Mysql-bin.000011

43275

192.168.1.61

Rep

Bobo365

3306

Turn on the switch:

Start slave

Check:

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Appendix:

Compare the first time, the second time to add the missing key steps:

Do the complete import in the main library and import from the slave library.