我有一台运行 Mysql 5.5.57(raspbian)的 Raspberry Pi(IP.28)和运行 MySql 5.7.19(IP.10)的 Ubuntu
我设置了主 -> 从复制,效果很好。然后我尝试更改为主 <-> 主设置,但是 Ubuntu 机器上根本没有发生任何数据库更改。
我正在尝试重新启动(删除 binlog 或更改文件夹路径),但是出现此错误;
mysql> flush master; ERROR 1186 (HY000): Binlog 关闭,无法 RESET MASTER
这是 rpi 上的 my.cnf 的一个片段;
server-id = 1
#bind-address = 192.168.0.10
#log_bin = /var/log/mysql/mysql-bin1.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = example
log_slave_updates = 1
如您所见,log_bin 已被注释掉。如果我取消注释,则无法成功重启 MySql 服务,我在 syslog 中收到以下错误;
Sep 11 16:08:25 raspberrypi003 /etc/init.d/mysql[9209]: #007/usr/bin/mysqladmin: connect to server at 'localhost' failed
Sep 11 16:08:25 raspberrypi003 /etc/init.d/mysql[9209]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Sep 11 16:08:25 raspberrypi003 /etc/init.d/mysql[9209]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Sep 11 16:08:25 raspberrypi003 /etc/init.d/mysql[9209]:
Sep 11 16:08:25 raspberrypi003 mysql[8373]: Starting MySQL database server: mysqld . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . failed!
Sep 11 16:08:25 raspberrypi003 systemd[1]: mysql.service: control process exited, code=exited status=1
Sep 11 16:08:25 raspberrypi003 systemd[1]: Failed to start LSB: Start and stop the mysql database server daemon.
Sep 11 16:08:25 raspberrypi003 systemd[1]: Unit mysql.service entered failed state.
重新注释该行之后,我能够重新进入 MySql,并且 syslog 中没有任何错误;
pi@raspberrypi003:/var/log $ tail syslog |grep mysql
Sep 11 16:15:02 raspberrypi003 systemd[1]: Started LSB: Start and stop the mysql database server daemon.
Sep 11 16:15:02 raspberrypi003 mysql[9322]: not closed cleanly..
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9903]: Upgrading MySQL tables if necessary.
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9908]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9908]: Looking for 'mysql' as: /usr/bin/mysql
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9908]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9908]: This installation of MySQL is already upgraded to 5.5.57, use --force if you still need to run mysql_upgrade
Sep 11 16:15:02 raspberrypi003 /etc/mysql/debian-start[9919]: Checking for insecure root accounts.
Sep 11 16:15:03 raspberrypi003 /etc/mysql/debian-start[9924]: Triggering myisam-recover for all MyISAM tables
我在网上找到的唯一信息是评论说运行sudo 服务 mysql 启动这绝对不是我的问题的答案。
答案1
我遇到的问题是遵循旧指南来设置 MySql 复制。
MySQL 5.5 中删除了以下选项。如果您尝试在 MySQL 5.5 中使用这些选项中的任何一个启动 mysqld,服务器将因未知变量错误而中止。我在尝试许多不同的配置时遇到了这个问题以及许多其他错误。最后我重新开始,仍然遇到同样的问题,直到我发现这些命令是导致问题的原因。
–master-host
–master-user
–master-password
–master-port
解决方法,注释掉master相关的变量。
在 Master 上执行以下操作:
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘‘; (Replace with a real password!)
mysql>FLUSH PRIVILEGES;
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
# get the DB dump.
mysql>UNLOCK TABLES;
在从属服务器上:
# import the DB dump
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_HOST=’prod_master’, MASTER_USER=’slave_user’, MASTER_PASSWORD=’‘, MASTER_LOG_FILE=’mysql-bin.0xx‘, MASTER_LOG_POS=33421;
mysql>start slave;
值得注意的是,您应该能够使用 mysql -h 选项连接到每个远程主机来测试凭据,在创建用户并授予权限后,不要忘记刷新权限命令。
/var/log/mysql/error.log 是你在这些反复试验情况下的好帮手。我建议在必要时重启 MySql 时跟踪日志。
注意,您将需要配置文件中的这些日志;
[mysqld]
log-error=/var/log/mysql/mysql.err
log-bin = /var/log/mysql/mysql-replication.log
一旦启动了从属设备;在每台主机上,您将通过以下方式看到一些数据;
SHOW MASTER STATUS;
SHOW BINARY LOGS;
如果你创建一个示例数据库然后创建表;
CREATE TABLE IF NOT EXISTS test (
task_id INT(11) NOT NULL AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (task_id)
)
您将会看到它出现在另一台主机上!