本文编写一个shell脚本,实现mysql主从环境的自动配置
1、在主服务器上编辑/etc/my.cnf文件,在[mysqld]的下面加入:
log-bin=mysql-bin
server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-do-db=centos binlog_ignore_db=mysql
配置说明:
server-id=1中的1可以任定义,只要是唯一的并且比从服务器的server-id小就行。
binlog-do-db=centos是表示binlog只记录centos数据库的日志,即只同步centos。 binlog_ignore_db=mysql表示忽略备份mysql。 不加binlog-do-db和binlog_ignore_db,那就表示备份全部数据库。
2、在从服务器编辑配置文件my.cnf,在[mysqld]下面加入:
server-id=2
3、脚本分为两个,一个是配置mysql主服务器的shell脚本,另一个是远程连接mysql从服务器的exp脚本。
1)、bash shell脚本:master.sh
复制代码代码示例:
#!/bin/bash
export mysqlbinpath="/usr/bin" #mysql路径 #variables for master export master_mysql_root_passwd="root" #mysql主数据库的root密码 export replication_user="copydb" #用于复制的mysql用户 export replication_passwd="123456" #mysql用户copydb的密码 export replication_db="centos" #需要同步的数据库名 export master_ip="8.8.8.8" #mysql主服务器IP #variables for slave export slave_mysql_root_passwd="123456" #mysql从数据库的root密码 export slave_ip="8.8.4.4" #从服务器IP地址 export slave_ssh_root_passwd="123456" #mysql从数据库的ssh的root密码 #create replication user { ${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} <<EOF CREATE USER '$replication_user'@'$slave_ip' IDENTIFIED BY '$replication_passwd'; GRANT REPLICATION SLAVE ON *.* TO '$replication_user'@'$slave_ip' IDENTIFIED BY '$replication_passwd'; FLUSH TABLES WITH READ LOCK; select sleep(10); EOF } & #export the database sql data. ${mysqlbinpath}/mysqldump -uroot -p${master_mysql_root_passwd} ${replication_db} > ${replication_db}.sql #get the master status info. export status=`${mysqlbinpath}/mysql -uroot -p${master_mysql_root_passwd} -e"show master status\G"` export binlogname=`echo "$status" | grep "File" | awk '{print $2}'` export position=`echo "$status" | grep "Position" | awk '{print $2}'` #create database on slave server. export createdb="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} -e'drop database if exists ${replication_db};create database ${replication_db};'" #import database sql data on slave server. export importsql="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} ${replication_db} < /root/${replication_db}.sql" #deploy the slave mysql server. export change_master="${mysqlbinpath}/mysql -uroot -p${slave_mysql_root_passwd} -e'stop slave;CHANGE MASTER TO MASTER_HOST=\"${master_ip}\",MASTER_USER=\"${replication_user}\",MASTER_PASSWORD=\"${replication_passwd}\",MASTER_PORT=3306,MASTER_LOG_FILE=\"${binlogname}\",MASTER_LOG_POS=${position},MASTER_CONNECT_RETRY=10;start slave;select sleep(3);show slave status\G'" yum -y install expect ./slave.exp
2)、expect脚本:slave.exp
复制代码代码示例:
#!/usr/bin/expect -f
spawn scp $env(replication_db).sql root@$env(slave_ip):/root expect { "*assword" {set timeout 300; send "$env(slave_ssh_root_passwd)\r";} "yes/no" {send "yes\r"; exp_continue;} } expect eof spawn ssh root@$env(slave_ip) "$env(createdb);$env(importsql);$env(change_master)" expect { "*assword" {set timeout 300; send "$env(slave_ssh_root_passwd)\r";} "yes/no" {send "yes\r"; exp_continue;} } expect eof
使用方法:
注意事项: |