mysql主从配置&&基于keepalived的主备切换

色婆ijqwj

　　mysql互为主从设置 && 主备切换配置
　　需求说明：
　　1.公司架构一直是一台单独的mysql在线上跑，虽然一直没有出现什么宕机事件，但是出于一个高可用的考虑，提出主从备份、主备切换的需求；
　　2.实现这个需求的前一段时间只是在做数据库备份的时候实现了主从热备，为了实现主备切换功能，继续操作上述需求；
　　实验环境：
　　master1:10.1.156.3:3306
　　master2:10.1.156.5:3306
　　my.cnf配置文件关于主从这块的修改：
　　master1:
　　server-id = 1 #主备id值一定要不同，而且sql语句不能写成死循环，防止主备设置陷入
　　log-bin=mysqlmaster-bin
　　#binlog-do-db = cad #指定需要进行主从的数据库
　　binlog_cache_size = 4M
　　binlog_format=mixed
　　expire_logs_days=3
　　binlog-ignore-db=test #不记录test库的binlog
　　replicate-ignore-db=test #不复制test库的binlog
　　sync_binlog=1
　　sql_mod=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
　　lower_case_table_names=1
　　master2:
　　server-id = 2
　　log-bin=mysqlslave-bin
　　#binlog-do-db = cad #指定需要进行主从的数据库
　　binlog_cache_size = 4M
　　binlog_format=mixed
　　expire_logs_days=3
　　binlog-ignore-db=test #不记录test库的binlog
　　replicate-ignore-db=test #不复制test库的binlog
　　sync_binlog=1
　　sql_mod=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
　　lower_case_table_names=1
　　主从配置：
　　1.实现从master1-->master2的单向主从配置：
　　master1：1)创建同步的账号：（ip地址从mysql：master2所属IP）

　　mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.1.156.5'>　　Query OK, 0 rows affected (0.13 sec)
　　mysql> flush privileges;
　　2)查看master1的master状态，获取二进制日志名和偏移量
　　mysql> show master status\G
　　*************************** 1. row ***************************
　　File: mysqlmaster-bin.000004
　　Position: 396
　　Binlog_Do_DB:
　　Binlog_Ignore_DB:
　　Executed_Gtid_Set:
　　1 row in set (0.00 sec)
　　（备注：show master statusG; 报错ERROR: No query specified 属于语法错误）
　　3)主mysql(master1)停止数据库操作，对数据库进行导出操作，然后导入到从mysql(master2)
　　#首先添加一个读锁保证数据库的一致性
　　mysql> flush tables with read lock;
　　mysql> quit;
　　mysqldump -h127.0.0.1 -uroot -p -P3306 --all-databases --triggers --routines --events > /opt/all.sql
　　#最后恢复Master机器的读锁
　　mysql> unlock tables;
　　master2: 1)从mysql(master2)将备份数据导入数据库：
　　mysql -h 10.1.156.3 -uroot -p -P3306 < /opt/all.sql
　　2)master2数据库设置同步数值参数：
　　mysql> change master to master_host='10.1.156.3',master_user='slave',master_password='test2015',master_log_file='mysqlmaster-bin.000004',master_log_pos=396;
　　3)启动从数据库复制线程，并查询从数据库的复制线程状态
　　mysql> start slave;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 10.124.165.254
　　Master_User: slave
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysqlmaster-bin.000004
　　Read_Master_Log_Pos: 1021

　　>　　Relay_Log_Pos: 914
　　Relay_Master_Log_File: mysqlmaster-bin.000004
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 1021
　　Relay_Log_Space: 1259
　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: 0
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 1
　　Master_UUID: 709e2dcf-0b51-11e5-9754-286ed489d502
　　Master_Info_File: /home/mysql/data/master.info
　　SQL_Delay: 0
　　SQL_Remaining_Delay: NULL

