[MySQL-MM] the process of automatically recovering an M2 database in MM in the production environment, sharing from scratch _ MySQL

[MySQL-MM] the process of automatically recovering an M2 database in MM in the production environment, sharing the automatic reconstruction script written from scratch and the concept bitsCN.com

[MySQL-MM] the process of automatically recovering an M2 database in MM in the production environment, and sharing the automatic reconstruction script and ideas written from scratch

1 Overview

If there are N0 + groups of MM nodes, if they are manually repaired each time, it would be okay to do it twice. if there are too many times, it will inevitably be useless because automatic scripts are created to avoid manual backup in the M database every time, manual copy, manual connection, manual setting of information replication process, my goal, a sh run command, everything is done properly, and then a cup of Nestle coffee or Pu'er tea, while waiting quietly or taking a nap, you can watch the M2 repair success:

2 Environment

It must be in the MM architecture, and one master database M1 is intact. one master database M2 can be rebuilt according to the M1 master database. if the MS architecture is used, you can perform a slight script modification and use it, shows the architecture:

3. set up the main script a_build_rep.sh.

The idea is divided into 13 steps, such as the remarks in the_build_rep.sh script.

#! /Bin/bash

Set-x

Set-e

Set-u

# (1) prepare the directory and global variables

BACKUP_FOLDER =/mysqldata/shared/backup2

TEMP_SETUP_FOLDER =/tmp/sharding_setup_1

MYSQL_EXEC =/opt/mysql/product/mysql/bin/mysql

# (2) set the parameter. $1 is the M2 library to be repaired, and $2 is the M1 library that is running well.

Db1 = $2

Db2 = $1

# (3) set some required environment variables, such as backing up users, backing up user passwords, copying account passwords, and even some super accounts.

BACKUP_USER = "backupuser"

BACKUP_PASSWORD = "# xx $"

AGENT_PASSWORD = '# xx $'

REPLICATION_USER = replication

REPLICATION_PASSWORD = '# xx $'

REPLICA_PASSWORD = '# xx $'

MONITOR_PASSWORD = '# xx $'

WRITER_ETH = 'eth0'

SUPER_USER = backupuser

SUPER_PASSWORD = '# xx $'

# (4) create these directories due to limited disk space. if the directory already exists, clear these directories.

Ssh-t $ db1 "rm-rf $ TEMP_SETUP_FOLDER & mkdir-p $ TEMP_SETUP_FOLDER & chmod 777 $ TEMP_SETUP_FOLDER"

Ssh-t $ db2 "rm-rf $ TEMP_SETUP_FOLDER & mkdir-p $ TEMP_SETUP_FOLDER & chmod 777 $ TEMP_SETUP_FOLDER"

# (5) copy the online backup script create_hot_backup.sh to the M1 database to be backed up online, and call this script for Online Backup. This backup is used to restore data to another M2 database.

Echo "Taking hotbackup on db1 ..."

Scp create_hot_backup.sh $ db1: $ TEMP_SETUP_FOLDER/create_hot_backup.sh

Ssh-t $ db1 "sudo-u mysql mkdir-p $ BACKUP_FOLDER"

Ssh-t $ db1 "sudo-I-u mysql $ TEMP_SETUP_FOLDER/create_hot_backup.sh '$ BACKUP_FOLDER'' $ BACKUP_USER ''$ BACKUP_PASSWORD' N Y"

# (6) stop the MySQL service of M2

Echo "Hotbackup completed. Now restore the hotbackup on db2 ..."

Ssh-t $ db2 "sudo/etc/init. d/mysql stop"

# (7) copy the restore_hot_backup.sh script to the M2 database and call this script to restore the M2 database.

Scp restore_hot_backup.sh $ db2: $ TEMP_SETUP_FOLDER/restore_hot_backup.sh

Ssh-t $ db2 "sudo-I-u mysql $ TEMP_SETUP_FOLDER/restore_hot_backup.sh '$ BACKUP_FOLDER'' $ BACKUP_USER ''$ BACKUP_PASSWORD'' $ TEMP_SETUP_FOLDER 'Y"

# (8) start the M2 database after recovery.

Ssh-t $ db2 "sudo/etc/init. d/mysql start"

Echo "Restore completed. Now rebuild replication between db1 and db2 ..."

# (9) create the replication function above M2. M1 is the master, M2 is the slave;

Echo "Setup replication from db1 to db2"

Echo "Setup replication from db1 to db2"

Scp $ db2: $ TEMP_SETUP_FOLDER/xtrabackup_binlog_info.master xtrabackup_binlog_info.db1

Binlog_filename = 'cat xtrabackup_binlog_info.db1 | awk '{print $1 }''

Binlog_pos = 'cat xtrabackup_binlog_info.db1 | awk '{print $2 }''

$ MYSQL_EXEC-h $ db2-ummm_agent-p "$ AGENT_PASSWORD"-e "slave stop ;"

