mysql 主从布置

master: 192.168.80.24

slave: 192.168.80.45

cat master.bash
#!/bin/bash
USER="root"
PASSWD="123456"
MYSQL=which mysql

主服务器进行操作

访问mysql,创建同步用户并授权

$MYSQL -u$USER -p$PASSWD << EOF
#insert into mysql.user(host,user,password) values("localhost","repl",password("repl"))
GRANT USAGE ON . TO 'repl'@'localhost' IDENTIFIED BY 'repl' WITH GRANT OPTION;
flush privileges
grant replication slave on . to 'repl'@'192.168.80.45' identified by 'repl'
EOF

编辑配置文件/etc/my.cnf,在[mysqld]下插入下面两行

sed -i '/^[mysqld]$/alog-bin=mysql-bin\nserver-id=24' /etc/my.cnf

重启主mysql服务

/etc/init.d/mysql restart

登陆mysql查询主数据库的状态,记下File以及Position的值，在后面进行从服务器操作的时候分别作为参数 $1 、$2 使用

$MYSQL -u$USER -p$PASSWD << EOF
show master status
EOF

cat slave.bash
#!/bin/bash
USER="root"
PASSWD="123456"
MYSQL=which mysql

readme

Usage: bash slave.bash $1 $2

从服务器进行操作

编辑配置文件/etc/my.cnf,在[mysqld]下插入下面两行

sed -i '/^[mysqld]$/alog-bin=mysql-bin\nserver-id=45' /etc/my.cnf

重启mysql服务

/etc/init.d/mysql restart

登陆mysql，执行同步的sql语句,然后开启slave，再查看slave状态

$MYSQL -u$USER -p$PASSWD << EOF
change master to master_host='192.168.80.24',master_user='repl',master_password='repl',master_log_file='$1',master_log_pos=$2
start slave
show slave status\G
EOF