Mysql数据库高可用

Mysql数据库高可用Mysql(5.7.19)+keepalive(1.3.0) +RHEL6.5Mysql下载地址https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gzKeepalived地址http://www.keepalived.org/software/keepalived-1.3.0.tar.gz规划如下ip端口备注Mysql1172.25.254.1203306Mysql2172.25.254.1303306keepalived172.25.254.111一:首先搭建主主数据库拷贝压缩包到系统/mnt下cd /mnt/tar xvf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz cp -a mysql-5.7.19-linux-glibc2.12-x86_64 /usr/local/mysqluseradd mysqlcd /usr/local/mysql/cd bin/./mysqld –initialize./mysql_install_db –user=mysql –basedir=/var/lib/mysql –datadir=/var/lib/mysql/datachown -R mysql /var/lib/mysql/data/./mysqld_safe –user=mysqlcd ..cp support-files/mysql.server /etc/init.d/mysqldrm -rf /etc/my.cnfcat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pideofln -s /usr/local/mysql/bin/mysql /bin/mysqlcd bin/service mysqld startsed -i ‘5askip-grant-tables’ /etc/my.cnfsed -i ‘$a[mysql]’ /etc/my.cnf sed -i ‘$asocket=/var/lib/mysql/mysql.sock’ /etc/my.cnfservice mysqld restartmysql -e “update mysql.user set authentication_string=password(‘123456′) where user=’root’ and Host=’localhost’;”sed -i ‘/skip-grant-tables/d’ /etc/my.cnfservice my开发云主机域名sqld restartmysql -uroot -p123456SQL>set password = password(‘123456’);sed -i ‘/[mysqld]$/alog-bin=/var/lib/mysql/log-bin’ /etc/my.cnfsed -i ‘/[mysqld]$/aserver_id=130’ /etc/my.cnf #两个id不一样即可service mysqld restart至此两个数据库搭建完成二：主主搭建mysql -uroot -p123456#登陆数据库grant replication slave on *.* to slaveuser@”172.25.254.120″ identified by “123456”;#130上配置grant replication slave on *.* to slaveuser@”172.25.254.130″ identified by “123456”;#120上配置登陆测试是否成功mysql> show master statusG;#分别查看两个节点****************** 1. row ***************************File: log-bin.000001Position: 154change master to master_host=”172.25.254.120″,master_user=”slaveuser”,master_password=”123456″,master_log_file=”log-bin.000001“,master_log_pos=154;#130上操作change master to master_host=”172.25.254.130″,master_user=”slaveuser”,master_password=”123456″,master_log_file=”log-bin.000001“,master_log_pos=154;#120上操作start slave;#两节点分别开启show slave statusG;Slave_IO_Running: YESSlave_SQL_Running: YES可以自己测试三：keepalived搭建cd /mnt/tar xvf keepalived-1.3.0.tar.gzyum -y install gcc gcc-c++ openssl-develcd keepalived-1.3.0./configuremake && make installmkdir /etc/keepalivedcd /usr/local/etc/cp -a keepalived/keepalived.conf /etc/keepalived/cp sysconfig/keepalived /etc/sysconfig/
cat /etc/keepalived/keepalived.conf ! Configuration File for keepalivedglobal_defs {router_id}vrrp_sync_group VGM{group{HA}}vrrp_script check_mysql {script “/root/check_mysql.sh“#脚本文件interval 5}vrrp_instance HA {state MASTER#从BACKUPinterface eth0virtual_router_id 51priority 100#从99advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {172.25.254.111/24 dev eth0}track_script {check_mysql}}cat /root/check_mysql.sh #脚本文件b=`ps aux | grep mysql | grep sqld | wc -l`if [ $b == 0 ]then service mysqld restartc=`ps aux | grep mysql | grep sqld | wc -l` if [ $c == 0 ] then kill -9 `ps aux | grep keep | grep lived | awk ‘{print $2}’` fifia=`ps aux | grep keep | grep lived |wc -l`if [ $a == 0 ]thensleep 300rebootfi
测试，与开机启动不在次次试验中，可自行测试

相关推荐: SQL获取字段字符串中文首字母

有中文有英文，获取中文首字母信息。SELECT person_name_cn ,ELT(INTERVAL(CONV(HEX开发云主机域名(LEFT(CONVERT(person_name_cn USING gbk),1)),16,10),0xB0A1,0xB0…