MySQL生产服务器主从热备份
1.设置www1为主服务器,并搬迁数据到/bak/mysql
配置my.inf
[client]
#socket = /var/lib/mysql/mysql.sock
socket = /bak/mysql/mysql.sock
[mysqld]
#socket = /var/lib/mysql/mysql.sock
socket = /bak/mysql/mysql.sock
datadir=/bak/mysql
log-slow-queries = /bak/mysql/sqlslow.log
#socket = /var/lib/mysql/mysql.sock
socket = /bak/mysql/mysql.sock
[mysqld]
#socket = /var/lib/mysql/mysql.sock
socket = /bak/mysql/mysql.sock
datadir=/bak/mysql
log-slow-queries = /bak/mysql/sqlslow.log
#mysql主服务器配置
server-id = 1
log-bin
binlog-do-db=bbs
binlog-do-db=cactidb
max_binlog_size = 104857600
log-bin
binlog-do-db=bbs
binlog-do-db=cactidb
max_binlog_size = 104857600
修改/etc/init.d/mysqld
get_mysql_option /etc/my.cnf datadir "/bak/mysql"
先停止mysql数据库
/etc/init.d/mysqld start
迁移数据库文件(注意权限)
mv /var/lib/mysql /bak/mysql
再启动mysql数据库
/etc/init.d/mysqld start
添加从服务器复制用户:
mysql -uroot -p
mysql>grant replication slave on *.* to 'rep'@'10.0.0.1' identified by '密码';
mysql>grant replication slave on *.* to 'rep'@'10.0.0.1' identified by '密码';
####################################################################################
2.配置www的从服务器,并同步数据
先安装
yum install mysql mysql-server mysql-devel
拷贝配置文件再修改:
scp -P 10023 www1:/etc/init.d/mysqld /etc/init.d/mysqld
scp -P 10023 www1:/etc/my.cnf /etc/my.cnf
scp -P 10023 www1:/etc/my.cnf /etc/my.cnf
vi /etc/my.cnf
read-only=1
server-id = 2
master-host = 10.0.0.2
master-user = rep
master-password = abcdefg
master-port = 3306
replicate-do-db = bbs
replicate-do-db = cactidb
server-id = 2
master-host = 10.0.0.2
master-user = rep
master-password = abcdefg
master-port = 3306
replicate-do-db = bbs
replicate-do-db = cactidb
scp -P 10023 -pr www1:/bak/mysql /bak/
启动数据库:
/etc/init.d/mysqld start
####################################################################################
3.相关命令
主服务器:
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}
从服务器:
slave start
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志
slave stop
SLAVE STOP IO_THREAD //此线程把master段的日志写到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此线程把写到本地的日志应用于数据库
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志
Sorry, comments for this entry are closed at this time.