5.7的最新版本
mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
地址：https://downloads.mysql.com/archives/community/
1.安装MySQL5.7.32
使用的主机：
192.168.2.164 master
192.168.2.165 slave
两台主机均需要安装
mysql必须装在系统最大分区
1、检查并卸载系统中低版本的mysql
# rpm -qa |grep mysql
# yum remove mysql mysql-server mysql-libs
# rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps
2添加用户和组:
添加用户和组,启动服务用mysql用户,后期维护也可以用mysql用户,如果后期不用mysql账户登录使用（useradd -r -g mysql mysql: -r表示系统用户，在这里不使用）
# groupadd mysql
# useradd -g mysql mysql -s /sbin/nologin
不用设置mysql用户密码
解压gz包并创建软连接
# tar -zxv -f mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /data
# cd /data/
# mv mysql-5.7.32-linux-glibc2.12-x86_64 mysql
# ln -sv /data/mysql /usr/local/mysql
# cd /usr/local/mysql(这一步没必要操作)
3创建目录及授权
可以写一个shell脚本批量执行这些命令
/etc/my.cnf配置文件中的路径要跟这些路径保持一致
# mkdir -pv /data/mysql/data
# mkdir -pv /data/mysql/dblog/iblog
# mkdir -pv /data/mysql/dblog/binlog
# mkdir -pv /data/mysql/dblog/relaylog
# mkdir -pv /data/mysql/dblog/errlog
# mkdir -pv /data/mysql/dblog/slowlog
# mkdir -pv /data/mysql/run
# mkdir -pv /data/mysql/tmp
# mkdir -pv /data/mysql/lock/subsys
# chown -R mysql:mysql /data/mysql
# chmod -R 755 /data/mysql
4修改配置文件my.cnf参数
# 这一步不用再执行：cp /data/mysql/support-files/my-default.cnf /etc/my.cnf (在5.7.18开始，二进制包不再包含示例文件my-default.cnf)
A、----------主数据库配置---------
# vim /etc/my.cnf
配置文件以https://tools.percona.com/wizard 生成为基础，修改了相关路径，配置为:
物理服务器，cpu 8 core 8G ram
---------------------------------------------------------------------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
#[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
[mysql]
# CLIENT #
port = 3306
socket = /data/mysql/run/mysql.sock
disable-auto-rehash
default-character-set=gbk
prompt=(\u@\h) [\d]>\_
[mysqld]
# GENERAL #
server_id = 128
port = 3306
user = mysql
explicit_defaults_for_timestamp=true
default-storage-engine = InnoDB
character_set_server = gbk
auto_increment_increment = 2
auto_increment_offset = 1
lower_case_table_names = 1
socket = /data/mysql/run/mysql.sock
pid_file = /data/mysql/run/mysqld.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 134217728
max_connections = 2048
max_user_connections = 1024
open_files_limit = 65535
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate-is-now = 1
# DATA STORAGE #
basedir = /data/mysql
datadir = /data/mysql/data/
tmpdir = /data/mysql/tmp
# BINARY LOGGING #
log-bin = /data/mysql/dblog/binlog/master-bin
log-bin-index = /data/mysql/dblog/binlog/master-bin.index
expire-logs-days = 15
sync-binlog = 1
binlog_format = ROW
#RELAY LOGGING
relay-log=/data/mysql/dblog/relaylog/master-relay-bin
relay-log-index=/data/mysql/dblog/relaylog/master-relay-bin.index
# sync_relay_log=1 最安全，崩溃后最多丢失一个事件，但是会引起同步延迟
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb_log_group_home_dir = /data/mysql/dblog/iblog
innodb_data_home_dir = /data/mysql/dblog/iblog
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
# LOGGING #
log_timestamps=system
general_log = off
log-error = /data/mysql/dblog/errlog/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/mysql/dblog/slowlog/mysql-slow.log
log_slave_updates=ON
5执行MySQL安装脚本
# cd /data/mysql/bin
# ./mysqld --defaults-extra-file=/etc/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data --user=mysql --initialize-insecure
检测日志：
/data/mysql/dblog/errlog/mysql-error.log
若报错：
/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决：
# yum install libaio (这一步不用执行)
# ./mysql_ssl_rsa_setup --basedir=/data/mysql --datadir=/data/mysql/data
(MySQL 5.7.6 版本之后,MySQL SSL Certificate and RSA Key Generation Utility)
6修改系统环境变量
# vim /etc/profile.d/mysql.sh
#!/bin/bash
PATH=/data/mysql/bin:$PATH
# source /etc/profile.d/mysql.sh
7自启动脚本
# cp /data/mysql/support-files/mysql.server /etc/init.d/mysqld
# vim /etc/init.d/mysqld
修改mysqld_pid_file_path=/data/mysql/run/mysqld.pid
修改 lockdir='/data/mysql/lock/subsys'
# chkconfig --add mysqld
# /etc/init.d/mysqld start
# /etc/init.d/mysqld stop
# service mysqld start
# service mysqld restart
8 删除匿名账户,设置root密码
密码是password
# mysql
mysql> use mysql;
mysql > DELETE FROM mysql.user WHERE User='';
mysql> DELETE FROM mysql.user WHERE host='localhost';
mysql> SELECT host, user, authentication_string FROM user WHERE user = 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
设置主从架构
master数据库修改配置,在上一步的基础上进行修改
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
default-character-set = utf8mb4
default-character-set=utf8mb4
character_set_server = utf8mb4
server_id = 100
#relay-log=/data/mysql/dblog/relaylog/master-relay-bin
#relay-log-index=/data/mysql/dblog/relaylog/master-relay-bin.index
slave数据库修改配置,在上一步的基础上进行修改
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
default-character-set = utf8mb4
default-character-set=utf8mb4
character_set_server = utf8mb4
server_id = 200
relay-log=/data/mysql/dblog/relaylog/slave-relay-bin
relay-log-index=/data/mysql/dblog/relaylog/slave-relay-bin.index
log-bin = /data/mysql/dblog/binlog/slave-bin
log-bin-index = /data/mysql/dblog/binlog/slave-bin.index
重启数据库
完整的master配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
#[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
default-character-set = utf8mb4
[mysql]
# CLIENT #
port = 3306
socket = /data/mysql/run/mysql.sock
disable-auto-rehash
default-character-set=utf8mb4
prompt=(\u@\h) [\d]>\_
[mysqld]
# GENERAL #
server_id = 100
port = 3306
user = mysql
explicit_defaults_for_timestamp=true
default-storage-engine = InnoDB
character_set_server = utf8mb4
auto_increment_increment = 2
auto_increment_offset = 1
lower_case_table_names = 1
socket = /data/mysql/run/mysql.sock
pid_file = /data/mysql/run/mysqld.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 134217728
max_connections = 2048
max_user_connections = 1024
open_files_limit = 65535
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate-is-now = 1
# DATA STORAGE #
basedir = /data/mysql
datadir = /data/mysql/data/
tmpdir = /data/mysql/tmp
# BINARY LOGGING #
log-bin = /data/mysql/dblog/binlog/master-bin
log-bin-index = /data/mysql/dblog/binlog/master-bin.index
expire-logs-days = 15
sync-binlog = 1
binlog_format = ROW
#RELAY LOGGING
#relay-log=/data/mysql/dblog/relaylog/master-relay-bin
#relay-log-index=/data/mysql/dblog/relaylog/master-relay-bin.index
# sync_relay_log=1 最安全，崩溃后最多丢失一个事件，但是会引起同步延迟
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb_log_group_home_dir = /data/mysql/dblog/iblog
innodb_data_home_dir = /data/mysql/dblog/iblog
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
# LOGGING #
log_timestamps=system
general_log = off
log-error = /data/mysql/dblog/errlog/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/mysql/dblog/slowlog/mysql-slow.log
log_slave_updates=ON
完整的slave配置
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
#[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
[client]
port = 3306
socket =/data/mysql/run/mysql.sock
default-character-set = utf8mb4
[mysql]
# CLIENT #
port = 3306
socket = /data/mysql/run/mysql.sock
disable-auto-rehash
default-character-set=utf8mb4
prompt=(\u@\h) [\d]>\_
[mysqld]
# GENERAL #
server_id = 200
port = 3306
user = mysql
explicit_defaults_for_timestamp=true
default-storage-engine = InnoDB
character_set_server = utf8mb4
auto_increment_increment = 2
auto_increment_offset = 1
lower_case_table_names = 1
socket = /data/mysql/run/mysql.sock
pid_file = /data/mysql/run/mysqld.pid
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 134217728
max_connections = 2048
max_user_connections = 1024
open_files_limit = 65535
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sysdate-is-now = 1
# DATA STORAGE #
basedir = /data/mysql
datadir = /data/mysql/data/
tmpdir = /data/mysql/tmp
# BINARY LOGGING #
log-bin = /data/mysql/dblog/binlog/slave-bin
log-bin-index = /data/mysql/dblog/binlog/slave-bin.index
expire-logs-days = 15
sync-binlog = 1
binlog_format = ROW
#RELAY LOGGING
relay-log=/data/mysql/dblog/relaylog/slave-relay-bin
relay-log-index=/data/mysql/dblog/relaylog/slave-relay-bin.index
# sync_relay_log=1 最安全，崩溃后最多丢失一个事件，但是会引起同步延迟
# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048
# INNODB #
innodb_log_group_home_dir = /data/mysql/dblog/iblog
innodb_data_home_dir = /data/mysql/dblog/iblog
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 6G
# LOGGING #
log_timestamps=system
general_log = off
log-error = /data/mysql/dblog/errlog/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /data/mysql/dblog/slowlog/mysql-slow.log
log_slave_updates=ON
登录master数据库创建同步数据库使用的用户
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'Gp.031!Jdd';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
# 查看是否开启binlog
mysql> show variables like 'log_bin';
mysql> show master status;
记录如下这俩值
master-bin.000005 | 625
登录【从数据库】，进入mysql命令行。
mysql> stop slave;
# 命令说明
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.164', # 主库ip
MASTER_PORT=3306, # 主库端口
MASTER_USER='repl', # 主库同步用的账号
MASTER_PASSWORD='Gp.031!Jdd', # 主库同步用的密码
MASTER_LOG_FILE='master-bin.000005', # 主库信息，上一步中记录的
MASTER_LOG_POS=625; # 主库信息，上一步中记录的
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.164',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Gp.031!Jdd',
MASTER_LOG_FILE='master-bin.000005',
MASTER_LOG_POS=625;
mysql> start slave;
Query OK, 0 rows affected
stop slave; //停止同步
start slave; //开始同步
//必须和【主数据库】的信息匹配。
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000005',
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=625;
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.164
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000005
Read_Master_Log_Pos: 625
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000005
Slave_IO_Running: Yes # 重要
Slave_SQL_Running: Yes # 重要
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 625
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: aff8daef-9767-11eb-a072-00505623c794
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。
如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。
然后是测试，在主数据库上创建库，创建表，新增数据，修改数据，删除数据
每一步都查看从数据库上的显示
补充：
如果【主服务器】重启mysql服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在bin-log中找到【从数据库】出错前的position，然后在【从数据库】上执行change master，将master_log_file和master_log_pos重新指定后，开始同步。