　　Slave_SQL_Running_State: Slave has read all>　　Master_Retry_Count: 86400
　　Master_Bind:
　　Last_IO_Error_Timestamp:
　　Last_SQL_Error_Timestamp:
　　Master_SSL_Crl:
　　Master_SSL_Crlpath:
　　Retrieved_Gtid_Set:
　　Executed_Gtid_Set:
　　Auto_Position: 0
　　1 row in set (0.00 sec)
　　查询结果中：Slave_IO_Running: Yes 同时出现才证明主从设置成功
　　Slave_SQL_Running: Yes
　　2.配置master2-->master1的主从实现双向主从设置：
　　master2: 1)创建同步的账号：（ip地址从mysql：master1所属IP）

　　mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.1.156.3'>　　Query OK, 0 rows affected (0.13 sec)
　　mysql> flush privileges;
　　2)查看master2的master状态，获取二进制日志名和偏移量
　　mysql> show master status\G
　　*************************** 1. row ***************************
　　File: mysqlslave-bin.000001
　　Position: 120
　　Binlog_Do_DB:
　　Binlog_Ignore_DB:
　　Executed_Gtid_Set:
　　1 row in set (0.00 sec)
　　master1: 1)master1数据库设置同步数值参数：
　　mysql> change master to master_host='10.1.156.5',master_user='slave',master_password='test2015',master_log_file='mysqlslave-bin.000001',master_log_pos=120;
　　2)启动从数据库复制线程，并查询从数据库的复制线程状态
　　mysql> start slave;
　　Query OK, 0 rows affected (0.01 sec)
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 10.1.156.3
　　Master_User: slave
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysqlslave-bin.000001
　　Read_Master_Log_Pos: 199

　　>　　Relay_Log_Pos: 367
　　Relay_Master_Log_File: mysqlslave-bin.000001
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 199
　　Relay_Log_Space: 540
　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: 0
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 2
　　Master_UUID: 6f7cf3a2-8d08-11e5-a52d-5254008c0566
　　Master_Info_File: /home/mysql/data/master.info
　　SQL_Delay: 0
　　SQL_Remaining_Delay: NULL

