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