mysql主从配置

[TOC]

环境准备

系统：centos7
软件版本：mysql-5.7.23-linux-glibc2.12-x86_64
安装目录：/usr/local/mysql
数据库目录：/data/mysql
数据库root密码: hc123456
A数据库：10.0.0.11 主
B数据库：10.0.0.12 从

1.两个数据库版本最好一致
2.两个数据库内数据保持一致，若不一致，可手动调整，比如A比B多一个库，那将这个库导入到B库，达到一致。

数据库同步账号: sync
数据库sync密码: pcpaeyphmp

数据库安装

两端都执行查看安装文档

curl http://home.onlycloud.xin/code/mysql-bin-install.sh -o mysql-install.sh
sh mysql-install.sh
source /etc/profile

A数据库操作

1.开启binlog(数据库二进制日志),设置server-id,重启mysql服务

cp /etc/my.cnf{,.`date +%F`}
sed -i '14a log-bin = mysql-bin' /etc/my.cnf
sed -i 's/server-id.*/server-id = 1/g' /etc/my.cnf
sed -i '18a log-bin-index = master-bin.index' /etc/my.cnf
systemctl restart mysqld

2.创建同步数据的用户,授权允许10.0.0.0/24网络使用sync用户登录

source /etc/profile
mysql -uroot -phc123456 
create user sync;
grant replication slave on *.* to 'sync'@'10.0.0.%' identified by 'pcpaeyphmp';
flush privileges;
show master status;
exit

# MySQL [(none)]> show master status;
# 记下 File, Position 的值
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      794 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

B数据库操作

1.配置从库只允许来自服务器线程或具有SUPER权限的数据库用户进行更新(root,sync),开启binlog(数据库二进制日志),设置server-id,重启mysql服务

cp /etc/my.cnf{,.`date +%F`}
sed -i '11a read-only' /etc/my.cnf
sed -i '15a log-bin = mysql-bin' /etc/my.cnf
sed -i 's/server-id.*/server-id = 2/g' /etc/my.cnf
sed -i '19a log-bin-index = master-bin.index' /etc/my.cnf
systemctl restart mysqld

2.配置,连接数据库A

source /etc/profile
mysql -uroot -phc123456
change master to master_host='10.0.0.11',  # A数据库 IP或主机名(需解析通)
master_port=3306,                          # A数据库A端口号
master_user='sync',                        # 同步账号
master_password='pcpaeyphmp',              # 同步账号的密码
master_log_file='mysql-bin.000003',        # A数据库执行 show master status; 获取的 File 值
master_log_pos=794;                        # A数据库执行 show master status; 获取的 Position 值
start slave;                               # 启动slave
show slave status\G;                       # 查看同步状态
exit

# MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.11
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 794
               Relay_Log_File: nfs-02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           ............................

# 保证以下两项状态为 Yes 则同步成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证

A创建数据库

create database test_01;
show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
+--------------------+

B查看数据库

show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
+--------------------+

排错

Slave_IO_Running:     # 状态不为Yes

1.数据库A与数据库B之间网络是否通畅
2.同步账号的用户权限,及是否允许数据库B登录
3.查看两端server-id(不能相同) 使用mysql命令查看 show variables like 'server_id'; 此配置项在my.cnf