1 #查看是否有已安装的mysql，如果有卸载
2 rpm -qa|grep -mysql
3 
4 # 通常系统自带mysql-libs，将其卸载
5 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 

1 # 查看随机初始密码
2 cat /root/.mysql_secret
3 service mysql start
4 # 初始化mysql
5 /usr/bin/mysql_secure_installation

 1 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
 2 SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 3 
 4 In order to log into MySQL to secure it, we'll need the current
 5 password for the root user.  If you've just installed MySQL, and
 6 you haven't set the root password yet, the password will be blank,
 7 so you should just press enter here.
 8 
 9 Enter current password for root (enter for none):
10 OK, successfully used password, moving on...
11 
12 Setting the root password ensures that nobody can log into the MySQL
13 root user without the proper authorisation.
14 
15 You already have a root password set, so you can safely answer 'n'.
16 
17 Change the root password? [Y/n] Y
18 New password:
19 Re-enter new password:
20 Password updated successfully!
21 Reloading privilege tables..
22 ... Success!
23 
24 By default, a MySQL installation has an anonymous user, allowing anyone
25 to log into MySQL without having to have a user account created for
26 them.  This is intended only for testing, and to make the installation
27 go a bit smoother.  You should remove them before moving into a
28 production environment.
29 
30 Remove anonymous users? [Y/n] Y
31 ... Success!
32 
33 Normally, root should only be allowed to connect from 'localhost'.  This
34 ensures that someone cannot guess at the root password from the network.
35 
36 Disallow root login remotely? [Y/n] Y
37 ... Success!
38 
39 By default, MySQL comes with a database named 'test' that anyone can
40 access.  This is also intended only for testing, and should be removed
41 before moving into a production environment.
42 
43 Remove test database and access to it? [Y/n] Y
44 - Dropping test database...
45 ... Success!
46 - Removing privileges on test database...
47 ... Success!
48 
49 Reloading the privilege tables will ensure that all changes made so far
50 will take effect immediately.
51 
52 Reload privilege tables now? [Y/n] Y
53 ... Success!
54 
55 All done!  If you've completed all of the above steps, your MySQL
56 installation should now be secure.
57 
58 Thanks for using MySQL!
59 
60 
61 Cleaning up...

1 cp /usr/share/mysql/my-default.cnf /etc/my.cnf
2 vim /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=1
log-bin=mysqlbin-log
symbolic-links=0
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 

[mysqld]
#修改此目录需要把源目录（/var/lib/mysql/）下的文件拷贝到此，并授权为mysql:mysql
datadir=/data/mysql 
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
log-bin=mysqlbin-log
symbolic-links=0
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

# /etc/init.d/mysql restart

 1 mysql>grant replication slave on *.* to slave@192.168.10.95 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

 1 mysql>grant replication slave on *.* to slave@192.168.10.94 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

mysql> STOP SLAVE;
mysql > SET GLOBAL  SQL_SLAVE_SKIP_COUNTER=1;
#跳过一个事务，可根据情况设置跳过多个错误。
mysql > SHOW GLOBAL VARIABLES LIKE 'SQL_SLAVE_SKIP_COUNTER';  +------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| sql_slave_skip_counter | 1     |
+------------------------+-------+
mysql > START SLAVE;

1 mysql> create database world;
2 mysql> use world;
3 mysql> create table t1 ( id int );
4 mysql> insert into t1 values (1),(2),(3);

mysql> use world;
mysql> select * from t1;
+------+
| id  |
+------+
|  1|
|  2 |
|  3 |
+------+
3 rows in set (0.00 sec)
再插入一行数据
mysql> insert into t1 values (4);

yum install keepalived-1.2.13-5.el6_6.x86_64.rpm

1 cd /etc/keepalived
2 mv keepalived.conf keepalived.conf.bak
3 vim keepalived.conf

! Configuration File for keepalived
 
global_defs {
   notification_email {
     root@lkgBlogs.com
   }
   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
        }
    }
 
}

1 #!/bin/bash
2 pkill keepalived

1 chmod +x mysql.sh
2 # node1和node2均启动keepalived
3 /etc/init.d/keepalived start

1 mysql> show variables like 'hostname%';