MySQL一主多从配置

机器环境

主服务器：10.38.100.01，已有数据库test1、test2且库中有表、函数以及存储过程。

从服务器：10.38.100.98，已安装数据库，除此之外未做任何配置。

从服务器：10.38.100.99，已安装数据库，除此之外未做任何配置。

准备工作
主从服务器需要有完全相同的初始状态：

主服务器配置加粗样式
1、 修改/etc/my.cnf配置文件，在my.cnf配置文件中[mysqld]下添加：
#主数据库端ID号

server_id=1

#开启二进制日志

log-bin=mysql-bin



#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可



binlog-do-db=test1

binlog-do-db=test2



#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中

log-slave-updates

#将函数复制到slave

log_bin_trust_function_creators=1

2、重启主服务器上的MySQL后，添加允许从服务器同步数据的账户
添加slave账号root，假设密码是123456

mysql> grant replication slave on *.* to 'root'@'10.38.100.98' identified by '123456';


mysql> grant replication slave on *.* to 'root'@'10.38.100.99' identified by '123456';

更新数据库权限

mysql> flush privileges;

3、 查看主服务器状态

mysql> show master status \G;


***************** 1. row ****************


File: mysql-bin.000001 #当前记录的日志


Position: 600 #日志中记录的位置


Binlog_Do_DB:


Binlog_Ignore_DB:

主服务器配置完毕，此刻开始不要再操作主服务器数据库，防止其状态值发生变化。

从服务器配置

1、修改/etc/my.cnf配置文件，在my.cnf配置文件[mysqld]中添加：

server_id=2


log-bin=mysql-bin


log-slave-updates


sync_binlog=0





#指定slave要复制哪个库，多个库就重复多遍


replicate-do-db=test1


replicate-do-db=test2





#MySQL主从复制的时候，Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据


slave-net-timeout=60


log_bin_trust_function_creators=1

2、 重启从服务器上MySQL后执行主从同步命令
执行同步命令，设置主服务器ip，同步账号密码，同步位置，两台机器分别执行

mysql> change master to master_host='10.38.100.1',master_user='root', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=600;

开启同步功能

mysql> start slave;

3、查看从服务器状态

mysql> show slave status\G;


*************************** 1. row ***************************


Slave_IO_State: Waiting for master to send event


Master_Host: 10.38.100.98


Master_User: root


Master_Port: 3306


Connect_Retry: 60


Master_Log_File: mysql-bin.000001


Read_Master_Log_Pos: 600


Relay_Log_File: db2-relay-bin.000002


Relay_Log_Pos: 700


Relay_Master_Log_File: mysql-bin.000033


Slave_IO_Running: Yes


Slave_SQL_Running: Yes


Replicate_Do_DB:


Replicate_Ignore_DB:


   ...


*************************** 1. row ***************************


Slave_IO_State: Waiting for master to send event


Master_Host: 10.38.100.99


Master_User: root


Master_Port: 3306


Connect_Retry: 60


Master_Log_File: mysql-bin.000001


Read_Master_Log_Pos: 600


Relay_Log_File: db2-relay-bin.000002


Relay_Log_Pos: 700


Relay_Master_Log_File: mysql-bin.000033


Slave_IO_Running: Yes


Slave_SQL_Running: Yes


Replicate_Do_DB:


Replicate_Ignore_DB:


   ...

Slave_IO_Running及Slave_SQL_Running进程必须是正常运行，即Yes状态，否则说明同步失败，若失败则需查看MySQL错误日志中具体报错详情来进行问题定位。
主从服务器配置完毕之后，在主服务器对应数据库上任意选择一条记录，进行修改，确认修改会自动同步到从服务器则说明配置完全成功。

遇到错误：
Got fatal error 1236 from master when reading data from binary log: ‘bogus data in log event; the first event ‘mysql-bin.000001’ at 600, the last event read from ‘./mysql-bin.000001’ at 123, the last byte read from ‘./mysql-bin.000001’ at 619.’

解决方案

解决办法（跳过报错binlog）：

slave stop;

change master to master_log_pos=0;

change master to master_log_file='mysql-bin.0000565';

slave start;