0

I have written a shell script to do an incremental backup of MySQL database.
For point in time recovery I'm using the binary logs generated in /var/lib/mysql directory.
For point-in-time recovery, Im creating an sql file using the following statement.

mysqlbinlog --start-datetime="$STARTTIME" --stop-datetime="$ENDTIME" $file > $NOW.test.sql

The incremental backup happens such that the $STARTTIME is going to be the end time of the Fullbackup and $file is the list of log files generated since the fullbackup endtime to the time of start of incremental backup.
My question is how can I create a .sql file in /var/lib/mysql folder and move that file to the destination . When I try the above statement, it says permission denied. How can i achieve this? I know the permissions for the file has to be changed. Is there any alternative for this.

3
  • how you are planing point in time recovery script. I means on what basis you are writing the script to find out last bin log file position and current bin log file,position.
    – ashuthosh
    Feb 21, 2013 at 5:53
  • @ashuthosh NEWFULLLOG=ls -d /usr/local/mysql/data/mysql-bin.??????| sed 's/^.*\.//'| sort -g| tail -n 1
    – Rudra
    Feb 21, 2013 at 11:10
  • I changed permissions of that directory ....it is working now
    – Rudra
    Feb 25, 2013 at 11:53

1 Answer 1

0
#!/bin/bash

# Variables
STARTTIME="YYYY-MM-DD HH:MM:SS"
ENDTIME="YYYY-MM-DD HH:MM:SS"
NOW=$(date +"%Y%m%d%H%M%S")
TEMP_DIR="/path/to/temp/dir"
FINAL_DEST="/var/lib/mysql"
BINLOG_PATH="/var/lib/mysql"

# Ensure TEMP_DIR exists
mkdir -p "$TEMP_DIR"

# Generate SQL from binary logs
mysqlbinlog --start-datetime="$STARTTIME" --stop-datetime="$ENDTIME" "${BINLOG_PATH}/mysql-bin.*" > "${TEMP_DIR}/${NOW}.test.sql"

# Move SQL file to the desired location (requires appropriate permissions)
# Option 1: Using sudo to move the file (if you have sudo privileges)
sudo mv "${TEMP_DIR}/${NOW}.test.sql" "${FINAL_DEST}/"

# Option 2: Change ownership and move the file (if you do not have sudo privileges but have access to the mysql user)
# chown mysql:mysql "${TEMP_DIR}/${NOW}.test.sql"
# mv "${TEMP_DIR}/${NOW}.test.sql" "${FINAL_DEST}/"

echo "Point-in-Time recovery file created and moved successfully."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.