如何基于生产环境mysql 5.6.25主从部署新的mysql从库操作指南

1,背景需求        由于现网的环境，经过近期监控，发现MYSQL主库的IO读写压力比较大，需要部署新的MYSQL从库，进一步分摊MYSQL主库的压力，以及现存MYSQL从库的读负载。为了减少对于MYSQL主库的IO读取性能冲击，采用基于MYSQL现存从库构建新的MYSQL从库。    2，现存的数据库架构  编号   IP地址                数据库角色 1      10.0.0.12            数据库主库 2      10.0.0.16            数据库从库1 3，准备好一个新的数据库从库2 编号   IP地址                数据库角色 3      10.0.0.14              数据库从库2 4，在新的数据库从库2，配置server_id=3,区别于上述2个节点 5，在数据库从库1，关闭sql thread,为了实现MYSQLDUMP备份的数据一致性； mysql> system hostname standby3 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) 6，在数据库从库1，获取show slave status之如下标注的2个列 （注：用于基于这个列的值，用于后续构建新的MYSQL从库） 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   ---应用主库二进制日志              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   ---应用主库二进制日志的位置               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从库1导出数据库备份 [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从库1的SQL THREAD mysql> start slave sql_thread; Query OK, 0 rows affected (0.01 sec) 9，把MYSQL从库1 复制数据库份到 MYSQL从库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从库2导入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主库创建用于构建MYSQL从库2连接MYSQL主库的复制数据库用户 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从库2构建至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，在MYSQL从库2 启动主从复制服务 mysql> start slave; Query OK, 0 rows affected (0.01 sec) 14，在MYSQL从库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，验证MYSQL主库与新部署的MYSQL从库2 数据同步正常 --MYSQL主库 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从库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主库执行，可见现配置2个MYSQL从库 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) 大家可以关注我的微信公众号，每天会定期发送一些数据库相关的文章，欢迎交流。