背景：由于項(xiàng)目要求，在加上服務(wù)器資源有限畜号，要盡快上線一個(gè)項(xiàng)目缴阎，因此直接搭建一套生產(chǎn)測試環(huán)境，供開發(fā)使用简软，其中涉及的到MySQL數(shù)據(jù)的主從配置蛮拔，但是服務(wù)器只有一臺(tái)。因此試著在搭建一臺(tái)服務(wù)器痹升，運(yùn)行兩個(gè)MySQL實(shí)例建炫，且配置主從。
服務(wù)器系統(tǒng)版本：Centos6.3
MySQL數(shù)據(jù)庫庫版本：5.6.36
1疼蛾、數(shù)據(jù)庫的安裝
卸載默認(rèn)安裝的MySQL數(shù)據(jù)庫
rpm -qa | grep mysql
rpm -e --nodeps 對應(yīng)的mysql版本
去官網(wǎng)下載MySQL安裝包并且上傳肛跌，然后解壓安裝
mkdir mysql && cd mysql
rz MySQL-5.6.36-1.el6.x86_64.rpm-bundle.tar
tart -xvf MySQL-5.6.36-1.el6.x86_64.rpm-bundle.tar
rpm -ivh MySQL*.rpm
2、修改配置文件、并且建立數(shù)據(jù)存儲(chǔ)目錄
首先查詢MySQL的默認(rèn)安裝目錄
#find / -name mysql -print
/var/lock/subsys/mysql
/var/lib/mysql ##mysql庫的默認(rèn)安裝路徑
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/perl5/DBD/mysql
/usr/lib64/perl5/auto/DBD/mysql
/usr/lib64/mysql
/usr/local/mysql
/usr/share/mysql ##mysql的默認(rèn)安裝路徑
/usr/include/mysql
/usr/include/mysql/mysql
/etc/logrotate.d/mysql
/etc/rc.d/init.d/mysql
建立數(shù)據(jù)庫存儲(chǔ)目錄衍慎，添加屬主屬組
mkdir /mnt/data/{mysql-3306,mysql-3307} -pv
chown -R mysql:mysql /mnt/data
復(fù)制MySQL的主配置文件到/etc/目錄下
cp -rf /usr/share/mysql/my-default.cnf /etc/my.cnf
編輯主配置文件
vim /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
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql-cli]
default-character-set=utf8
[mysqld]
skip-name-resolve
max_connections=1000
character-set-server=utf8
default-storage-engine=INNODB
lower_case_table_name=1
max_allowed_packet=1024M
# 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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = /var/lib/mysql
datadir = /mnt/data/mysql-3306
port = 3388
server_id = 1
socket = /var/lib/mysql/mysql-3306.sock
log-bin=mysql-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
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
復(fù)制一份转唉，并且重新命名
cp -rf /etc/my.cnf /etc/my3307.cnf
再次編輯，修改相關(guān)內(nèi)容
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql-cli]
default-character-set=utf8
[mysqld]
skip-name-resolve
max_connections=1000
character-set-server=utf8
default-storage-engine=INNODB
lower_case_table_name=1
max_allowed_packet=1024M
# 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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = /var/lib/mysql
datadir = /mnt/data/mysql-3307
port = 3307
server_id = 2
socket = /var/lib/mysql/mysql-3307.sock
log-bin=mysql-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
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
建庫,并且賦予對應(yīng)權(quán)限
mysql_install_db -datadir=/mnt/data/mysql-3306 --user=mysql
chmod -R 777 /mnt/data/mysql-3306
mysql_install_db -datadir=/mnt/data/mysql-3307 --user=mysql
chmod -R 777 /mnt/data/mysql-3307
啟動(dòng)端口為3306的MySQL數(shù)據(jù)庫
service mysql start
啟動(dòng)端口為3307的MySQL數(shù)據(jù)庫
/usr/bin/mysqld_safe --defaults-file=/etc/my3307.cnf &
檢查端口
#ss -tnl
Recv-Q Send-Q Local Address:Port Peer Address:Port
0 128 :::3306 :::*
0 128 :::3307 :::*
3稳捆、配置數(shù)據(jù)庫的主從服務(wù)
登錄3306數(shù)據(jù)庫
mysql -uroot -p -S /var/lib/mysql/mysql-3306.sock
更改root默認(rèn)密碼
set password for 'root'@'localhost' =password('123456');
授權(quán)root遠(yuǎn)程登錄
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;
登錄3307數(shù)據(jù)庫
mysql -uroot -p -S /var/lib/mysql/mysql-3307.sock
更改root默認(rèn)密碼
set password for 'root'@'localhost' =password('123456');
授權(quán)root遠(yuǎn)程登錄
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;
4赠法、配置主從復(fù)制
在主服務(wù)器上建立帳戶并授權(quán)slave:
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';
登錄主服務(wù)器的mysql，查詢master的狀態(tài)
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 399 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注：執(zhí)行完此步驟后不要再操作主服務(wù)器MYSQL乔夯，防止主服務(wù)器狀態(tài)值變化
配置從服務(wù)器Slave：
mysql>change master to master_host='localhost',master_user='mysync',master_password='q123456',master_port='3306',master_log_file='mysql-bin.000002',master_log_pos=399; //注意不要斷開砖织，399數(shù)字前后無單引號(hào)。
mysql>start slave; //啟動(dòng)從服務(wù)器復(fù)制功能
檢查從服務(wù)器復(fù)制功能狀態(tài)：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 399
Relay_Log_File: MasService-relay-bin.000002
Relay_Log_Pos: 399
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...................................................
注：Slave_IO及Slave_SQL進(jìn)程必須正常運(yùn)行驯嘱，即YES狀態(tài)镶苞，否則都是錯(cuò)誤的狀態(tài)(如：其中一個(gè)NO均屬錯(cuò)誤)。
5鞠评、主從服務(wù)器測試：
主服務(wù)器Mysql茂蚓，建立數(shù)據(jù)庫，并在這個(gè)庫中建表插入一條數(shù)據(jù)：
mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)
mysql> use hi_db;
Database changed
mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into hi_tb values(001,'bobu');
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
從服務(wù)器Mysql查詢：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hi_db | //I'M here剃幌，大家看到了吧
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use hi_db
Database changed
mysql> select * from hi_tb; //查看主服務(wù)器上新增的具體數(shù)據(jù)
+------+------+
| id | name |
+------+------+
| 1 | bobu |
+------+------+
1 row in set (0.00 sec)
6聋涨、從服務(wù)器配置只讀屬性
從服務(wù)器“read_only=1”只讀模式開啟的解鎖命令為設(shè)定“read_only=0”;設(shè)定全局鎖“flush tables with read lock;”，對應(yīng)的解鎖模式命令為：“unlock tables;”
set global read_only=1;####給從服務(wù)器設(shè)置只讀
set global read_only=0;####取消設(shè)置只讀屬性
7负乡、完成：
編寫一shell腳本牍白，用nagios監(jiān)控slave的兩個(gè)yes（Slave_IO及Slave_SQL進(jìn)程），如發(fā)現(xiàn)只有一個(gè)或零個(gè)yes抖棘，就表明主從有問題了茂腥，發(fā)短信警報(bào)吧。
