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