一台服务器启动两个mysql实例并且配置主从
2017-11-14 本文已影响40人
泡菜爱上WaSabi
背景：由于项目要求，在加上服务器资源有限，要尽快上线一个项目，因此直接搭建一套生产测试环境，供开发使用，其中涉及的到MySQL数据的主从配置，但是服务器只有一台。因此试着在搭建一台服务器，运行两个MySQL实例，且配置主从。
服务器系统版本：Centos6.3
MySQL数据库库版本：5.6.36
1、数据库的安装
卸载默认安装的MySQL数据库
rpm -qa | grep mysql
rpm -e --nodeps 对应的mysql版本
去官网下载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、修改配置文件、并且建立数据存储目录
首先查询MySQL的默认安装目录
#find / -name mysql -print
/var/lock/subsys/mysql
/var/lib/mysql ##mysql库的默认安装路径
/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的默认安装路径
/usr/include/mysql
/usr/include/mysql/mysql
/etc/logrotate.d/mysql
/etc/rc.d/init.d/mysql
建立数据库存储目录，添加属主属组
mkdir /mnt/data/{mysql-3306,mysql-3307} -pv
chown -R mysql:mysql /mnt/data
复制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
复制一份，并且重新命名
cp -rf /etc/my.cnf /etc/my3307.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-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
建库,并且赋予对应权限
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
启动端口为3306的MySQL数据库
service mysql start
启动端口为3307的MySQL数据库
/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、配置数据库的主从服务
登录3306数据库
mysql -uroot -p -S /var/lib/mysql/mysql-3306.sock
更改root默认密码
set password for 'root'@'localhost' =password('123456');
授权root远程登录
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;
登录3307数据库
mysql -uroot -p -S /var/lib/mysql/mysql-3307.sock
更改root默认密码
set password for 'root'@'localhost' =password('123456');
授权root远程登录
grant all privileges on *.* to root@'%' identified by '123456' with grant option;
flush privileges;
4、配置主从复制
在主服务器上建立帐户并授权slave:
mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';
登录主服务器的mysql，查询master的状态
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 399 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
配置从服务器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数字前后无单引号。
mysql>start slave; //启动从服务器复制功能
检查从服务器复制功能状态：
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进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。
5、主从服务器测试：
主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：
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)
从服务器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; //查看主服务器上新增的具体数据
+------+------+
| id | name |
+------+------+
| 1 | bobu |
+------+------+
1 row in set (0.00 sec)
6、从服务器配置只读属性
从服务器“read_only=1”只读模式开启的解锁命令为设定“read_only=0”;设定全局锁“flush tables with read lock;”，对应的解锁模式命令为：“unlock tables;”
set global read_only=1;####给从服务器设置只读
set global read_only=0;####取消设置只读属性
7、完成：
编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。
