mysql binlog pid_MySQL 5.7 基于 binlog 的主从复制

MySQL 5.7 基于 binlog 的主从复制

Hostname

内网 IP

mysql-master1

172.40.1.117

mysql-slave1

172.40.3.44

mysql-master2

172.40.0.149

mysql-slave2

172.40.5.110

编译安装 MySQL

安装依赖包

yum install -y gcc gcc-c++ cmake ncurses ncurses-devel bison

下载含有 boost 的源码包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.25.tar.gz

添加用户

useradd -s /sbin/nologin mysql

创建所需目录(data 目录、安装目录)

mkdir -p /data/mysql/data /usr/local/mysql

/data/mysql/data 目录应为空目录

解压

tar -zxf mysql-boost-5.7.25.tar.gz -C /usr/local/mysql/

编译安装

cd /usr/local/mysql/mysql-5.7.25

cmake -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=boost

make && make install

更改目录属性

chown -R mysql:mysql /data/mysql

chown -R mysql:mysql /usr/local/mysql

修改配置文件(没有则新建)

vim /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/data

pid-file = /data/mysql/mysql.pid

log_error = /data/mysql/mysql-error.log

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql-slow.log

skip-external-locking

key_buffer_size = 32M

max_allowed_packet = 1024M

table_open_cache = 128

sort_buffer_size = 768K

net_buffer_length = 8K

read_buffer_size = 768K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

thread_cache_size = 16

query_cache_size = 16M

tmp_table_size = 32M

performance_schema_max_table_instances = 1000

explicit_defaults_for_timestamp = true

#skip-networking

max_connections = 500

max_connect_errors = 100

open_files_limit = 65535

log_bin=mysql-bin

binlog_format=mixed

server_id = 232

expire_logs_days = 10

early-plugin-load = ""

default_storage_engine = InnoDB

innodb_file_per_table = 1

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 32M

sort_buffer_size = 768K

read_buffer = 2M

write_buffer = 2M

也可以从 /usr/local/mysql/support-files 这个目录复制 my-default.cnf 到 /etc/ 下作为 my.cnf 配置文件

初始化 MySQL

cd /usr/local/mysql/bin

./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

在初始化时，一定要加上命令中所示参数，而且在执行这一步操作前，/data/mysql/data 这个目录必须是空的；在这里指定的 basedir 和 datadir 目录必须要和 /etc/my.cnf 配置的目录一致。

拷贝可执行文件

cd ../support-files

cp mysql.server /etc/init.d/mysqld

启动

/etc/init.d/mysqld start

修改环境变量

echo "PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile

source /etc/profile

添加 MySQL 为系统服务并设置开机自启

vim /usr/lib/systemd/system/mysql.service

[Unit]

Description=Mysql

After=syslog.target network.target remote-fs.target nss-lookup.target

[Service]

Type=forking

PIDFile=/data/mysql/mysql.pid

ExecStart=/etc/init.d/mysqld start

ExecReload=/bin/kill -s HUP $MAINPID

ExecStop=/bin/kill -s QUIT $MAINPID

PrivateTmp=false

[Install]

WantedBy=multi-user.target

systemctl daemon-reload

systemctl enable mysql // 设置开机自启

# 其他可用命令有

systemctl start mysql // 启动

systemctl stop mysql // 停止

systemctl restart mysql // 重启

测试连接

mysql -hlocalhost -uroot -p // 无需密码，可直接登录

配置双主+主从复制

修改 root 密码

use mysql;

update user set authentication_string=password('%@cSX@PQ2qK^95L7my$C') where user='root' and host='localhost';

flush privileges;

重新登录

mysql -hlocalhost -uroot -p

Enter password: %@cSX@PQ2qK^95L7my$C

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.25-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改配置文件

# 确保每一个 MySQL 服务的 server_id 唯一

# 在需要配置双主的 MySQL 中添加如下内容

[mysqld]

sync_binlog=1

log-slave-updates

auto_increment_increment = 2

auto_increment_offset = 2

重启 Mysql

systemctl restart mysql

同步配置

mysql -hlocalhost -uroot -p

Enter password:

# master 上增加用于同步的 sync 用户即可

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'172.40.%' IDENTIFIED BY 'Xf8NehD72shBfsc#';

mysql> flush privileges;

# 设置同步 master1

mysql> change master to

master_host = '172.40.1.117',

master_user = 'sync',

master_password = 'Xf8NehD72shBfsc#',

master_log_file = 'mysql-bin.000002',

master_log_pos = 1482;

# 设置同步 master2

mysql> change master to

master_host = '172.40.0.149',

master_user = 'sync',

master_password = 'Xf8NehD72shBfsc#',

master_log_file = 'mysql-bin.000002',

master_log_pos = 1482;

# 启动 slave

mysql> start slave;

# 查看同步状态

mysql> show slave status\G

查找 master_log_file 及 master_log_pos

show master status;

Slave_IO_Running: Yes 与 Slave_SQL_Running: Yes 若不为 Yes，解决如下：

https://www.cnblogs.com/l-hh/p/9922548.html#_label2