mysqlhotcopy热备Mysql数据库

最新推荐文章于 2024-08-08 00:15:00 发布

最新推荐文章于 2024-08-08 00:15:00 发布 · 106 阅读

文章标签：

#数据库 #运维 #shell

本文介绍如何通过SSH无密码访问实现MySQL服务器的热备份，包括服务器间SSH配置及备份shell脚本的具体实现。

一、配制两台服务器(主=>从)无密码SSH访问;
二、添加热备用户权限：

view plain copy to clipboard print ?

grantselect,reload,LOCKTABLESon*.*tobackup@localhostidentifiedby'pass'

grant select,reload,LOCK TABLES on *.* to backup@localhost identified by 'pass'
三、备份SHELL
#!/bin/bash BACKUP_SERVER="192.168.100.2" BACKUP_USER="mysql" MYSQL_USER="backup" MYSQL_USER_PASS="pass" 　　 MYSQLHOTCOPY=/usr/local/mysql/bin/mysqlhotcopy # Change to MySQL data directory cd /usr/local/mysql/data 　　 # Create an array with all directories in the MySQL data directory # The directory name presents the database name in MySQL. ARRAY=`find * -type d -prune` for DATABASE in `echo $ARRAY` do # Create backup from database $MYSQLHOTCOPY --method='scp -Bq -i /home/foo/.ssh/identity'--user=`echo $MYSQL_USER` --password=`echo $MYSQL_USER_PASS` / $DATABASE `echo $BACKUP_USER`@`echo $BACKUP_SERVER`:/usr/local/mysql/data/$DATABASE done