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

1. Modify the configuration file my.cnf

Server A (172.16.16.70) is configured as follows

Server_id = 70

Socket = / tmp/mysql.sock

Innodb_buffer_pool_size = 10G

Character-set-server=utf8

Log_bin=mysql-bin

Expire_logs_days=3

Replicate-do-db=ixinnuo_sjcj

Binlog-ignore-db=mysql,information_schema

Auto-increment-increment = 2

Auto-increment-offset = 1

Server B (172.16.16.71) is configured as follows:

Server_id = 71

Socket = / tmp/mysql.sock

Innodb_buffer_pool_size = 10G

Character-set-server=utf8

Log_bin=mysql-bin

Expire_logs_days=3

Replicate-do-db=ixinnuo_sjcj

Replicate-ignore-db = mysql,information_schema

Auto-increment-increment = 2

Auto-increment-offset = 2

Restart both servers for the configuration to take effect:

# service mysqld restart

Description: auto-increment-offset is used to set the starting point of automatic growth in the database, back to set an automatic growth value of 2 for these two servers, so their starting points must be different, so as to avoid primary key conflicts when the two servers synchronize data.

Replicate-do-db specifies the synchronized database, which in this case is the ixinnuo_sjcj library. In addition, it is recommended that the hardware configuration of both servers be the same.

2. Synchronize data and establish replication account:

On server A (172.16.16.70):

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'slave'@'172.16.16.71' IDENTIFIED BY' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

On server B (172.16.16.71):

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'slave'@'172.16.16.70' IDENTIFIED BY' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Execute the change master command to synchronize:

On server A (172.16.16.70):

Mysql > show master status

+-+ +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+ +

| | mysql-bin.000047 | 411 | | mysql,information_schema |

+-+ +

1 row in set (0.00 sec)

On server B (172.16.16.71):

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000003 | 618 | |

+-+

1 row in set (0.00 sec)

Execute on server A:

Mysql > change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618

Query OK, 0 rows affected, 2 warnings (0.29 sec)

Execute on server B:

Mysql > change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411

Query OK, 0 rows affected, 2 warnings (0.34 sec)

Execute the following command on both servers:

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

four。 View status:

A server (172.16.16.70) status is as follows:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.16.71

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 618

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: ixinnuo_sjcj

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

Relay_Log_Space: 460

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

Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4

Master_Info_File: / usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

The status of server B (172.16.16.71) is as follows:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.16.70

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000047

Read_Master_Log_Pos: 411

Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000047

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: ixinnuo_sjcj

Replicate_Ignore_DB: mysql,information_schema

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

Relay_Log_Space: 460

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

Master_UUID: f1366940-266c-11e7-92c2-525400f39f79

Master_Info_File: / usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

It means that the configuration has been successful.

Test:

Create the table chenfeng in the ixinnuo_ sjcj library on server A (172.16.16.70):

Mysql > use ixinnuo_sjcj

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > create table chenfeng (name varchar (10))

Query OK, 0 rows affected (0.23 sec)

Mysql > insert into chenfeng values ('duansf')

Query OK, 1 row affected (0.13 sec)

Mysql > insert into chenfeng values ('liuyb')

Query OK, 1 row affected (0.02 sec)

View the table chenfeng created on server An on server B

Mysql > select * from chenfeng

+-+

| | name |

+-+

| | duansf |

| | liuyb |

+-+

2 rows in set (0.00 sec)

Mysql > desc chenfeng

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (10) | YES | | NULL |

+-+ +

1 row in set (0.00 sec)

Create the table duansf in the ixinnuo_ sjcj library on server B (172.16.16.71):

Mysql > create table duansf (name varchar (20))

Query OK, 0 rows affected (0.18 sec)

Mysql > insert into duansf values ('duansf')

Query OK, 1 row affected (0.02 sec)

Mysql > insert into duansf values ('liuky')

Query OK, 1 row affected (0.03 sec)

View the table duansf created on server B on server A

Mysql > select * from duansf

+-+

| | name |

+-+

| | duansf |

| | liuky |

+-+

2 rows in set (0.00 sec)

Mysql > desc duansf

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | name | varchar (20) | YES | | NULL |

+-+ +

1 row in set (0.00 sec)

The test is successful, which means that the MySQL master replication configuration is successful.

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.