背景
操作系统为RedHat6.5
192.168.101.165 , 192.168.101.167两台机器，每台两个实例，互为主备；
1. 下载安装包 https://dev.mysql.com/downloads/mysql/5.7.html#downloads mysql-5.7.23-linux-glibc2.12-x86_64.tar
2. 解压即用：tar -xvf mysql-5.7.23-linux-glibc2.12-x86_64.tar
3. 在安装目录下新建配置文件文件夹以及文件 mkdir conf ; touch my3306.cnf; touch my3307.cnf
4. 增加配置文件内容,注意3306，3307配置文件目录、端口不要重复；每个实例的server_id 不能重复
[client]
port=3306
socket =/usr/mysql5.7.23/mysql/mysql3306/mysql.sock
[mysqld]
basedir =/usr/mysql5.7.23/mysqldatadir =/usr/mysql5.7.23/mysql/mysql3306/data
log-bin=/usr/mysql5.7.23/mysql/mysql3306/mysql-bin
socket =/usr/mysql5.7.23/mysql/mysql3306/mysql.sock
log-error =/usr/mysql5.7.23/mysql/mysql3306/error.log
pid-file =/usr/mysql5.7.23/mysql/mysql3306/mysqld.pid
max_allowed_packet=64M
character-set-server=utf8
lower_case_table_names=1
port=3306
server_id=20
default-storage-engine=INNODB
default-tmp-storage-engine=INNODBsql_mode='IGNORE_SPACE,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'binlog_format=row
binlog-ignore-db=mysql
log-slave-updates
slave_skip_errors=all
sync_binlog=1
log_bin_trust_function_creators=TRUE
replicate_ignore_db=information_schema
max_connections=100
slow_query_log=1
slow_query_log_file=/usr/mysql5.7.23/mysql/mysql3306/slow.log
long_query_time=0.2
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=5
innodb_log_file_size=1G
innodb_log_files_in_group=3
innodb_flush_log_at_trx_commit=2
lower_case_table_names=1
#mysql并行复制
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-workers=16
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay_log_recovery=ON
query_cache_size = 0
query_cache_type = 0
expire_logs_days = 7
interactive_timeout=864000
wait_timeout=864000
5. 初始化 ./mysqld --initialize --user=umysql --basedir=/usr/mysql5.7.23/mysql --datadir=/usr/mysql5.7.23/mysql/mysql3306/data 没有相关目录创建即可
6. 启动mysql /usr/mysql5.7.23/mysql/bin/mysqld --defaults-file=/usr/mysql5.7.23/mysql/conf/my3306.cnf--console --skip-grant-tables & 并跳过验证
7. 登陆mysql mysql -h localhost -P 3306 -S /usr/mysql5.7.23/mysql/mysql3306/mysql.sock -u root -p
8. 修改密码update mysql.user set authentication_string=password('root') where user='root'；update mysql.user set password_expired='N' where user='root';flush privileges；
9. 启动mysql ./mysqld_safe --defaults-file=/usr/mysql5.7.23/mysql/conf/my3306.cnf --user=umysql &
10. 配置复制，登陆3306端口 在主(165)服务器上执行：grant replication slave on *.* to 'replication'@'192.168.101.167' identified by 'replication'; 查看主的状态：show master status\G; 填写 11下的master_log_file 以及master_log_pos
11. 登陆3307端口 在备(167)服务器上执行change master to master_host='192.168.101.165',master_user='replication',master_port=3306,master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=458;
12. 登陆3307端口 在主(165)服务器上执行：grant replication slave on *.* to 'replication'@'192.168.101.167' identified by 'replication'; 查看主的状态：show master status\G; 填写 13下的master_log_file 以及master_log_pos
13. 登陆3306端口 在备(167)服务器上执行change master to master_host='192.168.101.165',master_user='replication',master_port=3307,master_password='replication',master_log_file='mysql-bin.XXXXX',master_log_pos=XXX;
10-13后形成的复制关系为：
14. 配置复制，登陆3306端口 在主(167)服务器上执行：grant replication slave on *.* to 'replication'@'192.168.101.165' identified by 'replication'; 查看主的状态：show master status\G; 填写 15下的master_log_file 以及master_log_pos
15. 登陆3307端口 在备(165)服务器上执行change master to master_host='192.168.101.167',master_user='replication',master_port=3306,master_password='replication',master_log_file='mysql-bin.000002',master_log_pos=458;
16. 登陆3307端口 在主(167)服务器上执行：grant replication slave on *.* to 'replication'@'192.168.101.165' identified by 'replication'; 查看主的状态：show master status\G; 填写 17下的master_log_file 以及master_log_pos
17. 登陆3306端口 在备(165)服务器上执行change master to master_host='192.168.101.167',master_user='replication',master_port=3307,master_password='replication',master_log_file='mysql-bin.XXXXX',master_log_pos=XXX;
14-17执行完成后形成最终关系
网友评论