MySQL主从双向复制 (脚本实现)
1主服务器的主机名为: master,从服务器的主机名为:slave
2安装 MYSQL包
# rpm –ivh mysql-5.0.77-4.2.1.AXS3.i386.rpm
3在主库启动 MYSQL
#service mysql start
4在主库编辑 /etc/my.cnf,
#vim /etc/my.cnf
在 [mysqld]配置段添加如下字段
server-id=1
log-bin=cjxylog
编辑好 my.cnf后重启mysql
#service mysqld restart
5主库增加用户,用于从库读取主库日志
>grant replication slave on *.* to 'slave'@'10.10.10.52' identified by 'slave';
启动后用 show master status查看主库的日志文件
#mysql
>show master status
6启动从库的 mysql
#service mysqld start
7修改从库 /etc/my.cnf,增加选项:
#Mysql
#vim /etc/my.cnf
添加如下内容
server-id=2
master-host=10.10.10.51
master-user=slave
master-password=slave
master-port=3306
master-connect-retry=60
编辑完后重启 mysql
#service mysqld restart
8重启后用 show命令查看从库slave的运行情况
#mysql
>stop slave;
>start slave;
>show slave status\G;
9测试
主库创建名为 cjxy的数据库并用show databases;命令查看
#mysql
>start master
>create database cjxy;
>show databases;
从库只须用 show命令查看即可
#mysql
>show databases;
10单向复制成功 ,编写脚本,实现主从双向复制和故障自动切换角色 ,从而实现MYSQL数据库高可用性.
在两台 MYSQL服务器上配置3份MYSQL服务配置文件
分别为:
/etc/my.cnf
/etc/my.cnf.master
/etc/my.cnf.slave
其中,原主库服务器( IP地址:10.10.10.51)配置文件内容如下:
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=cjxylog
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf.master
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=cjxylog
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf.slave
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
master-host=10.10.10.52
master-user=slave
master-password=slave
master-port=3306
master-connect-retry=60
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
原从库服务器( IP地址:10.10.10.52)配置文件内容如下:
/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
master-host=10.10.10.51
master-user=slave
master-password=slave
master-port=3306
master-connect-retry=60
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf.master
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=swaplog
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf.slave
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
master-host=10.10.10.51
master-user=slave
master-password=slave
master-port=3306
master-connect-retry=60
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
服务器主从自动切换,当数据库服务器有一台离线,另外一台可以上线开始工作,实现高可用。
首先在原主库服务器( IP地址:10.10.10.51)上写入脚本,当主库服务器与WEB服务器失去联系后自动变成从属服务器(如果此服务器没有宕机)
脚本内容如下 :
#/bin/bash
#原 MYSQL 主服务器使用的脚本
check=`cat /etc/my.cnf | grep server-id`
if [ "$check" = "server-id=1" ]
then
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
if [ "$link" != "10.10.10.100:" ]
then
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
if [ "$link" = "10.10.10.52:" ]
then
Ifdown eth0:200
cat /etc/my.cnf.slave > /etc/my.cnf
service mysqld restart
fi
else
fi
else
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.52 | grep "from" | awk '{print $4}'`
if [ "$link" != "10.10.10.52:" ]
then
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
if [ "$link" = "10.10.10.100:" ]
then
ifconfig eth0:200 10.10.10.200 netmask 255.255.255.0
cat /etc/my.cnf.master > /etc/my.cnf
service mysqld restart
fi
else
fi
fi
然后在从服务器( IP地址:10.10.10.52)写入脚本,当从库与主库失去联系后,从库自动变成主库
脚本内容如下:
#/bin/bash
#原 MYSQL从服务器使用的脚本
check=`cat /etc/my.cnf | grep server-id`
if [ "$check" != "server-id=1" ]
then
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
if [ "$link" != "10.10.10.51:" ]
then
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
if [ "$link" = "10.10.10.100:" ]
ifconfig eth0:200 10.10.10.200 netmask 255.255.255.0
cat /etc/my.cnf.master > /etc/my.cnf
service mysqld restart
fi
else
fi
else
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.100 | grep "from" | awk '{print $4}'`
if [ "$link" != "10.10.10.100:" ]
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
link=`ping -c 1 10.10.10.51 | grep "from" | awk '{print $4}'`
if [ "$link" = "10.10.10.51:" ]
then
Ifdown eth0:200
cat /etc/my.cnf.slave > /etc/my.cnf
service mysqld restart
fi
else
fi
fi
将以上脚本分别命名并放置在 /opt/mysqlchange.sh
并且赋予执行权限 chmod a+x /opt/mysqlchange.sh
然后启动循环任务计划,每一分钟执行一次该脚本
Crontab –e
*/1 * * * * bash /opt/mysqlchange.sh
注:
一些错误信息的处理,主从服务器上的命令,及状态信息。
在从服务器上使用 show slave status\G
Slave_IO_Running, 为 No,
则说明 IO_THREAD没有启动,请执行start slave io_thread
Slave_SQL_Running为 No
1.首先停掉 Slave服务:slave stop
2.到主服务器上查看主机状态:
记录 File和Position对应的值。
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
3.到 slave服务器上执行手动同步:
mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3307,
> master_log_file='mysql-bin.000020',
> master_log_pos=135617781;
1 row in set (0.00 sec)
> slave start;
1 row in set (0.00 sec)
再次查看 slave状态发现:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..
Seconds_Behind_Master: 0