集群数据库：Mysql数据库一主多从部署的实现步骤 - 主机乐

[mysqld]
user=
port= 3307
basedir = /home/mysql1
datadir = /home/mysql1/data
socket= /home/mysql1/mysql.sock
pid_file= /home/mysql1/mysql.pid
log_error = /home/mysql1/data/error.log
#binlog日志文件
log_bin = /home/mysql1/data/mysql-bin
relay_log = /home/mysql1/data/relay-bin
slow_query_log_file = /home/mysql1/data/slow.log
#binlog过期清理时间
expire_logs_days= 15
log-slave-updates = 1
log_bin_trust_function_creators = 1
lower_case_table_names= 1
max_connections = 3000
max_connect_errors= 1000000
# 每个实例的id都设置成不一样的，比如a主机，3307 b主机设置成23307，c主机设置成33307，后面1主2从会用到
server-id = 3307
autocommit= 1
# pool_size根据实际情况进行更新
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances= 8
innodb_write_io_threads = 16
innodb_read_io_threads= 16
#mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED
binlog_format = ROW
gtid_mode = on
enforce_gtid_consistency= 1
innodb_flush_log_at_trx_commit= 1
#add 2
innodb_thread_concurrency = 20
innodb_print_all_deadlocks= 1
innodb_flush_method = O_DIRECT
innodb_io_capacity= 8000
innodb_io_capacity_max= 15000
enforce_gtid_consistency= 1
binlog_rows_query_log_events= 1
character_set_server= utf8mb4
default-storage-engine= INNODB
transaction_isolation = READ-COMMITTED
max_allowed_packet= 67108864
event_scheduler = 1
slow_query_log= on
explicit_defaults_for_timestamp = 1
master_info_repository= TABLE
relay_log_info_repository = TABLE
relay_log_recovery= 1
relay_log_purge = 0
slave_rows_search_algorithms= 'INDEX_SCAN,HASH_SCAN'
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers= 16
slave_preserve_commit_order = 1
slave_transaction_retries = 64
sync_relay_log= 0
sync_relay_log_info = 0
sync_master_info= 0
sync_binlog = 1
collation_server= utf8mb4_bin
skip_name_resolve = 1
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled= 1
rpl-semi-sync-slave-enabled = 1

8.数据文件初始化

1.初始化

./mysqld --defaults-file=/home/mysql1/my.cnf --initialize --basedir=/home/mysql1 --datadir=/home/mysql1/data --user=mysql1

2.启动

./mysqld_safe --defaults-file=/home/mysql1/my.cnf --user=mysql1 &

查看mysql进程已经起来，并且监听3307接口

3.本机登陆，root的初始密码可以在errorlog中找到

cat error.log | grep "temporary password"

4.本机登录指定sock文件以及指定端口及输入临时密码

./mysql -uroot -P3307 -S /home/mysql1/mysql.sock -p

5. 登录后修改root密码

set password=password('XXXXXXXX'); 修改密码
flush privileges;

6. 赋予权限

grant all privileges on *.* to 'root'@'%' identified by 'XXXXXXX' with grant option;
flush privileges;

7. 停止服务

./mysqladmin shutdown -uroot -p******** -S /home/mysql1/mysql.sock

4.使用mysql客户端datagrip连接mysql成功

5.通过如上相同操作安装从库，只修改配置文件相关信息，并且初始化，并且客户端连接

6.在master数据库上执行

1.创建repl用户并且赋予同步权限

CREATE USER 'repl' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'slavepass';
flush privileges;

2.查看master状态

SHOW MASTER STATUS\G;

7.在slave上面执行

1.配置slave从节点的master节点以及binlog偏移位置等

CHANGE MASTER TO MASTER_HOST='master地址',
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000003', #为master的log_file
MASTER_LOG_POS=1597, #master的POS
MASTER_PORT=3307;