MySQL management-binary log file management

The binary log of Mysql records the information of all mysql database changes, so it is very important to the database. The role of binary log is summarized as follows:

1. It can be used for restoration of databases based on time points.

2. It can be used to analyze database changes, such as database data changes caused by program bugs.

3. Binary logs can also be used to recreate the database.

From the above points, we can see that mysql binary logs are still very important. in daily work, binary logs often occupy a lot of space, especially for frequently written databases, binary logs increase rapidly. How can I manage binary logs in DBA's daily work? Next we will explain in detail how I manage binary logs in my daily work.

You can manage binary logs as follows:

1. Use shell scripts to flush binary logs every hour

2. Use the shell script to back up binary logs every hour. migrate the new binary logs generated in one hour to the nfs backup server, compress them, and store them. The script is attached!

3. Add expire_logs_days = 5 to the my. cnf configuration file, so that mysql will automatically delete logs generated five days ago.

The procedure is as follows:

1. Set up an NFS backup server.

It is very easy to set up NFS. You can complete the following steps:

Yum-y install setup-* initscripts-* nfs-utils-* portmap-* quota -*

Edit/etc/exports and add a backup entry for a line of binary logs:

Vim/etc/exports

/Data/binlog_backup 192.168.3.135 (rw, sync, no_root_squash) * (ro)

Allow the IP address 192.168.3.135 to be read and written, and read-only to other IP addresses.

After the configuration is complete, restart portmap and nfs:

/Etc/rc. d/init. d/portmap restart

/Etc/rc. d/init. d/nfs restart

Now the NFS settings on the server are complete. Next, set the client to how to mount the mysql server:

/Etc/rc. d/init. d/portmap start

Mkdir/data/binlog_backup

Mount-t nfs 192.168.3.92:/data/binlog_backup

If the portmap of the client is not started, mount is reported during mounting. nfs: Input/output error. Remember to add the mount command to/etc/rc. local, otherwise the backup of binary logs will fail after the server is restarted next time.

2. Place the script on the mysql database and set the scheduled task to run every hour.

The script content is as follows:

Cat bin_log_magment.sh

#! /Bin/bash
# Purpose: Manages mysql binary logs, refreshes binary logs every hour, and copies the logs to the nfs server for future recovery and problem analysis!
# Author: carl_zhang
# Date: 2012-5-15

 

# Some variables Parameters
# Define database information
USERNAME = root
PASSWORD = profile @ 123
HOSTIP = localhost
MYSQL =/usr/local/mysql/bin/mysql

# Set the path of the binary log
BIN_LOG_DIR =/data/dbdata
# Set the path for binary log backup
BIN_LOG_BACKUP_DIR =/data/binlog_backup
# Define a constant to skip the last log file
COUNT = 0
# Define a log file
LOGFILE = $ BIN_LOG_BACKUP_DIR/binlog_backup.log
# Locate the name prefix of the binary log
BINLOG_PREFIX = 'grep "log-bin ="/etc/my. cnf | awk-F' = ''{print $2 }''
# Check whether the backup. file exists. If this file does not exist during the first running, an error is returned.

If [! -F "$ BIN_LOG_BACKUP_DIR/backup. file"]; then
Touch $ BIN_LOG_BACKUP_DIR/backup. file
Fi
# Run flush logs before running file comparison
$ MYSQL-u $ USERNAME-p $ PASSWORD-h $ HOSTIP-e "flush logs"
# Compare the list of binary log files and the list of backed up binary files
FILE_LIST = 'comm-23 $ BIN_LOG_DIR/$ BINLOG_PREFIX.index $ BIN_LOG_BACKUP_DIR/backup. file'
# Count the number of log files
FILE_COUNT = 'comm-23 $ BIN_LOG_DIR/$ BINLOG_PREFIX.index $ BIN_LOG_BACKUP_DIR/backup. file | wc-l'

 

 

# Program body
# Start to back up binary log files
For file in $ FILE_LIST
Do
BINLOG_NAME = 'basename $ file'
Let COUNT = $ COUNT + 1
If [$ COUNT-eq $ FILE_COUNT]; then
# Skip the latest binary log file
Echo "skip the lastest binlog file"> $ LOGFILE
Else
Cp $ BIN_LOG_DIR/$ BINLOG_NAME $ BIN_LOG_BACKUP_DIR/
If [$? -Eq 0]; then
Echo "'date-d" today "+ % Y-% m-% d-% H-% M-% s' $ BINLOG_NAME backup success" >>$ LOGFILE
Else
Echo "'date-d" today "+ % Y-% m-% d-% H-% M-% s' $ BINLOG_NAME backup faild, please check it out ">>$ LOGFILE
Exit 5
Fi
# Compressing and storing binary logs
Gzip $ BIN_LOG_BACKUP_DIR/$ BINLOG_NAME
If [$? -Eq 0]; then
Echo "'date-d" today "+ % Y-% m-% d-% H-% M-% s' $ BINLOG_NAME gzip success" >>$ LOGFILE
Else
Echo "'date-d" today "+ % Y-% m-% d-% H-% M-% s' $ BINLOG_NAME gzip faild, please check it out ">>$ LOGFILE
Exit 5
Fi
Echo./$ BINLOG_NAME >>$ BIN_LOG_BACKUP_DIR/backup. file
Fi
Done

If you need to use this script, change the corresponding configuration information, such as the IP address, user name, password, and directory.

Note that the user name and password of mysql are available. Set the script permission to 700 and run the following command:

Chmod 700 bin_log_magment.sh

After the manual operation is complete, add it to the scheduled task:

00 */1 ***/var/script/bin_log_magment/bin_log_magment.sh>/dev/null 2> & 1

3. Set the my. cnf configuration file, add expire_logs_days = 5, and restart mysql.

This is not described in detail. It's a personal meeting!