Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to transfer Zabbix database through XtraBackup and MySQL master-slave replication. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, let's go straight to the topic, and hope that this article on how to transfer Zabbix database through XtraBackup and MySQL master-slave replication can bring you some practical help.

An application scenario description

At present, the server side and the MySQL side of the Zabbix monitoring system run on the same virtual machine. Recently, this virtual machine is not very stable, and MySQL is frequently killed by OOM. Therefore, it is necessary to migrate the Zabbix database to another physical machine with large memory in time.

Two migration steps

The idea of migration is to build MySQL master-slave replication, take the existing MySQL instance as Master, the new MySQL instance as Slave, now DUMP a copy of existing data to a new MySQL instance, then set master-slave synchronization, and finally turn off master-slave synchronization.

Here we use XtraBackup to back up Master data instead of mysqldump, because mysqldump backs up tables and takes a long time to back up.

1. Change the my.cnf of Master

Log-bin=mysql-binlog-bin-index=mysql-bin.indexserver-id=1binlog_format=mixed

two。 Change the my.cnf of Slave

Server-id = 2relay-log-index = slave-relay-bin.indexrelay-log = slave-relay-binreplicate-ignore-db=mysql

3. Start a backup using xtrabackup

. / innobackupex-- user=xxxxx-- password=xxxxx / backup/

Backup time varies depending on the amount of data, but much faster than mysqldump

You can use. / xtrabackup-- help to view detailed parameters

If the backup time is estimated to be long, you can open a screen window

After the data is backed up, you need to copy a copy of snapshot.

. / innobackupex-- user=xxxxx-- password=xxxxx-- apply-log / backup/2016-06-080059-31 /

4. Copy backup data to a new MySQL CVM

Rsync-avz / backup/2016-06-08. 00-59-31 172.28.2.xxRose ~

Make sure the new MySQL instance is closed

Mv / opt/data/mysql/* / backup/mv 2016-06-08: 00-59-31 Universe * / opt/data/mysql/chown mysql:mysql / opt/data/mysql

5. Add a copy account

CREATE USER repl_user;GRANT REPLICATION SLAVE ON *. * TO repl_user IDENTIFIED BY 'xyzzy';flush privileges

6. Check whether you can log in with a replication account on the Slave server.

# mysql-h 172.28.2.xxx-urepl_user-pmysql > show grants +-+ | Grants for repl_user@% | | +-+ | GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'%' IDENTIFIED BY PASSWORD | +-| -+ 1 row in set (0.03 sec)

7. Configure master-slave replication

# cat / opt/data/mysql/xtrabackup_binlog_info mysql-bin.000053 120mysql > CHANGE MASTER TO-> MASTER_USER='repl_user',-> MASTER_PASSWORD='xyzzy',-> MASTER_HOST='172.28.2.XXXX',-> MASTER_LOG_FILE='mysql-bin.000053',-> MASTER_LOG_POS=120;Query OK, 0 rows affected, 2 warnings (0.61 sec) mysql > START SLAVE;Query OK, 0 rows affected (0.02 sec)

SHOW SLAVE STATUS\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Mainly look at these three indicators

8. Stop Zabbix server and observe the synchronization delay time of MySQL Slave. When Seconds_Behind_Master is 0, you can perform the following steps.

9. Stop and reset MySQL Slave

Mysql > STOP SLAVE;Query OK, 0 rows affected (0.04 sec) mysql > RESET SLAVE;Query OK, 0 rows affected (0.13 sec) mysql > SHOW SLAVE STATUS\ gateway * 1. Row * *

Change the configuration file for Slave

Delete the original master-slave replication-related configuration items

Server-id = 2relay-log-index = slave-relay-bin.indexrelay-log = slave-relay-binreplicate-ignore-db=mysql

Add

Log-bin=mysql-binlog-bin-index=mysql-bin.indexserver-id=1binlog_format=mixed

Restart MySQL

10. Change the MySQL configuration of Zabbix server and start

In addition to changing the Zabbix server configuration file, you also need to change the zabbix web configuration file to change the mysql configuration to the new mysql

How to transfer Zabbix database through XtraBackup and MySQL master-slave replication will stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.