一:实验环境
操作系统:CentOS release 6.5 (Final)
mysql双主复制+keepalive实现了高可用:
master1:10.192.203.201
master2:10.192.203.202
vip 10.192.203.203
虚拟ip当前在master1上。程序写入的是vip。
二:实验步骤
2.1 升级master2
2.1.1 备份master2
show databases;查看下都有哪些库
mysqldump --single-transaction -u root -p-A > /download/bak/all.bak_20161125
检查备份文件,确保无误。
2.1.2 记录binlog位置
#在master1上加个读锁, 记录master1此刻写入的 File和Position:
flush tables with read lock; #锁定master1,不允许写入
show master status ;
#停止master2的slave:
stop slave;
show slave status \G; #记录读取和执行的binlog位置
#在master1上unlock tables;
之所以进行如上步骤,是为了升级完成master2,导入备份的数据后,知道应该从哪个位置开始复制,从而和master1保持一致。
2.1.3 卸载master2
卸载脚本内容如:
- mysqldir='/data/mysql'
- basedir='/usr/local/mysql'
- cnf=/etc/my.cnf
- password='123456'
-
- #1:关闭数据库
- mysqladmin -u root -p$password shutdown
- killall -u mysql
-
- #2:删除用户和组
- userdel mysql
- groupdel mysql
-
- #3:删除目录
- rm -rf $mysqldir
- rm -rf $basedir
-
- #4:取消开机自动启动
- rm -rf /etc/rc.d/init.d/mysql
- chkconfig --del mysql
-
- #5:删除 PATH
- #root用户
- sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /root/.bash_profile
- source /root/.bash_profile
- #mysql用户
- sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /home/mysql/.bash_profile
- source /home/mysql/.bash_profile
-
- #6:取消防火墙端口
- #修改文件/etc/sysconfig/iptables
- #删除-A INPUT -m state--state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
- sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 3306 -j ACCEPT/d' /etc/sysconfig/iptables
-
- service iptables restart
2.1.4 安装5.6.33版本mysql
2.1.4.1 安装脚本内容
- #记得先将mysql安装包上传至$dir目录下
- #配置文件my.cnf上传master2-sample-my.cnf,并根据实际情况修改下innodb_buffer_pool_size取值,记得根据实际的安装目录修改log-bin,relay-log,relay-log-index,log-bin取值
- #注意,mysqldir假如不是/data/mysql,要改成其他目录(如/database/mysql)的话,需要修改该脚本中这一行sed -i'47s/datadir=/datadir=\/database\/mysql/g' /etc/rc.d/init.d/mysqld
-
- #定义目录
- basedir='/usr/local/mysql'
-
- mysqldir='/data/mysql'
-
- datadir=$mysqldir
- #binlogdir=$mysqldir/binlog
-
- cnf=/etc/my.cnf
-
- dir='/download/'
-
- socket='/tmp/mysql.sock'
-
- port='3306'
-
- filename='mysql-5.6.33-linux-glibc2.5-x86_64'
-
- password='123456'
-
- password2=\'$password\'
-
- date=`date "+%y%m%d"`
-
- #修改配置文件
- mv /etc/my.cnf /etc/my.cnf_bak_$date
- mv $dir'master2-sample-my.cnf' /etc/my.cnf
-
- #建用户
- groupadd mysql
- useradd -g mysql mysql
-
- #安装依赖包
- yum install libaio -y
-
- #解压
- cd $dir
- if ( test -s $filename )
- then
- echo '已经解压过,无需重复解压'
- else
- tar -xvf $filename.tar.gz
- fi
-
- #拷贝解压后的mysql目录到系统的本地软件目录:
- cp $filename $basedir -r
-
- #新建目录
- #mkdir -p $datadir
- #mkdir -p $binlogdir
- mkdir -p $mysqldir
- chown -R mysql:mysql $basedir
- chown -R mysql:mysql $mysqldir
-
- #建立基本库
- $basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir
- #设置开机自动启动
- cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'
- #修改该文件datadir
- #已经知道datadir=在第47行
- sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld
-
- chmod +x /etc/rc.d/init.d/mysqld
-
- chkconfig --add mysqld
-
- chkconfig mysqld on
-
- #配置PATH
- #root用户
- sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile
- source /root/.bash_profile
-
- #mysql用户
- sed -i '/^PATH=/s/$/:\/usr\/local\/mysql\/bin/'/home/mysql/.bash_profile
- source /home/mysql/.bash_profile
- #启动数据库
-
- service mysqld start
-
- #开放防火墙端口
- #开放3306端口,插入到这一行-AINPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT后面
- sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables
-
- service iptables restart
- #公司的防火墙是关闭的,所以这里就不开放端口了。
-
- #修改mysql root 密码
- #根据执行该脚本最后输出的信息('建立基本库'中的root密码)来修改,改成$password,需要交互执行
- #mysql 5.6.33默认密码为空
- mysqladmin -u root password $password
-
-
- #待新建一个用于远程访问的用户,密码和本地用户一致。
- $basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by $password2"
2.1.4.2 配置文件master2-sample-my.cnf内容
由于是从5.5版本升级至5.6版本,为了避免低版本从库复制高版本主库时发生错误:
- Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log;
需要确保在配置文件中添加参数:binlog_checksum=none
master2-sample-my.cnf文件内容:
- [client]
- port =3306
- socket =/tmp/mysql.sock
- [mysqld]
- port =3306
- socket =/tmp/mysql.sock
- binlog_checksum=none
- innodb_buffer_pool_size = 请设置成合适的值G
- server-id = 2
- max_connections = 5000
- max_connect_errors = 300
- max_allowed_packet = 64M
- binlog_format=row
- slow_query_log
- long_query_time = 2
- sync_binlog=1
- innodb_support_xa=1
- innodb_file_per_table=1
- replicate-ignore-db = mysql
- replicate-ignore-db = information_schema
- relay-log=/data/mysql/master2-relay-bin
- relay-log-index=/data/mysql/master2-relay-bin.index
- log-bin=/data/mysql/master2-mysql-bin
- log_slave_updates
- [mysqldump]
- # Do not buffer the whole result set inmemory before writing it to
- # file. Required for dumping very largetables
- quick
-
- max_allowed_packet = 64M
-
- [mysql]
- no-auto-rehash
-
- # Only allow UPDATEs and DELETEs that usekeys.
- #safe-updates
-
- [myisamchk]
- key_buffer_size = 512M
- sort_buffer_size = 512M
- read_buffer = 8M
- write_buffer = 8M
-
- [mysqlhotcopy]
- interactive-timeout
-
- [mysqld_safe]
- open-files-limit = 8192
2.1.5 恢复数据
导入备份的数据
mysql -u root -p < all.bak_20161125
2.1.6 制造测试数据
在master1上插入几条测试数据
- mysql> use sds;
- Database changed
- mysql> show tables;
- Empty set (0.00 sec)
-
- mysql> create table t(id int);
- Query OK, 0 rows affected (0.06 sec)
-
- mysql> insert into t(id)values(1),(2),(3);
- Query OK, 3 rows affected (0.02 sec)
- Records: 3 Duplicates: 0 Warnings: 0
-
- mysql> select * from t;
- +
- | id |
- +
- | 1 |
- | 2 |
- | 3 |
- +
- 3 rows in set (0.02 sec)
2.1.7 将master2指向master1
master_log_file ,master_log_pos需要和2.1.2步骤中showmaster status ;的结果保持一致:
- change master tomaster_log_file='mysql-bin.000003',master_log_pos=1109,master_host='10.192.203.201',master_user='RepUser',master_password='beijing',master_port=3306;
- mysql> start slave;
- Query OK, 0 rows affected (0.03 sec)
-
- show slave status \G;检查下Slave_IO_Running,Slave_SQL_Running是否都为Yes。
-
- #验证下是否将差异数据同步了过来
- mysql> use sds;
- Database changed
- mysql> show tables;
- +
- | Tables_in_sds |
- +
- | t |
- +
- 1 row in set (0.00 sec)
-
- mysql> select * from t;
- +
- | id |
- +
- | 1 |
- | 2 |
- | 3 |
- +
- 3 rows in set (0.01 sec)
说明master2同步成功。
2.2 升级master1
2.2.1 漂移vip到master2机器
由于我在自己机器上配置了监控mysql定时任务(每分钟监控一次mysql状态,如果mysql宕机,将该机器上的heartbeat进程杀掉),因此升级master2,肯定会导致heartbeat被关闭。因此需要先启动master2的heartbeat进程:
service heartbeat start
#关闭master1进程:
service heartbeat stop
#在maser2上使用ip addr命令验证虚拟IP是否漂移成功。
- [root@slave2 download]# ip addr
- 1: lo: <LOOPBACK,UP,LOWER_UP> mtu16436 qdisc noqueue state UNKNOWN
- link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
- inet 127.0.0.1/8 scope host lo
- inet6 ::1/128 scope host
- valid_lft forever preferred_lft forever
- 2: eth0:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000
- link/ether 08:00:27:04:05:16 brd ff:ff:ff:ff:ff:ff
- inet 10.192.203.202/24 brd 10.192.203.255 scope global eth0
- inet 10.192.203.203/24 brd 10.192.203.255 scope global secondary eth0
- inet6 fe80::a00:27ff:fe04:516/64 scope link
- valid_lft forever preferred_lft forever
- 3: eth1:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000
- link/ether 08:00:27:3a:ec:3c brd ff:ff:ff:ff:ff:ff
- inet 10.0.0.2/24 brd 10.0.0.255 scope global eth1
- inet6 fe80::a00:27ff:fe3a:ec3c/64 scope link
- valid_lft forever preferred_lft forever
看到了vip:10.192.203.203,说明漂移成功。
2.2.2 备份master1
略
2.2.3 记录binlog位置
逻辑步骤请参考2.1.2。
2.2.4 卸载master1
卸载脚本内容同“2.1.3 卸载master2”一样。
2.2.5 安装5.6.33版本mysql
2.2.5.1 安装脚本内容
注意:和安装master2脚本只有一个地方不同:
mv $dir'master2-sample-my.cnf' /etc/my.cnf
mv $dir'master1-sample-my.cnf' /etc/my.cnf
--以下是安装脚本内容:
- #记得先将mysql安装包上传至$dir目录下
- #配置文件my.cnf上传master1-sample-my.cnf,并根据实际情况修改下innodb_buffer_pool_size取值,记得根据实际的安装目录修改log-bin,relay-log,relay-log-index,log-bin取值
- #注意,mysqldir假如不是/data/mysql,要改成其他目录(如/database/mysql)的话,需要修改该脚本中这一行sed -i'47s/datadir=/datadir=\/database\/mysql/g' /etc/rc.d/init.d/mysqld
-
- #定义目录
- basedir='/usr/local/mysql'
-
- mysqldir='/data/mysql'
-
- datadir=$mysqldir
- #binlogdir=$mysqldir/binlog
-
- cnf=/etc/my.cnf
-
- dir='/download/'
-
- socket='/tmp/mysql.sock'
-
- port='3306'
-
- filename='mysql-5.6.33-linux-glibc2.5-x86_64'
-
- password='123456'
-
- password2=\'$password\'
-
- date=`date "+%y%m%d"`
-
- #修改配置文件
- mv /etc/my.cnf /etc/my.cnf_bak_$date
- mv $dir'master1-sample-my.cnf' /etc/my.cnf
-
- #建用户
- groupadd mysql
- useradd -g mysql mysql
-
- #安装依赖包
- yum install libaio -y
-
- #解压
- cd $dir
- if ( test -s $filename )
- then
- echo '已经解压过,无需重复解压'
- else
- tar -xvf $filename.tar.gz
- fi
-
- #拷贝解压后的mysql目录到系统的本地软件目录:
- cp $filename $basedir -r
-
- #新建目录
- #mkdir -p $datadir
- #mkdir -p $binlogdir
- mkdir -p $mysqldir
- chown -R mysql:mysql $basedir
- chown -R mysql:mysql $mysqldir
-
- #建立基本库
- $basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir
- #设置开机自动启动
- cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'
- #修改该文件datadir
- #已经知道datadir=在第47行
- sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld
-
- chmod +x /etc/rc.d/init.d/mysqld
-
- chkconfig --add mysqld
-
- chkconfig mysqld on
-
- #配置PATH
- #root用户
- sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile
- source /root/.bash_profile
-
- #mysql用户
- sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /home/mysql/.bash_profile
- source /home/mysql/.bash_profile
- #启动数据库
-
- service mysqld start
-
- #开放防火墙端口
- #开放3306端口,插入到这一行-AINPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT后面
- sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables
-
- service iptables restart
- #公司的防火墙是关闭的,所以这里就不开放端口了。
-
- #修改mysql root 密码
- #根据执行该脚本最后输出的信息('建立基本库'中的root密码)来修改,改成$password,需要交互执行
- #mysql 5.6.33默认密码为空
- mysqladmin -u root password $password
-
-
- #待新建一个用于远程访问的用户,密码和本地用户一致。
- $basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by $password2"
2.2.5.2 配置文件master1-sample-my.cnf内容
#master1-sample-my.cnf与master2-sample-my.cnf配置文件内容只有几个地方不同:
server-id ,relay-log,relay-log-index
配置文件内容:
- [client]
- port =3306
- socket =/tmp/mysql.sock
- [mysqld]
- port =3306
- socket =/tmp/mysql.sock
- binlog_checksum=none
- innodb_buffer_pool_size = 请设置成合适的值G
- server-id = 1
- max_connections = 5000
- max_connect_errors = 300
- max_allowed_packet = 64M
- binlog_format=row
- slow_query_log
- long_query_time = 2
- sync_binlog=1
- innodb_support_xa=1
- innodb_file_per_table=1
- replicate-ignore-db = mysql
- replicate-ignore-db = information_schema
- relay-log=/data/mysql/master1-relay-bin
- relay-log-index=/data/mysql/master1-relay-bin.index
- log-bin=/data/mysql/master1-mysql-bin
- log_slave_updates
- [mysqldump]
- # Do not buffer the whole result set inmemory before writing it to
- # file. Required for dumping very largetables
- quick
-
- max_allowed_packet = 64M
-
- [mysql]
- no-auto-rehash
-
- # Only allow UPDATEs and DELETEs that usekeys.
- #safe-updates
-
- [myisamchk]
- key_buffer_size = 512M
- sort_buffer_size = 512M
- read_buffer = 8M
- write_buffer = 8M
-
- [mysqlhotcopy]
- interactive-timeout
-
- [mysqld_safe]
- open-files-limit = 8192
2.2.6 恢复数据
导入备份的数据
mysql -u root -p < all.bak_20161125
2.2.7 制造测试数据
略
2.2.8 将master1指向master2
思路和“2.1.7 将master2指向master1”一样。
- change master tomaster_log_file='master2-mysql-bin.000003' ,master_log_pos=489108,master_host='10.192.203.202',master_user='RepUser',master_password='beijing',master_port=3306;
- mysql> start slave;
- Query OK, 0 rows affected (0.03 sec)
最后检查下master1,master2上的从库是否都正常。
发现master2上的slave报错:
Last_IO_Error: error connecting to master'RepUser@10.192.203.201:3306' - retry-time: 60 retries: 33
我手动在master1上重建了下复制用户,然后stop slave;start slave;没有再报这个错误。
也不知道为什么会报这个错误。备份文件里明明也能看到这个复制用户的插入记录呀。
start slave时又报了一个新的错误:
Last_IO_Error: Got fatal error 1236 frommaster when reading data from binary log: 'Could not find first log file namein binary log index file'
在master1上flush logs;
show master status;
在master2上重新指定binlog位置,开始复制即可。
2.3 修改参数binlog_checksum
起初,安装mysql 5.6.33的时候,特意将binlog_checksum设置为none,原因请参考:http://blog.csdn.net/yabingshi_tech/article/details/53319904
现在master1,master2版本都变成了5.6.33了。因此现在需要注释掉配置文件里的binlog_checksum=none,然后重启mysql即可。这里也顺便将vip切换回master1。
master1:
注释掉配置文件里的binlog_checksum=none,然后重启mysql;
启动master1的heartbeat;
master2:
注释掉配置文件里的binlog_checksum=none,然后重启mysql;
重启master2的heartbeat;
在master1上执行ip addr命令验证是否vip漂移成功。
检查该参数是否修改成功:
mysql> show variables like '%checksum%';
+---------------------------+--------+
| Variable_name | Value |
+---------------------------+--------+
| binlog_checksum | CRC32 |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| master_verify_checksum | OFF |
| slave_sql_verify_checksum | ON |
+---------------------------+--------+