Mysql Replication problems

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!