1.環境準備：
主機：192@@@1.4,192@@@1.5
操作系統：centos 7.3
mysql數據庫版本：mysql 5.7.13
浮動IP:192@@@1.182
2.mysql 下載及解壓安裝配置
2.1 下載：
#wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz
2.2 解壓安裝

tar -xzvf mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

mv mysql-5.7.13-linux-glibc2.5-x86_64 mysql

#mv mysql /usr/local
2.3 配置
2.3.1 創建數據目錄（/mysqldata/mysql）
#mkdir /data/mysql
2.3.2 新建mysql用戶、組及目錄

—新建一個msyql組

groupadd mysql

useradd -r -s /sbin/nologin -g mysql mysql -d /usr/local/mysql —新建msyql用戶禁止登錄shell

2.3.3 修改目錄屬有者
#cd /usr/local
#chown -R mysql:mysql /usr/local/mysql
#chown -R mysql:mysql /data/mysql
2.3.4 配置參數初始化
#cd /usr/local/mysql
#bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
…
…
[Note] A temporary password is generated for root@localhost:YLi>7ecpe;YP
??注意：執行的輸出內容最后有臨時生成的密碼！
2.3.5 SSL的安裝與配置（/mysqldata/mysql）
#bin/mysql_ssl_rsa_setup --datadir=/data/mysql
執行完后數據目錄下多出了一些以pem結尾的文件，而這些文件就是開啟SSL連接所需要的文件。
2.3.6 修改系統配置文件

cp support-files/my-default.cnf /etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld

編輯：/etc/init.d/mysqld:
basedir=/usr/local/mysql
datadir=/data/mysql

??預設字符集：UTF-8
a.在[mysqld]下添加
　　default-character-set=utf8（mysql 5.5 版本添加character-set-server=utf8）
b.在[client]下添加
　　default-character-set=utf8

2.3.6.1 修改192@@@1.4 主機/etc/my.cnf配置文件 底部有實例
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M#datadir=/data/mysql
#socket=/var/lib/mysql/mysql.sockbasedir=/usr/local/mysql
datadir=/mysqldata/mysql
socket=/mysqldata/mysql/mysql.sock
character-set-server=utf8
max_connections=1000default-storage-engine=INNODB
innodb_large_prefix=on# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#主主復制配置:
auto_increment_offset=1
auto_increment_increment=2
#啟用二進制日志 注意：日志的目錄需要先建立，并將所有者該為mysql
log-bin=mysql-bin
binlog_format=row
log_bin_trust_function_creators=1
##################################################
#注：不加binlog-do-db和binlog_ignore_db，那就表示全部數據庫都記錄二進制日志。
#需要記錄二進制日志的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
binlog-do-db=ambari
binlog-do-db=hive
binlog-do-db=ranger
#不需要記錄二進制日志的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
###################################################
#超過30天的binlog刪除
expire_logs_days=30
#服務器唯一ID，默認是1，一般取IP最后一段
server-id=4
#配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數，以便從庫的從庫能夠進行日志同步
log-slave-updates
sync_binlog=1
#中繼日志文件
relay_log = mysql-relay-bin
##################################################
#需要復制的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
replicate-do-db=ambari
replicate-do-db=hive
replicate-do-db=ranger
#不需要復制的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
#replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
##################################################[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/mysqldata/mysql/mysql.sock
default-character-set=utf8

2.3.6.2 修改192@@@1.5 主機/etc/my.cnf配置文件
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M#datadir=/data/mysql
#socket=/var/lib/mysql/mysql.sockbasedir=/usr/local/mysql
datadir=/mysqldata/mysql
socket=/mysqldata/mysql/mysql.sock
character-set-server=utf8
max_connections=1000# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#主主復制配置(offset不同):
auto_increment_offset=2
auto_increment_increment=2
#啟用二進制日志 注意：日志的目錄需要先建立，并將所有者該為mysql
log-bin=mysql-bin
binlog_format=row
log_bin_trust_function_creators=1
################################################
#注：不加binlog-do-db和binlog_ignore_db，那就表示全部數據庫都記錄二進制日志。
#需要記錄二進制日志的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
binlog-do-db=ambari
binlog-do-db=hive
binlog-do-db=ranger
#不需要記錄二進制日志的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
################################################
#超過30天的binlog刪除
expire_logs_days=30
#服務器唯一ID，默認是1，一般取IP最后一段
server-id=5
#配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數，以便從庫的從庫能夠進行日志同步
log-slave-updates
sync_binlog=1
#中繼日志文件
relay_log = mysql-relay-bin
################################################
#需要復制的數據庫名，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
replicate-do-db=ambari
replicate-do-db=hive
replicate-do-db=ranger
#不需要復制的數據庫，如果有多個數據庫，需要重復設置此參數，每個數據庫一行
#replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
################################################[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid[client]
socket=/mysqldata/mysql/mysql.sock
default-character-set=utf8優化參數：

#mysqld
init_connect=‘SET collation_connection = utf8_unicode_ci’
init_connect=‘SET NAMES utf8’
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
#不區分大小寫
lower_case_table_names=1

