您现在的位置是：网站首页> 编程资料编程资料
Mysql一主多从部署的实现步骤_Mysql_
2023-05-26
373人已围观
简介 Mysql一主多从部署的实现步骤_Mysql_
1.下载地址
https://dev.mysql.com/downloads/mysql/
2.下载tar.gz包
选择redhat版本
3.安装
1.linux系统上创建mysql1用户
useradd mysql1
2.将tar.gz包上传到服务器上并且解压
tar -zxvf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
3.将mysql-5.7.31-el7-x86_64目录下的文件mv到 /home/mysql1下
4.mkdir -p /home/mysql1/data 文件存储mysql1数据目录
mkdir -p /home/mysql1/data
5.将/etc/my.cfg文件复制到mysql家目录下
cp /etc/my.cnf /home/mysql1/
6.对/home/mysql1下赋权
chmod -R 755 /home/mysq1l chown mysql:mysql -R /home/mysql1/data chmod 777 /home/mysql1/data
7.修改mysql配置文件
vi /home/mysql1/my.cnf
[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;
2.启动slave同步进程以及查看slave状态
start slave; #启动slave同步进程 show slave status\G;
8.测试
在mysql1中创建testdb，使用datagrip在mysql2中即可查看从mysql1中同步的新增的库以及数据，不过datagrip需要刷新下客户端。
9.总结主从部署
注意主从mysql的my.cnf配置文件的server-id必须配置不一样
到此这篇关于Mysql一主多从部署的实现步骤的文章就介绍到这了,更多相关Mysql一主多从内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！
您可能感兴趣的文章:
