如何基于生產(chǎn)環(huán)境mysql 5.6.25主從部署新的mysql從庫(kù)操作指南

1,背景需求

       由于現(xiàn)網(wǎng)的環(huán)境，經(jīng)過(guò)近期監(jiān)控，發(fā)現(xiàn)MYSQL主庫(kù)的IO讀寫(xiě)壓力比較大，需要部署新的MYSQL從庫(kù)，進(jìn)一步分?jǐn)侻YSQL主庫(kù)的壓力，以及現(xiàn)存MYSQL從庫(kù)的讀負(fù)載。為了減少對(duì)于MYSQL主庫(kù)的IO讀取性能沖擊，采用基于MYSQL現(xiàn)存從庫(kù)構(gòu)建新的MYSQL從庫(kù)。

2，現(xiàn)存的數(shù)據(jù)庫(kù)架構(gòu) 

編號(hào)   IP地址                數(shù)據(jù)庫(kù)角色

1      10.0.0.12            數(shù)據(jù)庫(kù)主庫(kù)

2      10.0.0.16            數(shù)據(jù)庫(kù)從庫(kù)1

3，準(zhǔn)備好一個(gè)新的數(shù)據(jù)庫(kù)從庫(kù)2

編號(hào)   IP地址                數(shù)據(jù)庫(kù)角色

3      10.0.0.14              數(shù)據(jù)庫(kù)從庫(kù)2

4，在新的數(shù)據(jù)庫(kù)從庫(kù)2，配置server_id=3,區(qū)別于上述2個(gè)節(jié)點(diǎn)

5，在數(shù)據(jù)庫(kù)從庫(kù)1，關(guān)閉sql thread,為了實(shí)現(xiàn)MYSQLDUMP備份的數(shù)據(jù)一致性；

mysql> system hostname

standby3

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

6，在數(shù)據(jù)庫(kù)從庫(kù)1，獲取show slave status之如下標(biāo)注的2個(gè)列

（注：用于基于這個(gè)列的值，用于后續(xù)構(gòu)建新的MYSQL從庫(kù)）

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   ---應(yīng)用主庫(kù)二進(jìn)制日志

             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   ---應(yīng)用主庫(kù)二進(jìn)制日志的位置

              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，在MYSQL從庫(kù)1導(dǎo)出數(shù)據(jù)庫(kù)備份

[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，重啟MYSQL從庫(kù)1的SQL THREAD

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

9，把MYSQL從庫(kù)1 復(fù)制數(shù)據(jù)庫(kù)份到 MYSQL從庫(kù)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，在MYSQL從庫(kù)2導(dǎo)入DUMP文件

[root@standby2mysql mysql]# mysql -uroot -psystem </dump_dir/for_create_2_slave.dump 

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

[root@standby2mysql mysql]# 

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

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

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.25-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

| zxydb              |

+--------------------+

5 rows in set (0.00 sec)

11，在MYSQL主庫(kù)創(chuàng)建用于構(gòu)建MYSQL從庫(kù)2連接MYSQL主庫(kù)的復(fù)制數(shù)據(jù)庫(kù)用戶

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，在MYSQL從庫(kù)2構(gòu)建至MYSQL主庫(kù)的主從復(fù)制關(guān)系

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，在MYSQL從庫(kù)2 啟動(dòng)主從復(fù)制服務(wù)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

14，在MYSQL從庫(kù)2查看主從復(fù)制運(yùn)行

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，驗(yàn)證MYSQL主庫(kù)與新部署的MYSQL從庫(kù)2 數(shù)據(jù)同步正常

--MYSQL主庫(kù)

mysql> use zxydb;

Database changed

mysql> insert into t_go select 5,5;

Query OK, 1 row affected (0.01 sec)

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從庫(kù)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，在MYSQL主庫(kù)執(zhí)行，可見(jiàn)現(xiàn)配置2個(gè)MYSQL從庫(kù)

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)

大家可以關(guān)注我的微信公眾號(hào)，每天會(huì)定期發(fā)送一些數(shù)據(jù)庫(kù)相關(guān)的文章，歡迎交流。