Mysql 主备配置

基础配置

主配置my.cnf (头部加"[mysqld]"):

log-bin=mysql-bin
server-id=1

从配置（加在mysqld下面 非mysqld_safe）：

server-id=2
log-bin=mysql-bin
replicate_wild_do_table=test.%
relay-log=mysqld-relay-bin
log-slave-updates=YES
read_only=1

Mysql 主备常见问题汇总

问题： [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf
解决办法： Please add [mysqld] at the top of my.cnf and then restart mysql server. and service mysql restart

问题： mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
解决办法：

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to …
mysql> start slave;

问题： 账号权限
配置如下：

CREATE USER ‘test’@’%’ IDENTIFIED BY ‘password’;
GRANT SELECT ,REPLICATION SLAVE,REPLICATION CLIENT ON . TO ‘test’@’%’ IDENTIFIED BY ‘password’;
GRANT FILE ON . TO ‘test’@’%’ IDENTIFIED BY ‘password’;
FLUSH PRI5、VILEGES;
FLUSH PRIVILEGES;