$ MYSQL_EXEC-h $ db2-ummm_agent-p "$ AGENT_PASSWORD"-e "change master to MASTER_HOST = '$ db1', MASTER_PORT = 3306, MASTER_USER = 'replicase ', MASTER_PASSWORD = '$ REPLICATION_PASSWORD', MASTER_LOG_FILE = '$ binlog_filename', MASTER_LOG_POS = $ binlog_pos ;"

$ MYSQL_EXEC-h $ db2-ummm_agent-p "$ AGENT_PASSWORD"-e "slave start ;"

# (10) Check the status of the replication function of the M2 database.

Echo "Check db2 replication status ."

If! $ MYSQL_EXEC-h $ db2-ummm_agent-p "$ AGENT_PASSWORD"-e "show slave status/G" | grep "Slave_ SQL _Running: Yes"

Then

Echo "[ERROR] Cannot start slave on db2! "

Exit-1

Fi

# (11) create the replication function above M1, M2 is the master, M1 is the slave;

Echo "Setup replication from db2 to db1"

./Build_replication.sh $ db2 $ db1

# (12) Check the status of the replication function of the M1 database.

Echo "Check db1 replication status ."

If! $ MYSQL_EXEC-h $ db1-ummm_agent-p "$ AGENT_PASSWORD"-e "show slave status/G" | grep "Slave_ SQL _Running: Yes"

Then

Echo "[ERROR] Cannot start slave on db1! "

Exit-1

Fi

# (13) clear backup files in the temporary directory and release disk space

Echo "Clean up the shared storage folder and tmp folder ."

Ssh-t $ db1 "rm-rf $ TEMP_SETUP_FOLDER"

Ssh-t $ db2 "rm-rf $ TEMP_SETUP_FOLDER"

Ssh-t $ db1 "sudo-u mysql rm-rf $ BACKUP_FOLDER"

Echo 'rebuild secondary done! '

4-point script (1): the online backup script create_hot_backup.sh on the M1 database

#! /Bin/bash

Set-x

Set-e

#!!! This file need to be run in mysql !!!

# (1) set some basic parameters, $1: backup file; $2: backup user name; $3: backup user name and password; $4: whether secure mode backup is required; $5: requires rsync information?

BACKUP_FOLDER = $1

HOTBACKUP_USER = $2

HOTBACKUP_PASSWORD = $3

NEED_SAFE_SLAVE = $4

NEED_RSYNC = $5

# (2) parameter verification. if necessary, add the -- safe-slave-backup or -- rsync parameter to the subsequent backup command.

INNOBACKUP_OPT = ""