show global variables like “%check%”;
set global foreign_key_checks=0;

2.3.7 添加系統路徑
# vim /etc/profile
添加：
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile

touch /data/data1/mysqllog/mysqld.log
chown -R mysql:mysql /data/data1/mysqllog
service mysqld start
2.4 啟動mysql
#service mysqld start (ok)
#bin/mysql --user=root –p

mysql -uroot -p

set global validate_password_policy=0;
set global validate_password_length=1;

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘Aa123456789Aa123456789!’;

修改root密碼
mysql>set password=password(‘@@@-2017’);
在5.6后，mysql內置密碼增強機制，低強度密碼會報錯：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

1)降低密碼安全度要求：
mysql>set global validate_password_policy=0;
mysql>set global validate_password_length=1;

2)增加root遠程登錄(兩個機器都要執行！)
mysql>CREATE USER ‘root’@‘%’ IDENTIFIED BY ‘@@@-2017’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ WITH GRANT OPTION;
mysql>CREATE USER ‘root’@‘nn01’ IDENTIFIED BY ‘@@@-2017’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@‘nn01’ WITH GRANT OPTION;
mysql>CREATE USER ‘root’@‘nn02’ IDENTIFIED BY ‘@@@-2017’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@‘nn02’ WITH GRANT OPTION;
mysql>CREATE USER ‘root’@‘135.0.97.208’ IDENTIFIED BY ‘@@@-2017’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘root’@‘135.0.97.208’ WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;
3）、為ambari創建數據庫
mysql>create database ambari; -----不用CHARACTER SET=latin1;
4）、配置ambari用戶和權限(兩個機器都要執行！
mysql>CREATE USER ‘ambari’@‘%’ IDENTIFIED BY ‘ambari123’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘ambari’@‘%’ WITH GRANT OPTION;
mysql>CREATE USER ‘ambari’@‘localhost’ IDENTIFIED BY ‘ambari123’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘ambari’@‘localhost’ WITH GRANT OPTION;
mysql>CREATE USER ‘ambari’@‘nn01’ IDENTIFIED BY ‘ambari123’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘ambari’@‘nn01’ WITH GRANT OPTION;
mysql>CREATE USER ‘ambari’@‘nn02’ IDENTIFIED BY ‘ambari123’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘ambari’@‘nn02’ WITH GRANT OPTION;
mysql>CREATE USER ‘ambari’@‘135.0.97.208’ IDENTIFIED BY ‘ambari123’;
mysql>GRANT ALL PRIVILEGES ON . TO ‘ambari’@‘135.0.97.208’ WITH GRANT OPTION;
mysql>FLUSH PRIVILEGES;

create database hive;
alter database hive character set latin1;
CREATE USER ‘hive’@‘%’ IDENTIFIED BY ‘Hive@123’;
GRANT ALL PRIVILEGES ON hive.* TO ‘hive’@‘%’;

create database ambari;
CREATE USER ‘ambari’@‘%’ IDENTIFIED BY ‘Ambari@123’;
GRANT ALL PRIVILEGES ON ambari.* TO ‘ambari’@‘%’;

create database ranger;
alter database ranger character set latin1;
CREATE USER ‘rangeradmin’@‘%’ IDENTIFIED BY ‘Ranger@123’;
GRANT ALL PRIVILEGES ON ranger.* TO ‘rangeradmin’@‘%’;
FLUSH PRIVILEGES;

CREATE USER ‘root’@‘%’ IDENTIFIED BY ‘Root@123’;
GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ WITH GRANT OPTION;
FLUSH PRIVILEGES;

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘Root@123’;

update mysql.user set Grant_priv=‘Y’,Super_priv=‘Y’;
SELECT host,user,authentication_string,Grant_priv,Super_priv,authentication_string FROM mysql.user;

mysql>select user,host from mysql.user; //查看授權用戶
mysql>show grants for repuser@‘slaveip’; //查看授權用戶情況
SELECT host,user,Grant_priv,Super_priv FROM mysql.user;

SELECT host,user,Grant_priv,Super_priv,authentication_string FROM mysql.user;

grant all privileges on . to root@‘%’ identified by ‘root1234’;
update mysql.user set Grant_priv=‘Y’,Super_priv=‘Y’;

5）、設置mysql開機自啟動
systemctl enable mysqld
systemctl restart mysqld

–輸入 2.3.4 操作步驟生成的臨時密碼
重新設置密碼：（Test%0831）
mysql> set password=password(‘Test%0831’);
允許遠程登錄：(本機登錄賦權)
mysql>grant all privileges on . to root@‘%’ identified by ‘Test%0831’;
mysql> flush privileges;
查看用戶信息
mysql> use mysql;
mysql> select host,user from user where user=‘root’;
±----------±-----+
| host | user |
±----------±-----+
| % | root |
| localhost | root |
±----------±-----+
2 rows in set (0.00 sec)

本機登錄：—ERROR 1045 (28000): Access denied for user ‘root’@‘%’ (using password: YES)
2.5 設置主主復制
創建Slave復制帳號,每個slave使用標準的MySQL用戶名和密碼連接master。進行復制操作的用戶會授予REPLICATION SLAVE 權限。
---- 在 4 上執行：
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘replication’@‘192@@@1.5’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@‘192@@@1.5’ IDENTIFIED BY ‘123’;
FLUSH PRIVILEGES;
---- 在 5 上執行：
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘replication’@‘192@@@1.4’ IDENTIFIED BY ‘password’;
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO ‘slave’@‘192@@@1.4’ IDENTIFIED BY ‘123’;
FLUSH PRIVILEGES;

CREATE USER ‘root’@‘浮動ip’ IDENTIFIED BY ‘*password’;
GRANT ALL PRIVILEGES ON . TO ‘root’@‘浮動ip’ ;
FLUSH PRIVILEGES;

遠程登陸
CREATE USER ‘root’@‘@@@174.19.85’ IDENTIFIED BY ‘password’;
GRANT ALL PRIVILEGES ON . TO ‘root’@‘@@@174.19.85’;
FLUSH PRIVILEGES;

–查看5 mysql master 狀態(MySQL服務器二進制文件名與位置)
mysql> show master status;
±-----------------±---------±------------------±-------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±------------------±-------------------------±------------------+
| mysql-bin.000009 | 154 | ambari,hive,ranger| mysql,information_schema | |
±-----------------±---------±------------------±-------------------------±------------------+
1 row in set (0.00 sec)

slave上指定master同步信息(告知二進制文件名與位置)
—按照5 master的狀態設置 slave 4 mysql–
change master to
master_host=‘192@@@1.5’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000009’,
master_log_pos=154; #對端狀態顯示的值
start slave;
-查看4 mysql master 狀態
mysql> show master status;
±-----------------±---------±------------------±-------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±------------------±-------------------------±------------------+
| mysql-bin.000008 | 1653 | ambari,hive,ranger| mysql,information_schema | |
±-----------------±---------±------------------±-------------------------±------------------+
1 row in set (0.00 sec)

—按照4 master的狀態設置 slave 5 mysql–
change master to
master_host=‘192@@@1.4’,
master_user=‘replication’,
master_password=‘password’,
master_log_file=‘mysql-bin.000008’,
master_log_pos=1653; #對端狀態顯示的值
start slave;

mysql>SLAVE START; #開啟復制
mysql>SHOW SLAVE STATUS\G #查看主從復制是否配置成功

2.6 配置mysql自動啟動

chmod 755 /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig --level 345 mysqld on

2.7 Keepalived 安裝配置
2.7.1 安裝：
#yum install keepalived -y
2.7.2.1 192@@@1.4 keepalived 配置：
#vi /etc/keepalived/keepalived.cnf
global_defs {
notification_email {
}
}

vrrp_instance PX_MYSQL {state MASTER#state BACKUPinterface bond1virtual_router_id 50priority 100advert_int 1authentication {auth_type PASSauth_pass password123}virtual_ipaddress {192@@@1.182}
}virtual_server 192@@@1.182 3306 {delay_loop 3lb_algo wrrlb_kind DRpersistence_timeout 9600protocol TCPreal_server 192@@@1.4 3306 {notify_down /usr/sbin/stop_keepalived.shweight 1TCP_CHECK {connect_timeout 10connect_port    3306}}
}

2.7.2.2 192@@@1.5 keepalived 配置：
global_defs {
notification_email {
}
}

vrrp_instance PX_MYSQL {state MASTER#state BACKUPinterface bond1virtual_router_id 50priority 90advert_int 1authentication {auth_type PASSauth_pass password123}virtual_ipaddress {192@@@1.182}
}virtual_server 192@@@1.182 3306 {delay_loop 3lb_algo wrrlb_kind DRpersistence_timeout 9600protocol TCPreal_server 192@@@1.5 3306 {notify_down /usr/sbin/stop_keepalived.shweight 1TCP_CHECK {connect_timeout 10connect_port    3306}}
}

??注意：interface 對應的網卡是 192.168網段地址對應的網卡

2.7.2.3 keepalived自監控
[root@OCDC-MYSQL-01 ~]# crontab -l crontab -e
##start keepalived if mysqld started.

• • • • • /usr/sbin/start_keepalived.sh &>/dev/null

[root@OCDC-MYSQL-01 ~]# cat /usr/sbin/start_keepalived.sh （service | systemctl命令監控mysql狀態）
#!/bin/sh

#start keepalived if mysqld started.
#* * * * * /usr/sbin/start_keepalived.sh &>/dev/null

if [ $(systemctl status mysql.service|grep -c “active (running)”) -eq 1 ];
then
if [ $(systemctl status keepalived.service|grep -c “active (running)”) -eq 0 ];
then
systemctl start keepalived
fi
fi
####################################
[root@OCDC-MYSQL-01 ~]# cat /usr/sbin/stop_keepalived.sh
#!/bin/sh

systemctl stop keepalived