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

1. Background demand

Due to the current network environment, after recent monitoring, it is found that the IO read and write pressure of the MYSQL master library is relatively high, so it is necessary to deploy a new MYSQL slave library to further share the pressure of the MYSQL master library and the read load of the existing MYSQL slave library. In order to reduce the impact on the IO read performance of the MYSQL master library, a new MYSQL slave library is built based on the existing MYSQL slave library.

2, existing database architecture

Numbered IP address database role

1 10.0.0.12 main database

2 10.0.0.16 Database slave 1

3, prepare a new database from Library 2

Numbered IP address database role

3 10.0.0.14 Database slave 2

4. Configure server_id=3 in the new database slave 2, which is different from the above two nodes

5. In database slave library 1, close sql thread to achieve data consistency of MYSQLDUMP backup

Mysql > system hostname

Standby3

Mysql > stop slave

Query OK, 0 rows affected (0.01 sec)

6. In the database from database 1, get the following marked columns of show slave status

(note: used to build new MYSQL slave libraries later based on the values of this column.)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State:

Master_Host: 10.0.0.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 120

Relay_Log_File: standby3-relay-bin.000004

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000002-Application main database binary log

Slave_IO_Running: No

Slave_SQL_Running: No

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: 120-the location where the binary log of the main database is applied

Relay_Log_Space: 500

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

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

Master_UUID: 65fddb9f-fd33-11e9-95f0-080027d1f3fc

Master_Info_File: / var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

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)

ERROR:

No query specified

Mysql >

7, export database backup from library 1 in MYSQL

[root@standby3 mysql] # mysqldump-uroot-psystem-- databases zxydb > / dump_dir/for_create_2_slave.dump

Warning: Using a password on the command line interface can be insecure.

[root@standby3 mysql] #

8, restart the SQL THREAD of MYSQL slave library 1

Mysql > start slave sql_thread

Query OK, 0 rows affected (0.01 sec)

9, copy the MYSQL from database 1 to MYSQL from library 2

[root@standby2mysql mysql] # mkdir-p / dump_dir

[root@standby2mysql mysql] #

[root@standby3 mysql] # scp / dump_dir/for_create_2_slave.dump root@10.0.0.14:/dump_dir/

Root@10.0.0.14's password:

For_create_2_slave.dump 100% 2530 2.5KB/s 00:00

[root@standby3 mysql] #

10, import the DUMP file from library 2 in MYSQL

[root@standby2mysql mysql] # mysql-uroot-psystem show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | zxydb |

+-+

5 rows in set (0.00 sec)

11, create a replication database user in the MYSQL master library for building MYSQL slave library 2 to connect to the MYSQL master library

Mysql > create user 'repl'@'10.0.0.14' identified by' system'

Query OK, 0 rows affected (0.00 sec)

Mysql > grant replication slave on *. * to 'repl'@'10.0.0.14'

Query OK, 0 rows affected (0.00 sec)

12, build the master-slave replication relationship from library 2 to the MYSQL master library in MYSQL

Mysql > change master to master_host='10.0.0.12',master_user='repl',master_password='system',master_log_file='binlog.000002',master_log_pos=120

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

13, start the master-slave replication service in MYSQL slave library 2

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

14, view the master-slave replication run in MYSQL slave library 2

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

Read_Master_Log_Pos: 437

Relay_Log_File: standby2mysql-relay-bin.000003

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000002

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

Relay_Log_Space: 938

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

Master_UUID: 65fddb9f-fd33-11e9-95f0-080027d1f3fc

Master_Info_File: / var/lib/mysql/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)

ERROR:

No query specified

15. Verify that the data synchronization between the MYSQL master library and the newly deployed MYSQL slave library 2 is correct

-- MYSQL main library

Mysql > use zxydb

Database changed

Mysql > insert into t_go select 5

Query OK, 1 row affected (0.01sec)

Records: 1 Duplicates: 0 Warnings: 0

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

+-+ +

4 rows in set (0.00 sec)

-MYSQL from Library 2

Mysql > select * from zxydb.t_go

+-+ +

| | a | b | |

+-+ +

| | 1 | 1 |

| | 2 | 2 |

| | 3 | 3 |

| | 5 | 5 |

+-+ +

4 rows in set (0.00 sec)

16. Execute in the MYSQL master library. You can see that 2 MYSQL slave libraries are now configured.

Mysql > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | Slave_UUID | |

+-+

| | 3 | | 3306 | 1 | 699a379f-e36d-11e9-a4e2-0800274dcc79 |

| | 2 | | 3306 | 1 | 891bc123-fd72-11e9-8cf5-080027dddbcd | |

+-+

2 rows in set (0.00 sec)

You can follow my official Wechat account and send some database-related articles regularly every day. Welcome to communicate.

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.