mysql-5.7.26 安装已经 主从同步复制

目录

一、准备环境

Mysql 安装包

5.7.26 mysql下载地址

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

配置host 的IP 映射 

192.168.1.57　　node57  数据库主节点

 192.168.1.58　　node58 数据库从节点

 192.168.1.59　　node59 数据库从节点

二、解压安装包

2.1我这里使用的目录/home/ap

tar -zvxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C

2.2修改名称

mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql

2.3创建data 和 log目录（我这里没有创建，初始化失败，可以不创建初始化尝试一下）

三、创建Mysql用户

useradd mysql

3.1 赋予mysql 目录 Mysql权限

chown mysql.mysql -R /home/ap/mysql

3.2初始化

/home/ap/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/home/ap/mysql --datadir=/home/ap/mysql/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp

3.2查询登录密码

grep 'password' /home/ap/mysql/log/mysql-error.log

3.3修改启动服务指定data 和 base目录(可以忽略这步)

vi /home/ap/mysql/support-files/mysql.server

basedir=/home/ap/mysql
datadir=/home/ap/mysql/data

vi  bin/mysqld_safe
DATADIR=/usr/local/mysql/data 改为 自己定义的目录 /home/ap/mysql/data

3.4拷贝启动配置文件

cp /home/ap/mysql/support-files/mysql.server /etc/init.d/mysql

3.5赋予执行权限

chmod +x  /etc/init.d/mysql

四、编辑/etc/my.cnf配置文件

4.1

vi /etc/my.cnf

[client]
port = 3306
socket = /home/ap/mysql/mysql.sock
[mysqld]
server_id=10
port = 3306
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /home/ap/mysql/mysql.sock
basedir =/home/ap/mysql
datadir = /home/ap/mysql/data
pid-file = /home/ap/mysql/mysql.pid
max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
server-id=1
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row
log_error = /home/ap/mysql/log/mysql-error.log 
skip-name-resolve
log-slave-updates=1
relay_log_purge = 0 
slow_query_log = 1
long_query_time = 1 
slow_query_log_file = /home/ap/mysql/log/mysql-slow.log 

4.1启动Mysql

/etc/init.d/mysql start

4.2登录mysql  

mysql -uroot -p查询的密码

4.3修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123'; 

4.4数据库主节点操作完成

 接下来，192.168.1.58 和 192.168.1.59  都安装以上操作安装mysql服务。

配置文件如下脚本

https://www.cnblogs.com/pythonx/protected/p/12166539.html

五、开启同步复制

三台数据库操作

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
select * from mysql.plugin;
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
set global rpl_semi_sync_slave_enabled=1;
show variables like '%rpl_semi%';

5.1 主数据库操作

创建复制用户:GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' IDENTIFIED BY "123456" WITH GRANT OPTION;
show master statusG;
*************************** 1. row ***************************
             File: master-bin.000002
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 282c4f16-3416-11ea-aa4a-000c29d11afe:1-1511,
2b02edf5-3416-11ea-961c-000c295bffbb:1-11650,
b8b012b5-3416-11ea-a65b-000c2927fefc:1-8
1 row in set (0.00 sec)

ERROR: 
No query specified

5.2 两台从数据操作

出现以下为正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

stop slave;
change master to master_host='192.168.1.57',master_port=3306, master_user='slave', master_password='A123456',master_log_file='master-bin.0000002', master_log_pos=194;
start slave;
start slave;
show slave statusG;

　

脚本

#!/bin/bash

WORK_DIR=/home/ap
MYSQL_FILE=$WORK_DIR/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
MYSQL_NAME=$WORK_DIR/mysql-5.7.26-linux-glibc2.12-x86_64
MY_MYSQL=$WORK_DIR/mysql
MYSQL_DATA=$MY_MYSQL/data
MYSQL_LOG=$MY_MYSQL/log

#解压Mysql安装包
tar -zxvf $MYSQL_FILE -C $WORK_DIR

mv $MYSQL_NAME $MY_MYSQL

mkdir -p $MYSQL_DATA $MYSQL_LOG

#创建mysql用户
useradd mysql

chown mysql.mysql -R $MY_MYSQL

#初始化

cd $MY_MYSQL

#./bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
./bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/home/ap/mysql --datadir=/home/ap/mysql/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp

#备份原来的my.cnf

mv /etc/my.cnf /etc/my.cnf.bak

#
echo > /etc/my.cnf

cat >> /etc/my.cnf << EOF
[client]
port = 3306
socket = /home/ap/mysql/mysql.sock
[mysqld]
server_id=10
port = 3306
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /home/ap/mysql/mysql.sock
basedir =/home/ap/mysql
datadir = /home/ap/mysql/data
pid-file = /home/ap/mysql/mysql.pid
max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
server-id=1
gtid_mode=on
enforce_gtid_consistency=on
log-slave-updates=1
log-bin=master-bin
log-bin-index = master-bin.index
relay-log = relay-log
relay-log-index = relay-log.index
binlog_format=row
log_error = /home/ap/mysql/log/mysql-error.log
skip-name-resolve
log-slave-updates=1
relay_log_purge = 0
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /home/ap/mysql/log/mysql-slow.log
EOF

cat >> ~/.bashrc << EOF
#ADD mysql
export PATH=$PATH:/home/ap/mysql/bin
EOF

#拷贝配置文件

cp $MY_MYSQL/support-files/mysql.server /etc/init.d/mysql

#启动mysql
/etc/init.d/mysql start

#查询密码
grep 'password' $MYSQL_LOG/mysql-error.log

echo -e "Run: mysql -uroot -p"

echo -e "

ALTER USER 'root'@'localhost' IDENTIFIED BY '123';"

　

六、初始化数据库常见问题

https://www.cnblogs.com/pythonx/p/12095751.html