Mysql主主部署

        服务器配置

  • 10.8.8.80
  • 10.8.8.81
  • 硬盘挂载/home/hifo

一、mysql5.7二进制包安装

1、下载包

1
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz

        安装依赖包

1
yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libxml libgcrypt libtool bison

2、解压

1
2
tar zxvf mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.12-linux-glibc2.5-x86_64 /usr/local/mysql

3、初始化

1
2
3
4
5
useradd -M -s /sbin/nologin mysql 
mkdir -p /home/hifo/mysql
chown mysql /home/hifo/mysql
cd /usr/local/mysql
./bin/mysqld --initialize --user=mysql --datadir=/home/hifo/mydql

        注意：这一步最后有一个提示

1
[Note] A temporary password is generated for root@localhost: B*s1i(*,kXwg

        最后的字符串为root密码

1
./bin/mysql_ssl_rsa_setup --datadir=/home/hifo/mysql

4、拷贝配置文件和启动脚本

1
2
3
4
5
6
cp support-files/my-default.cnf /etc/my.cnf 
vim /etc/my.cnf //编辑或者修改
basedir = /usr/local/mysql
datadir = /home/hifo/mysql
port = 3306
socket = /tmp/mysql.sock

        配置启动文件

1
2
3
4
cp support-files/mysql.server /etc/init.d/mysqld
vim /etc/init.d/mysqld //编辑或者修改
basedir=/usr/local/mysql
datadir=/home/hifo/mysql

5、启动服务

1
/etc/init.d/mysqld start

6、设置root密码

        使用初始化密码登录

1
2
/usr/local/mysql/bin/mysql -uroot -p'B*s1i(*,kXwg' //进入后直接设置密码
mysql>set password = password('hifo123456'); //一定要设置一下新密码

        退出以后再使用新的密码登录就可以了

二、主主配置

        更改两台服务器的mysql配置文件

1
vim /etc/my.cnf

        10.8.8.80添加

1
2
3
4
server-id=1
log-bin=mysqlmaster-bin.log
auto-increment-increment=2
auto-increment-offset=1

        10.8.8.81添加

1
2
3
4
server-id=2
log-bin=mysqlmaster-bin.log
auto-increment-increment=2
auto-increment-offset=2

        说明：

  • auto-increment-increment的值设为真个结构中服务器的总数，此实验为两台服务器，所以值为2
  • auto-increment-offset是用来设定数据库中自动增长的起点的，因为服务器都设定了一次自动增长的值为2，所以它们的起点必须得不同，这样才能避免两台服务器数据同步时出现主键冲突

        另可以设置relicate-do-db=database-name指定需要同步的数据库

        重启mysql服务使配置生效

        登录mysql，添加同步数据所需要的用户

        10.8.8.80上

1
>GRANT REPLICATION SLAVE ON *.* TO 'systop1'@'10.8.8.%' IDENTIFIED BY 'systop';

        10.8.8.81上

1
>GRANT REPLICATION SLAVE ON *.* TO 'systop2'@'10.8.8.%' IDENTIFIED BY 'systop';

        查看两台服务器的master状态

        10.8.8.80

1
2
3
4
5
SHOW MASTER STATUS;
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000002 |      452 |              |                  |                   |

        10.8.8.81

1
2
3
4
5
show master status;
+------------------------+----------+--------------+------------------+-------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------------+----------+--------------+------------------+-------------------+
| mysqlmaster-bin.000001 |      452 |              |                  |                   |

        说明二进制日志路径

        10.8.8.80上执行

1
CHANGE MASTER TO MASTER_HOST='10.8.8.81',MASTER_USER='systop2',MASTER_PASSWORD='systop',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=452;

        10.8.8.81上执行

1
CHANGE MASTER TO MASTER_HOST='10.8.8.80',MASTER_USER='systop1',MASTER_PASSWORD='systop',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=452;

        开启复制功能

        两台机器都执行

1
start slave

        查看复制连接状态

        执行

1
show slave status \G;

        查看

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

        都显示yes表示启动正常

        测试：两台服务器都做数据更新操作，查看数据是否更新