　　Slave_SQL_Running_State: Slave has read all>　　Master_Retry_Count: 86400
　　Master_Bind:
　　Last_IO_Error_Timestamp:
　　Last_SQL_Error_Timestamp:
　　Master_SSL_Crl:
　　Master_SSL_Crlpath:
　　Retrieved_Gtid_Set:
　　Executed_Gtid_Set:
　　Auto_Position: 0
　　1 row in set (0.00 sec)
　　3.注意：
　　1）
　　Slave_IO_Running: NO
　　Slave_SQL_Running: Yes
　　mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。
　　出现上面所述情况，可能有三种情况：1)网络有问题，连接不上；2)配置my.cnf可能有问题；3)授权的问题，replication slave和file权限是必须的。
　　解决Slave_IO_Running: NO
　　mysql>stop slave;
　　mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
　　mysql>stop slave;
　　2）
　　Slave_IO_Running: connecting
　　Slave_SQL_Running: Yes
　　导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面：
　　1)、网络不通
　　2)、密码不对
　　3)、pos不对
　　可以参考这个连接：http://blog.csdn.net/i_bruce/article/details/17055135
　　我这里出现的原因是在my.cnf文件中没有使用这个参数：
　　[mysqld]
　　skip-name-resolve
　　所以导致数据库远程连接过慢，一直处于一个正在连接的状态。
　　参考这个连接：解决远程连接mysql很慢的方法(mysql_connect 打开连接慢) http://www.iyunv.net/article/27616.htm
　　主备切换配置：(基于keepalived的vip漂移功能实现)
　　master1:
　　1)keepalived编译安装:
　　wget -P /usr/local/src http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
　　tar xf /usr/local/src/keepalived-1.2.19.tar.gz -C /usr/local/src
　　cd /usr/local/src/keepalived-1.2.19 && ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-279.el6.x86_64&& make && make install
　　2)将keepalived做成服务：
　　cd /usr/local/keepalived/ && cp etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ && cp etc/sysconfig/keepalived /etc/sysconfig/ && mkdir /etc/keepalived && cp etc/keepalived/keepalived.conf /etc/keepalived/ && cp sbin/keepalived /usr/sbin
　　3)keepalived配置文件
　　vim /etc/keepalived/keepalived.conf
　　=====================================================
　　! Configuration File for keepalived
　　global_defs {
　　notification_email {  //定义邮件服务的
　　root@localhost  //定义收件人，这里改为本机，只是测试使用
　　}
　　notification_email_from kaadmin@localhost  //定义发件人，
　　smtp_server 127.0.0.1  //定义邮件服务器，一定不能使用外部地址
　　smtp_connect_timeout 30 //超时时间
　　router_id LVS_DOWNLOAD
　　}
　　vrrp_instance VI_1 {  //定义虚拟路由，VI_1 为虚拟路由的标示符，自己定义名称
　　state MASTER  //开启后，该节点的优先级比另一节点的优先级高，所以转化为MASTER状态
　　interface eth0  //所有的通告等信息都从eth0这个接口出去
　　virtual_router_id 7  //虚拟路由的ID，而且这个ID也是虚拟MAC最后一段的来源，这个ID号一般不能大于255，且这个ID一定不能有冲突
　　priority 100  //初始优先级
　　advert_int 1  //通告的个数
　　authentication {  //认证机制
　　auth_type yzkj  //认证类型
　　auth_pass yzkj1234   //密码，应该为随机的字符串
　　}
　　virtual_ipaddress {  //虚拟地址，即VIP
　　10.1.156.2
　　}
　　}
　　master2:
　　1)keepalived编译安装:
　　wget -P /usr/local/src http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
　　tar xf /usr/local/src/keepalived-1.2.19.tar.gz -C /usr/local/src
　　cd /usr/local/src/keepalived-1.2.19 && ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-279.el6.x86_64 && make && make install
　　2)将keepalived做成服务：
　　cd /usr/local/keepalived/ && cp etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ && cp etc/sysconfig/keepalived /etc/sysconfig/ && mkdir /etc/keepalived && cp etc/keepalived/keepalived.conf /etc/keepalived/ && cp sbin/keepalived /usr/sbin
　　3)keepalived配置文件
　　vim /etc/keepalived/keepalived.conf
　　=====================================================
　　! Configuration File for keepalived
　　global_defs {
　　notification_email {  //定义邮件服务的
　　root@localhost  //定义收件人，这里改为本机，只是测试使用
　　}
　　notification_email_from kaadmin@localhost  //定义发件人，
　　smtp_server 127.0.0.1  //定义邮件服务器，一定不能使用外部地址
　　smtp_connect_timeout 30 //超时时间
　　router_id LVS_DOWNLOAD
　　}
　　vrrp_instance VI_1 {  //定义虚拟路由，VI_1 为虚拟路由的标示符，自己定义名称
　　state   //开启后，该节点的优先级比另一节点的优先级高，所以转化为MASTER状态
　　interface eth0  //所有的通告等信息都从eth0这个接口出去
　　virtual_router_id 7  //虚拟路由的ID，而且这个ID也是虚拟MAC最后一段的来源，这个ID号一般不能大于255，且这个ID一定不能有冲突
　　priority 99  //初始优先级
　　advert_int 1  //通告的个数
　　authentication {  //认证机制
　　auth_type yzkj  //认证类型
　　auth_pass yzkj1234   //密码，应该为随机的字符串
　　}
　　virtual_ipaddress {  //虚拟地址，即VIP
　　10.1.156.2
　　}
　　}
　　注意：使用keepalived设置主备会出现一定的问题，参考链接：http://my.oschina.net/moooofly/blog/223822
　　另外，互为主从的设置，主1一旦出现问题，主2是不是也会有相应的问题出现呢