mysqlhotcopy热备Mysql数据库

最新推荐文章于 2025-09-06 11:18:45 发布

cnbird2008

最新推荐文章于 2025-09-06 11:18:45 发布

阅读量805

点赞数

文章标签： mysql database user server 数据库服务器

本文介绍如何通过配置无密码SSH实现两台服务器间的MySQL热备，并提供了一段Shell脚本来自动化这一过程。

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

view plain copy to clipboard print ?

grant select,reload,LOCK TABLES on *.* to backup@localhost identified by '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