This is the command to set everything:

   CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000673', MASTER_LOG_POS=4, MASTER_USER='replicator', 
   MASTER_PASSWORD='*******', MASTER_HOST='127.0.0.1', MASTER_PORT=4002;
   

where the numbers 000673 and 4 will need to be changed.

This deserves a page to itself.

To change in the current running server:

   SET GLOBAL log_output = 'FILE';
   SET GLOBAL general_log_file = '/var/log/mysql/general.log';
   SET GLOBAL general_log = 'ON';
   

This assumes that replication was previously working.

It is easy for replication to be upset slightly, resulting in an error on the slave that stops it operating. It then gets behind the master and manual intervention is needed.

Sometimes more substantial intervention is needed, but often bypassing the error is all that is needed. Before you start, check the situation by going into a MySQL client and entering:

  show slave status\G

Look at the error to see whether it is the sort of thing that can reasonably be ignored on a slave. If so, in a MySQL client enter the following statements:

  stop slave;
  set global sql_slave_skip_counter = 1;
  start slave;
  show slave status\G

Or, combine the first three on a single line:

  stop slave; set global sql_slave_skip_counter = 1; start slave;
  

It's best to pause before checking slave status, as failures may soon reoccur.

Check whether the slave is now running. Sometimes this has to be repeated – checking each time that the error can be ignored. Often this will get replication working again and the slave will catch up with the master.

This may be blocked by the MariaDB service. To fix correctly override the service file with a local copy:

  sudo cp /lib/systemd/system/mariadb.service /etc/systemd/system

Edit /etc/systemd/system/mariadb.service and change ProtectHome to be false (ProtectHome=false). Save the file, then:

  sudo systemctl daemon-reload # refresh the systemd services so it sees your override file
  sudo systemctl start mysql # and it should finally start

If the MySQL or MariaDB database is running on a different machine (whether real or virtual) from the application, a connection through a network is needed. Assuming the network cannot be assumed to be secure, some kind of encryption is needed.

Various alternatives exist, the main ones being and SSH tunnel, TLS connection, VPN. Of these, the SSH tunnel is the easiest to configure, and also the most efficient.

I used to set up SSH tunnels with autossh which provides a means of restarting SSH if it should fail. More recently, I found that it is possible in systems that support systemd to run the tunnel as a service. The service mechanism will handle the restarting without the need for extra software. The creation of tunnels can also be made quite elegant.

The solution I have now used is well described at this Github article.

Except that I had a big problem with one connection, and it seems the fix is to change “LOCAL_ADDR=localhost” to “LOCAL_ADDR=127.0.0.1”.

By default, this is not allowed and will cause errors. But there are situations where /home is a good place for the data. To fix this in Debian, create and edit this file:

   vim /etc/systemd/system/mariadb.service.d/override.conf
   

Set the contents to:

   [Service]
   ProtectHome=false

It should be possible to achieve this without knowing exactly where the file is by using:

   systemctl edit mariadb
   

but when I tried, it seemed not to work.

Remember to set the correct name for the database in this code and make sure output file does not exist:

   SET @MY_SCHEMA = "myremos_j41";
   SELECT DISTINCT
    CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA=@MY_SCHEMA
     AND TABLE_TYPE="BASE TABLE"
   INTO OUTFILE '/tmp/convert.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
   

Then run this command (changing database name as appropriate):

   mysql myremos_j41 < /tmp/convert.csv     
   

   SUBSTRING_INDEX(file_path, '/', -1) AS basename
   
   LEFT(file_path, LENGTH(file_path) - LENGTH(SUBSTRING_INDEX(file_path, '/', -1)) - 1) AS dirname
   

   CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';
   GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;