服务器配置MySQL主从复制搭建,一主多从
一、前期准备
1、目标效果效果
目标:搭建三台MySQL服务器,一台作为主服务器,两台作为从服务器,实现主从复制
二、配置步骤
1、环境
- 主数据库: 192.168.137.130 账号master密码123456
- 从数据库: 192.168.137.131 账号master密码123456
- 从数据库: 192.168.137.132 账号master密码123456
2、数据与自定义账号
- 保证两个数据库中的库和数据是一致的;
- 在主从数据中创建一个同步账号(可不创建使用现有的),如果仅仅为了主从复制创建账号,只需要授予REPLICATION SLAVE权限。
创建一个账号,账号:master,密码: 123456
CREATE USER 'master'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE ON *.* TO 'master'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
3、配置主数据库
- 要主数据库,你必须要启用二进制日志(binary logging),并且创建一个唯一的Server ID,这步骤可能要重启MySQL。
- 主服务器发送变更记录到从服务器依赖的是二进制日志,如果没启用二进制日志,复制操作不能实现(主库复制到从库)。
- 复制组中的每台服务器都要配置唯一的Server ID,取值范围是1到(232)−1,你自己决定取值。
- 配置二进制日志和Server ID,你需要关闭MySQL和编辑my.cnf或者my.ini文件,在 [mysqld] 节点下添加配置。
- 下面是启用二进制日志,日志文件名以“master-bin”作为前缀,Server ID配置为1,如下:
[mysql]
#设置sql客户端默认字符集
default-character-set=utf8
[mysqld]
#skip_grant_tables 这个先不要,这是忽略权限。
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/home/installed/mysql
# 设置mysql数据库的数据的存放目录
datadir=/home/installed/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=500M
#主从复制配置
#创建一个唯一的Server ID
server-id=1
#也可指定二进制日志生成的路径,如:log-bin=/opt/Data/mysql-bin
log-bin=master-bin
#二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件
log_bin_index =master-bin.index
#mytest是要同步的数据库的名称(此参数表示只记录指定数据库的二进制日志,默认全部记录。)
binlog_do_db=mytest
#此参数表示不记录指定的数据库的二进制日志。
binlog_ignore_db=mysql
#就是以哪个操作系统用户来执行mysqld进程
user=mysql
查看服务状态
show master status;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 | 6609 | mytest | mysql | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
注意:记录好File和Position,后面要用
4、配置从数据库
从服务器,同理,要分配一个唯一的Server ID,需要关闭MySQL,修改好my.cnf后再重启,如下:
[mysql]
#设置sql客户端默认字符集
default-character-set=utf8
[mysqld]
#skip_grant_tables 这个先不要,这是忽略权限。
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/home/installed/mysql
# 设置mysql数据库的数据的存放目录
datadir=/home/installed/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=500M
#从服务器配置
#从服务器,同理,要分配一个唯一的Server ID,另一个从只需要修改这一个参数
server-id=2
log-bin=slave-bin
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
在从服务器里配置连接主服务器的信息:
进入mysql:
stop slave;
change master to master_host='192.168.137.130', master_port=3306, master_user='master', master_password='123456', master_log_file='master-bin.000008', master_log_pos=6609 ;
#防止
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
查看状态
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.137.130
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 6609
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5607
Relay_Log_Space: 1219
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 6d8c65b2-589e-11ec-83ca-5254001d996a
Master_Info_File: /home/installed/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
远程连接需要授权
GRANT ALL PRIVILEGES ON *.* TO 'master'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
三、测试
1、主数据库测试样例
然后刷新主服务器,就会发现多了一个叫mytest的数据库,从服务器里也实现了同步,也有了 一个叫mytest的数据库,在主服务器里进行表及数据的增删改,从数据库都会实现同步。
#创建库
CREATE DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
#删除库
DROP DATABASE mytest;
#创建表
CREATE TABLE tb_emp1 (
id INT ( 11 )
);
#删除表
drop table tb_emp1;