Tony 老师搭建 MySQL 主从服务器

来杯可乐不加糖的个人博客 / 0 / 1 / 创建于 5年前 / 更新于 5年前

搭建MySQL主从服务器

目的

如果mysql主服务器的访部量较大，可以通过复制数据，在从服务器进行查询操作，从而降低主服务器的访问压力

同时从服务器作为主服务器的备份，可以避免主服务器因为故障导致数据丢失的问题。

方式

基于日志点的复制

基于GTID的复制

主从复制：（基于日志点复制）

1、在主DB服务器上建立复制账号
* CREATE USER repl@'192.168.71.%' identified by 'password'; #创建，建议从服务器IP段
* GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';   #授权
2、配置主数据库服务器      # vim /etc/my.cnf
* bin_log = mysql-bin    #可指定二进制日志存放目录，目录需开启写入权限
* server_id = 100        #集群中唯一，如主机IP后几段
* #配置完重启
3、 配置从数据库服务器           #vim /etc/my.cnf
* bin_log = mysql-bin          #可指定二进制日志存放目录，目录需开启写入权限
* server_id = 101              #集群中唯一，如主机IP后几段
* relay_log = mysql-relay-bin  #默认主机名，需指定名字
* log_slave_updates = on       #是否开启relay_log写入从服务器bin_log中 集群时做主服务器时需要配置 [可选]
* read_only = on #安全配置参数，阻止没有任何没有sever权限的用户进行写操作，并且可以保证从服务器中由于程序错误导致的写操作不会发生 [可选]
* #配置完重启
4、同步数据
* flush tables with read lock; //锁表
* mysqldump --single-transaction --master-data=2 --triggers --routines cron -uroot -p >>log.sql //导出数据
* vim log.sql 在首行中加入：use cron;
* scp all.sql root@192.168.71.244:/root //将文件备份到从服务器root目录下【若未安装，则安装】
* mysql -uroot -p < log.sql //导入数据
* unlock tables // 备份完成后，解除全局读锁定
**5、启动复制链路**
* CHANGE MASTER TO MASTER_HOST = `master_host_ip`, MASTER_USER = `repl`,MASTER_PASSWORD=`123456`,MASTER_LOG_FILE=`mysql_log_file_name`,MASTER_LOG_POS= 4;
* #注：mysql_log_file_name,MASTER_LOG_POS 在log.sql中查看【也可查看master status:master_log_file，master_log_pos】
6、启动slave
* start slave
7、查看状态
* show slave status\G
8、常用操作：
- stop salve   #停止slave连路
- reset slave  #重置slave连路
- start slave  #开启slave连路
- stop master  #停止master连路
- reset master #重置master连路
- start master #开启master连路
9、常见问题汇总：
* 若需change 某参数
* 首先，stop slave，其次，change master to '参数' = '值'
* 设置防火墙（主从均需要允许3306端口）
* window：添加防火墙规则
* linux：（centos7）
    systemctl status firewalld //系统中应先判断firewalld 是否在运行
    firewall-cmd --list-all //查看所有端口
    firewall-cmd --permanent --zone=public --add-port=3306/tcp //放行3306
    firewall-cmd --reload //使规则生效
    若出现unit is masked 则 systemctl unmask firewalld
* Q：Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
* A：change master to master_auto_position=0;

主从复制：（基于GTID复制）

1、在主DB服务器上建立复制账号
* CREATE USER repl@'192.168.71.%' identified by 'password';   #创建，建议从服务器IP段
* GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';     #授权
2、配置主数据库服务器    #vim /etc/my.cnf
* bin_log = mysql-bin  #可指定二进制日志存放目录，目录需开启写入权限
* server_id = 100      #集群中唯一，如主机IP后几段
* gtid_mode = on  
* enforce_gtid_consistency = on
* log-slave-updates = on
* 配置完重启
3、 配置从数据库服务器            #vim /etc/my.cnf
* bin_log = mysql-bin           #可指定二进制日志存放目录，目录需开启写入权限
* server_id = 101               #集群中唯一，如主机IP后几段
* relay_log = mysql-relay-bin   #默认主机名，需指定名字
* log_slave_updates = on        #是否开启relay_log写入从服务器bin_log中 集群时做主服务器时需要配置 [可选]
* read_only = on                #安全配置参数，阻止没有任何没有sever权限的用户进行写操作，并且可以保证从服务器中由于程序错误导致的写操作不会发生 [可选]
* gtid_mode = on
* enforce_gtid_consistency = on
* master_info_repository = TABLE [建议] #默认存在文件中，主服务器与从服务器连接信息与中继日志信息的存储方式
* relay_log_info_repository = TABLE [建议]
* 配置完重启
4、同步数据
* 同上
5、启动复制链路
* CHANGE MASTER TO MASTER_HOST = `master_host_ip`, MASTER_USER = `repl`,MASTER_PASSWORD=`123456`,MASTER_AUTO_POSTITION = 1;
**6、启动slave**
* start slave
**7、查看状态**
* show slave status\G

主主复制：

1、配置第一台主DB
* server-id = 1
* log-bin=/var/lib/mysql/mysql-bin
* auto_increment_increment = 2
* auto_increment_offset = 1
* #配置完重启
2、配置另一台DB
* server-id = 3
* log-bin=/var/lib/mysql/mysql-bin
* auto_increment_increment = 2
* auto_increment_offset = 2
* 配置完重启
3、创建用户并授权
* 登录MySQL mysql -uroot -p
* 创建用户：CREATE USER repl@'192.168.71.%' identified by 'password';
* 授权:GRANT REPLICATION SLAVE ON *.* TO repl @'192.168.71.%';
* Q:若遇到 your password does not satisfy the current policy requirements
* A: set global validate_password_policy=0;
*    set global validate_password_length=1;
4、数据同步
* flush tables with read lock;  #锁表
* mysqldump --single-transaction --master-data = 2 --triggers --routines cron -uroot -p >> log.sql #导出数据
* vim log.sql 在首行中加入：use cron;
* scp all.sql root@192.168.71.244:/root  #将文件备份到从服务器root目录下【若未安装，则安装】
* mysql -uroot -p < log.sql  #导入数据
* unlock tables  #备份完成后，解除全局读锁定
5、建立关系（第二台关联第一台）
* 登录第一台MySQL
* show master status  #查看状态【可查看master_log_file，master_log_pos】
* 登录第二台MySQL
* change master to
* master_host='192.168.71.37', # 主节点
* master_port=3306, #主节点的端口号
* master_user='repl', # 账号
* master_password='repl', # 密码
* master_log_file='mysql-bin.000002', # show master status 对应的的日志
* master_log_pos=2072; # show master status 对应的
* start slave #启动库
6、建立关系（第一台关联第二台）
* 登录第二台MySQL
* show master status #查看状态【可查看master_log_file，master_log_pos】
* 登录第一台MySQL
* change master to
* master_host='192.168.71.244', # 主节点
* master_port=3306, #主节点的端口号
* master_user='repl', # 账号
* master_password='repl', # 密码
* master_log_file='mysql-bin.000001', # show master status 对应的的日志
* master_log_pos=702; # show master status 对应的
* start slave #启动库
* #日志复制：若master 重启，则日志点发生改变，需要重新配置slave日志点和位置,故笔者建议配置为GTID

mysql