What is the method to add slave database in MYSQL?

##1. MySQL master-slave replication

Common master-slave architecture:

• One master and one slave: One Master, one Slave

• One master and multiple slaves: One Master, multiple Slave

For details, refer to the figure below:

##Implementation details

MySQL What are the underlying implementation details of master-slave synchronization? In order to analyze the reasons for the master-slave delay and the optimization plan later, make theoretical preparations.

#In summary, MySQL’s master-slave replication:

Asynchronous single-threaded.

• Master

  has 1 IO thread, responsible for transmitting to Slave binary log（binlog)

  ##There are 2 threads on Slave:
IO thread
• and

  SQL execution thread, among which:

IO thread
• : Append the obtained log information to the relay log;

Thread that executes SQL
• : If it is detected that the content in the relay log has been updated, execute sql on the Slave;

Special Note

: MySQL 5.6.3 starts to support "

Multi-threadedMaster-slave replication", One databaseOne thread,Multiple databasescanMultiple threads.

Complete master-slave replication process between Master & Slave:

2. Add a slave

Requirements:

Currently there is a database ucenter on my master, and I need to add a slave to it (there is currently one slave)

Analysis:

I have a database backup at 4:00 the day before.

• There is an existing ucenter slave

• The key point is to find the time point of binlog
Operation:

1. Configure slave information on the slave machine, modify the mysql.cfg configuration and restart the slave database

In [mysqld] Add

replicate-do-db = ucenter     #同步的数据库名字
slave-skip-errors=all         #同步是忽略错误
server-id       = 1112        #和master与其他slave保持不通

2. Find the backup of the database the day before, copy it to the newly added slave machine, and import the database

[root@ucenter_slave /data]# mysql ucenter < ucenter_20171102.sql[root@ucenter_slave /data]# du -sh ucenter_20171102.sql 24G    ucenter_20171102.sql

3. Locate binlog timestamp (key point)

Search binlog on master

[root@Ucenter /data/mysqldata]# ll -t *bin*
-rw-rw---- 1 mysql mysql   30709744 Nov  2 21:32 mysql-bin.000268
-rw-rw---- 1 mysql mysql        171 Nov  2 19:41 mysql-bin.index
-rw-rw---- 1 mysql mysql 1021101094 Nov  2 19:41 mysql-bin.000267
-rw-rw---- 1 mysql mysql 1073742415 Oct 30 06:07 mysql-bin.000266
-rw-rw---- 1 mysql mysql 1073742062 Oct 26 12:03 mysql-bin.000265
-rw-rw---- 1 mysql mysql 1073742636 Oct 22 19:21 mysql-bin.000264
-rw-rw---- 1 mysql mysql 1073741961 Oct 18 22:49 mysql-bin.000263
-rw-rw---- 1 mysql mysql 1073742141 Oct 15 12:47 mysql-bin.000262
-rw-rw---- 1 mysql mysql 1073742284 Oct 11 10:18 mysql-bin.000261
-rw-rw---- 1 mysql mysql 1073742184 Oct  7 09:49 mysql-bin.000260

My backup time starts at 4 o'clock on the 2nd, so I should search for 2 in mysql-bin.000267 POS before 4 o'clock, start positioning

[root@Ucenter /data/mysqldata]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000267 |grep '3:59:' |grep -v '13:59:'
#171102 3:59:58 server id 21323 end_log_pos 824385760 Query thread_id=3023086382 exec_time=0 error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824386335 Query    thread_id=3023086386    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824386363 Intvar
#171102 3:59:58 server id 21323 end_log_pos 824386698 Query    thread_id=3023086386    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824387706 Query    thread_id=3023086388    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824387734 Intvar
#171102 3:59:58 server id 21323 end_log_pos 824388209 Query    thread_id=3023086388    exec_time=0    error_code=0
#171102 3:59:58 server id 21323 end_log_pos 824388534 Query    thread_id=3023086398    exec_time=0    error_code=0

, so position to the end end_log_pos 824388534

4. Check master.info on another slave

[root@LeduPass02/data/mysqldata]# cat master.info 
15
mysql-bin.000268
11367810
192.168.100.70
omsync
om@123
3306
60
0

0
0

5. Configure the slave on the slave for synchronization.

Configure the relevant settings on the slave to tell the slave how to connect to the master, including the host address, login credentials, etc.

[root@ucenter_slave /data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.51-Community-Server-log Source

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql (none)>change master to master_host='192.168.100.70',master_port=3306,master_user='omsync',master_password='om@123',master_log_file='mysql-bin.000267',master_log_pos=824388534;

Open slave

mysql (none)>start slave;

Check slave status:

The above is the detailed content of What is the method to add slave database in MYSQL?. For more information, please follow other related articles on the PHP Chinese website!