mysql 主从互备份_mysql互为主从实战设置详解及自动化备份(Centos7.2)

mysql互为主从实战设置详解(Centos7.2)

第一步：mysql配置

my.cnf配置

服务器1 (10.89.10.90)

[mysqld]

server-id=1

log-bin=/usr/local/mysql/binlog

binlog-do-db = ms

replicate-do-db = ms

skip-slave-start=0

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

socket=/tmp/mysql.sock

user=mysql

bind-address=0.0.0.0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/usr/local/mysql/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

服务器2 (10.89.10.91)

[mysqld]

server-id=1

log-bin=/usr/local/mysql/binlog

binlog-do-db = ms

replicate-do-db = ms

skip-slave-start=0

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

socket=/tmp/mysql.sock

user=mysql

bind-address=0.0.0.0

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/usr/local/mysql/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

第二步：用root账户登录mysql命令行

mysql -uroot -p12345678

第三步：mysql用户配置

添加互为主从账户ms 密码 12345678

分配ms账户的主备权限

服务器1 (服务器1登录服务器2的权限及将服务器2设置成当前服务器1的主服务器)

grant replication slave on *.* to 'ms'@'10.89.10.91' identified by '12345678';

change master to master_host='10.89.10.91',master_user='ms',master_password='12345678',master_log_file='binlog.91',master_log_pos=154;

服务器2 (服务器2登录服务器1的权限及将服务器1设置成当前服务器2的主服务器)

grant replication slave on *.* to 'ms'@'10.89.10.90' identified by '12345678';

change master to master_host='10.89.10.90',master_user='ms',master_password='12345678',master_log_file='binlog.90',master_log_pos=154;

第四步

启动服务器1的slave

start slave;

启动服务器2的slave

start slave;

第五步

查看服务器1的slave的状态

show slave status\G;

查看服务器2的slave的状态

show slave status\G;

下面两项都是 yes表示配置成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果出现Slave_IO_Running 又错误，请核对master_log_file 文件名，是否与/usr/local/mysql下的日志文件名一致，不一致请修改后，从第三步重新做

第六步

测试库表及测试语句

#drop table T0001;

服务器1

create table T0001(F0001 bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',F0002 varchar(20), F0003 varchar(30), primary key (F0001));

insert into T0001(F0002,F0003) values('90R1F2', '90R1F3');

insert into T0001(F0002,F0003) values('90R2F2', '90R2F3');

服务器2

insert into T0001(F0002,F0003) values('91R1F2', '91R1F3');

insert into T0001(F0002,F0003) values('91R2F2', '91R2F3');

每台上面都执行一下

select * from T0001;

如果数据都一致，那么配置成功！

下面附一段 mysql自动化备份脚本.txt

#!/bin/sh

# cpm_backup.sh: backup mysql databases and keep newest 5 days backup.

#

# your mysql login information

# db_user is mysql username

# db_passwd is mysql password

# db_host is mysql host

# -----------------------------

db_user="root"

db_passwd="12345678"

db_host="localhost"

# the directory for story your backup file.

backup_dir="/cpmbackup"

# date format for backup file (dd-mm-yyyy)

time="$(date +"%d-%m-%Y")"

# mysql, mysqldump and some other bin's path

MYSQL="/usr/local/mysql/bin/mysql"

MYSQLDUMP="/usr/local/mysql/bin/mysqldump"

MKDIR="/bin/mkdir"

RM="/bin/rm"

MV="/bin/mv"

GZIP="/bin/gzip"

# check the directory for store backup is writeable

test ! -w $backup_dir && echo "Error: $backup_dir is un-writeable." && exit 0

# the directory for story the newest backup

test ! -d "$backup_dir/backup.0/" && $MKDIR "$backup_dir/backup.0/"

# get all databases

all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')"

for db in $all_db

do

$MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db | $GZIP -9 > "$backup_dir/backup.0/$time.$db.gz"

done

# delete the oldest backup

test -d "$backup_dir/backup.5/" && $RM -rf "$backup_dir/backup.5"

# rotate backup directory

for int in 4 3 2 1 0

do

if(test -d "$backup_dir"/backup."$int")

then

next_int=`expr $int + 1`

$MV "$backup_dir"/backup."$int" "$backup_dir"/backup."$next_int"

fi

done

exit 0;

每天3点自动执行备份脚本

vi /etc/crontab 添加下面的行：

01 3 * * * root /cpmbackup/cpm_backup.sh