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

This article mainly introduces mysql master-slave configuration to do full backup method, I hope you can supplement and update some knowledge, if there are other problems to understand, you can continue to pay attention to my update article in the industry information.

Purpose: 19 as 20 from the library

A full backup on 20

/usr/bin/innobackupex-1.5.1--user=root --password=`cat /etc/savep` --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --slave-info--stream=tar /usr/local/backup | gzip >/usr/local/backup/db20.tar.gz

Copy db20.tar.gz to 19

--slave-info saves the main library log file and offset

--stream=tar/usr/local/backup |gzip > /usr/local…Compression method

[When using stream=tar backup, your xtrabackup_logfile may be temporarily placed in the/tmp directory, and multiple instances of xtrabackup will generate fixed files/tmp/xtrabackup_logifle during backup, resulting in mutual overwriting. --tmpdir= ]

Two starts a new instance on 19

Create/etc/myNew.cnf Note that the server id should be different from 20 my.cnf server_id xxx

initialize the database

3. scripts/mysql_install_db --user=mysql --datadir=/data/mysqlNew/dataNew --basedir=/usr/local/mysql

Start a new instance to be used as a slave library

5. /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/myNew.cnf --user=mysql &

Three on 19 install xtrabackup

Old Version 5.1

wgethttp://115.182.52.17/software/xtrabackup-1.6.5-328.rhel6.x86_64.rpm

rpm -ivh xtrabackup-1.6.5-328.rhel6.x86_64.rpm --nodeps (rpm mode xtrabackup. install)

New version 5.6

wget -O/root/soft/percona-xtrabackup-2.2.11-1.el6.x86_64.rpm

http://115.182.52.17/software/percona-xtrabackup-2.2.11-1.el6.x86_64.rpm

rpm -ivh percona-xtrabackup-2.2.11-1.el6.x86_64.rpm --nodeps

4. Restore cold backup data of the main library on 19

decompression

2. mv db20.tar.gz /usr/local/backup/

3. tar -izxvf db20.tar.gz

Here tar unpacking must use the-i parameter, otherwise the decompressed file has only one backup-my.cnf

In general, after the backup is complete, the data is not yet available for recovery operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but not yet synchronized to the data file. Therefore, at this point the data file is still processing inconsistencies. -- The apply-log is used to bring the data file into a consistent state by rolling back uncommitted transactions and synchronizing committed transactions to the data file.

5. innobackupex --apply-log . --user=root --password=`cat /etc/savep` --defaults-file=/etc/myNew.cnf

Copy data files

7. mysqladmin -uroot -S /tmp/mysqlNew.sock shutdown -p Stop library delete

8. cd /data/mysqlNew/dataNew/

9. rm -rf *

10. mv /usr/local/backup/* .

11. chown -R mysql.mysql *

12. mysqld_safe --defaults-file=/etc/myNew.cnf --user=mysql &

tail -f log.err Check if there is an error

enter the database to see if the database is the same as the main database.

So far, 20 database cold backups have been restored to 19

Five start synchronization

Determine the binlog location of the cold backup of the master repository

2. cd /data/mysqlNew/dataNew/

3. cat xtrabackup_binlog_info (used to determine the binlog location of the master if the cold backup was backed up directly from the master)

4. or catxtrabackup_slave_info (used to determine the binlog location of the master if the cold backup was backed up from another slave profile of the master)

Enter a new instance and create a new synchronization

6. mysql -uroot -S /tmp/mysqlNew.sock -p

7. mysql>change master to

8. -> master_host='10.0.0.20',

9. ->master_user='re4399pl',

10. ->master_port=3306,

11. ->master_password='re4399plpassword',

12. ->master_log_file='mysql-bin.000006',

13. ->master_log_pos=110632233;

The above step requires creating an account on the main library now

GRANT FILE,SELECT,REPLICATION SLAVE ON*.* TO 're4399pl'@'10.0.0.% 'IDENTIFIED BY 're4399plpassword';

master_log_filemaster_log_pos is the content in 5.1

initiate synchronization

15. mysql>start slave;

16. mysql> show slave status\G

17. If you want to restart the library,

18. 1，mysqladmin -uroot -pxxxx 2,mysqld_safe -user=mysql

6 Other Points of Attention

Note the different data directories and my.cnf files under multiple instances

If chain synchronization is required, binlog is enabled from the library: log_slave_updates=1

skip-slave-start has this parameter, the database will not automatically synchronize after restarting

After the master and slave are abandoned, they should stop slave and reset slave in time so as not to forget to synchronize again next time

Seven practices on synchronizing to slave libraries and filtering partial tables

Add from library

replicate-wild-do-table=diygame_admin.%

replicate-wild-ignore-table=diygame_online.diygame_game_data%

replicate-wild-do-table=diygame_online.%

8 Redirect to another library after synchronization

Add from library

replicate-wild-do-table=diygame_admin.diygame_template_preview

replicate-wild-do-table=3387_base_admin.%

replicate-rewrite-db=diygame_admin->3387_base_admin

replicate-rewrite-db, and only redirects libraries, not tables, and also does not work with cross-database updates

ten-time backup

0 0 * * * /cron/db_backup.sh > /tmp/db_backup.log 2>&1

cat /cron/db_backup.sh

#!/ bin/bash

PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/sbin:/sbin

DATE=`date +%Y%m%d`

IPADDR=`/sbin/ifconfig em1|grep 'inet addr'|awk '{print $2}'|awk -F ":" '{print $2}'`

passwd=`cat /etc/savep`

NAME=db185_hebi

mkdir /tmp/$NAME

/usr/bin/innobackupex-1.5.1 --user=root --password=`cat /etc/savep` --defaults-file=/etc/my.cnf --slave-info --tmpdir=/tmp/$NAME/ --stream=tar /usr/local/backup | gzip > /usr/local/backup/$NAME-$DATE-$IPADDR.tar.gz

cd /usr/local/backup/

/usr/local/bin/rsync -R -avz --progress --password-file=/etc/49cn_pass $NAME-$DATE-$IPADDR.tar.gz 49cn@13.7.3.9::49cn/49cn

find /usr/local/backup/ -type f -mtime +7 | xargs rm -f

Read the above on mysql master-slave configuration to do full backup method, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

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.