Quickly add a slave database for mysql and add a slave database for the slave database

In reality, mysql is usually a master-slave multi-slave database, but the problem occurs in the master database. it needs to be upgraded from the master database to the master database, but in fact this operation is very complicated, basically, all slave databases must be operated. In this case, you may want to make mysql into the ABC structure, where A is the master database, B is the slave database of A, and C is the slave database of B. in case of A problem, you can directly

In reality, mysql is usually a master-slave multi-slave database, but the problem occurs in the master database. it needs to be upgraded from the master database to the master database, but in fact this operation is very complicated, basically, all slave databases must be operated. In this case, you may want to make mysql into the ABC structure, where A is the master database, B is the slave database of A, and C is the slave database of B. in case of A problem, you can directly

In reality, mysql is usually a master-slave multi-slave database, but the problem occurs in the master database. it needs to be upgraded from the master database to the master database, but in fact this operation is very complicated, basically, all slave databases must be operated.

In this case, we may make mysql into the ABC structure. A is the master database, B is the slave database of A, and C is the slave database of B.

In this case, the problem occurs in A: B can be directly promoted to the master database, and other sub-databases do not need any operation.

This operation is recorded here. in my case, A and B already exist, A is the master, B is the slave

The requirement is to add A slave database C of A and A slave database D of C.

Quickly add a mysql slave database (provided that the same version of mysql has been installed)

First, stop the master and slave databases from database B.

mysql> stop slave;Query OK, 0 rows affected(0.06 sec) mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State:                  Master_Host: 10.10.1.171                  Master_User: mysqlab                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000096          Read_Master_Log_Pos: 585421644               Relay_Log_File: mysql-relay-bin.000279                Relay_Log_Pos: 2043143        Relay_Master_Log_File: mysql-bin.000096             Slave_IO_Running: No            Slave_SQL_Running: No

Then, package the data files from database B and copy them to the new slave database D from database C and database C (do not pack the sock and pid files)

synctar  -czf mysqldata3326.tar.gz mysqldata3326/scp  mysqldata3326.tar.gz 10.10.1.173:/home/scp  mysqldata3326.tar.gz 10.10.1.174:/home/

Decompress the package to the data directories of C and D (delete master/slave information files)

cd /hometar zxf mysqldata3326.tar.gzmv /home/mysqldata3326/datadir/master.info /home/mysqldata3326/datadir/master.info.bak

Modify the server-id of the configuration file C and D, C is 11, and D is 21. (I have the difference between ports here, based on the actual situation)

vim /home/mysqldata3326/my.cnf

Start C and D respectively

/usr/local/mysql/bin/mysqld_safe --defaults-file=/home/mysqldata3326/my.cnf --user=mysql &

Configure the master and slave nodes of C and D (the master and slave nodes of C and D must be configured first)

C: view the master information of C.

mysql> show master status;+------------------+----------+--------------+---------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |+------------------+----------+--------------+---------------------------------------------+| mysql-bin.000092 |  1953402 |              | mysql,test,information_schema,sphinx_search |+------------------+----------+--------------+---------------------------------------------+

D: configure the master/slave information of D.

mysql> slave stop;mysql> reset slave;mysql> change master to master_host='10.10.1.173',master_user='mysqlab',master_password='xxxxx',master_log_file='mysql-bin.000092',master_log_pos=1953402;mysql> slave start;mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State:                  Master_Host: 10.10.1.173                  Master_User: mysqlab                  Master_Port: 3326                Connect_Retry: 60              Master_Log_File: mysql-bin.000092          Read_Master_Log_Pos: 1953490               Relay_Log_File: mysql-relay-bin.000279                Relay_Log_Pos: 214513        Relay_Master_Log_File: mysql-bin.000092             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

Modify the synchronization information of C's master database (the master/slave information here must be consistent with the information of stop B's slave database)

mysql> slave stop;mysql> reset slave;mysql> change master to master_host='10.10.1.171',master_user='mysqlab',master_password='xxxxxx',master_log_file='mysql-bin.000096',master_log_pos=585421644;mysql> slave start;

Finally, start master/slave of B! Complete

Verification:

Use show slave status \ G; to check whether the master/slave information and SQL and IO threads are yes

The simplest verification is to create A database in A and show whether the database is synchronized in D.