mysql-5.7.26 安装已经 主从同步复制
目录
一、准备环境
Mysql 安装包
5.7.26 mysql下载地址
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
注意:mysql 5.7.+ 初始化 ./bin/mysqld --initialize 拷贝启动配置文件 cp ./support-files/mysql.server /etc/init.d/mysqld
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 status\G; *************************** 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 status\G;
脚本
#!/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';"
