Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the method of MySQL5.7 master-slave adding new slave library". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL master-slave replication, adding new slave nodes without downtime:

1. Create an account in the main database:

Modify the repl password of the main library:

Show master status

Alter user repl@'%' identified by '123456'

Grant replication slave,replication client on *. * to 'repl'@'%'

Flush privilegs

2. Slave database configuration (the process of creating slave database is brief):

Turn on binlog

[root@centos_TP data1] # cat / etc/my.cnf

[mysqld]

# datadir=/var/lib/mysql

# socket=/var/lib/mysql/mysql.sock

# user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Basedir=/usr/local/mysql

Datadir=/data1/data

Socket=/tmp/mysql.sock

Port=3306

Server-id = 60182

Replicate-wild-ignore-table=mysql.%

Replicate-wild-ignore-table=performance_schema.%

Replicate-wild-ignore-table=information_schema.%

Replicate-wild-ignore-table=sys.%

Log-bin = / data1/log/mysql-bin

Binlog_format = MIXED

Skip-slave-start = 1

Expire_logs_days=3

# validate_password_policy=0

# validate_password_length=3

Relay-log-index=/data1/log/mysql-relay

Relay-log=/data1/log/mysql-relay

Log-bin=/data1/log/mysql-bin

# log-error=log.err

Explicit_defaults_for_timestamp=true

[mysqld_safe]

Log-error=/data1/log/mysql.err

Pid-file=/data1/tmp/mysqld.pid

Initialize the database:

Normal initialization:

[root@centos_TP bin] # / mysqld-- defaults-file=/etc/my.cnf-- initialize-- user=mysql

2020-01-14T08:48:27.965207Z 0 [Warning] InnoDB: New log files created, LSN=45790

2020-01-14T08:48:28.175008Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2020-01-14T08:48:28.270192Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a2408f8d-36aa-11ea-a1c6-00505695cefc.

2020-01-14T08:48:28.273709Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2020-01-14T08:48:28.278708Z 1 [Note] A temporary password is generated for root@localhost: (,% E6LnwWrrq

Specify the initialization profile:

/ usr/local/mysql/bin/mysqld-defaults-file=/etc/my.cnf-initialize-user=mysql

# Open the database

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf &

# Log in to the database and change the root password

Mysql-p

Previously initialized password

Set sql_log_bin=0

Mysql > alter user root@'localhost' identified by '123456'

Mysql > flush privileges

Set sql_log_bin=1

Add root Telnet users:

Mysql > create user root@'%' identified by '123456'

Query OK, 0 rows affected (0.01 sec)

Mysql > grant all privileges on *. * to root@'%'

Mysql > flush privileges

# create a slave account

Mysql > grant replication slave,replication client on *. * to 'repl'@'%' identified by' 123456'

# execute on the slave node

Mysql > set global read_only=1

# because the slave library can be upgraded to the master library at any time, it cannot be written in the configuration file

3. Back up the main database:

[root@localhost dbdata] # mysqldump-uroot-p-routines-- single_transaction-- master-data=2-B cat qc_bh > all.sql

Parameter description:

-- routines: export stored procedures and functions

-- single_transaction: sets the transaction isolation state at the beginning of the export and starts the transaction with a consistent snapshot, then unlock tables; and lock-tables locks a table and cannot write until the dump is complete.

-- master-data: by default, equal to 1, write the dump start (change master to) binlog point and posvalue to the result, and equal to 2, write change master to to the result and comment.

4. Create a database from the library and import data

Copy the data from dump to import the data from the library

Mysql >

Create database cat

Create database qc_bh

Mysql > source / data1/all.sql

...

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

5. Check the binlog and pos values of the backup file

[root@centos_TP data1] # head-25 all.sql

-- MySQL dump 10.13 Distrib 5.7.20, for linux-glibc2.12 (x86 / 64)

--

-- Host: localhost Database: cat

-

-- Server version 5.7.20-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.000037', MASTER_LOG_POS=621697642

--

-- Current Database: `cat`

You can see the MASTER_LOG_FILE='mysql-bin.000037', MASTER_LOG_POS=621697642

6. Start the slave library

Mysql > change master to

-> master_host='192.168.60.181'

-> master_user='repl'

-> master_password='123456'

-> master_log_file='mysql-bin.000037'

-> master_log_pos=621697642

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Queueing master event to the relay log

Master_Host: 192.168.60.181

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000037

Read_Master_Log_Pos: 677960018

Relay_Log_File: mysql-relay.000002

Relay_Log_Pos: 24887

Relay_Master_Log_File: mysql-bin.000037

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: mysql.%,performance_schema.%,information_schema.%,sys.%

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 621722209

Relay_Log_Space: 56262899

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

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

Master_UUID: a524c954-c8a8-11e9-8082-00505697e9db

Master_Info_File: / data1/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Reading event from the relay log

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

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.01 sec)

ERROR:

No query specified

Display:

It is seen that the IO and SQL threads are both YES, indicating that the master-slave configuration is successful.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Read_Master_Log_Pos: 677960018 indicates that you have been chasing binlog logs.

This is the end of the introduction of "the method of adding new slave libraries from MySQL5.7 master to slave". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.