I have a raspberry pi (IP.28) running Mysql 5.5.57 (raspbian) and Ubuntu with MySql 5.7.19 (IP.10)
I setup replication for Master -> Slave, this worked fine. I then tried changing to a Master <-> Master setup, however no DB changes were coming across at all to the Ubuntu machine.
I'm trying to start again (removing binlogs or changing folder path), however I get this error;
mysql> flush master; ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER
This is a snippet from my.cnf on the rpi;
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 As you can see, the log_bin is commented out. If I uncomment it, I am unable to restart MySql service successfully, I get the following error in 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. After recommenting the line out, I am able to get back into MySql with no errors in 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 The only info I have found online is comments saying run sudo service mysql start This is definitely not the answer to my problem.
1 Answer
The problem I had was following older guides for the setup of MySql replication.
The following options are removed in MySQL 5.5. If you attempt to start mysqld with any of these options in MySQL 5.5, the server aborts with an unknown variable error. I experienced this, amonst numerous other errors as I attempted many differing configurations. In the end I started fresh and still had the same problem until I found out that these commands are what's causing the problem.
–master-host –master-user –master-password –master-port Solution, comment the master- related variables.
Do following, On 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; On Slave:
# 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; It's worth noting you should be able to connect to each remote host using mysql -h option to test credentials, after creating users and granting privileges, dont forget the flush privileges command.
/var/log/mysql/error.log is your friend during these trial and error scenarios. I suggest tailing the log whilst restarting MySql as required.
note, you will need these logs in the config file;
[mysqld] log-error=/var/log/mysql/mysql.err log-bin = /var/log/mysql/mysql-replication.log once you have start slave; on each host, you will see some data via;
SHOW MASTER STATUS; SHOW BINARY LOGS; if you create an example database and then table;
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) ) you will see it pop up on the other host!
