产品狗的小人书 2017-12-25
node1(mysql1) | 192.168.10.94 |
node2(mysql2) | 192.168.10.95 |
vip | 192.168.10.222 |
数据库 | mysql-5.6.26 |
#查看是否有已安装的mysql,如果有卸载 rpm -qa|grep -mysql # 通常系统自带mysql-libs,将其卸载 yum remove mysql-libs
1 tar -xvf MySQL-5.6.35-1.el6.x86_64.rpm-bundle.tar 2 3 yum localinstall MySQL-client-5.6.35-1.el6.x86_64.rpm MySQL-server-5.6.35-1.el6.x86_64.rpm MySQL-devel-5.6.35-1.el6.x86_64.rpm
进行初始化脚本之前需要先启动mysql服务和复制mysql的随机密码。在初始化总第一步将使用随机密码。
# 查看随机初始密码 cat /root/.mysql_secret service mysql start # 初始化mysql /usr/bin/mysql_secure_installation
初始化:
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] Y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...
yum安装找不到/etc/my.cnf,先将配置文件拷贝到/etc下
cp /usr/share/mysql/my-default.cnf /etc/my.cnf vim /etc/my.cnf
分别修改node1和node2上的/etc/my.cnf
[mysqld] #生产环境要把落盘目录放到挂载盘中 #修改此目录需要把源目录(/var/lib/mysql/)下的文件拷贝到此,并授权为mysql:mysql #chown -R mysql:mysql mysql/ datadir=/data/mysql socket=/var/lib/mysql/mysql.sock user=mysql #主要两个配置文件区别 server-id= log-bin=mysqlbin-log symbolic-links= default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #需要授权 [client] default-character-set=utf8
node2:只需要修改server-id
[mysqld] #修改此目录需要把源目录(/var/lib/mysql/)下的文件拷贝到此,并授权为mysql:mysql datadir=/data/mysql socket=/var/lib/mysql/mysql.sock user=mysql server-id= log-bin=mysqlbin-log symbolic-links= default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #需要授权 [client] default-character-set=utf8
node1和node2重启mysql
# /etc/init.d/mysql restart
node1设置:
1 mysql>grant replication slave on *.* to [email protected] identified by '123456'; 2 mysql> flush privileges; 3 4 mysql> change master to master_host='192.168.10.95', master_user='slave', master_password= ‘123456’; 5 6 #启动并查看状态 7 mysql> start slave; 8 Query OK, 0 rows affected (0.01 sec) 9 10 mysql> show slave status\G;
显示结果:
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.95 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql2-bin.000003 Read_Master_Log_Pos: 408 Relay_Log_File: mysql1-relay-bin.000004 Relay_Log_Pos: 572 Relay_Master_Log_File: mysql2-bin.000003 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: 408 Relay_Log_Space: 910 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: 1343e450-b350-11e5-9ecb-005056b721a6 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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) ERROR: No query specified
node2登录mysql
1 mysql>grant replication slave on *.* to [email protected] identified by '123456'; 2 mysql> flush privileges; 3 4 mysql> change master to master_host='192.168.10.94', master_user='slave', master_password= ‘123456’; 5 6 #启动并查看状态 7 mysql> start slave; 8 Query OK, 0 rows affected (0.01 sec) 9 10 mysql> show slave status\G;
显示结果:
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.94 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql1-bin.000004 Read_Master_Log_Pos: 408 Relay_Log_File: mysql2-relay-bin.000005 Relay_Log_Pos: 572 Relay_Master_Log_File: mysql1-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: 408 Relay_Log_Space: 910 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: 2b56309a-b350-11e5-9ecb-005056b77a1b Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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) ERROR: No query specified
出现“Slave_IO_Running: Yes”和“Slave_SQL_Running: Yes”说明成功
如MySQL在主从复制的时候经常遇到错误而导致Slave复制中断,这个时候就需要人工干涉,来跳过这个错误,才能使Slave端的复制,得以继续进行;
跳过错误的方法:
mysql> STOP SLAVE; mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER=; #跳过一个事务,可根据情况设置跳过多个错误。 mysql > SHOW GLOBAL VARIABLES LIKE 'SQL_SLAVE_SKIP_COUNTER'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | sql_slave_skip_counter | | +------------------------+-------+ mysql > START SLAVE;
在node1进入mysql:
mysql> create database world; mysql> use world; mysql> create table t1 ( id int ); mysql> insert into t1 values (),(),();
在node2可以看到已经建立的word数据库和数据库中t1表
mysql> use world; mysql> select * from t1; +------+ | id | +------+ | | | | | | +------+ rows in set (0.00 sec) 再插入一行数据 mysql> insert into t1 values ();
在node1可以看到新插入的一条数据
yum install keepalived-1.2.-.el6_6.x86_64.rpm
在每个节点上备份keepalived配置文件,并设置。
cd /etc/keepalived mv keepalived.conf keepalived.conf.bak vim keepalived.conf
Node1设置配置文件:
! Configuration File for keepalived global_defs { notification_email { [email protected] } notification_email_from keepalived@localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL } vrrp_instance HA_1 { state BACKUP #master和slave都配置为BACKUP interface eth0 #指定HA检测的网络接口 virtual_router_id 80 #虚拟路由标识,主备相同 priority 100 #定义优先级,slave设置90 advert_int 1 #设定master和slave之间同步检查的时间间隔 nopreempt #不抢占模式。只在优先级高的机器上设置即可 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { #设置虚拟IP,可以设置多个,每行一个 192.168.10.222 #MySQL对外服务的IP,即VIP } } virtual_server 192.168.10.222 3306 { delay_loop 2 #每隔2秒查询real server状态 lb_algo wrr #lvs 算法 lb_kinf DR #LVS模式(Direct Route) persistence_timeout 50 protocol TCP real_server 192.168.10.94 3306 { #监听本机的IP weight 1 notify_down /etc/keepalived /mysql.sh TCP_CHECK { connect_timeout 10 #10秒无响应超时 bingto 192.168.10.222 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } }
node2服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。
注意:
虚拟路由标识(virtual_router_id),主备相同。如果集群中有多个主从或者主主mysql,则需要将两个集群的virtual_router_id设置成不同的值。
node1和node2上创建/etc/keepalived/mysql.sh:# vim /etc/keepalived/mysql.sh
#!/bin/bash pkill keepalived
node1和node2上均执行
chmod +x mysql.sh # node1和node2均启动keepalived /etc/init.d/keepalived start
1.通过mysql客户端登录通过VIP11.11.168.222登录MySQL,查看MySQL连接状态
mysql> show variables like 'hostname%';
当杀死一个mysql后,自动转移到另一个mysql上。
2.ip addr