mysql搭建主从的目的_Tony 老师搭建 MySQL 主从服务器

搭建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

本作品采用《CC 协议》，转载必须注明作者和本文链接

来杯可乐不加糖。