MYSQL Master-Slave add New Slave Library
MySQL master-slave replication, adding new slave nodes without downtime
1. Create an account in the master database:
show master status;GRANT REPLICATION SLAVE ON . to 'reader'@'%' identified by 'readerpwd';flush privilegs
2. Configuration from library
Open binlog
log-bin=/var/lib/mysql/mysql-binserver-id=3 //Refer to the original slave library configuration +1
3. Backup main library
mysqldump -uroot -p123 --routines --single_transaction --master-data=2 --databases testdb > testdb.sql
Parameter Description:
--routines: export stored procedures and functions--single_transaction: set transaction isolation state at the beginning of export, and use consistency snapshot to start transaction, then unlock tables; lock-tables locks a table from writing until dump is complete. -- master-data: default = 1, dump start (change master to) binlog point and pos value written to the result, equal to 2 is to write change master to the result and comment.
4. Create a database from the library and import the data
Copy the dump data to the slave library and start importing data
mysql> grant all pricileges on *.* to testdb.* identified by 'testdb';mysql> create database testdb;mysql> source /tmp/testdb.sql
5. View binlog and pos values of backup files
# head -25 testdb.sqlroot@mysql20151:/tmp# head -25 /tmp/0907.sql -- MySQL dump 10.13 Distrib 5.5.46, for debian-linux-gnu (x86_64)---- Host: localhost Database: vphotos-- -------------------------------------------------------- Server version 5.5.46-0ubuntu0.14.04.2-log/*! 40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*! 40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*! 40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*! 40101 SET NAMES utf8 */;/*! 40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*! 40103 SET TIME_ZONE='+00:00' */;/*! 40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*! 40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*! 40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*! 40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.003789', MASTER_LOG_POS=49778941;
MASTER_LOG_FILE='mysql-bin. 003789', MASTER_LOG_POS=49778941;
6. Start slave library
mysql> change master to master_host='10.*.*.* ',master_user ='reader',master_password ='readerpwd ',master_log_file ='mysql-bin. 003789', master_log_pos=49778941;//Verify slave status mysql> show slave status\G;*********************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.3.16.7 Master_User: slave02 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.003791 Read_Master_Log_Pos: 99002276 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.003789 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: ..................
Note: See IO and SQL threads are YES, indicating that the master-slave configuration is successful.
Reference:
https://yq.aliyun.com/articles/38826
