How to add a new slave in an online mysql database without downtime

For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn). At work, there are more master-slave environments, but basically all of them are stopped on DBSERVER (gaming companies) today

For more blog posts, please note: Children without umbrellas must work hard (www.xuchanggang.cn). At work, there are more master-slave environments, but basically all of them are stopped on DBSERVER (gaming companies) today

At work, the master-slave environment is a lot better, but basically it was built when the db server was shut down (the game company). Today, I was suddenly asked by a Technical Officer, the general idea of how to add a master-slave instance online and answer questions has not been put into practice. Let's test it. You can also think about your own ideas: mysql 5.1, binary log files (some binary logs are cleared regularly after a long time), pos number

Note: in mysql 5.6, master-slave Replication Based on GTID (that is, you do not need to know the log file and position number). You only need to restore the latest backup ,, here we will only discuss mysql 5.1

I. Current Basic Environment:

Primary DB: 192.168.1.101

From DB1: 192.168.1.102

From DB2: 192.168.1.103 (prepare to add a mysql database as a slave online)

Database version: mysql 5.1.61 (select version ())

Server version: red hat linux 6

Note: currently, the master and slave nodes are normal (show slave status \ G), and the synchronized database is login.

The backup software (xtrbackup) of the three DB servers has been installed.

The synchronized table is an innodb Engine table. Otherwise, the table will be locked with xtrabackup.

Assume that the environment is: Table a in the login database now inserts data during backup, and the final behavior is 5418272

Note: The idea is backup, but mysqldump backup locks the table. If the data volume is large, mysqldump backup cannot be used in the online environment.

Therefore, we use xtrabackup for online hot backup. As for its principle, please google

2. Deploy the instance:

1. Back up the primary database while online data is normally written

Mysql> insert into a select * from a; [root @ client101 backup] # innobackupex -- user = root -- password = kongzhong -- defaults-file =/etc/my. cnf -- port = 3306 -- database = login/tmp/backup/>/tmp/backup/innoback. log 2> & 1 # After the backup is complete, you can view the xtrabackup_binlog_info file in the backup file directory. This file records the log and position number on the master [root @ client101 2014-02-22_07-43-36]

2. compress the backup file and transfer it to DB2 (192.168.1.103) for restoration.

[Root @ client101 backup] # tar zcvf login.tar 2014-02-22_07-43-36 [root @ client101 backup] # scp login.tar 192.168.1.103:/tmp/

3. Extract the backup from DB2 and use it for restoration.

[Root @ client103 tmp] # tar-xf login.tar # Application log, ensure data consistency [root @ client103 tmp] # innobackupex -- apply-log/tmp/2014-02-22_07-43-36/# Stop mysql [root @ client103 mysql] #/etc/init. d/mysqld stop # Move all files in the mysql data directory to/tmp/mysqlbackup/. Otherwise, there is a problem with recovery # [we recommend that you copy only data files here, and do not recommend copying (logs, index, innodb shared tablespace, innodb log files, slow logs, etc.)] [root @ client103 mysql] # mv/var/lib/mysql/*/tmp/mysqlback # recover data [root @ client103 mysql] # innobackupex -- copy-back/tmp/2014-02-22_07-43-36/ # copy the content of the original database to the mysql data directory (here is what I think this software is poor, you can also give some suggestions) [root @ client103 mysqlback] # mv/tmp/mysqlbac k/*/var/lib/mysql/# change the permission of the data directory to mysql permission [root @ client103 mysql] # chown mysql. /var/lib/mysql/# Start the database [root @ client103 mysql] #/etc/init. d/mysqld start # verify data consistency: 5418272 (primary row) mysql> select count (*) from a; + ---------- + | count (*) | + ---------- + | 5418272 | + ---------- +

4. direct data from the DB2 host to the master DB1

# After replying, there will be a file: xtrabackup_binlog_pos_innodb --> This file records the log file and pos number during Backup [root @ client103 mysql] # cat xtrabackup_binlog_pos_innodb/var/lib/mysql/mysql-101-bin.0000082423 # Build master-slave relationship mysql> change master master_host = '2017. 168.1.101 ',-> master_user = 'slave',-> master_password = 'slave ',-> master_log_file = 'mysql-101-bin.000008',-> master_log_pos = 2423; # enable mysql synchronization> start slave; # view the synchronization status mysql> show slave status \ G;

5. If all values are yes, the master and slave nodes are created! You can test it on your own.

Note:

Another way is to add a master-slave instance online, that is, the original slave instance only performs redundancy. You can temporarily stop the master-slave instance on the slave instance,

Then, perform a backup on the slave machine to check which file and location (the log file and pos Number of the master) the log is executed on the current slave machine)

If you disagree with this Article or have a better way to do so, please feel free to give your feedback and make common progress!

This article is from the blog "children without umbrellas must be running hard". Please keep this source