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

Background

After the main node has been running online for a period of time, the amount of data is increasing day by day, so it is necessary to add slave nodes to better support the development of the business. However, there are also a certain number of users at this time, so you cannot stop the master node, but add the slave node without downtime. The specific implementation method is as follows:

Both the environment firewall and selinux turn off masterslaveIP192.168.7.71192.168.7.72 hostname master01slave01OSCentOS7CentOS7MySQL version 5. 7. 285. 7. 28 procedure 1. Make a full backup of the database on master and copy it to slave node 1.1 to view the database mysql > show databases on the current master +-+ | Database | +-+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | sys | +-+ 1.2 use mysqldump tools Full library backup [root@master01 ~] # mysqldump-p-A-F-- single-transaction-- master-data=1 > / backup/fullbackup_ `date +% Fathers% T`.sqlEnter password: [root@master01 ~] # ll / backup/total 832 RWFLEnter fullbackup_2019-1 root root 849153 Nov 29 20:22 fullbackup_2019-11-29 fullbackup_2019 22: 04.sql [root @ master01 ~] # scp-p / backup/fullbackup_2019-11-29 fullbackup_2019 20\: 22 \: 04.sql root@192.168.7.72:/data/fullbackup_2019-11-29_20:22:04.sql 100% 829KB 14.9MB/s 00829KB 14.9MB/s 001.3 create an authorized replication account grant replication slave on *. * to repluser@'192.168.7.%' identified by '123.com' two。 Import the full backup copied by the master node on the slave node 2.1 MySQL installation process 2.2 add the following to the my.cnf file, and then restart the database. [mysqld] server-id = 2read_only = ON # is set to read-only, but is not valid for superusers. 2.3 Import and view [root@slave01 ~] # mysql-p

< /data/fullbackup_2019-11-29_20\:22\:04.sql Enter password: [root@slave01 ~]#mysql -p -e "show databases"Enter password: +--------------------+| Database |+--------------------+| information_schema || db1 || db2 || mysql || performance_schema || sys |+--------------------+可以看到已经将master节点上的所有库导入成功了。3.在slave节点执行以下命令，指定连接主服务器的信息，以进行数据同步。3.1 找到完全备份的position信息[root@slave01 ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-29_20\:22\:04.sql CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;3.2 配置连接主服务的信息（从完全备份的位置之后开始复制）[root@slave01 ~]#mysql -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

CHANGE MASTER TO-> MASTER_HOST='192.168.7.71',-> MASTER_USER='repluser',-> MASTER_PASSWORD='123.com',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='mysql-bin.000003',-> MASTER_LOG_POS=154;Query OK, 0 rows affected, 2 warnings (0.01 sec) 3.3 start the slave node and check the synchronization status. Mysql > start slave Query OK 0 rows affected (0.00 sec) mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.7.71 Master_ User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: slave01-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes... Omit the information. Slave_IO_Running and Slave_SQL_ running threads are both Yes, indicating that master-slave synchronization is successful, so adding slave libraries online has been implemented.

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.