If [[$ NEED_SAFE_SLAVE = "Y"]

Then

INNOBACKUP_OPT = $ INNOBACKUP_OPT "-- safe-slave-backup"

Fi

If [[$ NEED_RSYNC = "Y"]

Then

INNOBACKUP_OPT = $ INNOBACKUP_OPT "-- rsync"

Fi

# (3) start to execute the backup command

Echo "Run xtrabackup to take hotbackup ..."

Export MYSQL_HOME =/opt/mysql/product/mysql

Innobackupex $ INNOBACKUP_OPT -- user = $ HOTBACKUP_USER -- password = $ HOTBACKUP_PASSWORD $ BACKUP_FOLDER

Script (2) restore_hot_backup.sh to restore data in the M2 database.

#! /Bin/bash

Set-x

Set-e

# (1) $1: backup data storage directory; $2: Restore User; $3: restore user password; $4: Temporary folder directory; $5: whether resync is required;

SHARED_STORAGE_PATH = $1

HOTBACKUP_USER = $2

HOTBACKUP_PASSWORD = $3

TEMP_SETUP_FOLDER = $4

NEED_RSYNC = $5

# (2) whether the resync parameter needs to be added

INNOBACKUP_OPT = ""

If [[$ NEED_RSYNC = "Y"]

Then

INNOBACKUP_OPT = "-- rsync"

Fi

# (3) put MYSQL_HOME in the environment variable

Export MYSQL_HOME =/opt/mysql/product/mysql

# (4) If the directory exists, delete the old files in these database directories.

Echo "Delete existing mysql instance ..."

Rm-rf/mysqldata/data

Rm-rf/mysqldata/shared/restore

Rm-rf/mysqldata/binlog /*

Rm-rf/mysqldata/iblogs /*

# (5) prepare the data directory and back up the data directory. create a new one if it does not exist and grant the mysql operation permission to the linux system account.

Echo "Apply and copy back backup files ..."

Mkdir-p/mysqldata/data & chown-R mysql: mysql/mysqldata/data & chmod 700/mysqldata/data

Mkdir-p/mysqldata/shared/restore

Backup_folder = $(ls-1 $ SHARED_STORAGE_PATH | sort-rn | head-n1) # this ls command is useful for finding the latest file.

# (6) for security reasons, you can copy the backup file data directory to the specified temporary recovery directory, and then perform -- apply-log and -- copy-back operations in the temporary directory, however, for efficiency, I removed the copy time (the copy time takes about one hour) and directly performed the -- apply-log and -- copy-back operations in the original backup data directory.

# Cp-r $ SHARED_STORAGE_PATH/$ {backup_folder}/*/mysqldata/shared/restore/

# Innobackupex -- user = $ HOTBACKUP_USER -- password = $ HOTBACKUP_PASSWORD -- ibbackup xtrabackup -- apply-log/mysqldata/shared/restore/

# Innobackupex -- user = $ HOTBACKUP_USER -- password = $ HOTBACKUP_PASSWORD $ INNOBACKUP_OPT -- copy-back/mysqldata/shared/restore/

Innobackupex -- user = $ HOTBACKUP_USER -- password = $ HOTBACKUP_PASSWORD -- ibbackup xtrabackup -- apply-log $ SHARED_STORAGE_PATH/$ {backup_folder }/

Innobackupex -- user = $ HOTBACKUP_USER -- password = $ HOTBACKUP_PASSWORD $ INNOBACKUP_OPT -- copy-back $ SHARED_STORAGE_PATH/$ {backup_folder }/

# (7) copy the copy point information in the original backup to the temporary directory and grant the corresponding permissions to the copied file.

Cp-f $ SHARED_STORAGE_PATH/$ {backup_folder}/xtrabackup_binlog_info $ TEMP_SETUP_FOLDER/xtrabackup_binlog_info.master

Chmod 644 $ TEMP_SETUP_FOLDER/xtrabackup_binlog_info.master

# (8) after the restoration is successful, delete the backup data in the original temporary directory and release the disk space. if the disk space is sufficient, this step can be omitted.

Echo "Remove backup files ..."

Rm-rf $ SHARED_STORAGE_PATH/$ {backup_folder }/

6. build the replication above M1. M2 is the master, M1 is the slave, and MM architecture.

#! /Bin/sh

Set-x

Set-u

Set-e

# (1) $1: master database server host name or IP address; $2: slave master database server host name or IP address;

MASTER_SERVER = $1

SLAVE_SERVER = $2

MYSQL_CNF_DIR = '/opt/mysql/product/mysql/etc'

MYSQL_EXEC = '/opt/mysql/product/mysql/bin/mysql'

# (2) obtain the copy point information from the master database $1

Master_file = $ (ssh-t $ MASTER_SERVER "$ MYSQL_EXEC-u $ SUPER_USER-p $ SUPER_PASSWORD-e 'show master status/g'" | grep "File" | awk '{print $2 }')

Master_file = $ (sed-e's/[/r/n] // '<"$ master_file ")

Master_pos = $ (ssh-t $ MASTER_SERVER "$ MYSQL_EXEC-u $ SUPER_USER-p $ SUPER_PASSWORD-e 'show master status/g'" | grep "Position" | awk '{print $2 }')

Master_pos = $ (sed-e's/[/r/n] // '<"$ master_pos ")

# (3) execute the copy SQL command operation:

Ssh-t $ SLAVE_SERVER "$ MYSQL_EXEC-u $ SUPER_USER-p $ SUPER_PASSWORD-e /"

Stop slave;

Reset slave;

Change master to master_host = '$ MASTER_SERVER', master_port = 3306, master_user = '$ REPLICATION_USER ',

Master_password = '$ REPLICATION_PASSWORD', master_log_file = '$ master_file', master_log_pos = $ master_pos;

Start slave;

/""

7. perform the operation

In the root directory, run

[Root @ xx-control xx] # nohup/home/cc/a_build_rep.sh m2.xx.com m1-.xx.com.> rebuild. log &, run in the background, use nohup to prevent sudden screen failure.

Use tail-f rebuild. log to view the progress:

[Root @ xx-control xx] # tail-f rebuild. log

+ Set-e

+ BACKUP_FOLDER =/mysqldata/shared/backup

Run xtrabackup to take hotbackup...

+ HOTBACKUP_USER = backupuser

+ HOTBACKUP_PASSWORD = '# xxx $'

+ NEED_SAFE_SLAVE = N

+ NEED_RSYNC = Y

+ INNOBACKUP_OPT =

+ [[N =/Y]

+ [[Y =/Y]

+ INNOBACKUP_OPT = '-- rsync'

+ Echo 'run xtrabackup to take hotbackup ...'

+ Export MYSQL_HOME =/opt/mysql/product/mysql

+ MYSQL_HOME =/opt/mysql/product/mysql

+ Innobackupex -- rsync -- user = user' -- password = # xxx $ '/mysqldata/shared/backup

........................................ .........................................

........................................ ........................................

Rebuild secondary done!

BitsCN.com